Control on Form has User Input Related Value of Foreign Key

[bozic12]

Hi All,

I have a relational database and am having issues that I'm guessing are easy to fix, I'm just overlooking the obvious answer. There is a main stackup table, that has attributes of a cutter id, holder id, and spindle id, which are foreign keys to respective tables for cuttters, holders, and spindles. These all have associtated, user defined, numbers (i.e a cutter number, holder number, etc.) that the user defines when the create a new cutter, holder, and spindle record.

I have a form for the user to enter the stackup values that are in the main stackup table, including the cutter, holder, and spindle used in the stackup. I have forms for the user to be able to select which cutter/holder/spindle numbers then are using for this stackup, if they don't know them off hand, to input into the text boxes on the main stackup form.

I would like for the user to be able to input a cutter number, holder number and spindle number (either by typing or using the forms i've created) and have the main stackup record being created to be populated with the associated unique ID's found on the respective tables.

For instance, if the user inputs a cutter number of 123, which on the cutter table has an ID of 4, to have the cutter ID attribute of the main stackup recored being created to be 4.

If it helps, a stackup is unique by the combination of cutter, holder, and spindle, each of which are unique based on their respective attributes (so cutter ID is a unique value, but many stackups can use the same cutter). As stated before, the cutter ID, holder ID, and Spindle ID have a many to one relationship defined to the ID fields of their respective tables.

I hope I'm not being too confusing!! Thanks for any help.

Jeff

 

[PHV]

Have you tried to play with the ComboBox wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bozic12]

Hi PH,

Thank you for the reply. I don't necessarily want these to be comboboxes (although they are in other similar situations). I am referring to text boxes here; I have a makeshift search form (which will become more extensive after future revisions), which will populate the text boxes, or the user can manually type in the values if they know them offhand. There may be several hundred records per, and comboboxes would be quite cumbersome in my current design.

I suppose I could creat an DAO object of the current recordset, and either using a query or code, match that cutter/spindle/holder number to the associated ID in their respective tables, and populate the current record's ID fields with those values. I was hoping there was an easier, and more importantly, less bytes solution, maybe just using 1 or 2 queries with no need to create dao objects and using code to manipulate records.

I did try the combobox wizard, but was not able to figure out how to use the selected value to populate the record with the related foreign key.

Again, thank you, and sorry if I'm being confusing. This is more of a conceptual syntax issue rather than error producing.

Jeff