Combo box and then fill textboxes

[kate10123]

Hi there,

I am using a combo box with a control source consisting of a query of these fields:

Course
Faculty
Department

I have two textboxes called 'faculty' and 'department'. The way it should work is as follows:

The user selects a course from the dropdown list, the corresponding faculty and department go in their respective textboxes.

All of this data in aligned in a table called tbl_course and the query shows all of the necessary data in it.

Any ideas how this can be done?

 

[LeeInEngland]

public sub cbocourse.lostfocus ()

txtFaculty.value = DLOOKUP ("Faculty", "tbl_Course", "Course = " & cbocourse.value)
txtDepartment.value = DLOOKUP ("Department", "tbl_Course", "Course = " & cbocourse.value)
End Sub

 

[rainman89]

there are numerous posts here showing solutions to your problem..

the general syntax would be

=yourcomboboxname.column

 

[speakers_86]

there are numerous posts here showing solutions to your problem..

the general syntax would be

=yourcomboboxname.column

This is how I did it just a few days ago. When you create the combo box, be sure to include those other fields with the combo. If you dont want those fields seen in the combo, just set the width to zero.

 

[LeeInEngland]

The 'problem' with that is that you're filling up a combo box with values that you don't need to be in there. Isn't a combox box limited to a certain number of characters, like a list box?

Just a thought

 

[speakers_86]

I dont believe there is a limit, besides the width of the column in the combo box. If the width of the extra fields is set to zero, there shouldnt be much of a problem. If you dont like this method, dlookup is an option. I actually posted the same question on this site about 2 weeks ago. I got exactly the same responses. I tried dlookup, but had problems with that for some unknown reason. But the combo box method works fine, and is extremely easy to do through the wizard.

 

[rainman89]

dlookup will cause some problems somtimes and It is also not as fast as the other option.

As for the question about certain # of characters, I have never heard of that. and havent had problems with putting large values in either. Just do like speaker said and set the width to 0

 

[boblarson]

there are numerous posts here showing solutions to your problem..

the general syntax would be

=yourcomboboxname.column

Yes, this is the way you would do it.

 

[kate10123]

Thanks for all the responses.

I have got it working thanks to all your help!

 

[BillBee]

I hope I can come in at this point. I have a similar situation where I have a combo box selecting two fields from a table. The combo box gathers the right info but I cannot get the second of the two text boxes to show the correct data. The first field (Class) always reads correctly but the second (Entry) is not changing. I have the control source set as =MyComboBoxName.Control(0) for the first and the number as (1) for the second. The fields are Class and Entry and in appear as 1 1, 1 2, 1 3. and 21 1, 21 2 etc in their respective columns in the table. When I first set the form up I was getting the Entry text box to show a different number but it was not always the number selected in the combo box. The first field is always correct. Can anyone offer some explanation?

 

[speakers_86]

So your having trouble with this:
=MyComboBoxName.Control(1)

If that doesnt work, there are 3 obvious possibilities
1. Spelling is not constant
2. The combo box was not done correctly
3. You are referencing the wrong column in the text box

Good luck.

 

[BillBee]

Speaker_86 I am confident items 1 and 3 from your list are correct. I do think you are right about the Combo Box not being done correctly. Below is the syntax (?)/code(?) used in the row source.

SELECT Entries.ClassID, Entries.CageNo FROM Entries ORDER BY Entries.ClassID, Entries.CageNo;

Any suggestions on what is wrong?