By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,589 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,589 IT Pros & Developers. It's quick & easy.

Searching on a form using two combo boxes

P: 75
I am using Microsoft Access 2010.I am trying to perform a search in a form (this form is a continuous form) using two combo boxes: a combo box called ClientID and another combo box called PremiumInvoiceNumber. No result is returned after selecting the two combo boxes. Can you tell me what I am doing wrong? Thank you in advance for your help.

Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim ClientID, PremiumInvoiceNumber As String
  3. Dim task, strCriteria As String
  4.  
  5. If IsNull(Me.CboClientID) Then
  6.     ClientID = "[ClientID] like '*'"
  7. Else
  8.     ClientID = "[ClientID] = " & Me.CboClientID
  9. End If
  10.  
  11. If IsNull(Me.CboPremiumInvoiceNumber) Then
  12.     PremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
  13. Else
  14.     PremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
  15. End If
  16. strCriteria = ClientID & "And" & PremiumInvoiceNumber
  17.     task = "Select * from frmBillingInvoicePremiums where " & strCriteria
  18.     Me.frmInvoicePremiumsSub.Form.RecordSource = task
  19.     Me.frmInvoicePremiumsSub.Form.Requery
  20.  
  21. End Function
Aug 12 '15 #1

✓ answered by jforbes

Do you have a function named PremiumInvoiceNumber() and is it defined as Public? If you don't have a function by that name, then you would get the error you are getting.

Did you ever find out if there was anything in the task variable, or have you fixed that and moved on?

Share this Question
Share on Google+
8 Replies


jforbes
Expert 100+
P: 1,107
Line 16 should have some spaces around And:
Expand|Select|Wrap|Line Numbers
  1. strCriteria = ClientID & " And " & PremiumInvoiceNumber 
Aug 12 '15 #2

P: 75
Thanks, jforbes, but the no result is returned after selecting the two combo boxes. I have changed the field name from ClientID to ClientName.

Some more background: the subform called frmInvoicePremiumsSub,is set as a continuous form and it is referencing a query called queryBillingsPremium.

The code is shown below: -

Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim strClientName, strPremiumInvoiceNumber As String
  3. Dim task, strCriteria As String
  4.  
  5. If IsNull(Me.CboClientName) Then
  6.     strClientName = "[ClientName] like '*'"
  7. Else
  8.     strClientName = "[ClientName] = '" & Me.CboClientName & "'"
  9. End If
  10.  
  11. If IsNull(Me.CboPremiumInvoiceNumber) Then
  12.     strPremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
  13. Else
  14.     strPremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
  15. End If
  16. strCriteria = strClientName & " And " & strPremiumInvoiceNumber
  17.     task = "Select * from queryBillingsPremium where " & strCriteria
  18.     Me.frmInvoicePremiumsSub.Form.RecordSource = task
  19.     Me.frmInvoicePremiumsSub.Form.Requery
  20.  
  21. End Function
Aug 12 '15 #3

jforbes
Expert 100+
P: 1,107
What I do when I get into the situation you are in:
  • Put a breakpoint on line 18. (Debug|Toggle Breakpoint)
  • Run the code and do whatever needs to be done to have Access Break on line 18.
  • Ctrl-G to open up the Immediate Window.
  • Type "?task" into the Immediate Window and press Enter.
  • Take a look the SQL that is returned and see if there is anything odd.
  • If nothing stands out, copy the SQL onto the Clipboard.
  • Open a new Query.
  • Switch to SQL View.
  • Paste the SQL from the Immediate Window into the Query.
  • Attempt to run the Query and see what happens. Often, Access will give an error and highlight what it thinks is wrong.
  • At this point the SQL can be messed with to test out different possible solutions.
  • Also, the Query can be flipped back and forth into Design View to use the QBE Editor.
Aug 12 '15 #4

P: 75
Hi jforbes,

