Accessing a Query

[RevJeff]

Hi,

How can I access a query in a module. To access a form I use:

lastname = Forms(x)![lastname]

Is it possible to do the same thing for a query? I tried:

lastname = Query(x)![lastname]

But it didn't work. Any Ideas?

Thanks

 

[Mile-O]

It's a bit more complex than that.

A form typically only shows one record at a time whereas a query can have thousands of entries under the Last Name field.

If you are using Access '97 this code, with a bit of customisation to your database will work

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("YourQueryNameHere")

strLastName = rs.Fields("Last Name")

rs.Close

If you are using Access 2000 or 2002 then you might have to set your DAO references (or use ADO) - you can search for them on this site.

 

[RevJeff]

I used the code placed below but now I'm getting an error message saying " Run-Time Error '13': Type Mismatch "

Any Ideas what the problem is?

Thanks

 

[WayneRyan]

Hi Rev,

The type mismatch is caused by referencing fields without the
appropriate punctuation. SQL wants:

VBA code:

strSQL = "Insert into MyTable (Adate, Astring, Anumber) " & _
"Values (#" & Me.Mydate & "#, '" & Me.Mystring & "', " & _
Me.Mynumber & ");"

SQL's Translation:

Insert into MyTable (Adate, Astring, Anumber)
Values 02/03/03#, 'abcde', 4745);

You'll get used to the syntax.

Wayne