make a field value a condition for select to populate rest of form

  • Thread starter Thread starter EdStevens
  • Start date Start date
E

EdStevens

Given a form with 3 fields:

ssn
firstname
lastname

Currently, data source for the form is a table, 'emp'. So when we
open the form we can scroll through each of the rows in emp. We need
to be able to enter a value in ssn and use that value to query emp to
populate the other two fields.
 
Given a form with 3 fields:

ssn
firstname
lastname

Currently, data source for the form is a table, 'emp'. So when we
open the form we can scroll through each of the rows in emp. We need
to be able to enter a value in ssn and use that value to query emp to
populate the other two fields.
Click to expand...

One simple way would be to use a combo box with a rowsource set to
this table and ssn as the first field. As you type the ssn, it will
match the rest of the record. Alternatively, just have the ssn in the
combo and use it to filter the records on the underlying form (use
this method if you need to edit the other field).

-- James
 
One simple way would be to use a combo box with a rowsource set to
this table and ssn as the first field. As you type the ssn, it will
match the rest of the record. Alternatively, just have the ssn in the
combo and use it to filter the records on the underlying form (use
this method if you need to edit the other field).

-- James
Click to expand...

James,

Thanks for the reply. Unfortunately, neither of us are very
conversant with Access, so need a little more detailed explanation.
Yes, we will need to edit the other fields, so the second solution
would be what we want. So... exactly how do we use the value of the
combo box to filter the records?
 
You mention "rows" so I have to assume you're either using continuous form
or datasheet view. In order to use this (or any other method of record
retrieval I know of) youha ve to use either continuous or single view forms.
If yours is datasheet view, you need to change it to continuous. You can then
format it to look like a datasheet view. The reason for this is that while
you can add controls like command buttons and comboboxes to datasheet view
forms, in Design View, they don't appear when the form runs!so, once you have
a continuous form:

Add a combo box to your form, in the header. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

From the query the form is based on, click on the field you're searching by
(your SSN field) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type. Hit <Enter> and the record will be retrieved.

Good Luck!

Linq

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
What if you want to have a combo box that will allow you to search
through existing records and modify the data in the firstname and
lastname boxes as well as create a new record if the ssn_sm does not
exist? Thanks in advance.
 
Back
Top