Populating a FORM Using a Subform

[bd02eagle]

I have FORM#1 which is populated from Table 1 and field - PART_NUM on this form has a combo box which can automatically populate the entire form when the correct PART_NUM is choosen. However, if the user does not know the proper PART_NUM that they are looking for, I have a CHOOSE PART# button which when clicked pulls up another FORM #2 which has information from Table 2 (Part # and Part_Name and Comments). These two tables are linked. When the user figures out which PART_NAME corresponds to the PART_NUM that they are searching for, they chose that PART_NUM and press a button#2 I've created which in turn fills in the Part_Num in FORM #1.

In the OnClick property of button#2 I've placed this code to make the two table link when clicking the button:

FORM![TABLE1]![PART_NUM] = Me.PART_NUM

and it works great.

I also used this piece of code just as a tester to see if one of the other fields could be populated using the same button:

FORM![TABLE1]![D1] = FORM1[TABLE1]![D1]


and it also worked great at 1st leading me to think that I had my solution for all the other fields I needed to auto-populate in FORM#1 but then it crapped out on me and I'm stuck in a rut because I have no idea how to populate the entire FORM#1 using button#2 in FORM#2. Hopefully my problem is clearly defined and hopefully someone can help me out.
Thanks

 

[kjv1611]

but then it crapped out on me and I'm stuck in a rut

Ok, if this is true, maybe you should get some antacid to your database!

But seriously, when you said it's messing up, what exactly is happeneing? An error message? Blank fields? Something else in the fields?

 

[BSman]

Not sure I understand what the difference is between selecting the part_num from the main combo box versus selecting the part_num from form #2, but if you need to do that (rather than just selecting from the combo box on form #1), I'd suggest setting up a public variable (using a module) that you set to blank when form #1 is opened, but when the part_num is selected on pop up form #2, set the value of the variable to the part_num selected on form #2. Be sure that you force the cursor to return to the combo box on form #1. For the OnGotFocus property of that combo box, check the value of the public variable. If it's not a blank or zero, set the value of the combo box to it's value and force the combo box to continue the rest of it's VBA code to populate the rest of your form.

Bob

 

[bd02eagle]

To kjv1611, I the fields are blank whenever I click the button in form number two. When I first tested the button out with the D1 field, it worked but the I tried it again and it didnt work anymore and it doesnt work for any of the other fields.


To BSman, the only reason I've created form#2 is because while Form#1 has the same part_num as #2, these two forms contain different information, hence the use of the part_name in form#2 just in case the user has no idea what the particular part_num they want but they know the part name. Hoepfully this clarifies the problem for you a little bit better. Thanks for the help so far.

 

[BSman]

Why not just put two combo boxes on your Form #1, one box displaying the part_num, the other displaying the part_name, even though both could have the part_num as the value of the control and the field the control is bound to. Your user could use either one to select the part.

Bob

 

[bd02eagle]

The table in used in Form#1 doesn't have the part_name in it but the table in Form#2 does.

 

[BSman]

But your table source for the combo box can include the part_name as well as the part_num. I assume you have a parts table that lists all of the different parts (one record for each part) and should include both the part_num and the part_name. Your record source for the combo box (on form #1) should be something like:

Select part_num, part_name from [Parts] Order By part_num

The combo box should be bound to the part_num field of the table that is the source for form #1.

The second combo box I'm suggesting would also be bound to the part_num field, but the display would be set up as 0" width for the first column, and wide enough to display the second column (part_num). So, whichever combo box the user wants to use to select the part, both will work.

Bob

 

[bd02eagle]

Bob, TABLE#1 on FORM #1 does NOT have the Part_Name field available. Whoever designed the database felt the need not to place it in that table seeing that it was already placed in another TABLE#2(Which is the Parent table for TABLE#1, the table I'm having the problem with) and I cant change or manipulate the data. I wish I could just simply create a second Combo box which would have the part_name from Table number one but I cant.Once again, I can only use the corresponding FORM#2 which has the Part_Num and Part_Name to pull the info.

 

[BSman]

You can use any table/query you want as the source for your combo box on form #1. Doesn't matter what the source table is for the form. So just set up your source for the combo box as I described.

Bob

 

[bd02eagle]

Thanks for your help Bob, after toying with it all day, I discovered that my approach to what I was trying to do was wrong anyway. I really didnt need what I thought I needed in the first place so thanks for trying to help. I got it to work now. If I need something else, I'll be sure to ask. Thanks