VB in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I am trying to create a record set using VB within Access (code below). I
get an error at the first Execute Line. I'm sure I will get an error with
the second one also, but I haven't been able to pass the first error.

Private Sub Command41_Click()

'extract month and year from Fleet_DD and put into a table
strsql = "Select * from ERIP_SERNOtbl order by Dash_16_SERNO"
Set rsFleetDate = Execute (strsql)

rsFleetDate.MoveFirst

While Not rsFleetDate.EOF

strFleetDate = rsFleetDate!Fleet_DD
YR = DatePart("yyyy", strFleetDate)
Mo = DatePart("mm", strFleetDate)
intID = rsFleetDate!Dash_16_SERNO

strsql = "Insert into TempFleet values(" & intID & ", '" & YR & "', '" & Mo
& "')"
Execute (strsql)

rsFleetDate.MoveNext
Wend
End Sub

I tried to hard code the database, to put db infront of execute to use
db.execute. The problem is it doesn't recognize the word Database in "Dim db
as Database"

Does anyone see what I'm doing wrong, or what I need to do instead?

Thanks in Advance!
 
Hi,
It sounds like that you are not referencing a library or 2. Go to VB Editor
and look under "Tools" and "References". See what is being checked.
 
Hi,

These three things are checked:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation

Would you happen to know what else I need to check?

Thank you so much!
Elena
 
Two things:

You need to also check the DAO library.

And Execute is a method of CurrentDb, which is a member of the DAO.Database
collection.

CurrentDb.Execute(strsql)
 
In addition, if you are trying to get records into a recordset you should be using

Set strFleetDate = CurrentDb.OpenRecordset(StrSQL)
 
Thanks, John... I 'overlooked' the first use of Execute for some odd
reason...
 
Private Sub Command41_Click()
'extract month and year from Fleet_DD and put into a table
strsql = "Select * from ERIP_SERNOtbl order by Dash_16_SERNO"
Click to expand...
'Set rsFleetDate = Execute (strsql)

dim rsFleetDate as DAO.recordset
Set rsFleetDate = CurrentDB.OpenRecordset(strsql)


(david)
 
Thank you, everyone! It finally works!

Elena


david epsom dot com dot au said:
'Set rsFleetDate = Execute (strsql)

dim rsFleetDate as DAO.recordset
Set rsFleetDate = CurrentDB.OpenRecordset(strsql)


(david)
Click to expand...
 
Back
Top