Access Query directly into Recordset (VBA)?

[Idlemind23]

Ahoy! I'm hoping for some help on what (I believe) should be a simple task.

I have a form with a button. I would like that button to have a caption with a date. That date should be pulled out of a table when the form loads. (e.g. the button would read "Last Audit was 2006/12/21" - that date is on my "Audit" table).

I created an actual query object to return the largest date on the Audit table. I'm trying to find a way in VBA to open read the 1 record, 1 field from that query when the form loads. I thought I could just load it into a recordset and use the button.caption to set it - but I can't even figure out how to open an Access query (in Access) without displaying the query some way. I just want to get the value from that 1 field, then set the button's caption.

(I used a query object instead of a VBA-hard-coded SQL statement just in case I want to change the query without digging through my VBA code)

I used to be good with Access, years ago before falling in love with MySQL and MS SQL Server. Now - I need help. Much Thanks.

[jpatchak]

Ahoy! I'm hoping for some help on what (I believe) should be a simple task.

I have a form with a button. I would like that button to have a caption with a date. That date should be pulled out of a table when the form loads. (e.g. the button would read "Last Audit was 2006/12/21" - that date is on my "Audit" table).

I created an actual query object to return the largest date on the Audit table. I'm trying to find a way in VBA to open read the 1 record, 1 field from that query when the form loads. I thought I could just load it into a recordset and use the button.caption to set it - but I can't even figure out how to open an Access query (in Access) without displaying the query some way. I just want to get the value from that 1 field, then set the button's caption.

(I used a query object instead of a VBA-hard-coded SQL statement just in case I want to change the query without digging through my VBA code)

I used to be good with Access, years ago before falling in love with MySQL and MS SQL Server. Now - I need help. Much Thanks.

Looks like you are looking for the dlookup function, something like:

cmdButton.caption = DLookup("[Date], "QueryName")

You'll have to change the names here, obviously. That should be sufficient if your query only has one record. Just put it in your Form_Load event

[NeoPa]

Try :

cmdButton.Caption = DMax("[Date]","Audit")

[MMcCarthy]

Try :

cmdButton.Caption = DMax("[Date]","Audit")

You won't need your query with the above solution either it will do it all in one go.

cmdButton.Caption =  "Last Audit was " & DMax("[Date]","Audit")
 

Mary