Not sure what is the best way

[jln via AccessMonster.com]

OK what im trying to do is creat a form that has a combo box that holds and
inv# ( the investor number is conected to muitl records) when the user
selects a inv# it will populate a subform that pulls all the records that
have matching inv#. Thats one thing. The 2nd is someone revirews the records
that go with the selected inv# and they need to beable to have checkbox to be
marked when the records are reviewed and approved. The check box needs to
show only once on the main form and once checked all the records with say
inv# 612 get checked from that one check box. Advice Please.

 

[Dirk Goldgar]

OK what im trying to do is creat a form that has a combo box that
holds and inv# ( the investor number is conected to muitl records)
when the user selects a inv# it will populate a subform that pulls
all the records that have matching inv#. Thats one thing.

That's easy enough. Use the combo box as the Link Master Field for the
subform control, with the [inv#] field of the subform's recordsource
serving as the Link Child Field. The subform wizard doesn't make it
clear that you can use unbound controls in the Link Master Fields
property.

The 2nd is
someone revirews the records that go with the selected inv# and they
need to beable to have checkbox to be marked when the records are
reviewed and approved. The check box needs to show only once on the
main form and once checked all the records with say inv# 612 get
checked from that one check box. Advice Please.

There may be a data structure issue here. If all the records for inv#
612 should either be checked or unchecked at any given time, then the
"approved" status is really associated with the investor, not the
subform records related to that investor. In that case, the yes/no
field indicating that status should be stored in the investor record,
not in the subform's recordsource.

That would imply that your main form should be bound to the table of
investors, with the check box bound to the status field. And in that
case, your combo box on the main form would be used for navigation -- to
position the form to the investor record -- and not as the Link Master
Field after all. You'd have another control on the form bound to the
[inv#] field, and you'd use that as the Link Master Field.

On the other hand, if some subform records could logically be checked
while others are unchecked (for the same investor), then the structure
needs to be different, and you need a bit of code if you want to check
or uncheck all the records at once.

 

[jln via AccessMonster.com]

Im waing to use the check box say yes all the investor files have been view.
I was thinking of placing it on the main form along with the combo box if
that makes sense.

 

[Dirk Goldgar]

Im waing to use the check box say yes all the investor files have
been view. I was thinking of placing it on the main form along with
the combo box if that makes sense.

Then, as I said in my first reply, you'll want to define a field in the
Investors table -- or some relevant table that has one record per
investor -- and bind your check box to that field. You seem to be
thinking of your forms as containers that store data, but really they
aren't. It's the tables that store data, while the forms give you a
window on the data in the tables. To store any information that is
specific to an investor, you must have a table with one record for each
investor. Then you can base your main form on that table, and have your
subform based on the table of investor files.

 

[jln via AccessMonster.com]

Well i went qith you idea to create a table that each investor number and a
check box. Since a investor cant be audit by record, its done as a group of
records. BUt im stuck. What i have is the combobox with each investor once
and the checkbox on the main form and then on the sub form is the records for
the investor that is selected in the combobox. That is where im having the
problem How do i make it run/rerun the query when a new investor number is
changed in the combobox?

 

[Dirk Goldgar]

Well i went qith you idea to create a table that each investor number
and a check box. Since a investor cant be audit by record, its done
as a group of records. BUt im stuck. What i have is the combobox
with each investor once and the checkbox on the main form and then on
the sub form is the records for the investor that is selected in the
combobox. That is where im having the problem How do i make it
run/rerun the query when a new investor number is changed in the
combobox?

Subform linkage via the Link Master/Child Fields will take care of that,
if you set it up right.

Let's see what you have. You have a table -- I'll assume it's called
"Investors" -- with [Inv#] as its primary key, and at least one other
field, which for the sake of example I'll call "FilesViewed".

You have another, related table that (again, for this example) I'll call
"InvestorFiles". This table will have a primary key field of its own,
miscellaneous data fields I don't know about, and the [Inv#] field to
serve as the foreign key relating it to Investors.

You need to create two forms: one bound to the Investors table to serve
as the main form, and one bound to InvestorFiles to serve as the
subform. When I say "bound to" with regard to a form, I mean that it
has the table or query I named as its RecordSource. The form bound to
InvestorFiles does not have to be bound to a query that refers to the
main form for criteria -- it can be bound directly to the table (unless
there's some other reason you need it to be a query).

The main form, as I said, will be bound to Investors. It will have at
least three controls on it before you add the subform:

cboFindInvestor (combo boxs, unbound)
chkFilesViewed (check box, bound to FilesViewed)
txtInvNo (text box, bound to [Inv#])

When I say "bound to" with regard to a control, I mean that the control
has the field I named as its ControlSource.

The main form may have other controls on it, bound to other fields (if
any) in the Investors table. You might want to have InvestorName on
there, for example.

Note that cboFindInvestor is unbound. That's because we are going to
use it for navigation among records on the main form. The RowSource of
cboFindInvestor should be a SQL statement that selects at least the
[Inv#] field from all the records in the Investors table. If you like,
you can use the Combo Box wizard to build this combo box -- just tell
the wizard you want it to "find a record on my form".

Now, having created the two forms, the main form and the form that will
appear on the main form as a subform, open the main form in design view
and drag the subform from the database window and drop it on the detail
section of the form. That will create a subform control on the main
form, with the InvestorFiles form as its source object.

Access may successfully guess at the correct values for the Link Master
Fields and Link Child Fields properties of the subform control, but you
should open the property sheet of the subform control, go to the Data
tab, and check those properties. The Link Child Fields property should
be "[Inv#]". The Link Master Fields property should be either "[Inv#]"
or "txtInvNo".

At this point, as you navigate through the records on the main form,
whether by advancing a record at a time or by using cboFindInvestor to
jump to a specific investor, you should see on the subform only the
records that are related to the current investor.