By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Access Query directly into Recordset (VBA)?

P: 1
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.
Dec 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: 76
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 28 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
Try :
Expand|Select|Wrap|Line Numbers
  1. cmdButton.Caption = DMax("[Date]","Audit")
Dec 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try :
Expand|Select|Wrap|Line Numbers
  1. cmdButton.Caption = DMax("[Date]","Audit")
You won't need your query with the above solution either it will do it all in one go.

Expand|Select|Wrap|Line Numbers
  1. cmdButton.Caption =  "Last Audit was " & DMax("[Date]","Audit")
  2.  
Mary
Jan 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.