Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combo box returning null value in Access VBA

Posted on 2011-03-18
6
Medium Priority
?
882 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 400 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Simple Linear Regression
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

704 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