Searching on a form using two combo boxes

[wirejp]

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 PremiumInvoiceN umber. 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.

Function SearchCriterica()
Dim ClientID, PremiumInvoiceNumber As String
Dim task, strCriteria As String

If IsNull(Me.CboClientID) Then
    ClientID = "[ClientID] like '*'"
Else
    ClientID = "[ClientID] = " & Me.CboClientID
End If

If IsNull(Me.CboPremiumInvoiceNumber) Then
    PremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
Else
    PremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
End If
strCriteria = ClientID & "And" & PremiumInvoiceNumber
    task = "Select * from frmBillingInvoicePremiums where " & strCriteria
    Me.frmInvoicePremiumsSub.Form.RecordSource = task
    Me.frmInvoicePremiumsSub.Form.Requery
    
End Function

[jforbes]

Line 16 should have some spaces around And:

strCriteria = ClientID & [iCODE]" And "[/iCODE] & PremiumInvoiceNumber

[wirejp]

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 frmInvoicePremi umsSub,is set as a continuous form and it is referencing a query called queryBillingsPr emium.

The code is shown below: -

Function SearchCriterica()
Dim strClientName, strPremiumInvoiceNumber As String
Dim task, strCriteria As String

If IsNull(Me.CboClientName) Then
    strClientName = "[ClientName] like '*'"
Else
    strClientName = "[ClientName] = '" & Me.CboClientName & "'"
End If

If IsNull(Me.CboPremiumInvoiceNumber) Then
    strPremiumInvoiceNumber = "[PremiumInvoiceNumber] like '*'"
Else
    strPremiumInvoiceNumber = "[PremiumInvoiceNumber] = '" & Me.CboPremiumInvoiceNumber & "'"
End If
strCriteria = strClientName & " And " & strPremiumInvoiceNumber
    task = "Select * from queryBillingsPremium where " & strCriteria
    Me.frmInvoicePremiumsSub.Form.RecordSource = task
    Me.frmInvoicePremiumsSub.Form.Requery
    
End Function

[jforbes]

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.

[wirejp]

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: -

Function SearchCriterica()
Dim myClientName As String
 Dim myBillingID As Integer
Dim task, strCriteria As String

If IsNull(Me.CboClientName) Then
    myClientName = "[ClientName] like '*'"
Else
    myClientName = DLookup("ID", "queryBillingsPremium", "[ClientName] =  #" & Me.CboClientName & "#")
End If

If IsNull(Me.CboPremiumInvoiceNumber) Then
    myBillingID = "[BillingID] like '*"
Else
    myBillingID = DLookup("ID", "queryBillingsPremium", "PremiumInvoiceNumber] = #" & Me.CboPremiumInvoiceNumber & "#")
End If
strCriteria = myClientName & " And " & myBillingID
    task = "Select * from queryBillingsPremium  where " & strCriteria
        Me.frmInvoicePremiumBalanceSubform.Form.RecordSource = task
    Me.frmInvoicePremiumBalanceSubform.Form.Requery

End Function

Kindly note the following: -

(i) the source code for the ClientName search combo box is

SELECT [qryBillingsPremium].[ClientName], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID];

(ii) the source code for the PremiumInvoiceN umber search combo box is: -

SELECT [qryBillingsPremium].[PremiumInvoiceNumber], [qryBillingsPremium].[BillingID] FROM qryBillingsPremium ORDER BY [BillingID];

[jforbes]

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

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

[wirejp]

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

[jforbes]

Do you have a function named PremiumInvoiceN umber() 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?

[wirejp]

Hi jforbes, I neither had a function named PremiumInvoiceN umber() 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:

Private Sub CboPremiumInvoiceNumber_AfterUpdate()
    'Moves to PremiumInvoiceNumber field and
    'finds records where "Premium Invoice Number" matches whatever is selected in the combo box
    DoCmd.ShowAllRecords
    Me!PremiumInvoiceNumber.SetFocus
    DoCmd.FindRecord Me!CboPremiumInvoiceNumber

    'Set value of combobox equal to an empty string
    Me!CboPremiumInvoiceNumber.Value = ""
End Sub

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.