Combobox not updating

[lostfan789]

Hi all, I've tried to research this problem online and I know I'm missing something. So hopefully someone can help me.
I am in the business of creating clothing catalogs. These catalogs can be built very differently, with different covers and different inside (body) pieces. I have created a form with the main information about the catalog on top and subforms beneath it (on tabbed pages) to display very specific information about the catalog. On one particular subform (frmForms), I have a datasheet where the user can record the various pieces and assign an Alpha_Code to it (A, B, C, etc.). They also designate in the Form1 field whether it's a Cover or a Body. On a separate subform (datasheet called frmDemo), I want the Cover_Code field to drop down to show the Cover codes and the Body_Code1 field to show the Body codes as listed on the previous subform. This would allow the user to mix and match the covers with the bodies for the various combinations. Hope this all makes sense.
So my problem is that I can't get the codes to update automatically. I've been working on this for a couple of days and trying ideas as listed online, but to no avail. When I close the form and reopen it, it's fine. I've tried using the AfterUpdate in the Alpha_Code field. I have it listed as this:
Private Sub F_AlphaCode_AfterUpdate()
[frmDemo]![Cover_Code].Requery
[frmDemo]![Body_Code1].Requery
[frmDemo]![Body_Code2].Requery
[frmDemo]![Body_Code3].Requery

End Sub

But I'm getting a run-time error '424'. Object required.

Any help you can provide would be very useful! Thanks.

[pbaldy]

I believe the syntax in referring to the form and control are off:

Forms Refer to Form and Subform properties and controls

[lostfan789]

So in my case, it should read:

Me!frmDemo.Cover_Code.Requery
Me!frmDemo.Body_Code1.Requery
Me!frmDemo.Body_Code2.Requery
Me!frmDemo.Body_Code3.Requery

?

[pbaldy]

Since it sounds like this code is on a different subform, not the main form, you'd need the full reference.

[lostfan789]

Thanks Paul. So now it's reading as Me.Parent!Demo.Form!Cover_Code.Requery. It appears to work, but now is leading into another issue.

The list in Cover_Code is repeating itself. So if the first subform contains lists covers A, B and F, the second subform lists A, B, F, A, B, F, A, B, F. Any ideas why?

[pbaldy]

What is the SQL of the subform? It sounds like a join issue.

[lostfan789]

The Cover_Code Record Source is

SELECT Forms.F_AlphaCode, Forms.Form1 FROM Forms INNER JOIN Demo ON Forms.Catalog_Title=Demo.Cat_Title WHERE (((Forms.Form1)="Cover"));

I'm using this to get just the cover codes. For the other fields, I swap out Cover for Body. It also seems be just showing three sets of the letters, never more than that.

[pbaldy]

If you run that as a query, does it return correct results, or duplicated results? Can you post the db?

[lostfan789]

It's duplicating results. For some reason, every record that is created in the second subform (every combination), it's duplicating the list. I've attached the db. The Catalogs form is the main form and it should open automatically. The tabbed page Demo Version contains the Cover_Code and the page Form Entry contains the Alpha_Codes.

Thank you so much for helping me out!

[pbaldy]

Why is the demo table included in the SQL if you don't use any fields from it? Also, I would expect a further restriction by catalog, but I won't pretend to understand your needs at this point. Does this work for you?

SELECT Forms.F_AlphaCode, Forms.Form1
FROM Forms
WHERE Forms.Form1="Cover" AND Forms.Catalog_Title=[Forms]![Catalogs]![Catalog_Title]

Which would also require a requery in the main form's current event.

[lostfan789]

Thanks, Paul. This seemed to work, but now another issue. When I go to another record and look at the dropdown for Cover_Code, the letters for the first record are there too. Each record is unique so they shouldn't share the same list. Maybe that's what you were referring to with the "further restriction".

[lostfan789]

And one more thing: When I actually do enter some Alpha_Codes on the second record, the Cover_Code then does show only those codes and not from the first record.

[pbaldy]

That is exactly what I was referring to, and as you can see the SQL I posted does that. As I mentioned, you also have to requery your combos in the current event of the main form, which fires when you change records:

Me.Demo.Form!Cover_Code.Requery
Me.Demo.Form!Body_Code1.Requery
Me.Demo.Form!Body_Code2.Requery
Me.Demo.Form!Body_Code3.Requery

[lostfan789]

Works perfectly. Thank you!

[pbaldy]

No problem, and welcome to the site by the way!