VB Help

[Mark121]

Hi all

I am having trouble with this piece of Visual basic behind one of the forms.

Originally the databse was written in access 2000 and worked fine. I am now using 2003 but it seems to break at this sub-routine.

The part it highlights is .LastSeqNumber (line 15).

Can anyone see anything wrong with the VB and advise me how i can fix the problem?
Forgot to mention the error i recieve is "Compile Error: Method or Data member not found"

Many thanks

Mark


Private Sub GenerateCodeNumbers(TheOrganisation As String)
Dim MyDB As Database, MySet As Recordset, MyTable As Recordset, MyQuery As QueryDef
Dim x, i, SQLQuery As String, SeqNumbString As String, RandString, CodeNumberString As String
Dim SeqNumber As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
'MsgBox TheOrganisation

SQLQuery = "Select LastSeqNumber from OrganisationTbl where Organisation =""" & TheOrganisation & """;"


Set MySet = MyDB.OpenRecordset(SQLQuery)
SeqNumber = MySet.LastSeqNumber
On Error Resume Next
MyDB.Execute ("drop table CodeTbl")
MyDB.Execute ("create table CodeTbl (CodeNumber Text)")
On Error GoTo 0
Set MyTable = MyDB.OpenRecordset("CodeTbl", DB_OPEN_TABLE) ' Create table reference.

For i = 0 To 24
Randomize
x = Int(9999 * Rnd)
RandString = Trim(Str$(x))

If Len(RandString) < 4 Then
Do
RandString = "0" & RandString
'MsgBox RandString
Loop Until Len(RandString) = 4
End If
'Debug.Print RandString

SeqNumbString = Trim(Str$(SeqNumber))
If Len(SeqNumbString) < 4 Then
Do
SeqNumbString = "0" & SeqNumbString
'MsgBox SeqNumbString
Loop Until Len(SeqNumbString) = 4
End If
'Debug.Print SeqNumbString
CodeNumberString = SeqNumbString & RandString
Debug.Print CodeNumberString

MyTable.AddNew
MyTable("CodeNumber") = CodeNumberString
MyTable.Update

SeqNumber = SeqNumber + 1

Next i
MyTable.Close

Rem now print The Report
DoCmd.OpenReport "TestCodeRpt"
DoCmd.OpenReport "TestCodeRpt"
Rem Now put SeqNumber into LastSeqNumber in OrganisationTbl
Dim Criteria As String
Criteria = "Organisation = """ & TheOrganisation & """"
'MsgBox Criteria
Set MySet = MyDB.OpenRecordset("OrganisationTbl", DB_OPEN_DYNASET)
MySet.FindFirst Criteria
Do Until MySet.NoMatch
MySet.Edit
MySet!LastSeqNumber = SeqNumber
MySet.Update
MySet.FindNext Criteria
Loop

On Error Resume Next
MyDB.Execute ("drop table CodeTbl")
On Error GoTo 0

End Sub

 

[tehNellie]

try

MySet![LastSeqNumber]

Instead.

 

[Mark121]

Tried that that gives me an different error "Compile Error: Invalid use of Property"

highlights SeqNumber =

Thanks

 

[boblarson]

Use MySet!LastSeqNumber instead, but you also cannot set a recordset equal to a field. So, you need something else from SeqNumber as it is declared as a recordset.

So what FIELD from SeqNumber do you want:

SequNumber!YourFieldName = MySet!LastSeqNumber

 

[Mark121]

Thanks for your help guys.

Ive changed the SeqNumber variable from a recordset to a long.

And all works well.

Mark

 

[boblarson]

Glad to hear