Populate control with query result

Posted on 2013-10-01
Medium Priority
Last Modified: 2013-10-01
Hello, I have a textbox on a form that I would like to populate with the results of a query. I would like this to happen when a form opens.

If it helps this is my query

SELECT Max([DMR Tracking].[DMR#]+1) AS myDMR
FROM [DMR Tracking];

I'm new to access and have a form to create a new record. This form when loaded does not know the last defalutId of the previous record. I thought that I can get the last record and get the next value to populate the textbox.

Any help is appreciated!
Question by:gogetsome
  • 3
  • 2
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 39537656
In VBA, in the current event of the form:

Private Sub Form_Current()

If me.newrecord = true then
Me.TextBoxName = DLookup("myDMR", "MyQueryName")
end if

end sub

Open in new window

(You'll have to replace Textboxname and MyQueryName with the real names as used in your database)

Author Comment

ID: 39537740
Thanks for helping!

I'm getting a compile error: Method or data member not found

is my code which is in the form load event

Private Sub Form_Load()

Dim stDocName As String

    DoCmd.Echo False
    DoCmd.GoToRecord , , acNewRec
    If Me.NewRecord = True Then
Me.DMR# = DLookup("myDMR", "GetNextDMRID")
End If

    DoCmd.GoToControl "DMR#"
    DoCmd.Echo True

End Sub
LVL 61

Expert Comment

ID: 39537760
Try renaming your control to not include the #.

txtDMRNumber is a safe and standard convention for a text box.

Author Comment

ID: 39537809
Changing the control name broke some macros and such> I will get in touch once I fix those errors. Thanks for your Help. I will return.

Author Closing Comment

ID: 39537864
Thank you for your assistance. I was able to overcome my issues. WOW I inherited a mess :-) One more bug off the list.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question