DLookup Help Needed

[krakalackin]

Hey guys, I'm really rusty on Access. What I've done is created a call center form where a plant calls in needed assistance and a technician can log a service case against the automation system and the plant. In the Case Form when I select a System No from the combo box I want the "Plant No" and "Plant Name" from the "SystemInformationTable" associated with that "System No" to be pulled into the "Plant No" and "Plant Name" of Case Form. I have tried using a Control Source string but can't make it work. This is driving me nuts! Attached are the 2 tables and the one form. Any help is appreciated! Thanks!

[June7]

Why are you duplicating data into CaseTable? Just save the RecordID.

Options for displaying the related info:

1. columns of combobox have the info, expression in textbox ControlSource references the combobox column

2. include the lookup table in the form RecordSource - join type "Retrieve all records from CaseTable and only those from SystemInformationTable that match", bind textboxes to the associated fields and set them as Locked Yes and TabStop No to prevent editing.

3. DLookup()

[krakalackin]

I have changed the tables and form now. The "System No" Combo Box in the Case Form pulls the "System Numbers" from the "System Information Table". The "Plant No" Text Box is now displaying "#Error" using the attaching DLookup Control Source. What is wrong with the this Control Source? Again I'm not an expert, closer to being a beginner... Thanks for the help!

[June7]

The DLookup option is listed as #3 because this would be my last choice to accomplish this. Option 1 is the best.

[krakalackin]

I figured out Option 1 and got it to work. It broke a couple other functions but I was able to fix them. Thanks for your help!