Linking combo boxes 2

[PrintNET]

I want to make it so that when the user selects an item in one box, which displays all items in a certain column, the other box would automatically change to another item in a set column on the same row. Any tips on how to do this?

 

[PrintNET]

That was when I used the code builder. When I just typed it directly into the field 'After Update' there was the same error as before.

 

[Rmck87]

ok, what were you typing in the code builder?


And some things, that should not have been forgotten, were lost.

 

[PrintNET]

I was typing the exact same thing:
Me.cbo2.Requery
and I tried
Me!cbo2.Requery

I also tried Forms!frmMyForm!cbo2.Requery

 

[Rmck87]

ok were goin to try something a little different. I had to end up using it, and it at least brings back some answers in the combo. The only problem i have with it is that it doesnt bring back all options, but it could just be a problem with my coding, and if you try it and it works then i will know for sure that it is a problem with my coding. Alright in the 'afterupdate' of cbo1 type this:

Private Sub Combo48_AfterUpdate()

Me.Cbo2.Requery

End Sub

Then in the 'gotfocus' event of cbo2 type this:

Private Sub Combo52_GotFocus()

Cbo2.RowSource = (['your select statement here'])

End Sub

And make sure that you are linking every single possible table, and field to each other. This should work. if it doesnt then im going to have to figure out what i did different.


And some things, that should not have been forgotten, were lost.

 

[Rmck87]

Also, make sure that both combos are bound to column: 1

And some things, that should not have been forgotten, were lost.

 

[PrintNET]

in cbo2_GotFocus()
I try to do

cbo2.RowSource= SELECT [Sheet1 Query].[Description], [Sheet1 Query].[SKU] FROM [Sheet1 Query] WHERE ((([Sheet1 Query].[SKU])=[Forms]![frmMyForm]![cbo1]))

but it gives me a Compiler error.
It says 'Expected: expression' and then it highlights the word SELECT.

 

[PrintNET]

Bound to column 1? I'm not sure what you mean? How do I do that?

 

[Rmck87]

it needs to be written with quotes around it or it doesn't read it as a Select Statement.

cbo2.RowSource= "SELECT [Sheet1 Query].[Description], [Sheet1 Query].[SKU] FROM [Sheet1 Query] WHERE ((([Sheet1 Query].[SKU])=[Forms]![frmMyForm]![cbo1]));"


Like this. Bound to Column. ok Right Click on both cbo1 and cbo2, and select properties. inside there go to the Data tab, and right below Row Source there should be something that says: 'Bound Column' in the textbox of that write '1'. and thats how it gets bound to column 1.


And some things, that should not have been forgotten, were lost.

 

[PrintNET]

Okay, I did what you said and I noticed something. Now the only choice in cbo2 is the one that corresponds to the selection in cbo1. However, it does not autofill cbo2. Does yours do this?

 

[PrintNET]

Hmmm... I could just have cbo2 be a textbox. But that would make all this time spent worthless...

 

[Rmck87]

Yes i have had the same thing happen to me, and before i tried to synchronize the combos, i had the bound to column set to 0, and then i made cbo1 and cbo2 = to 0. and that set a default value. But, now that i have them synchronized, it doesnt allow me to set default values. But im going to start working on that next. I well keep you updated on my ideas. and we can probably work together, write down your email, and i will keep contacted with you.

And some things, that should not have been forgotten, were lost.

 

[PrintNET]

That sounds good. My email is tony@print-net.com

 

[ajarantes]

I did what you all suggested and it worked. I have cbo1 from tb1 and cbo2 from tb2. They are associated to cbo1 and cbo2, fields of the form frmName.

cbo1 has as source row a query like

SELECT cbo1
FROM tb1

cbo2 has as source row a query like

SELECT cbo2
from tb2
where tb2.cbo1 = Forms!frmName!cbo1

also, I linked an After Update Event to cbo1, asking to requery cbo2

It works, in principle, but two things happen that I don't understand:

1 - The macro associated to the AfterUpdate Event just run if a mention cbo2 simply as cbo2. If I mention it as Forms!frmName!cbo2, access tells me that the field doesn't exist in the form (the name of the field in the form is cbo2)

2 - Each time the requery happens, it works for all records on the form (for both single form and continuous form). This means that if cbo1 on the current record is different from that in the previous record, cbo2 for the previous record disappear. But only in the form, not in the database.

Any clues on these two problems?

 

[Rmck87]

Well, the first one i think that i understand where you coming from but im not too sure. So, correct me if im wrong. What if you typed it as me.cbo2, or what if you did this, tb2.cbo2; would that work?

And some things, that should not have been forgotten, were lost.