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)


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

Open in new window

UTSWPedsBudget ManagerAsked:
Who is Participating?
 
UTSWPedsBudget ManagerAuthor Commented:
I removed the on change events from the combo boxes and they appear to be working now.
0
 
rgn2121Commented:
First of, if you are in the after update of the control, you can use me.cbo_editAlloc_Recipient.Value instead of the Forms!frm_EditAllocations!cbo_EditAlloc_Recipient.

Also, I would be sure and check for a null value before doing a lookup...

if not IsNull(me.cbo_editAlloc_Recipient.Value) then
....

0
 
rgn2121Commented:
Also...do you have anything in the On_Change event of that control...?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
UTSWPedsBudget ManagerAuthor Commented:
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.
0
 
UTSWPedsBudget ManagerAuthor Commented:
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_LostFocus()
  If IsNull(Me.cbo_EditAlloc_Recipient.Value) 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.
0
 
UTSWPedsBudget ManagerAuthor Commented:
Didn't know the on change event on a combo box triggered in the manner it does.  Removing this event fixed the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.