Text box value from a query (97/2K)

Topic

New Reply

I have a form (bound to a table) which consists of 4 text boxes. By typing a number into the CustomerID box, the relevant customer details appears in the subform. The subform contains a text box showing, among others, the Area in which the customer lives. On the main form is another text box which I want to display the Driver ID corresponding to the value in the subform Area text box. This can be obtained from a driver table which stores Driver ID and Area.
I have constructed a query which displays the correct Driver ID.
How do I get the text box(Driver ID on the main form) to display its result?
Also, once displayed, can this be stored in the bound table?

Many thanks

Rob

Reply | Quote

Replies

>>How do I get the text box(Driver ID on the main form) to display its result?
If you have a query that will find the right Driver ID, then put this value into the text box using a dlookup.
In the after update event for the text box where you type the customerID put something like:

me!driverID = dlookup(“[Driverid]”,”qryfindDriverID”)

>>also, once displayed, can this be stored in the bound table?
Yes assuming the field is in the underlying table. Just set the control source of the driverid control to the driverid field.

Reply | Quote

Thanks John for the response, but may I check something?
You said “In the after update event for the text box where you type the customerID put something like:”
Do you actually mean the CustomerID text box or do you mean the DriverID text box?

Rob

Reply | Quote

I meant the CustomerID because I thought youy wanted the DriverID looked up when you enter a customer.
the afterUpdate event happens after you enter or change the contents of a form control.

Reply | Quote

Hi John

I have done as you suggested but I get an error message stating that MsAccess cannot find the macro

I’ve made sure that my names are valid.

Help!

Rob

Reply | Quote

Rob
To put this code in the afterupdate event, you have to first select [event procedure] , then click on [event procedure] so that the three dots appear to the right of it. Then click these three dots to be taken off the VB editor. the code I gave you needs to written off in the VB editor.

If you just write it into the properties box Access assumes what you wrote is the name of a macro. (I assume that is what you have done.)

Reply | Quote

Thanks John. I had indeed typed the code into the AfterUpdate box. Following your instructions, it now works.
However, another problem has arisen. When I select a Customer ID from a drop down list (I’ve changed the text box for a combo box) the Customer Details subform displays the appropriate record. This worked fine until a few moments ago when it ceased to function. Now, when I select a Customer ID, the Customer Details subform does not display a new record.
If you have the time, I would be grateful for any suggestions.

Rob

Reply | Quote

You must have changed something for it to stop working.
Here are two things that can cause the behaviour you describe.

Is the subform linked via a mster field – child field relationship? In design view, if you look at the properties of the subform control do you have values in the Link child Fields and Link Master fields properties ? Are they the correfct ones?

Sometimes subforms don’t use these link fields. Instead they have a data source that is a query that uses the combo box in the main form as a parameter. In this case, you need to requery the subform after entering data in the combo.

Reply | Quote

Thanks for getting back, John. Both Link Child Fields and Link Master Fields properties show the value which I presume is correct. I don’t think I’ve changed anything, but…..
Should I recreate the primary form?

Rob

Reply | Quote

I don’t know what to suggest!
Recreating the form might the quickest thing to do.
Have you been playing aroiund with the combo box? Are you sure that the customerID is still the bound column?
(It is common with combos to display the name but store the ID)

Reply | Quote

Confused. I’ve recreated the form and subform from scratch. When I open the main form, the previously selected record is displayed in the subform. If I select a new Customer ID the subform does not change. However, if I close the form then reopen it, the new Customer details appear in the subform.

Can anyone help?

Rob

Reply | Quote