Question about Cascading combo box not pulling right

[computer_man20037]

Hello,

I have been a mostly a reader on this forum for a while but I recently came across something that I semi understand how to do but just can't seem to wrap my head around it understand.

After many days of searching Google and trying to find a well explained guide so that I could actually understand how cascading combo boxes work:

I was able to finally get one set of combo boxes working correctly and so I wanted to do it again on the form but this time I did it exactly the same and just changed the tables it was going to but for some reason even though its pulling everything correctly (... Looks like it to me)

It just comes up saying that there is no match in my column I am looking at [Trying to match type of Catalog]

Here is my code:

Private Sub Publication_AfterUpdate()
Dim StrTest As String
StrTest = "SELECT Page_Position FROM" & _
" Publication_Ads WHERE Publication = " & "'" & _
Me.Publication.Column(1) & "'" & _
" ORDER BY Publication"
MsgBox StrTest
Me.Page_Position.RowSource = StrTest


Me.Page_Position.Requery
End Sub

So that should pop up a msgbox telling me that Me.Publication.Column(1) = [Flyer, Catalog, etc]

But what I am seeing is that even after I click on the drop down box it just shows me nothing even though I do have data in the tables that should match and be pulled up. I guess I might be overlooking something really simple but I wanted to post this and hope someone might be able to point me in the right direction and help me to understand it a little more ...

I did run into some other issues but I was able to determine that all the fields need to be the same type and not mixed which solved earlier errors but this now doesn't give me any errors and thus makes me wonder why it won't pull in the values for me now?

Thanks for looking!

Sorry for the really long post :\ and probaly a really stupid noob-ish question.

[jzwp11]

Could you provide the row source of the Publication combo box?

[June7]

Is Publication.Column(1) the primary key field that relates to the foreign key field in Publication_Ads? Which column is the BoundColumn?

An alternative is to have Page_Position combobox have a RowSource sql that references the Publication combobox column with PK value. Then the code in AfterUpdate would only be the Requery.

[computer_man20037]

Could you provide the row source of the Publication combo box?

Here is my row source:

SELECT [Publications].[RecNum], [Publications].[Publication] FROM Publications ORDER BY [Publication];

Is Publication.Column(1) the primary key field that relates to the foreign key field in Publication_Ads? Which column is the BoundColumn?

Publication.Column(1) is the row I used to match up my catolog types [catalog, flyer, etc]. Primary Key in all my tables is the RecNum which is a auto number we did that so that it would be hard to dup the records. I am not sure what you mean by BoundColumn

[June7]

The BoundColumn property of the combobox specifies the column that will set the value of the combobox. This is not necessarily the column that is displayed in the combobox. If the combobox has a ControlSource (bound to field of form RecordSource) then it is the BoundColumn that will provide the value saved to table.

You mean Publication.Column(1) is column (not row) used to match types? If RecNum is PK is that the value saved as FK in Publication_Ads? If saving the RecNum as FK then need to use that field as criteria for Publication_Ads combobox, not the descriptive type.

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[computer_man20037]

Sorry for such a late response I have been a little busy and after I read about the bound column I did think about that and I did go about changing it from a "1" to "2" (don't know why I did that) so I changed it back to "1" and from what I have seen so far it is still blank but I could possibly have something else wrong in my formula.... I will do a little more looking today and if I can't figure it out I will go about making a new database with the form and table with generic sample data to see if you guys might know whats missing.

Edit - I have done a little more testing and still kind of confused why its not pulling right but between this test database i created and my actual one I am getting the same result. Hopefully maybe someone looking at it can figure out why I am seeing a blank second combo box instead of values that should be showing up in there.

Thanks

File is attached below:

CascadeTest.zip

[jzwp11]

The first issue I saw was that you used a lookup field in your table. Although Access has this capability, having table level lookups can cause many problems as detailed on this site. I corrected that and then fixed the cascading combo boxes accordingly in the attached DB.

[computer_man20037]

jzwp11 - Thanks for your help and input on fixing the cascading combo boxes in that example database I uploaded but one question I have is my other combo boxes that are cascading use the lookup value to go about choosing the correct listing for the catalog (flyer, main book, secert sale, etc). Will those break later on if I continue to use them that way or am I looking at possibly doing something else like you kind of did instead? I haven't looked into it to much as of right now but just a thought that came to mind after looking your example you uploaded.

[June7]

Removing the Lookup from table should not impact comboboxes already built.

[computer_man20037]

Removing the Lookup from table should not impact comboboxes already built.

If i remove the lookup from the table will I still be able to do a combo box with a lookup for that field? (if that makes any sense at all)

[jzwp11]

Yes, you will still be able to use a combo box on your form. The combo box wizard steps you through the process of creating it.

[computer_man20037]

Can I then go about saving that to a specified field in my table even though its not a lookup combo box? ... I guess what I mean is that on the forms itself i can use the combo box but when i want to save that record I want to make sure my correct selection is saved to the correct field on the table? Since I don't want the users to be able to access the tables at this current point in time.

[jzwp11]

Yes, you can bind the combo box to a specific field in the forms underlying record source (i.e. the table). The combo box wizard prompts you for this as well.

[computer_man20037]

Well I don't want to keep this topic open much longer since my original question has been answered and fixed... Thank you for all your help and I will close this thread and mark it as solved.

[jzwp11]

You're welcome. Good luck with your project!