I added a breakpoint at line 18 and no errors occurred.
I followed your suggestion to type "?task' in the Immediate Window and press Enter. No result/SQL was returned in the immediate window.
I rebuilt a new form and I added the two search combo boxes. I tried the revised code below but no result returned: -
Expand|Select|Wrap|Line Numbers
  1. Function SearchCriterica()
  2. Dim myClientName As String
  3.  Dim myBillingID As Integer
  4. Dim task, strCriteria As String
  5.  
  6. If IsNull(Me.CboClientName) Then
  7.     myClientName = "[ClientName] like '*'"
  8. Else
  9.     myClientName = DLookup("ID", "queryBillingsPremium", "[ClientName] =  #" & Me.CboClientName & "#")
  10. End If
  11.  
  12. If IsNull(Me.CboPremiumInvoiceNumber) Then
  13.     myBillingID = "[BillingID] like '*"
  14. Else
  15.     myBillingID = DLookup("ID", "queryBillingsPremium", "PremiumInvoiceNumber] = #" & Me.CboPremiumInvoiceNumber & "#")
  16. End If
  17. strCriteria = myClientName & " And " & myBillingID
  18.     task = "Select * from queryBillingsPremium  where " & strCriteria
  19.         Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = task
  20.     Me.frmInvoicePremiumBalanceSubform.Form.Requery
  21.  
  22. End Function
Kindly note the following: -

(i) the source code for the ClientName search combo box is
Expand|Select|Wrap|Line Numbers
  1. SELECT [qryBillingsPremium].[ClientName], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID]; 
(ii) the source code for the PremiumInvoiceNumber search combo box is: -
Expand|Select|Wrap|Line Numbers
  1. SELECT [qryBillingsPremium].[PremiumInvoiceNumber], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID]; 
Aug 13 '15 #5

jforbes
Expert 100+
P: 1,107
I wouldn't expect an error to be displayed. Doing all these things in Debug Mode are to just peek into the code and see what is going on.

I'm surprised that there wasn't a value for task, the variable should have something in it since it was set in the line previous to the breakpoint.The only thing I can think of is that the code wasn't running when entering ?task into the Immediate Window. Was the code running when you debug.printed the task variable? Or to make it easier, maybe we should put
Expand|Select|Wrap|Line Numbers
  1. debug.print task
in between line 17 and 18 of the original code.

To backup a step and possibly to help understand the debug functions, you may want to take a look at this from Microsoft: https://support.microsoft.com/en-us/kb/108438
and NeoPa wrote a cool article that has some useful information in it here: http://bytes.com/topic/access/insigh...-debugging-vba
Aug 13 '15 #6

P: 75
hi jforbes, referring to the code in post #3 and following your explanation in post #6, when I enter for example ?PremiumInvoiceNumber("PBM13-01") in the Immediate window and Press Enter, I receive a Compile error: Sub or Function not defined
Aug 14 '15 #7

jforbes
Expert 100+
P: 1,107
Do you have a function named PremiumInvoiceNumber() and is it defined as Public? If you don't have a function by that name, then you would get the error you are getting.

Did you ever find out if there was anything in the task variable, or have you fixed that and moved on?
Aug 17 '15 #8

P: 75
Hi jforbes, I neither had a function named PremiumInvoiceNumber() nor it is defined as a Public variable. There was not anything in the task variable. I am not very good at VBA coding. I am now trying a different approach to accomplish my goal. Instead, I am now using one combo box to search for the Premium Invoice numbers using the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboPremiumInvoiceNumber_AfterUpdate()
  2.     'Moves to PremiumInvoiceNumber field and
  3.     'finds records where "Premium Invoice Number" matches whatever is selected in the combo box
  4.     DoCmd.ShowAllRecords
  5.     Me!PremiumInvoiceNumber.SetFocus
  6.     DoCmd.FindRecord Me!CboPremiumInvoiceNumber
  7.  
  8.     'Set value of combobox equal to an empty string
  9.     Me!CboPremiumInvoiceNumber.Value = ""
  10. End Sub
  11.  
I can now select an invoice number from the combo box and it will bring show all of the records related to this invoice. My only problem now is that I want to print this list of records from the form to a report. I found a link in the forum: http://bytes.com/topic/access/answer...rm-into-report, along with Allen Browne's link http://allenbrowne.com/ser-50.html which explain the method of printing multiple records from a form to a report. I have not had a chance yet to work through the information. If I have any problems, I will ask for some more help.
Aug 17 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.