combo box query

[lynn atkinson]

I am trying to set up a combo box in which the entries are
based on the content of another field.
ie
I have a venue which may have various rooms avalable.
Venue information ie venue name and venue code, is
displayed on a form (eventschedule). I want users to
choose from a combo box which displays only the rooms for
that venue. (rooms are held in a separate table - rooms).
I have managed to get the combo box to display rooms based
on one venue, but I cannot get this to change. ie at
present it is displaying the rooms available at venue 3.
If I change the venue to venue 4, it still displays the
rooms for venue 3. How do I get it to update the combo
list according to the venue id?

I am using (if this helps)
SELECT roomselectqry.[room ID], roomselectqry.[room name],
roomselectqry.[venue code] FROM roomselectqry;

can anyone help.
PS I have looked at the posting on 10th march and tried to
apply this.

 

[Gerald Stanley]

One way is to change the RowSourceSQL for the Rooms
comboBox in the AfterUpdate eventhandler of the control
where the Venue is selected e.g.

Private Sub {yourvenuecontrolname}_AfterUpdate()
{yourroomcombocontrolname}.RowSource = SELECT [room ID],
[room name] FROM {yourroomtablename} WHERE [venue code] = "
& {yourvenuecontrolname}.Value
End Sub

You will have to change the above to supply your names for
the controls and the room table.

Hope That Helps
Gerald Stanley MCSD

 

[lynn atkinson]

I am still struggling to get this to work. Can I clarify a
few details?

can you confirm that I put this statement on the After
update of the Venue field, so that when I enter the venue,
the rowsource of my rooms combo should be amended as
applicable?

Also, where you have put {}, I just type the name without
brackets?

At the moment in the rowsource of the combo box I have a
query as follows:
SELECT roomselectqry.[room ID], roomselectqry.[room name],
roomselectqry.[venue code]
Should this be there or is the rowsource defined totally
by the afterupdate statement? (I have taken this statement
out of the rowsource of the combobox, but then get nothing
displayed in the dropdown.

-----Original Message-----
One way is to change the RowSourceSQL for the Rooms
comboBox in the AfterUpdate eventhandler of the control
where the Venue is selected e.g.

Private Sub {yourvenuecontrolname}_AfterUpdate()
{yourroomcombocontrolname}.RowSource = SELECT [room ID],
[room name] FROM {yourroomtablename} WHERE [venue code] = "
& {yourvenuecontrolname}.Value
End Sub

You will have to change the above to supply your names for
the controls and the room table.

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
I am trying to set up a combo box in which the entries are
based on the content of another field.
ie
I have a venue which may have various rooms avalable.
Venue information ie venue name and venue code, is
displayed on a form (eventschedule). I want users to
choose from a combo box which displays only the rooms for
that venue. (rooms are held in a separate table - rooms).
I have managed to get the combo box to display rooms based
on one venue, but I cannot get this to change. ie at
present it is displaying the rooms available at venue 3.
If I change the venue to venue 4, it still displays the
rooms for venue 3. How do I get it to update the combo
list according to the venue id?

I am using (if this helps)
SELECT roomselectqry.[room ID], roomselectqry.[room name],
roomselectqry.[venue code] FROM roomselectqry;

can anyone help.
PS I have looked at the posting on 10th march and tried to
apply this.
.

.

 

[Gerald Stanley]

-----Original Message-----
I am still struggling to get this to work. Can I clarify a
few details?

can you confirm that I put this statement on the After
update of the Venue field, so that when I enter the venue,
the rowsource of my rooms combo should be amended as
applicable?
Correct


Also, where you have put {}, I just type the name without
brackets?
Correct


At the moment in the rowsource of the combo box I have a
query as follows:
SELECT roomselectqry.[room ID], roomselectqry.[room name],
roomselectqry.[venue code]
Should this be there or is the rowsource defined totally
by the afterupdate statement? (I have taken this statement
out of the rowsource of the combobox, but then get nothing
displayed in the dropdown.

It can be a good idea to leave a default RowSourceSQL in
case the use clicks on the comboBox before entering the
Venue information

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
One way is to change the RowSourceSQL for the Rooms
comboBox in the AfterUpdate eventhandler of the control
where the Venue is selected e.g.

Private Sub {yourvenuecontrolname}_AfterUpdate()
{yourroomcombocontrolname}.RowSource = SELECT [room ID],
[room name] FROM {yourroomtablename} WHERE [venue code] = "
& {yourvenuecontrolname}.Value
End Sub

You will have to change the above to supply your names for
the controls and the room table.

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
I am trying to set up a combo box in which the entries are
based on the content of another field.
ie
I have a venue which may have various rooms avalable.
Venue information ie venue name and venue code, is
displayed on a form (eventschedule). I want users to
choose from a combo box which displays only the rooms for
that venue. (rooms are held in a separate table - rooms).
I have managed to get the combo box to display rooms based
on one venue, but I cannot get this to change. ie at
present it is displaying the rooms available at venue 3.
If I change the venue to venue 4, it still displays the
rooms for venue 3. How do I get it to update the combo
list according to the venue id?

I am using (if this helps)
SELECT roomselectqry.[room ID], roomselectqry.[room name],
roomselectqry.[venue code] FROM roomselectqry;

can anyone help.
PS I have looked at the posting on 10th march and tried to
apply this.
.

.

.

 

[lynn atkinson]

I am getting a syntax error.
My code looks like this

Private Sub venue_AfterUpdate()
roomcombo.RowSource = SELECT [room ID],[room name] FROM
rooms WHERE [venue code] = " & venue.Value "
End Sub

Can you see where I have gone wrong?

 

[Gerald Stanley]

You have to be careful with the quotation marks when
setting string values. Try
Private Sub venue_AfterUpdate()
roomcombo.RowSource = "SELECT [room ID],[room name] FROM
rooms WHERE [venue code] = " & venue.Value
End Sub

Hope That Helps
Gerald Stanley MCSD

 

[Michel Walsh]

Hi,


For a RowSource, with a Jet+Access database, you can try:


RoomCombo.RowSource= "SELECT roomID, [room name] FROM rooms WHERE [venue
code]= FORMS!FormNameHere!venue "


and you don't have to worry about possible delimiter to be used (in case
[venue code] is alphanumerical rather than being numerical, as example).


Hoping it may help,
Vanderghast, Access MVP

You have to be careful with the quotation marks when
setting string values. Try
Private Sub venue_AfterUpdate()
roomcombo.RowSource = "SELECT [room ID],[room name] FROM
rooms WHERE [venue code] = " & venue.Value
End Sub

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
I am getting a syntax error.
My code looks like this

Private Sub venue_AfterUpdate()
roomcombo.RowSource = SELECT [room ID],[room name] FROM
rooms WHERE [venue code] = " & venue.Value "
End Sub

Can you see where I have gone wrong?


.