How to uniquely link subforms to main form (1 Viewer)

[quicksilver1024]

lol..

Sorry Tim!
Funny thing - I had my browser open for the entire day and did not see all the other posts you left for me! When I had posted that I have found out where the REW_WRAP_INDICATOR was, I did not see or read the post you made explaining it. And the exclamation mark wasn't at all intended to make me sound mad, I was just letting you know I found it by myself

Now that that's all sorted out..
I forgot about your code. You are right - I need to put that snippet in the forms where I have that text box.
And thanks for explaining how I can have multiple forms writing into a single table. It all makes sense now!

And regarding:

Quote:
Originally Posted by quicksilver1024
I've removed the duplicates. I have made a lot of changes and didn't bother removing them.

Huh? Now I'm confused. Duplicates in whose tables, mine or yours, and did you or didn't you remove them?

I meant to say this for the multiple Machine ID's and Machine Categories being stored in separate tables.

You should take a look at my database now! A lot of things have been fixed and I am slowly moving on to writing the calculations (via queries) for the report. I'll send you my program tomorrow, there are a few things I need to fix first.

Cya

 

[Tim L]

Great, I love to hear about it when things go well (it makes such a change... lol).

Glad that you're getting it sorted.

Tim

 

[quicksilver1024]

I have been playing around with this thing for hours and I still can't get it to work properly.

Right now, when I click on the classifications buttons (REW,WRAP1,WRAP2)with your code the Sheet ID (for SheetMaster) always resets to '0'. Once this does this I am unable to enter entries.

Also, when I click on the classifications buttons my text box, 'txtMachineCategory' (equivalent to your txtREW_WRAP_INDICATOR) does not show anything.

I have attached my program. Can you please look at it Tim?

I have no idea what is going on!

 

[Tim L]

Right now, when I click on the classifications buttons (REW,WRAP1,WRAP2)with your code the Sheet ID (for SheetMaster) always resets to '0'. Once this does this I am unable to enter entries.

I can see why. Because I used a single form (rather than three forms) and changed the Record Source on it, I could keep the frame and its code on the form which was changing. Because your need three forms you need to move everything on the to main form. Unfortunately you didn't modify the code to reside on the main form so it now changes the Record Source of the main form and not the subform. You could change the code, but there is an easier solution.

As I suspected in my last post you don't have to actually set the Record Source when choosing the form; because you have three separate forms you can, and should, set the Record Source on the form (it's either that or more lines of VBA). Copy the contents of the three SELECT statements (i.e: everything from the S in Select to the semi-colon, not the quotes) to their respective forms' Record Source and delete those lines from the Select Case options, and don't forget to save the subforms with these new Record Sources.

Also, when I click on the classifications buttons my text box, 'txtMachineCategory' (equivalent to your txtREW_WRAP_INDICATOR) does not show anything.

This is more problematical, becuase you now need to reference the control on the subform and the field 'MACHINE_CATEGORY', which this control is bound to, updates once you make a selection in the MACHINE_NAME combo. BUT not until you click on the sub-sub-form... Given that the information in this field is actually shown in the drop-down it doesn't really need to be repeated, or stored twice.

'A' solution (needs to be done to all three of the sub-forms):

• Change the query which the MACHINE_NAME combo is based on to include the text column from the Machine_Category table so that you have two columns (use the join that Access prompts you). For the two WRAP forms the query will need extra criteria in the Machine_Category column, Like "*1" or Like "*2" accordingly.
• Remove the Machine_Category field entirely from the SheetMachine table (really, it is not needed because the information is already available through calculation on the MACHINE_NAME field), but leave the text box where it is.
• Name the Machine_NAME combo cboMACHINE_NAME.
• Ensure that in the Format tab of the combo that the column count is 2.
• If necessary, hide the second column by setting the Column Widths to <as desired> cm;0 cm.
• Set the Record Source for 'txtMachineCategory' to be =cboMACHINE_NAME.column(1). Now, as soon as a selection is made in the Combo the value in the hidden column of the combo will be displayed in the Category text box.
• Delete the Before_Update events that set the value of txtMachineCategory, they will no longer be valid.

HTH

Tim