Invalid use of New Keyword

[jeanhurtado]

Hi, I'm Jean C. and try to finish my project. I have a compile error
that it says: "Invalid use of New Keyword". The error tell me that
the command "New Recordset" is the problem but don't know why. I have
use run without problems but when run to a final test the error comes
up. Can you help me? Here is the code.

*********************************************************************************************************************
Private Sub cmdOk_Click()
Dim rstTickets As ADODB.Recordset
Dim strSql As String
Dim nTickets As Integer

nTickets = Val(txtticketqty.Value)

Set conDatabase = CurrentProject.Connection
strSql = "SELECT TICKETDATE FROM CycleCount WHERE TICKETDATE Is
Null"

Set rstTickets = New Recordset
rstTickets.Open strSql, conDatabase, adOpenDynamic,
adLockOptimistic

With rstTickets
For x = 1 To nTickets
!TICKETDATE = txtTicketDate
.Update
.MoveNext
Next
End With

rstTickets.Close
conDatabase.Close
Set rstTickets = Nothing
Set conDatabase = Nothing

'Generate Ticket Number code

Dim rst1Tickets As ADODB.Recordset
Dim rst2Tickets As ADODB.Recordset
Dim str1SQL As String
Dim str2SQL As String
Dim lastrec As String
Dim nTicketnumber As Integer
Dim lastticket As String


nTicketnumber = 100


'GET THE LAST TICKET NUMBER FROM THE CYCKETICKETNUMBER TABLE TO BE
USE AS STARTING POINT
'TO GET THE RECORDS.

Set conDatabase = CurrentProject.Connection
str1SQL = "SELECT [Ticket Number] FROM CycleCount WHERE [Ticket
Number] Is Null"

Set conDatabase = CurrentProject.Connection
str2SQL = "SELECT TICKETNUMBER FROM CYCLETICKETNUMBER WHERE
TICKETNUMBER Is NOT Null"

Set rst1Tickets = New Recordset
rst1Tickets.Open str1SQL, conDatabase, adOpenDynamic,
adLockOptimistic

Set rst2Tickets = New Recordset
rst2Tickets.Open str2SQL, conDatabase, adOpenDynamic,
adLockOptimistic

lastrec = DMax("TICKETNUMBER", "CYCLETICKETNUMBER")

With rst1Tickets
For x = 1 To nTicketnumber
![Ticket Number] = (lastrec + x)
.Update
.MoveNext
Next
End With

lastticket = DMax("[Ticket Number]", "CycleCount")

With rst2Tickets
For x = 0 To 1
!TicketNumber = lastticket
.Update
Next
End With

rst1Tickets.Close
rst2Tickets.Close
conDatabase.Close
Set rst1Tickets = Nothing
Set rst2Tickets = Nothing
Set conDatabase = Nothing
Set conDatabase = Nothing


Sleep 500

DoCmd.OpenForm "TICKETPRINTINGSYSTEM", acNormal


End Sub
***************************************************************************************************************************

Thanks so much for your help.


JC

 

[Stefan Hoffmann]

hi Jean,

Dim rstTickets As ADODB.Recordset
Set rstTickets = New Recordset

Use an explict

Set rstTickets = New ADODB.Recordset

I think it is reference problem. If the library (namespace) is not
given, the order of the references decides which object you get.


mfG
--> stefan <--