Basing a form on a query versus a table

[RichKorea]

I'm putting together a service report form, and I'm not sure how to connect
the customer info. Currently I've got the following:

Service Table
Service_ID - key
CustomerID
Service_Description

Task Table
Task_ID - key
Service_ID
Task_Description

Customer Table
Customer_ID - Key
CustomerName
Customer Factory

I created a query, which I used to generate a form using the Wizard. The
form pulls in all of the data, and if If I manually enter a Customer_ID, the
customer name and factory name update, but when entering a new service
report, I want to be able to generate the customer number using a combo box
to select the customer, and then a second combo box to select the customer's
factory.

Is this the right approach? I'd appreciate it if someone could point me in
the right direction.

 

[Arvin Meyer [MVP]]

Yes, it is the right approach. An example, using list boxes, or getting 1
combo to fill another is at:

http://www.accessmvp.com/Arvin/Combo.zip

 

[RichKorea]

Thanks for the link. The sample got me half way to where I need to be. I
can now populate a couple of un-bound combo boxes to select the service site,
and once the service site is fully selected, I update the Service Table with
the Customer ID.

The remaining question is how to go the other way. As the user is scrolling
through the records, I want to be able to display the customer information in
those same combo boxes. Is there a way to trigger off when the record from
the query has changed to know that I should update the unbound customer combo
boxes?

Thanks

 

[Arvin Meyer [MVP]]

I typically set the combo to an empty string in the after update event,
right after moving to the record I want, but you could use the form's
current event to change the combo as well:

Private Sub Form_Current()
Me.cboSearch = Me.txtID
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[RichKorea]

The On Current event did the trick. I do an IsNull test on the field that
contains the key back to my Customer Table, and if it is (new record), I set
everything to an empty string, and if it's not, I use a query to find the
values associated with the key using a DLookUp to populate the various boxes.

Thanks.

 

[Arvin Meyer [MVP]]

Actually, you do not need to test for Null. On new records (which are the
only ones where a primary key is null) the value in the combo will return
null if there's no value in the key.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com