Form and record selection

[accessnewbie]

i'm an access newcomer, so forgive me if i don't detail my question properly.
basically, i'm creating a basic database and a form to input data into that
database. i'd like to create a form that uses a combo box with a list of
company names and when the user clicks on or types in the company name access
will pull up the record. as it is, i can only edit records by clicking on
the arrow keys at the bottom of the page.

also, every time i click on my form, it opens with "enter parameter value"
with "Overview.CEO" underneath. how do i get rid of that? thanks for your
help!

 

[Ofer Cohen]

Look at this link on "Using a Microsoft Access Combo Box to Search for a
Record:"

http://www.databasedev.co.uk/combo_box_search.html

It will give you a good start

 

[Ken Sheridan]

Add an unbound combo box, cboFindCompany say, to the form and set its
RowSource up to return a sorted list of companies. Lets assume you have a
table Companies with a numeric CompanyID column and a text CompanyName column
amongst others, in which case the RowSource would be:

SELECT CompanyID, ComanyName FROM Companies ORDER BY CompanyName;

Set other properties of the combo box like so:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, OwnerID, column, so only the name
shows.

Put code along these lines in its AfterUpdate event procedure:

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "CompanyID = " & cboFindCompany
Me.Bookmark = rst.Bookmark

This assumes CompanyID is a number data type. If it were text you'd wrap
the value in quotes characters:

rst.FindFirst "CompanyID = """ & cboFindCompany & """"

To keep the combo box in sync with the form's current record if you navigate
via the navigation buttons or keyboard put the following code in the form's
Current event procedure:

Me.cboFindCompany = Me.CompanyID

As regards the parameter prompt this suggests that you have Overview.CEO
somewhere and Access is not recognizing this as a reference to column in the
form's underlying table or query. This could be in a variety of places such
as the underlying query itself or in the ControlSource property of a control
on the form (or any subform embedded in it). You'll need to ferret about
until you find the unrecognized reference and then either eliminate it or
correct it.

Ken Sheridan
Stafford, England

 

[accessnewbie]

I tried this and the Companyname comes up in the unbound control. However,
when I click on the Companyname for which record I'd like to update, it does
not produce the right record. When I highlight a company name in the combo
box, it simply passes me to the next record rather than correct record tied
to the company name. Any advice? Thanks!

 

[accessnewbie]

I tried the link and wasn't sure what to do when Query Builder came up. I
tried the code it suggested as it applied to my database and the combo box
still does not pull up the corresponding record to the name that the I choose
in the combo box. It may be something that I'm missing while reading the
website.

 

[Ken Sheridan]

It’s a pretty standard procedure so should do the trick. Does the table have
a numeric primary key in the way that I described or is the company name the
key? If the latter the approach is similar but the properties of the combo
box and the code would need amending.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

i'm an access newcomer, so forgive me if i don't detail my question properly.
basically, i'm creating a basic database and a form to input data into that
database. i'd like to create a form that uses a combo box with a list of
company names and when the user clicks on or types in the company name access
will pull up the record. as it is, i can only edit records by clicking on
the arrow keys at the bottom of the page.

Ofer's link will do that. Basically you need to use the Combo Box Wizard in
form design to create an unbound combo box, using the option "use this combo
to find a record".

also, every time i click on my form, it opens with "enter parameter value"
with "Overview.CEO" underneath. how do i get rid of that? thanks for your
help!

Somewhere in your Form - in the Recordsource, in the Sort Order, someplace -
you have a reference to CEO. Access can't find that field in the Overview
table so it's asking for it. Open the form in design view and view its
Properties; on the Data tab select the Recordsource property (first on the
list) and click the ... icon by it. A query design window will open. If
there's a column for CEO then delete it, or correct the spelling or the
fieldname so it's referencing a table field that actually does exist.

John W. Vinson [MVP]

 

[accessnewbie]

The primary key is numeric (auto number that access defaults to) and it's
called "ID". I can't figure out why when I click on the name of the Company
that the record doesn't pull up. (If I hit enter, it pulls up the next
sequential record.) Does the control need something in the "Control Source"
line? It just seems like the control isn't connected to anything maybe. If
it helps any, I'm using Microsoft 2007. THank you for your patient advice!!!