How to search on numbers via dropdown box to return results in subform

CLCoop

Board Regular
Joined
May 30, 2018
Messages
56
I've created the query on InvoiceID which is a large number. I have already created the search drop down box showing numbers in order. However when I select the number the Subform blanks out (there is data) rather than showing the results of the InvoiceId. I have used this code on other searches but I think my challenge is how to use it for NUMBERS rather than text. I tried to convert the numbers into text using a query however didn't seem to work either...

FormName: Invoice Search by Combobox
Drop down box located on the Invoice Search by Combobox form: INVOICEID is equal to Name: InvoiceIDcbo with Data, RowSource is set to: InvoiceID Number Search by Combox
Query: LoanInvoiceALLQ Field: InvoiceID (large number value)
Subform: LoaninvoiceALL that does have the field: InvoiceID (large number value)

Added the drop down box to execute after update as follows (InvoiceIDcbo):
Private Sub InvoiceIDcbo_AfterUpdate()
Dim myInvoiceID As String
myInvoiceID = "Select * from LoanInvoiceALLQ where ([InvoiceID] = ' & Me.InvoiceIDcbo & ')" (think this is where my error is!)
'myInvoice = "Select * from LoanInvoiceALLQ where ([InvoiceID] = '" & Me.InvoiceIDcbo & "')" (I also tried this but this is for text not numbers).
Me.LoanInvoiceALL.Form.RecordSource = myInvoiceID (this is where it errors out)
Me.LoanInvoiceALL.Form.Requery


Me.cboBPA = Null
Me.CboCallOrder = Null


Me.cmdInvoiceId.Visible = False
Me.cmdInvoiceId.Visible = True


End Sub


Thanks for your special talents!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
'myInvoice = "Select * from LoanInvoiceALLQ where ([InvoiceID] = '" & Me.InvoiceIDcbo & "')" (I also tried this but this is for text not numbers).

I'm not sure why you commented that out. It is correct for numbers (i.e, with numeric InvoiceIDs you would not use the single quotes, while with text invoice IDs you would use single quotes).

Note that when we speak of invoiceIDs as Text or Numeric, we mean the underlying data type. A numeric value can be stored as a number 123, or as Text '123'. You can't know which it is by looking at a value as displayed in a form or query (or in a MrExcel post for that matter).
 
Upvote 0
I'm not sure why you commented that out. It is correct for numbers (i.e, with numeric InvoiceIDs you would not use the single quotes, while with text invoice IDs you would use single quotes).

Note that when we speak of invoiceIDs as Text or Numeric, we mean the underlying data type. A numeric value can be stored as a number 123, or as Text '123'. You can't know which it is by looking at a value as displayed in a form or query (or in a MrExcel post for that matter).

Neither of these two worked. My InvoiceID is a number, is there another work around?

myInvoice = "Select * from LoanInvoiceALLQ where ([InvoiceID] = '" & Me.InvoiceIDcbo & "')"
myInvoiceID = "Select * from LoanInvoiceALLQ where ([InvoiceID] = ' & Me.InvoiceIDcbo & ')"
 
Upvote 0
myInvoice = "Select * from LoanInvoiceALLQ where ([InvoiceID] = '" & Me.InvoiceIDcbo & "')"
myInvoiceID = "Select * from LoanInvoiceALLQ where ([InvoiceID] = ' & Me.InvoiceIDcbo & ')"

That second one is invalid syntax.
You probably meant:
myInvoiceID = "Select * from LoanInvoiceALLQ where ([InvoiceID] = " & Me.InvoiceIDcbo & ")"

YOu should also be confirming the value of Me.InvoiceIDcbo and in general all variables when you test-run this:
Code:
msgbox me.InvoiceIDcbo
msgbox myInvoice
msgbox myInvoiceID
 
Last edited:
Upvote 0
YEA THAT WORKED GREAT and I put in the recommend MSG on all my searches, guess the user will need that part more than me... now just have to figure out how to take the search results and put them into a report, I have the button made and it comes up but comes up empty...

Private Sub cmdInvoiceId_Click()
Dim sql As String


DoCmd.Openreport "InvoiceList", acViewNormal
Call InvoiceList.LoanInvoiceALLQ(Me!InvoiceList.Form.RecordSource)


Me.Recordset = Forms!LoanInvoiceALL.Recordset
End Sub
 
Upvote 0
Going line by line in the above I can't really know.

The first line opens the report (so whatever the report is based on is what you get at that point).

The other lines I'm not sure what they are for or what the are expected to do (except of course for the obvious general features - the first does something using the Form recordset, and the second sets the (currentform?) recordset to a new recordset - but not sure how those things would be expected to affect a report).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top