Select value from query when cbo item selected

[ellie]

Hello

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

I have a query on the first 4 fields that is sorted to give the last reference_numbe r 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_numbe r
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_numbe r 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.

[ChipR]

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:

Private Sub cmbFromDept_AfterUpdate()
    txtLastReference = DLookup("last_reference", "query1", "from_dept = " & cmbFromDept)
End Sub

[ezechiel]

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_d ate" is the most recent? Or is it something else?

And sorry, I can't open your file, I only have Access 2003.

[ellie]

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.docum ent_date (last), maintable.ID (max), maintable.refer ence_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??

[ellie]

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

[ezechiel]

you're welcome, but ChipR is the one who gave you the solution :)
He's a Jedi, I'm still a padawan..