Solved

Combo box returning null value in Access VBA

Posted on 2011-03-18
6
801 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:UTSWPeds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 12

Assisted Solution

by:rgn2121
rgn2121 earned 100 total points
ID: 35167345
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
 
LVL 12

Expert Comment

by:rgn2121
ID: 35167371
Also...do you have anything in the On_Change event of that control...?
0
 

Author Comment

by:UTSWPeds
ID: 35167387
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:UTSWPeds
ID: 35168085
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
 

Accepted Solution

by:
UTSWPeds earned 0 total points
ID: 35168560
I removed the on change events from the combo boxes and they appear to be working now.
0
 

Author Closing Comment

by:UTSWPeds
ID: 35196729
Didn't know the on change event on a combo box triggered in the manner it does.  Removing this event fixed the problem.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question