Button Command Not Linking to Combo Box

[EddieLampert]

Good day,

I have a button in a form that is supposed to link to a combo box. In the
combo box, I have "sectors" pulled from a table. Each sector has many
companies, so I want it to where I hit the button and pulls up all the
companies that are in the sector that is displayed in the combo box. Here is
the code. Any help is appreciated.

Private Sub cmdGetSectors_Click()
On Error GoTo Err_cmdGetSectors_Click
Dim varWhere As Variant
Dim rst As DAO.Recordset
Const qts As String = ""
If Not IsNull(Me.comSectors) Then
varWhere = "[Target Sector]=" & qts & Me![comSectors] & qts
DoCmd.OpenForm "frmDeals", acFormDS, , varWhere
Forms!frmDeals.SetFocus
Else
MsgBox "Please select a sector.", vbCritical, gstrAppTitle
Exit Sub
End If
Exit_cmdGetSectors_Click:
Exit Sub

Err_cmdGetSectors_Click:
If Err.Number = 2501 Then Resume Exit_cmdGetSectors_Click
MsgBox Err.Description
Resume Exit_cmdGetSectors_Click
End Sub


Private Sub Detail_Click()

End Sub

Private Sub GetSectors_Click()

End Sub

 

[Dirk Goldgar]

Good day,

I have a button in a form that is supposed to link to a combo box. In the
combo box, I have "sectors" pulled from a table. Each sector has many
companies, so I want it to where I hit the button and pulls up all the
companies that are in the sector that is displayed in the combo box. Here
is
the code. Any help is appreciated.

Private Sub cmdGetSectors_Click()
On Error GoTo Err_cmdGetSectors_Click
Dim varWhere As Variant
Dim rst As DAO.Recordset
Const qts As String = ""
If Not IsNull(Me.comSectors) Then
varWhere = "[Target Sector]=" & qts & Me![comSectors] & qts
DoCmd.OpenForm "frmDeals", acFormDS, , varWhere
Forms!frmDeals.SetFocus
Else
MsgBox "Please select a sector.", vbCritical, gstrAppTitle
Exit Sub
End If
Exit_cmdGetSectors_Click:
Exit Sub

Err_cmdGetSectors_Click:
If Err.Number = 2501 Then Resume Exit_cmdGetSectors_Click
MsgBox Err.Description
Resume Exit_cmdGetSectors_Click
End Sub


Private Sub Detail_Click()

End Sub

Private Sub GetSectors_Click()

End Sub

You don't say exactly what is happening -- nothing opens, all records appear
unfiltered, an error message appears, a parameter prompt appears, or what.
Please clarify.

The only obvious thing I see is your declaration of the constant qts:

Const qts As String = ""

That is defining qts as a zero-length string, where I would expect it to be
a double-quote (") character. To represent a double-quote character in a
string literal, you have to double-up the quotes in the literal, so your
declaration would be:

Const qts As String = """"

Yes, that's four quotes in a row.

That may or may not fix your problem. Please post back to let us know.

 

[EddieLampert]

Dirk,

I included your four quote idea. Same result, which is nothing opens. No
error message, just nothing happens. Thanks again.

Good day,

I have a button in a form that is supposed to link to a combo box. In the
combo box, I have "sectors" pulled from a table. Each sector has many
companies, so I want it to where I hit the button and pulls up all the
companies that are in the sector that is displayed in the combo box. Here
is
the code. Any help is appreciated.

Private Sub cmdGetSectors_Click()
On Error GoTo Err_cmdGetSectors_Click
Dim varWhere As Variant
Dim rst As DAO.Recordset
Const qts As String = ""
If Not IsNull(Me.comSectors) Then
varWhere = "[Target Sector]=" & qts & Me![comSectors] & qts
DoCmd.OpenForm "frmDeals", acFormDS, , varWhere
Forms!frmDeals.SetFocus
Else
MsgBox "Please select a sector.", vbCritical, gstrAppTitle
Exit Sub
End If
Exit_cmdGetSectors_Click:
Exit Sub

Err_cmdGetSectors_Click:
If Err.Number = 2501 Then Resume Exit_cmdGetSectors_Click
MsgBox Err.Description
Resume Exit_cmdGetSectors_Click
End Sub


Private Sub Detail_Click()

End Sub

Private Sub GetSectors_Click()

End Sub

You don't say exactly what is happening -- nothing opens, all records appear
unfiltered, an error message appears, a parameter prompt appears, or what.
Please clarify.

The only obvious thing I see is your declaration of the constant qts:

Const qts As String = ""

That is defining qts as a zero-length string, where I would expect it to be
a double-quote (") character. To represent a double-quote character in a
string literal, you have to double-up the quotes in the literal, so your
declaration would be:

Const qts As String = """"

Yes, that's four quotes in a row.

That may or may not fix your problem. Please post back to let us know.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

I included your four quote idea. Same result, which is nothing opens. No
error message, just nothing happens. Thanks again.

I notice in your posted code that you have two event procedures with very
similar names: cmdGetSectors_Click, with all the code we've been looking
at, and GetSectors_Click, which is an empty procedure. What is the actual
name of your command button? Which of these procedures is being executed
when you click it?

 

[EddieLampert]

The name of the command button is "cmdGetSectors". Thanks again.

 

[Dirk Goldgar]

The name of the command button is "cmdGetSectors". Thanks again.

In that case, I would first make sure that the button's OnClick property is
set to "[Event Procedure]". Then, if it is, I would set a breakpoint in the
event procedure, open the form and click the button, and see what happens.
I'd step through the code from the breakpoint to see what lines of code get
executed and what the values of the variables are.