Combo box?

[Paul Axelrod]

One of my databases tracks progress of various projects, in several
related tables. I would like to be able to put a control on a form
with subform, such that when a project ID# is selected from a
drop-down list, the form will be populated with the appropriate data.
I have tried a combo box, but can not get it to work.
Would anyone have any suggestions that might get the job done. If the
ID# needs to be typed, that would suffice.
Many thanks for any suggestions.

 

[Bogdan Zamfir]

Hi,

Suppose your combo is called cmbProject
You need to set the rowsource of your combo to a SQL query which will
retrieve as first field the project ID (and can have the second field the
project name or any other relevant information)

Then, add a subform to your form, with the rowsource a SQL query who
retrieves the related data, and also mist contains the foreign key projectID

Add a control (it can be hidden) to that subform, name it txtProject, and
bind it to Project ID field from the subform's recordsource
Set the following properties for the subform:

LinkMasterFields: cmbProject
LinkChildFields: txtProject

Now, when you'll select a project in the combo, the subform will show
automatically only related records

You can add as many subforms like this, and they all will be updated.

HTH,
Bogdan Zamfir
__________________________

Independent consultant

 

[Ken Snell]

This is a standard process for ACCESS. Is the form or the subform supposed
to move to the desired record?

Assuming it's the main form, put this code on the AfterUpdate event of the
combo box:

Private Sub cboBoxName_AfterUpdate()
Me.Requery
End Sub

Be sure that the form's RecordSource is a query that is reading the combo
box's value in a criterion expression. In other words, in the query, under
the field that is to be matched to the value in the combo box, put this
expression:
[Forms]![MainFormName]![cboBoxName]


If you want the subform to move to the desired record, the above subroutine
could be changed to this:

Private Sub cboBoxName_AfterUpdate()
Me.SubformName.Requery
End Sub

In this example, SubformName is the name of the subform control (the control
that holds the subform, which may or may not be the same name as the
control's Source Object [which is the name of the form that is being used as
the subform]).


Another way of doing this, if the form's Recordsource is a query that
returns all records, without filtering based on the combo box's value, and
then to use the value of the combo box to move to the desired record, is
found on this page of The ACCESS Web:
http://www.mvps.org/access/forms/frm0005.htm

 

[DanK]

Another way to do it:

Create a query with the fields you need in the subform and
in the Criteria row of the projectID# column, enter:

=Forms![NameOfParentForm]![NameOfComboBox]

Create a CombBox and base it on either the query you just
created or its own SQL with just the minimal fields
necessary. You may want ot start with just the projectID
field at first. I prefer the SQL method.

In the AfterUpdate property of the ComboBox enter:
Me![SubformName].Requery

I also had to make the NAME of the subform and the
SourceObject two different things. (it doesn't really
matter what NAME you give the subform, but it DOES matter
what you put in the SourceObject.

Start with one form, one subform, one ComboBox and two
tables with one field in each of them. Once you see how
it works, you can build from there or apply it to your
stuff. I got it to work in about 5 minutes. The
important thing is to UNDERSTAND WHY someone's suggestion
works.