Assign a query result to a field...

[Zandor435]

Hi all,


I am still having trouble refreshing a form.

I have a table customer w/ name, id to keep it simple.
I want to be able to choose an id from a combo box on a form and then either the name is updated w/ an after update macro or w/ a command button.

Someone earlier mentioned using the dlookup command. I have had some trouble w/ this. I am not sure how to use it exactly and it isn't in the help.

Thanks for any help.

Zach

 

[Isadore]

Zan: If your combo is "bound" to the Name field an update is automatic - but I suppose the update occurs in another table (check too to see if you can bring in the field from the other table via a link to that table in the underlying query). If none of that works you can try, e.g.,

Combo box, show Names, but hide ID. Call it cboNames.
On the afterupdate (or button event) you can update immediately. You can use the following (requires DAO dll in library - make sure you have that set up - go to the code behind the form, choose Tools, and make sure you have DAO selected). Try this on the after update:

'now first save the new site in table Sitios...
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("tblCustomer&quot
rst.Edit
rst![ID] = Me![ID]
rst.Update
rst.Close
db.Close

...that's it. Not sure you need to use a DLookUp unless you're not sure the name is there in the first place, but if you do, use a Variant in case a NULL is there (or alternatively use DCount and check if you bring back anything).

DLookUp are straight forward, q.v.,
x=DLookUp("[fieldIwant]", "tblIwant", "[fieldtomatch]=Forms!myForm!mymatchingfield&quot

..basically an SQL statement.

 

[Zandor435]

I have been looking through help, and i have come across the update query.

I think this may be what i am looking for, but i am not sure how to use it. Is this what i use in order to set a field (customer name) = the result of a query w/ the input of the customer id. I have the customer id on a form and i want to take it and use it in a query to find the name associated w/ that id and set the customer name on the form equal to that name that the query finds. I will keep playing with it.
If i am way off or there is a simpler way to do this, any suggestions are appreciated.

Thanks.

Zach

 

[telephoto]

Within Access 97 (at least) when you add a combo box on a form using the wizard, the option of having the form display records referring to the value selected in the combobox is offered.

This is what you ar elooking for?

Telephoto

 

[Zandor435]

Thanks for your help i got it.
zach