Problem synchronising unbound combo boxes

[tjp]

I'm pretty hopless with VB but I am trying very hard (hours spent on this forum reading code I only slightly get the gist of) to implement a form that takes input from two synchronised unbound combo boxes and then displays records matching the input in a sub-form in datasheet mode. I'm failing at the first part just now: the combo boxes. No doubt I'll fail at the second part too but I'll deal with that when I get this fixed.

I was using the northwind DB to learn how to synchronise the combo boxes. I successfully did it by following the instructions and using this code from MS kb article 209595 in the After Update procedure:

Me.Products.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
Me.Products = Me.Products.ItemData(0)

I then tried to implement this on my database by changing the relevant information to reflect my database info. as shown below, and it does not work. I switched Product references for my Unit references and Category references for my Item references but I don't think I got it right or over-simplified it due to the table structure.

Private Sub Item_AfterUpdate()
Me.Units.RowSource = "SELECT Unit FROM" & _
" Units WHERE ItemID = " & Me.Items & _
" ORDER BY Unit"
Me.Units = Me.Units.ItemData(0)
End Sub

When I compile the code it stops at Me.Items with the error "Method or data member not found". So I assume that I have not referenced the right objects when I modified the code. I thought this was maybe due to the two tables I'm using being in a many-to-many relationship via a junction table, as opposed to the northwind tables which are directly linked by a one-to-many relationship. If this is indeed the case, can anyone help me put this right. I have attached my table structure / relationships in a .jpg to this post.

Any help from all you smart developer dudes would be muchly appreciated.

 

[RuralGuy]

Hi tjp,
You're on the right track. I believe the first ComboBox is named Item not Items. Try:
" Units WHERE ItemID = " & Me.Item & _

 

[tjp]

Thanks for taking the time RG. This shows that I know nothing about VB. I thought that Me.Items was referring to the Items table not the combo box name. I'm about ready to give up though as I cannot get it to work. It compiles fine now after I made the change you pointed out, but that alone shows that I don't have the ability to get it to do what is required. One database, one form to do one function and it is beyond me. All I wanted to do was select an Item, have the relevant Units be available in the second combo box, select the unit and have all matching suppliers, supplier item codes and costs displayed underneath so that you could see the cheapest supplier. I thought it would be simple; and it is, but it is beyond my ability. Thank you very much again for your time and help.

BTW I changed the combo box name to Items rather than the code. Just had a thought. I've uploaded the db in case any kind soul want's to take a look and put me out my misery.

 

[RuralGuy]

Here's some links that you might find useful: Cascading Lists for Access Forms
and be sure and review the rest of his site: http://www.fontstuff.com/access/index.htm

 

[tjp]

Thanks RG, i'll have a look

 

[RuralGuy]

If you wish to learn and want to complete this project I have some time to assist. Post your next problem and we'll deal with it.

 

[tjp]

RG you are a star. I attached the db a couple of posts up. I don't even know if this is the easiest way to do what I want. I want to have the user select an item from the first combo box, which then populates the second combo box with units pertaining to that item. Then when they select a unit from the second combo box I'd like all records of that item to be displayed underneath like this:

Supplier Supplier Code Cost
" " "
" " "
" " "

and so on. Preferably with the cheapest as the first record.

It is very frustrating as this is probably very easy. I'm a server / network guy who usually avoids any coding but got roped into this for a friend. I am indebted to you, thank you.

 

[tjp]

If I can get the combo box synch working I can move onto trying to get the matching records to display.

I'm going through that article you mentioned, it is very good and explains each bit of the code very well. Fingers crossed.

 

[RuralGuy]

I'm not sure what you want done with the Units but I made some changes to your db. It might get you a little further along with your project.

 

[tjp]

RG, I owe you one. My specialized subjects are poker (all varieties), Exchange Server, all windows server and desktop platforms and networking. If I can ever repay you in kind please let me know. That is exactly what I wanted to do. All I need to do now is to refine it so that the unit combo box comes into play. I'm going to analyze your modifications and learn from it. Many, Many thanks.

I see you've used the original unnormalized table as the source for the new query. I'm going to try and replicate what you have done with the normalized tables.

 

[tjp]

RG, I worked with the db you sent back and got everything working the way I want and learned a great deal from it as well. Thank you for your time and effort.

 

[RuralGuy]

You are certainly most welcome. Thanks for posting back with your success. That is exactly what these forums are about, learning!