refresh on a combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form in edit mode with two combo boxes, one is RoomNamecombo and the
other is SiteNamecombo.

A site can have many rooms, so when selecting from SiteNamecombo, I want the
RoomNamecombo box to refresh to the corrent selection of rooms for that site.

The code I have in the RoomNamecombo row source is:

SELECT [tblRoom].[RoomName] FROM [tblRoom] WHERE [tblRoom].[SiteName]
=Forms![frmEquipmentAssessmentEdit]!SiteNamecombo

I also have the following code:

Private Sub SiteNamecombo_AfterUpdate()

Me.RoomNamecombo = Null
Me.RoomNamecombo.Requery

End Sub

This all works fine in the form that is in Data Entry mode but does not seem
to work for the form in Edit mode.
 
I'm not sure why it's not working as you have it, but try setting the
RowSource explicitly in the sitecombo's AfterUpdate event procedure:

Me![RoomNamecombo].RowSource = "SELECT [tblRoom].[RoomName] " & _
"FROM [tblRoom] " & _
"WHERE [tblRoom].[SiteName]= " & _
"Forms![frmEquipmentAssessmentEdit]!SiteNamecombo"
Me![RoomNamecombo].Requery

Hope that helps.
Sprinks
 
Kevin,

Sorry, my earlier post has an error in it. It should not contain quotes
around the reference to the SiteNamecombo value, which already evaluates to a
string. It should read:

Me![RoomNamecombo].RowSource = "SELECT [tblRoom].[RoomName] " & _
"FROM [tblRoom] " & _
"WHERE [tblRoom].[SiteName]= " & _
Forms![frmEquipmentAssessmentEdit]!SiteNamecombo
Me![RoomNamecombo].Requery

Sprinks
 
Sprinks,

This does not seem to work as expected. When I open the form and select the
SiteNamecombo, Access requests a parameter for the RoomNamecombo. Would this
be due to the fact that the underlying table for Room field has a lookup to
the table Room?

If I make the field text, would this solve the problem?

Kevin
--
Kevin


Sprinks said:
Kevin,

Sorry, my earlier post has an error in it. It should not contain quotes
around the reference to the SiteNamecombo value, which already evaluates to a
string. It should read:

Me![RoomNamecombo].RowSource = "SELECT [tblRoom].[RoomName] " & _
"FROM [tblRoom] " & _
"WHERE [tblRoom].[SiteName]= " & _
Forms![frmEquipmentAssessmentEdit]!SiteNamecombo
Me![RoomNamecombo].Requery

Sprinks


kmhnhsuk said:
I have a form in edit mode with two combo boxes, one is RoomNamecombo and the
other is SiteNamecombo.

A site can have many rooms, so when selecting from SiteNamecombo, I want the
RoomNamecombo box to refresh to the corrent selection of rooms for that site.

The code I have in the RoomNamecombo row source is:

SELECT [tblRoom].[RoomName] FROM [tblRoom] WHERE [tblRoom].[SiteName]
=Forms![frmEquipmentAssessmentEdit]!SiteNamecombo

I also have the following code:

Private Sub SiteNamecombo_AfterUpdate()

Me.RoomNamecombo = Null
Me.RoomNamecombo.Requery

End Sub

This all works fine in the form that is in Data Entry mode but does not seem
to work for the form in Edit mode.
Click to expand...
Click to expand...
 
I've not developed a body of knowledge on lookup fields because I was advised
early on that they are a horrific mess of a misfeature, so I can't say for
sure. But I would advise you not to use them in the future, and certainly
try adding a new text field, and running an Update query to copy the existing
field data to the new field, before deleting the old one.

And as always when changing the structure of an existing data, be sure it is
backed up first.

Hope that helps.
Sprinks

kmhnhsuk said:
Sprinks,

This does not seem to work as expected. When I open the form and select the
SiteNamecombo, Access requests a parameter for the RoomNamecombo. Would this
be due to the fact that the underlying table for Room field has a lookup to
the table Room?

If I make the field text, would this solve the problem?

Kevin
--
Kevin


Sprinks said:
Kevin,

Sorry, my earlier post has an error in it. It should not contain quotes
around the reference to the SiteNamecombo value, which already evaluates to a
string. It should read:

Me![RoomNamecombo].RowSource = "SELECT [tblRoom].[RoomName] " & _
"FROM [tblRoom] " & _
"WHERE [tblRoom].[SiteName]= " & _
Forms![frmEquipmentAssessmentEdit]!SiteNamecombo
Me![RoomNamecombo].Requery

Sprinks


kmhnhsuk said:
I have a form in edit mode with two combo boxes, one is RoomNamecombo and the
other is SiteNamecombo.

A site can have many rooms, so when selecting from SiteNamecombo, I want the
RoomNamecombo box to refresh to the corrent selection of rooms for that site.

The code I have in the RoomNamecombo row source is:

SELECT [tblRoom].[RoomName] FROM [tblRoom] WHERE [tblRoom].[SiteName]
=Forms![frmEquipmentAssessmentEdit]!SiteNamecombo

I also have the following code:

Private Sub SiteNamecombo_AfterUpdate()

Me.RoomNamecombo = Null
Me.RoomNamecombo.Requery

End Sub

This all works fine in the form that is in Data Entry mode but does not seem
to work for the form in Edit mode.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top