Drop-Down menu and fields

  • Thread starter Thread starter mark morency
  • Start date Start date
M

mark morency

I hope this will be a fairly straight-forward question.
I have *never* used access before.

I am trying to create a form which will have a drop-down
menu listing about 15 items as the first field. When an
item is selected from that drop-down list, I would like
the rest of the fields populated automatically with
specific facts regarding that item.

I have made drop-down fields without a problem, but
getting other fields to populate based on the selection
is the sticky point for me. Any help is appreciated.

--Mark
 
There's a dozen different ways, depending on how you want
to display the data, to achieve this. The simplest way is
to create a query based on the selection from you combo
box. (Not to be technical but it's not called a drop
down, it's called a combo box).

Open a new query and select the tables(s) you want to
query on. Double click all the fields in the upper
portion of the query wizard that you want to display
including the field you are using to populate the combo
box. To make it simple let's call that field ComboPop.
At the bottom of the query wizard, you will notice a row
labeled Criteria. Right click in the Criteria field under
the ComboPop field and select Build. Double click Forms
on the left. Double click the Form that has the combo box
ComboPop then in the center of the 3 boxes double click
the ComboPop. Then click OK. You should now have a
formula that looks like [Form1]![ComboBoxName]!
[ComboPop]. Save the query as qryAnyName

Now you need to create a Macro to run qryAnyName. Open a
new Macro. Click the down arrow and select OpenQuery.
That will create new fields at the bottom left. Click
inside the text box that says query name and select
qryAnyName. Save the Macro as mcrAnyName

Go to the form with the ComboPop combo box and right click
on the combo box and click Properties. Scroll down to
where it says OnChange click on the drop down arrow and
select mcrAnyName. Now whenever there is a change in the
value of ComboPop it will run and display the
aforementioned query.

Last: I have not told you a tenth of what you needed to
know and know doubt the really good ACCESS people are
laughing at this response because it is not the "right"
way to achieve what you are trying to achieve. I would
not recommend this process to anyone other than a novice
trying to achieve a specific task in short order. I have
introduced you to many things. If you take the time to
experiment, you can learn a lot very quickly. Please ask
questions that are more specific. As you can see, is took
a lot of writing. People have written chapters about how
to achieve what you've asked. Maybe a good ACCESS book
would go a long way if you're interested in learning.

Marty
 
Hi,

I can suggest you the following approaches, depending on your real
requirements:

1. If you want to use an unbound form, and for the purpose only to show the
details for selected record on ComboBox:

Set the RecordSource of the ComboBox to a query which contains all fields
you need to display. You can set the combo-box to show all fields as columns
in Drop-down

Add several text box fields to the form. It is reccomendede to change their
names to some meaningful name, like txtName, txtAddress, txtCity ans so on
(supposing the combo box contains contact persons)

Then, to each textbox, change its ControlSource property to:

=[Combo4].Column(2)
=[Combo4].Column(3)
etc.

Combo4 is the name of your combo-box ctrl, and column(i) is the column index
you want to get data for that box.

Another approach is to let the ControlSource property empty, and in
combo_afterUpdate add the folowing code

me.txtName = me.combo4.column(1)
me.txtAddress = me.combo4.column(2)

The same, you have to change column index to reffer to your actual column

2. Another approach will be if you want to be able to edit the details data
for selected record.

In this case you should make the code as follows:

Set the form's RecordSource property to the table or query who retieve your
data

Add textboxes for every details field and bind them to appropriate fields in
database

Add a combo box (unbound) and set its's RecordSource propert to a query who
retrieve the record ID and the desctiptive field you want to let users
select from

For example, your query for the combo might looks like this

select ContactID, ContactName from Contacts order by ContactName

In the combo_AfterUpdate event add the following code

dim rs as recordset
set rs = me.recordsetclone
rs.FindFirst "ContactID=" & me.cmbLookup.value
if not rs.notmatch
me.bookmark = rs.bookmark
endif

If you need any further assistance, you can contact me again.

HTH,
Bogdan
 
Since you are new to ms-access, the let the wizard build the combo box for
you.

All you need is to open your existing form in design mode, and then place a
combo box on the screen. However, BEFORE you place the combo box, make sure
the little magic wand (the wizard button) is selected. If you do that, then
the combo box you need will be generated by the system. You are given
several options by the wizard, and one option is to lookup records based on
what you enter.

The result is a list of names in a combo box, and then select that name, the
form is moved to that record. Thus, all fields are filled with all the
information of that record. It sounds exaclty what you need,and you just
need to use the wizard, as it will do all the work for you.
 
Back
Top