Using a combo box to pull record into a form

[KerryL]

OK, I'm frustrated. I've done this before, but I can't get this one to work correctly.

I have a form which contains standard customer data. In order for users to edit a customer's record via the form, I added a combo box. The combo box lists customer names. When the user selects one I want to form to be populated with that customer's data.

Here are the particulars:
tblCustomers (contains custID, CustLName, CustFName, etc)
frmEditCustomerData
cboSelectCustomer

I even used the wizard to create the combo box with the option to "find record on form based on value selected in combo box" but it's not working.

I'm sure it's something obvious but I'm stuck.

TIA for your help.

 

[gusbrunston]

Hi Kerry:

When you open the query that your form is based on, are the records you're looking for displayed?

Are you sure your controls on the form are bound to the right fields that are in your table or query? In the properties sheet for the form, are you sure that you have "no" indicated for data entry? (Otherwise you have a data entry form only.) Controls enabled and not locked?

If you want to populate from the columns in your combo box for data entry, in "After update" event for the combo box you can enter:

Me.NameOfTextBox = NameOfCombo.Column(0)
Me.MyOtherTxtBox = NameOfCombo.Column(1)
etc.

Is this in a sub form? If so, where does your cursor go after the combo box selection?

You've probably already done all these. Lemme think...

The last time this happened to me, it was that only one record would come up. Because of enforced referential integrity (?) the query would not display records with a null value in one field. I wanted to keep my table relationships as they were, so I made the query change null to zero (

ZeroThisField = Nz([ThisField],0)

.

Good luck,

Gus Brunston
An old PICKer, using Access2000
padregus@home.com

 

[KerryL]

Thanks for the help, Gus. Here's my answers to your questions.

The form isn't based on a query; its record source is tblCustomers. I was trying to create a select statement for the row source of the combo box that would pull up the record of the cbo selection. Or do I need to create an "after update" event?

Yes, the fields on the form are bound correctly to the corresponding data fields in the table.

The "Data Entry" property was set to yes. I changed it to no. The "Allow Edits" property is set to yes.

Not sure what you mean by "controls enabled and not locked."

The form is not a sub-form.

Thanks again for your help.

 

[gusbrunston]

Hi.
I'm on AT&T@home and my internet service has been intermittent.

Why don't you try the combo box wizard again, and be sure to select the choice "I want to select a record based on the choice in my combo box." (or similar).

Now that you have set your data entry property of the form to "no", it may work for you.

Probably irrelevant to your present problem, but you find the "enable" and "locked" properties in the property sheet, "Data" for each control.

I'm going to open a small new database and follow the procedures for installing a combo box, and see if I can duplicate your problem.

See ya, Gus Brunston
An old PICKer, using Access2000
padregus@home.com

 

[gusbrunston]

Kerry, I dunno...

My wizard makes me a workable combo box.

Make sure that the bound column of your box is bound to the primary key of your table. (When the wizard asks, "What is the value you want to use to look up the record? (or something like that), make sure you select the primary key of your table.

Hope someone else can get a handle on what the problem is.

Gus Brunston
An old PICKer, using Access2000
padregus@home.com

 

[KerryL]

I created a new combo box using this criteria:

"Find a record on my form based on the value I selected in my combo box."

When asked which fields to include in the cbo, I selected:
CustID (the primary key field)
CustFirstName
CustLastName

Key column is hidden. Then I gave it a name.

When I try and select a value (first & last name) from the combo box I receive the following error:

Run time error '3021.'
No current record.

I don't understand why, because I know the record I'm trying to load into the form exists in the table. Maybe it has something to do with the table properties or the form settings. I'll take a look.

Kerry

 

[gusbrunston]

Hi again,

Thought: With the wizard open you drag the combo box to the form, doesn't the wizard then ask you What table or query the combo box will use? If you have failed to select the correct table/query, then you won't find the records you need. Hope this or something soon will help.
Gus Brunston
An old PICKer, using Access2000
padregus@home.com

 

[gusbrunston]

OK,

I created a database named CustName.mdb and put a combo box in the form header. It's 168 kb and I could easily email it to you if you like. If so, send me your email address (see mine below) and I'll attach it to the reply.
Gus Brunston
An old PICKer, using Access2000
padregus@home.com

 

[gusbrunston]

Kerry:
I finally realized I've received no email since Excite@Home went bankrupt. I can get on the internet, but not email has arrived. In the meantime, I'll be checking for email at gustrel@aol.com.
Gus Brunston An old PICKer, using Access2000
padregus@home.com

 

[Zeroanarchy]

I think this is what you are after.

Private Sub selectcustomer_BeforeUpdate(Cancel As Integer)
Dim strFilter As String
strFilter = "custLname = " & Me!SelectCustomer

Me!custID= DLookup("custID", "customers", strFilter)
Me!suburbname = DLookup("custFname", "customers", strFilter)
Me!IDrating = DLookup("IDrating", "Suburb", strFilter)
End Sub


if you want it to look up any other info just add more

Me![fieldname] = Dlookup("Fieldintable","Tablename", strfilter)

Hope this is what you are after.

Zero

 

[Zeroanarchy]

this above method works well if you want to only bring in specific info from a table based on the value you choose. It is no good if you want to say select a customers name and have all records or jobs relating to that customer cone up, in other word filter to just records that match the customers name.

To do that:
'After Update Event of the Filter for Customer Combo Box
If Me![Filter for Customers] = &quot;<All>&quot; Then
DoCmd ShowAllRecords
Else
DoCmd ApplyFilter , &quot;[customerslastname] = Forms![Filter for Customers]![Filter for Customers]&quot;
End If

I have a running example if you want it emailed supply your email address.

Zero