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

[CLCoop]

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!

 

[xenou]

'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).

 

[CLCoop]

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 & ')"

 

[xenou]

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:

msgbox me.InvoiceIDcbo
msgbox myInvoice
msgbox myInvoiceID

 

[CLCoop]

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

 

[xenou]

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).