Solved

Combo box returning null value in Access VBA

Posted on 2011-03-18
6
774 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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