Link to home
Start Free TrialLog in
Avatar of UTSWPeds
UTSWPedsFlag for United States of America

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)


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

SOLUTION
Avatar of rgn2121
rgn2121
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also...do you have anything in the On_Change event of that control...?
Avatar of UTSWPeds

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 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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Didn't know the on change event on a combo box triggered in the manner it does.  Removing this event fixed the problem.