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

Select value from query when cbo item selected

P: 3
Hello

I have a table (MainTable) with the fields
(PK)ID
(cbo)from_dept
document_date
reference_number
last_reference

I have a query on the first 4 fields that is sorted to give the last reference_number for each from_dept This is the value I need to be automatically displayed in last_reference when the from_dept is selected from it's cbo The idea being that when I enter a new record I select from_dept and the last_reference for that from_dept pops up automatically and I add the next sequential value into reference_number
ad nauseaum

I have attached the file if someon can have a look and for me - I am now wondering after trying lookups, queries and failing to get codes written properly if I need another table into which the reference_number is entered rather than finding the data from the MainTable ???

Does anyone have any ideas? If you look at query_reference the value I need to be seen in last_reference is LastOfreference_number.

I just can't get this last bit sorted out.
Attached Files
File Type: zip Mail Log Database Original.zip (331.7 KB, 116 views)
Aug 12 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
If from_dept is not unique in your table, you'll want to create a query that groups by from_dept and selects from_dept and the max of last_reference. With that information, you can write an After_Update event for the from_dept combo box which will look up the value and put it in the last_reference text box. Something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbFromDept_AfterUpdate()
  2.     txtLastReference = DLookup("last_reference", "query1", "from_dept = " & cmbFromDept)
  3. End Sub
Aug 12 '09 #2

P: 53
indeed, _AfterUpdate is the way to go but I have a question..

In your main table, is "last reference" referring to a record with a given "from_dept" and "reference" where the "document_date" is the most recent? Or is it something else?

And sorry, I can't open your file, I only have Access 2003.
Aug 12 '09 #3

P: 3
Hello (sorry for the late reply. I am in Saudi Arabia and Thurs/Fri is the weekend) Thanks for the responses.

Further information - my query is sorted like this: maintable.from_dept (group ascending), maintable.document_date (last), maintable.ID (max), maintable.reference_number (last)

so I can select the field 'last_reference' value from the field LastOfreference_number value, based on the unique ID for each record. However (not sure here if it is an access 2007 thing) but I can't get AfterUpdate to work.

I have managed to get a subform based on the same query to work perfectly (in that I can select a from_dept and the last reference number used pops up in the subform, I can then enter the next reference number for the new record) but it's a bit messy - data table on the form.

ezechiel - I tried to save it in 2003 for you but I can't "dumb it down" in 2007 or even save it in compatible format without losing current programming. Sorry.

ChipR - I tried your solution but no dice.

I'd prefer to have a textbox using an AfterUpdate option that works as well as the subform, but nothing seems to work. Thoughts??
Aug 15 '09 #4

P: 3
Hi I solved this one using a subform (AfterUpdate wouldn't work on my computer) Thanks to ChipR and ezechiel for your help :-) Much appreciated. Now I have Access2007 version sorted out I need to do the same in Access2003 (I may be back for help on this later :-) )

Thanks again Ellie
Aug 16 '09 #5

P: 53
you're welcome, but ChipR is the one who gave you the solution :)
He's a Jedi, I'm still a padawan..
Aug 19 '09 #6

Post your reply

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