Linking form to subform by field

[Guest]

I'm working on a budgeting database. I have a Purchases form where purhcases
are entered. The person will put in their clock number, purchase date,
amount, etc. When the person puts their clock number in I want a subform to
fill up with all of the purchases related to that clock number. So far I
have a query for the subform that calls all purchases, but I don't know how
to make the subform show only the active clock number's purchases. What kind
of relationships are needed for this? How would I go about wiring the
subform properly?

Thanks!
floprock

 

[Guest]

Floprock,

Ownership of child records is established by setting the subform's
LinkMasterFields and LinkChildFields properties to the name of the matching
field(s) in the main form's and subform's underlying recordsets respectively.

Sprinks

 

[Guest]

When I click on the ellipsis on either Link Child Fields or Link Master
Fields I get "Can't build a link between unbound forms." I think I've built
my main form in the wrong way, but I can't bend my mind around why it's
wrong. It's a Purchases form to enter new purchases, and I have it based
on a query pulling data from the Purchases table. The subform that's
currently on it is based on a different query pulling basically the same
information from the Purchases table. Is there something wrong with basing
both forms on the same table's data? Arg!

floprock

 

[Guest]

floprock,

I've never seen this error message before. If data is showing on the forms,
presumably they ARE bound. Please post the RecordSource property of both the
main and subforms, and I'll see if I can help. If they are the name of
stored queries, open each in Design view, select View, SQL, and cut and paste
the SQL of each.

Sprinks

 

[Guest]

Okay, the form frmNewPurchases has a "Record Source" of "qryNewPurchases".
The subform has a "Source Object" of "Query.qryHistPurchases". When the form
is in Form View the form is fine for entering purchases, but the subform just
sits there statically showing the contents of the tblPurchases table.

Do I even have the tables structured correctly? I have a table tblBuyers
for the people who are entering in their purchases, and the tblPurchases
where the purchases are stored. Should I somehow be separating the purchases
into two tables so that the form and subform are not looking at the same
table?

floprock

 

[Guest]

Your subform is currently Unbound, which explains the error message you're
receiving. Set the SourceObject property of the subform *control* to the
name of the form it represents. Then double-click on the subform to enable
editing the subform *itself*.

Confused? I know I first was, but when you drag a form from the Forms
window onto a main form in Design view, the subform becomes a control in the
main form, just like a textbox or combo box control. When you click on the
subform in design view of the main form, you are accessing this control, two
of whose properties are LinkMasterFields and LinkChildFields, along with the
SourceObject property, which tells Access which form the control is
associated with. If SourceObject is set to something other than a form name,
it is Unbound, and you cannot therefore set the linking fields.

Regarding your second question, although it's a little unusual, I don't see
anything wrong with showing additional detail records based on the same table
to which the main form is bound.

Good luck.
Sprinks

 

[Guest]

All right!! This crazy thing DOES work!! Thanks a ton for all of your help!
Have a great day!

floprock