VBA Access - Finding a record based on user input

[miow]

Hi there

I am a newbie trying to be adventurous with my Access database...my query is how to locate a record based on user input.

Details:

I have a table with customer information with each customer assigned a unique customer ID number.

I want the user to be able to search through the table and locate records based on this unique ID number.

So far I have created a form with a command box and am trying to figure out how to write the code. I would like to do something like this:

1. Create an input box using the Inputbox function (I have done the code for this already)
2. Store the input the user gives for the customer number...I am not sure how to store the input in my code
3. Find the customer record based on the ID number
4. Open the customers form showing the correct record retrieved.

I understand that there is a findrecord method in VBA but couldn't find any information about this in the object browser....

Thanks in advance...thanks for helping out a newbie

Cheers!

 

[fneily]

Replace your field and tablenames where appropriate.
Create a small popup form (In my example, I called it Customer_Input_Name) with a title and a combobox showing your CustomerIDs. The RowSource of the combobox, place:

SELECT Distinct [CustomerID] FROM [CustomerTableName] order by [CustomerID];

Click in the box next to AfterUpdate for the combobox. You will see a button with three dots. Click this button and click Code Builder. Do the following:
Then on the AfterUpdate event of the combobox placethe Sub and End Sub will already be there)
Private Sub comboboxname_AfterUpdate()
Dim stDocName As String
stDocName = "Customer_Form"
DoCmd.OpenForm stDocName
End Sub

Create a query with the fields you want to show from Customers. Under the field CustomerID on the criteria row place:
[Forms]![Customer_Input_Name]![ComboBoxName]

On the Main Customer form's RecordSource, place the query name.

Now you can place a command button to open the Customer_Input_Name, they select a CustomerID which then opens the Customer_Form to that record.

 

[Zion7]

That's a great idea fneily but, one would only be able
to open the customers' form, if Customer_Input_Name,
was open? Maybe this is, miow's intent but, maybe not.

Why not as you had it, simply,

Private Sub comboboxname_AfterUpdate()
Dim stDocName As String, strCiteria As String

stDocName = "Customer_Form"
strCriteria = "CustomerID =" & Me.ComboBoxName
DoCmd.OpenForm stDocName,,,strCriteria

End Sub

Or, incorporate miow's suggestion...

Private Sub cmdCustomerID_Click()
Dim stDocName As String, strCiteria As String
Dim varID As Variant

varID = InputBox("SElect ID for Customer")

stDocName = "Customer_Form"
strCriteria = "CustomerID =" & varID

DoCmd.OpenForm stDocName,,,strCriteria

End Sub

 

[miow]

Hi fneily and Zion 7 thank you for your kind advice and helping to get in the right direction.

FOR FNEILY

I did have a slight issue with fniely's suggestion as the customer_input_name and the frmCustomer both had to be open, I also think I went somewhere wrong with the one of the steps.

One question i had, I wasn't sure which from fneily was referring to as "the Main Customer form's RecordSource". I had to place the query here. I assume fneily means the main frmCustomer that is bound from the tblCustomers?

FOR ZION7

The method you suggested seems work great...but I have 2 queries which I hope you can clarify for me. I thought it would be easier to use a combo box and then when a user selects the required CustomerID the form will open, so I went with the first option in your suggestions.

However I get the error:

"the value you entered isn't valid for this field - For example you my have entered text in a numeric field or a number that is larger than the field size permits "

when I am selecting the Customer ID from the combo box (which is looking up the Customer ID field in the tblCustomer)...The customer ID field in the tblCustomer is using Autonumber to generate the Customer ID using a \C00000 as the format. I have checked the field type in the table and this is set to number....{confused I am


I can't find any reference to this on Microsoft's VBA documentation.

2nd Query

I tried doing the same method for trying to search for records for the customers surname so I:

1. Created a combo box based on the CustSName field from the tblCustomers
2. Used the same code and just set the strCriteria to CustSName.

The code is:
Private Sub Combo48_AfterUpdate()
Dim stDocName As String, strCriteria As String
stDocName = "frmCustomer"
strCriteria = "CustSName =" & Me.Combo48
DoCmd.OpenForm stDocName, , , strCriteria
End Sub

When I trying running this by making a selection from the combo box I get the error "Run time error 2501, the open form operation was cancelled." and then the debugger points to the 2nd to last line of the code e.g.

"DoCmd.OpenForm stDocName, , , strCriteria"

Any ideas greatly appreciated...I am finding this fun...but didn't realise how pain staking it can be to get one small bit of a database to work.

Thanks in advance.

Cheers

Miow

 

[miow]

Hi there,

FOR ZION7 I have worked out why I was getting the error about combo box and the values, it's because i had created a bound combo box which was attached to my tblCustomers. Reading some other websites I see where I went wrong I had to create an unbound combo box and attach the query for finding a specific CustNo to this combo box.

I have got this bit working....but when i select the values from the combo box I am not getting any information about the record being returned. E.g if I select C0001, the form doesn't fill in the rest of the details such as name, address etc. Hope this makes sense.

Cheers

Miow - thanks!

 

[ssatech]

the value you entered isn't valid for this field - For example you my have entered text in a numeric field or a number that is larger than the field size permits "

In your combobox property, Data, set the
Limit To List...... Yes


"but when i select the values from the combo box I am not getting any information about the record being returned. E.g if I select C0001, the form doesn't fill in the rest of the details such as name,"

In your form: "frmCustomer", you need to add those fields to your query of your frmCustomer's recordsource.

e.g.
SELECT tblCustomers.CustomerID, tblCustomers.FirstName, tblCustomers.LastName, tblCustomers.StreetAddress, tblCustomers.City, tblCustomers.StateOrProvince, tblCustomers.PostalCode, tblCustomers.Phone, tblCustomers.Email
FROM tblCustomers
WHERE (((tblCustomers.CustomerID)=[Forms]![Customer_Input_Name]![Combo48]));

Then, add these field to your frmCustomer