Autofill Text Box from Cascading Combobox

[desibabu90]

Hey Everyone,

I have a cascading combo box setup that works completely fine. I now want to autofill several text boxes with information related to the second combo box.

For example, in the first combo box the user is asked to select a facility. In the second combo box, it will list all the unit numbers of the inventory at that facility. As I said earlier, I was able to work this out.

Now I am trying use Dlookup in the control source of the text boxes but I am not able to get anything to work.

This is how I have set up my Dlookup:

=DLookUp("[ItemIWantFilled]","TableName","[PrimaryIDinTable]=" & Forms![Formname]![2ndComboboxname.Column(X)]) and I just get an "#Error".

Note I have several columns in the 2nd combo box and have also tried
=comboboxname.Column(X) and nothing shows up.



Please help!

Thanks in advance

[pbaldy]

I'd go the second way:

http://www.baldyweb.com/Autofill.htm

If nothing is showing up, make sure the column count property of the combo is correct.

[desibabu90]

hey i tried doing that..if I have one combo box this method works but when I have two combo boxes this method is not working. Here is what happens: When I go from Design View to Form View my text box will be populated as long as the first combo box is not updated. However when I change the first combo box, i.e. pick a different facility and then update the second combo box the textbox doesn't get filled.

let me know if u need more clarification.

[pbaldy]

It should update whenever you change the value in the second combo. Can you post the db?

[ssanfu]

I don't like using DLookup(), unless I absolutely have to. I prefer to "push" the data into the text boxes.

What you are attempting to do is called "Cascading Combo Boxes". When you change the first combo box, you need code in the after update event to requery the second combo box.

There is an example at:
http://www.rogersaccesslibrary.com/forum/topic389.html

In the after update event code, I 'push' the data from the second combo box into the text boxes. (remember that the columns are zero based, so the second column is column(1).)

[desibabu90]

unfortunately, no because all the data is linked to a different server and i wont be able to release all this information anyways.

[desibabu90]

I don't like using DLookup(), unless I absolutely have to. I prefer to "push" the data into the text boxes.

What you are attempting to do is called "Cascading Combo Boxes". When you change the first combo box, you need code in the after update event to requery the second combo box.

There is an example at:
http://www.rogersaccesslibrary.com/forum/topic389.html

In the after update event code, I 'push' the data from the second combo box into the text boxes. (remember that the columns are zero based, so the second column is column(1).)

hey, I already have the cascading combo boxes working fine...how exactly do you push the data? is it by using:

Me.textboxname = Me.combobox.Column(X)? I tried doing that as well and once again nothing happening.

[ssanfu]

hey, I already have the cascading combo boxes working fine...

You are missing the VBA that re-queries the combo boxes.

how exactly do you push the data? is it by using:

Me.textboxname = Me.combobox.Column(X)? I tried doing that as well and once again nothing happening.

Yes, but you have to have it in the after update event of the second combo box.

Attached is a A2K mdb that I did a long time ago (don't laugh too hard). But it has multiple cascading combo boxes with data being pushed into text boxes. One button opens a form with combo boxes and another one does the same thing as the first form, but uses list boxes. If I had it to do over again, I would use a different structure for the tables, but it will work as an example. (good or bad..... )

[desibabu90]

okay, i will try looking at this and see what i can do. thankx.. i will try to post my results tomorrow..thanks for all your help guys!

[desibabu90]

hey ssanfu, I looked at your database but I have a different set up than you and thus your method won't work for me. I am attaching a sample database that looks similar to what I will have. please try to have a look at it and show me a way in which the textbox can be filled.

[pbaldy]

Your code to populate the second combo:

Private Sub Combo0_AfterUpdate()
On Error Resume Next
    Combo2.RowSource = "Select AllData.VehicleID " & _
        "FROM AllData " & _
        "WHERE AllData.Facility = '" & Combo0.Value & "' " & _
        "ORDER BY AllData.VehicleID;"
End Sub

Where is Year in that?

[desibabu90]

haha thanks...now i'm a newbie in VBA so can u guide me as to which argument i should be using so that my Year, Make fields are added to the second combo box..u don't have to give me the whole argument but atleast something that i can start with.

would it be similar to the Where clause??

Thanks again.

[pbaldy]

It would look like the SQL that the combo starts with:

Combo2.RowSource = "SELECT AllData.VehicleID, AllData.Facility, AllData.Year, AllData.Cost, AllData.Make " & _

[desibabu90]

thanks a lot! ur a life saver...

[pbaldy]

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