list dependent on another list

[bakerboy_1111]

I have created a form called "Cable_To_Drum". This form has a list box called "Cable_Code_Box". I am currently creating a list box called "Drum_Same".

I have a table called Drum_Table which contains various columns, two of which are called "Cable_Code" and "Drum_Number" respectively.

The idea is that many drum numbers can have the same cable code. That is, say, for example, Drum_Number 1 has Cable_Code 3DF, and Drum_Number 2 also has Cable_Code 3DF.

I wish to create a list box ("Drum_Same") in my form that will show all the drum numbers that have the same cable code as that shown in the list box "Cable_Code_Box".

I've tried the following:

SELECT [Drum_Table].[Drum_Number] FROM Drum_Table WHERE ((([Drum_Table].[Cable_Code])=[Forms]![Cable_To_Drum]![Cable_Code_Box]));


This will not work and I don't understand why?

Can somebody please help me with this?

 

[Mile-O]

Have you requeried the combo after selecting something with the listbox?

 

[bakerboy_1111]

I don't know how to do that...... so I'm guessing no!

 

[Pat Hartman]

Put the requery in the AfterUpdate event of the Cable_code combo.

Me.Drum_Number.Requery

 

[bakerboy_1111]

I somehow got the list box to work once. That is, it produced the correct answer when I opened the form, but then the answer stayed the same no matter what I changed or clicked.

I figured that adding the line you suggested would help this (makes sense), but for some reason it didn't.

Can you suggest anything else I could try?

Thanks for helping me.

 

[bradcccs]

in the after update of Cable_Code_Box:

me.Drum_Same.requery

 

[bakerboy_1111]

Yeh I tried that also but nothing different.

Thanks anyway.

I did notice that now I'm getting the error that it can't find the macro me.

I don't understand this.

By the way I like your picture bradcccs. Go Aussie!!

 

[bradcccs]

Pat's previous post listed

me.Drum_Number.requery (Must be late in PatLand)

You should be able to requery the Drum_Same Listbox.

What you are trying to achieve is a useful approach to many problems. I am sure that is only a name conflict or the like that is stopping you from succeeding.

If possible, zip and post your db (remove unneccessary info if you want). I will have a quick sqizz.

As for the VB, well I am about due.

Cheers

Brad.

 

[bakerboy_1111]

I appreciate that offer. But the db is secure and I can't copy it, and its 37meg as it is.
I'm trying to take out the relevant bits and whip up a quick db. may take a while though. If you don't mind i'll still send it when I get that done.

Thanks.

ps: it 3:50 pm on a friday for me..... I think a VB's not far off for me either..

 

[bakerboy_1111]

Hi again brad,

i've whipped up a very crude version of the original. only problem is that it's too big by only a few bytes to attach.

is there maybe an email address i can send it to instead?

 

[bradcccs]

I have pm'd you my address.

 

[Pat Hartman]

When someone gives you VBA code, you need to put it in the appropriate VBA sub. To get to the VBA code, click the builder button (three dots) at the right of the box following the name of the event. Choose the code option. The builder will create the first and last lines of the sub. The requery goes between them.

You're right. It was late in Pat land. I used the column name rather than the control name. You need to use the control name.

 

[bradcccs]

Pat,

A quick note to keep you sane.

AND: To provide info for those following the thread. I hate it when a thread is completed by PM or personal email - Kind of leaves you hanging.

After looking at the db sent by BakerBoy, the requery was actually functioning. However, a previous event (Selection from a combo) was re-writing the SQL recordsource for the listbox, so the list box was not giving the expected result (hence the thought of it not requerying).

As for the other problem bakerboy was having: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58572 - The form was using a single line listbox (appearing as a text box) for comparison of Length v capacity. However as nothing was selected in the listbox, it is not possible to compare values (Well it is but they are both NULL).

Cheers

Brad

 

[Pat Hartman]

Thanks Brad.