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

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
'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

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
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:

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
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:

Forum statistics

Threads
1,086,133
Messages
5,388,022
Members
402,097
Latest member
byorj

Some videos you may like

This Week's Hot Topics

Top