UTSWPeds
asked on
Combo box returning null value in Access VBA
I have a combo box (cbo_EditAlloc_Recipient) that I use to select a person from a list (2 columns, bound column is hidden from the user). This is used to drive a second combo box limited to only valued pertaining to the selected person.
WHen I use the mouse to drop down the value and manually select it, everythign works fine. I get a run-time error '3075', however, when I try to select via keystrokes (meaning to go down to someone whose name starts with "S" by keying S, I get the error). The combo's rowsouce is driven by a query. I then use the resulting value of that combo box to drive a dLookup in VBA to limit the rowsource of the second combo box.
I don't understand why the error occurs. I'm attaching my After Update event code, in the event that this is the issue. (I wonder if it could be simply a function of where I have the variable value established in the VBA)
WHen I use the mouse to drop down the value and manually select it, everythign works fine. I get a run-time error '3075', however, when I try to select via keystrokes (meaning to go down to someone whose name starts with "S" by keying S, I get the error). The combo's rowsouce is driven by a query. I then use the resulting value of that combo box to drive a dLookup in VBA to limit the rowsource of the second combo box.
I don't understand why the error occurs. I'm attaching my After Update event code, in the event that this is the issue. (I wonder if it could be simply a function of where I have the variable value established in the VBA)
Private Sub cbo_EditAlloc_Recipient_AfterUpdate()
Dim intProgCount As Integer
Dim strProgramName As String
Dim StrRowSource_SQL As String
intProgCount = DCount("Program", "tbl_Recipient", "[PersonNum] = " & Forms!frm_EditAllocations!cbo_EditAlloc_Recipient)
strProgramName = DLookup("Program", "tbl_Recipient", "[PersonNum] = " & Forms!frm_EditAllocations!cbo_EditAlloc_Recipient)
StrRowSource_SQL = "SELECT tbl_Recipient.Program, tbl_Recipient.PersonNum " & _
"FROM tbl_Recipient GROUP BY tbl_Recipient.Program, tbl_Recipient.PersonNum " & _
"HAVING (((tbl_Recipient.PersonNum)= " & _
[Forms]![frm_EditAllocations]![cbo_EditAlloc_Recipient] & _
"));"
Me.cbo_EditAlloc_Program.Enabled = True
If intProgCount = 1 Then
Me.cbo_EditAlloc_Program.Requery
Me.cbo_EditAlloc_Program.Value = strProgramName
'Purpose updates
Me.cbo_EditAlloc_Purpose.Requery
Me.cbo_EditAlloc_Purpose.SetFocus
Else
Me.cbo_EditAlloc_Program.Value = ""
Me.cbo_EditAlloc_Program.RowSourceType = "Table/Query"
Me.cbo_EditAlloc_Program.RowSource = StrRowSource_SQL
Me.cbo_EditAlloc_Program.SetFocus
End If
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also...do you have anything in the On_Change event of that control...?
ASKER
I have essentially the same code in the on change event. I should have mentioned that. I have no other code on any other events.
I'm suspect you are aware, but the intProgCount variable is performing a count of records from a different table than the rowsource.
I'm suspect you are aware, but the intProgCount variable is performing a count of records from a different table than the rowsource.
ASKER
I changed the Forms!... to Me. and I also set an On Lost Focus property of the initial combo box to check for nulls...
Private Sub cbo_EditAlloc_Recipient_Lo stFocus()
If IsNull(Me.cbo_EditAlloc_Re cipient.Va lue) Then
Me.cbo_EditAlloc_Recipient .SetFocus
End If
End Sub
I'm still getting the error, the debugger takes me right to the first line of the on change property where the DCount is found.
Private Sub cbo_EditAlloc_Recipient_Lo
If IsNull(Me.cbo_EditAlloc_Re
Me.cbo_EditAlloc_Recipient
End If
End Sub
I'm still getting the error, the debugger takes me right to the first line of the on change property where the DCount is found.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Didn't know the on change event on a combo box triggered in the manner it does. Removing this event fixed the problem.