Type Mismatch

[atomicpetrie]

Hi everyone, i have only jsut started VBA, i have the following code, simply trying to get data to be displayed in a listbox.

Private Sub Form_Load()
Set db = OpenDatabase("C:\data.mdb")
Set rs = db.OpenRecordset("SELECT * FROM Customer")

With rs
Do

ListCust.AddItem !FName
.MoveNext
Loop Until .EOF
End With

End Sub

I have added module, and referenced the Microsoft DAO, but i get "Type Mismatch" on the line "Set rs = db.OpenRecordset("SELECT * FROM Customer")"

If anyone could get back to me asap, it would be much appreciated.

Thanks,

Tom

 

[nateobot]

Do you have db and rs declared somewhere else in your code?

Dim db as DAO.Database
Dim rs as DAO.Recordset

 

[atomicpetrie]

YOU GOD!!

I added that to the formload, and now the details of the Name field are displayed in the listbox.

I cant thank you enough!

Thank you so much.

I'm jsut gonna try to get the information to display in a text box once sumthing is selected in the listbox.

Thanks again!!

 

[atomicpetrie]

Hi again, this is my code now:

Option Compare Database

Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = OpenDatabase("C:\data.mdb")
Set rs = db.OpenRecordset("SELECT * FROM Customer")

ListCust.RowSource = ""

With rs
Do

ListCust.AddItem !FName & ";" & ![customer no]
.MoveNext
Loop Until .EOF
End With

End Sub


Private Sub ListCust_Click()
Set rs = db.OpenRecordset("SELECT * FROM Customer WHERE [customer no]=" & ListCust.Column(1))

With rs

Text2 = .Fields("[customer no]")
Text4 = .Fields("FName")

End With
End Sub

I have 2 columns in my listbox, the second one is the primary key from the database (autonumber), and when clicking a detail from the listbox, i get the following error.

"Run-time error '91': Object variable or With block variable not set"

and it errors on the line:

Set rs = db.OpenRecordset("SELECT * FROM Customer WHERE [customer no]=" & ListCust.Column(1))

Once again, if you can get back to me asap, it will be much appreciated,

Thank you,

Tom

 

[Bat17]

You error comes from failing to declare your variables.
If you add "Option Explicit" to the top of each module then Access will check that you have declared your variables. this is a great way of spotting spelling errors things like "filed1" instead of "field1" and also as in this case you have not set up the DB variable.
change
Set rs = db.OpenRecordset("SELECT * FROM Customer WHERE [customer no]=" & ListCust.Column(1))
to
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customer WHERE [customer no]=" & ListCust.Column(1))

If, in a module, you go to Toos>options and select the Editor tab you can select "Require Variable Declearations" which will add "Option Explicit" each time you create a module.

HTH

Peter

 

[atomicpetrie]

I sorted it mate,

I had to set:

Dim db As DAO.Database
Dim rs As DAO.Recordset

as global in my module,

Thanks for your help, (everyone who replied!)

Cheers,

Tom