CODEWITHSUNDEEP
vbams-access
Dennis
Dennis

Reputation: 33

Combo Box depends on another combo box

I have a form with two combo boxes, one Wards the other room number. Wards are medical units, while the rooms are room numbers (like GMU-01).

I'm trying to limit the room names based on the wards value, ie list all the bed numbers for a particular unit.

SELECT DISTINCT [TblWards].[Wards] FROM TblWards ORDER BY [TblWards].[Wards];

The row source from the Wards combo box (First box)

Private Sub Wards_AfterUpdate()
Dim txt As String
txt = "SELECT TblWards.Room FROM TblWards WHERE (TblWards.Wards)= '" & 
Me.Wards.Column(0) & "' ORDER BY TblWards.Room;"
Me.RoomN.RowSource = txt
End Sub

SELECT [TblWards].[Room] FROM TblWards WHERE ((([TblWards].[Wards])=AMU));

The row source from the second combo box RoomN

I get an error when I attempt to choose a value from the Wards combo box. If I line out the afterupdate code, I can choose a value. The error is unexpected error, access needs to shut down.

Then, I get an error if I attempt to select a value from the second combobox. asking for the AMU parameter.

I inherited this code and trying to determine how to go about it, rewrite or try to salvage it.

Upvotes: 0

Views: 303

Answers (2)

mukeshesic
mukeshesic

Reputation: 77

Try Following-

  1. Clear ControlSource and RowSorce Property of comboBox 'RoomN' Manually.

  2. Try following code in AfterEvent of 'Wards' comboBox

    Private Sub Wards_AfterUpdate()
Dim txt As String
txt = "SELECT Room FROM TblWards WHERE [Wards] = '" & Me.Wards.Value & "' ORDER BY Room;"
Me.RoomN.RowSource = txt
End Sub

Upvotes: 0

Erik Loebl
Erik Loebl

Reputation: 66

from what I understand from your message, you can try this:

Private Sub cboWard_AfterUpdate() Dim strSQL As String

strSQL = "SELECT * FROM tblRooms WHERE rWardID = " & Me.cboWard

Me.cboRoom.RowSource = strSQL

End Sub

Upvotes: 0

Related Questions