Issues displaying data from multiple tables on one form

[brharrii]

I am building a spec database for everything we produce. My spec database has a few different tables for different types of data:



- tblProductInfo
ItemNumber
Description
MasterCase


- tblpackaging
PackageID
Description
Pallet Configuration

- frmSpecification
ItemNumber
Description
MasterCase (this is a combo box with a listing of descriptions from tblpackaging)
Unbound text box - MastercaseID
Unbound text box - Pallet configuration



There are a few more, but that's the idea.

So I have a form and a report that I am using to pull data from each one of these tables to build a final specification. The form can be used to build a new product specification and uses combo boxes to add data from the different tables.

So lets say I have a product "x" that needs a master case "y". I would open the form, look for product "x" and use the combo box that sources from tblPackaging to select Master Case "y". Easy enough.

The problem I'm running into is that most tables have more than one piece of data that needs to show up on the form. For example, If I use Master Case "y", There is an assigned corresponding ID number and a pallet configuration that needs to be on the specification as well. the Id number and pallet configuration are stored in the tblPackaging I'm just struggling to get it to show up on the form.

So what I've done is in tblProduct info, There is a field called MasterCase. It uses a query that pulls values from the table: tblPackaging and displays 3 columns if information, Mastercase, ID, and palletconfiguration. Then on my form, I select the master case through a combobox and would like to have my selection automatically populate additional text boxes with ID and pallet configuration. I was able to accomplish this by editing the control source in unbound text boxes as so:

=[MasterCase].[column](1)

I got everything setup and it was working great and then I saved my database and left for the night and when I came back in this morning I opened my form and got the following Error:

The expression on current you entered as the event property settting produced the following erro: Return without gosub.

After pushing OK I raelized that every one of the text boxes with edited control sources now say #NAME in them and no longer perform their desired function. I deleted all of the affected text boxes on the form and recreated them again and it worked again so I decided to test it to see if closing the database would cause the problem again. And it did. So for some reason my attempt to set up this auto populating feature only works until the database is closed and then everything gets messed up for some reason. I'm wondering if there is way for me to fix this or if I'm going about this in a much too complicated way and maybe there is an easier way to do this?

[John_G]

Are you compiling your code whenever you make changes? If not, you should. Compiling your code would have caught the "Return without GoSub error" and allowed you to fix it. It could be that you are making the kind of mistake in all your code modules.

If you get your code fixed, and still get the #NAME error, then there are other things that need fixing. My guess would be that you have a issue with some of the form's controls referencing fields that are not part of the Record Source for the form.

HTH

John