Combo Box RowSource Problem

[Guest]

With the code below I'm trying to change the row source for a combo box on a
form. Problem is that when Case Is = 2, I get the correct record set but when
I click on an item in the list it displays a different record. If the record
I click on is, for example, Medtronic PacerX, I get Medtronic PacerY or Z or
something other than the selection I made.

Any help would be greatly appreciated. Thanks, Rob
*********************************************************
Private Sub cmdMedtronicFilter_Click()
On Error GoTo cmdMedtronicFilter_Click_Error

Dim DeviceType As String

DeviceType = Nz(Me.optGroupDeviceType, "")


Select Case DeviceType

Case Is = 1

Me.cboPacerExplant.RowSource = "SELECT * FROM qryPacersLU " & _
"WHERE fldManufacturerNo =('2') " & "ORDER BY fldModelName"


Case Is = 2

Me.cboPacerExplant.RowSource = "SELECT * FROM qryICDlu " & _
"WHERE fldManufacturerNo = ('2') " & "ORDER BY fldModelName"

End Select
On Error GoTo 0
Exit Sub

cmdMedtronicFilter_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdMedtronicFilter_Click of VBA Document Form_frmDevices"

End Sub

 

[Bob Hairgrove]

With the code below I'm trying to change the row source for a combo box on a
form. Problem is that when Case Is = 2, I get the correct record set but when
I click on an item in the list it displays a different record. If the record
I click on is, for example, Medtronic PacerX, I get Medtronic PacerY or Z or
something other than the selection I made.

Any help would be greatly appreciated. Thanks, Rob
*********************************************************
Private Sub cmdMedtronicFilter_Click()
On Error GoTo cmdMedtronicFilter_Click_Error

Dim DeviceType As String

DeviceType = Nz(Me.optGroupDeviceType, "")


Select Case DeviceType

Case Is = 1

Me.cboPacerExplant.RowSource = "SELECT * FROM qryPacersLU " & _
"WHERE fldManufacturerNo =('2') " & "ORDER BY fldModelName"


Case Is = 2

Me.cboPacerExplant.RowSource = "SELECT * FROM qryICDlu " & _
"WHERE fldManufacturerNo = ('2') " & "ORDER BY fldModelName"

End Select
On Error GoTo 0
Exit Sub

cmdMedtronicFilter_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdMedtronicFilter_Click of VBA Document Form_frmDevices"

End Sub

First of all, DeviceType is a string, so you should be comparing it to a string:
Case Is = "1"
However, the value of optGroupDeviceType is numeric anyway, so you should
probably just declare DeviceType as Long and continue using the present "Case"
syntax. Also, if optGroupDeviceType has a default value, you don't need to wrap
the statement which initializes DeviceType in the Nz() function.

Next, you should add this line after the "End Select" statement:

Me.cboPacerExplant.Requery

Also, it isn't a good idea to hard code literals such as the value '2' inthe
criteria "WHERE fldManufacturerNo = ('2')". This will cause problems when the
manufacturer number changes, or when you need to use a different manufacturer.
If this value is contained in the form's recordset, or in a control on your
form, you should probably be using that.

At the very least, if this value is not likely to change anytime soon, I would
assign it to a constant and keep it in a separate module. I usually have all of
my constants in one module by themselves so that if I do have to change any of
them, I don't have to hunt all of them down in several different modules.Using
constants has the significant additional advantage that you can use the
IntelliSense feature to pick them from a list when you are writing code, and you
can choose a descriptive name which helps your code become self-documenting.

 

[Guest]

Bob, thanks for your suggestions. I implemented them but I still have the
same problem as I said in my first post,

"Problem is that when Case Is = 2, I get the correct record set but when I
click on an item in the list it displays a different record. If the record in
the lsit clicked on is, for example, Medtronic PacerX, I get Medtronic PacerY
or Z or something other than the selection I made."

Any help you can provide with this problem is greatly appreciated. Thanks, Rob

 

[Bob Hairgrove]

Bob, thanks for your suggestions. I implemented them but I still have the
same problem as I said in my first post,

"Problem is that when Case Is = 2, I get the correct record set but when I
click on an item in the list it displays a different record. If the record in
the lsit clicked on is, for example, Medtronic PacerX, I get Medtronic PacerY
or Z or something other than the selection I made."

Any help you can provide with this problem is greatly appreciated. Thanks, Rob

Can you please post the SQL text of the queries "qryPacersLU" and "qryICDlu"?
Also, it would help to know what the underlying tables look like (especially
data types, constraints and default values). Post the DDL for the tables if you
can, please.

 

[AccessVandal via AccessMonster.com]

Hi Rob,

RobUCSD wrote:

Dim DeviceType As String

DeviceType = Nz(Me.optGroupDeviceType, "")

Why convert to string? I don't see any good reasons.
Would it be better to use the control directly? Like..
"Select Case Me.optGroupDeviceType"

Select Case DeviceType

Case Is = 1

Try using "Case 1" instead of "Case Is = 1"

Me.cboPacerExplant.RowSource = "SELECT * FROM qryPacersLU " & _
"WHERE fldManufacturerNo =('2') " & "ORDER BY fldModelName"


Case Is = 2

Me.cboPacerExplant.RowSource = "SELECT * FROM qryICDlu " & _
" & "ORDER BY fldModelName"

And the rowsource, remove the curve brackets in the "WHERE fldManufacturerNo
= ('2') "
to "WHERE fldManufacturerNo = '2' " if it is Text, if number "WHERE
fldManufacturerNo = 2 "