conditional .rowsource and .value in combo boxes

[shadowsong]

I have two combo boxes that get their rowsources via a select query on
enter. currently, the first one gives company names, and the second
one gives salesperson names for whichever company was selected.

I would like the second one to populate with all salesperson names if
company is blank on enter, and set company name to the one associated
with the selected salesperson, if company name is blank on exit.

Here's what I tried:

Private Sub SALESNAME_Enter()
If Forms![SALESPERSON OUTPUT]![DLRNUM].Value Is Not Null _
Then Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA WHERE [DATA].[DLRNUM]
= FORMS![SALESPERSON OUTPUT]![DLRNUM]" _
Else Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA"
End Sub

Private Sub SALESNAME_Exit(Cancel As Integer)
If Forms![SALESPERSON OUTPUT]![DLRNUM] Is Null _
And Forms![SALESPERSON OUTPUT]![SALESNAME] Is Not Null _
Then Forms![SALESPERSON OUTPUT]![DLRNUM].Value = _
"SELECT DISTINCT [DATA].[DLRNUM] FROM DATA WHERE [DATA].[SALESNAME]
= Forms![SALESPERSON OUTPUT]![SALESNAME]"
End Sub

It gives me an "object required" runtime error and highlights from
"if" to "then", for whichever sub i trigger. Which object is it
talking about? Did I forget to define something?

 

[Douglas J. Steele]

In VBA, you need to use the IsNull function:

Private Sub SALESNAME_Enter()

If IsNull(Forms![SALESPERSON OUTPUT]![DLRNUM].Value) Then _
Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA WHERE [DATA].[DLRNUM]
= FORMS![SALESPERSON OUTPUT]![DLRNUM]" _
Else
Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA"
End If

End Sub

 

[shadowsong]

In VBA, you need to use the IsNull function:

Private Sub SALESNAME_Enter()

If IsNull(Forms![SALESPERSON OUTPUT]![DLRNUM].Value) Then _
Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA WHERE [DATA].[DLRNUM]
= FORMS![SALESPERSON OUTPUT]![DLRNUM]" _
Else
Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA"
End If

End Sub

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have two combo boxes that get their rowsources via a select query on
enter. currently, the first one gives company names, and the second
one gives salesperson names for whichever company was selected.

I would like the second one to populate with all salesperson names if
company is blank on enter, and set company name to the one associated
with the selected salesperson, if company name is blank on exit.

Here's what I tried:

Private Sub SALESNAME_Enter()
If Forms![SALESPERSON OUTPUT]![DLRNUM].Value Is Not Null _
Then Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA WHERE [DATA].[DLRNUM]
= FORMS![SALESPERSON OUTPUT]![DLRNUM]" _
Else Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource = _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA"
End Sub

Private Sub SALESNAME_Exit(Cancel As Integer)
If Forms![SALESPERSON OUTPUT]![DLRNUM] Is Null _
And Forms![SALESPERSON OUTPUT]![SALESNAME] Is Not Null _
Then Forms![SALESPERSON OUTPUT]![DLRNUM].Value = _
"SELECT DISTINCT [DATA].[DLRNUM] FROM DATA WHERE [DATA].[SALESNAME]
= Forms![SALESPERSON OUTPUT]![SALESNAME]"
End Sub

It gives me an "object required" runtime error and highlights from
"if" to "then", for whichever sub i trigger. Which object is it
talking about? Did I forget to define something?

That was exactly what I needed. However, it's now not working for two
new and different reasons.


Private Sub SALESNAME_Enter()
If Not IsNull(Forms![SALESPERSON OUTPUT]![DLRNUM].Value) _
Then Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource
= _
"SELECT DISTINCT [DATA].[SALESNAME] FROM DATA WHERE
[DATA].[DLRNUM] = FORMS![SALESPERSON OUTPUT]![DLRNUM]" _
Else Forms![SALESPERSON OUTPUT]![SALESNAME].RowSource
_
= "SELECT DISTINCT [DATA].[SALESNAME] FROM DATA"
End Sub

SALESNAME doesn't populate properly until DLRNUM has been clicked in
and out of first. Selecting SALESNAME immediately after opening the
form results in a blank dropdown.


Private Sub SALESNAME_Exit(Cancel As Integer)
If IsNull(Forms![SALESPERSON OUTPUT]![DLRNUM]) _
And Not IsNull(Forms![SALESPERSON OUTPUT]![SALESNAME]) _
Then Forms![SALESPERSON OUTPUT]![DLRNUM].Value = _
"SELECT DISTINCT [DATA].[DLRNUM] FROM DATA WHERE
[DATA].[SALESNAME] = Forms![SALESPERSON OUTPUT]![SALESNAME]"
End Sub

Selecting a SALESNAME (after clicking on DLRNUM to make it populate)
and then trying to select a DLRNUM results in an error, "The value you
entered isn't valid for this field". The sql query should only return
a single number, but it's possible that the number is considered a
"record" and thus isn't a valid single value. I'm not sure how to fix
that, though. Also, the default rowsource for the DLRNUM box includes
both number and name. Only number shows up when you've selected an
entry, so I only included number in the sql code, but that could be
related to the problem I'm having here.