Controls and Tabs

[puakenikeni]

I apologize for having so many questions today!

I have a form that has tabs in it with record source as the table "Master". The first form uses the fields from the Master table so that the user can input data into the table. The second tab is used to query the existing data in the Master table using a subform. The combo boxes have the same control source as the controls on the first tab, so whenever I make a selection on the combo box on the second tab, it "changes" the selection on the first tab as well.

How do I stop it from doing that? I don't want to accidentally change the data on the input side (first tab) or change the query information on the lookup side (second tab). I want things to be kept separately, but get its values from the same table.

 

[Pat Hartman]

Remove the master/child links from the subform on the second page so that it will operate independently of the main form.

 

[puakenikeni]

I tried doing that, but if I remove the parent/child fields for the subform, then the bound combo boxes don't filter the information correctly I've uploaded a screenshot of tab 1 and tab 2 of my form. For instance, I changed the Item Class combo box in tab 2, and go to tab 1 (which is the data entry side) and see that the combo box for Item Class (since they both get their source from the same field in the same table) is also changed there, which I don't want it to do. How do I change that?

 

[missinglinq]

Did you create these comboboxes using the Combobox Wizard and choosing the "Find a record on my form based on the value I selected in my combobox?" IF not, what code is behind the comboboxes AfterUpdate events?

 

[puakenikeni]

I created the combo boxes with the wizard by dragging over the combo box control, instead of dragging over the field list. However, I chose for it to look up values in a table or query.

In the AfterUpdate event, I have this for the combo box:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemClass] = '" & Me.cboItemClass_tab2 & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.Repaint
Me.subMaster.Requery
If Me.Dirty Then Me.Dirty = False

Should I have selected "Find a record on my form based on the value I selected in my combobox" instead?

 

[boblarson]

The problem comes in that your combo should not be bound in the second form. It should just be an unbound combo for searching. If you bind to the field then of course it will change both tabs where it is used. Remove the recordsource from the spreadsheet view tab.

 

[puakenikeni]

I removed the record source from the combo boxes in the spreadsheet view tab and gave the drop-down values as a value list. Do I need to change the AfterUpdate event so that it grabs the text value from the combobox?

 

[boblarson]

I removed the record source from the combo boxes in the spreadsheet view tab and gave the drop-down values as a value list. Do I need to change the AfterUpdate event so that it grabs the text value from the combobox?

You shouldn't be having the values as a value list. The ROWSOURCE should be a query that pulls the same values that the other combo does. It just doesn't have a control source.

 

[puakenikeni]

Ahh. . .gotcha. Thanks for all of your help! It now doesn't have a control source, but it queries the values from the table. For some reason, though, when I click on values in the combobox, it doesn't show anything in the subform now. Should I requery the other comboboxes to update all of their values?

I'm sorry for all the questions--it's been awhile since I did all of this!

 

[boblarson]

You would need to requery anything that is dependent upon that combo. So, if the subform's query is set to filter by it, then you would need.

Me.YoursubformContainerControl.Form.Requery

where YoursubformContainerControl is the control that houses the subform on the mainform, not the subform itself.

Or Me.OtherComboName.Requery

etc.

 

[puakenikeni]

Thanks boblarson. In the AfterUpdate code, I have this:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemClass] = '" & Me.cboItemClass_tab2 & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

If Me.Dirty Then Me.Dirty = False

Me.tabSpreadsheet.Requery
Me.cboProgramName_tab2.Requery
Me.cboPropertyClass_tab2.Requery
Me.subMaster.Form.Requery

However, it's not refreshing the subform with the updated information from the comboboxes. I opened up the query by itself with the form open and values selected, and information is showing up fine in the query, but for some reason, I can't get it to refresh in the form itself. Am I referring to the subform incorrectly?

 

[boblarson]

Make sure that the container on the main form is actually named subMaster. It could be the name of the subform, but if the subform container isn't named subMaster then you do have the wrong syntax.

If you aren't sure about what is the subform container then:

 

[puakenikeni]

I checked it and it looks correct:

It says the container name is subMaster. However, I noticed that the Source Object is also subMaster. Should I change that to the query name instead?

 

[boblarson]

I checked it and it looks correct:

It says the container name is subMaster. However, I noticed that the Source Object is also subMaster. Should I change that to the query name instead?

No, that's the name of your subform. The subform and subform container CAN have the same name (one of the few things that can like that).


I guess the next thing is to make sure that these

Me.cboProgramName_tab2.Requery
Me.cboPropertyClass_tab2.Requery

are actually the control names.

And if that still doesn't work, I think it is time to have you post the db.

 

[puakenikeni]

Yes, those are also the control names for the comboboxes. I uploaded my database to Megaupload (it was too large to attach here), so please take a look at it. I still don't know what I'm doing wrong. . .

 

[boblarson]

I went there but it would appear you have to join and log in to download. I prefer to not do that at that location. Can you find another place to do it that doesn't require someone who is downloading to be a member?

 

[puakenikeni]

You shouldn't have to log in to download the file--I know you have to wait for awhile for the download link. I uploaded it it Sendspace instead. Please let me know if you have any trouble downloading it.