subform data entry

[judy jones via AccessMonster.com]

I have a donor form with a donations subform. The user should be able to
enter donation data on new or existing donors, but does not HAVE to have
donation data. I am not able to enter data into the donation subform at
all. Any new or change data on the donor works correctly, and existing
donation data shows up correctly in the subform.

I know that I am not able to ENTER data, because a linked record does exist
for the new donation. It seems to me that I should be able to key in the
donorID (visible from the main form) into the subform; and that this would
create the linked record that the next fields on the subform could be
entered on.

And of course, if I could get it to accept a manual key into the donorID
field of the subform, I would then prefer to auto load the field by either
using a macro button labeled NEW DONATION ENTRY, and remove the tab stop
from the preloaded field.

Donor is based on a table with a primary key of donorID and the donation
subform is based on a table that contains donorID. Both fields are set to
LongInteger.

In creating the subform I used the wizard that loaded the Link Child and
Link Master Fields on the donation subform.

Where have I gone wrong?
fyi - I am fairly novice at code. It 'usually' makes sense when I see it,
but always let the wizards and tools create it for me.

judy

 

[Steve Schapel]

Judy,

If you have the Link Master Fields and Link Child Fields of the subform
set to donorID, as you suggested, then there should be no need to enter
the donorID into the subform. It should automatically be entered for
you as soon as you start to enter a new record in the subform. In fact,
in most cases the normal procedure here would be to not even have a
donorID control visible on the subform. There is no need for any macro
or code to handle this.

So, try entering data into another control on the subform, not the
donorID, and if it doesn't work, please post back with some details of
what actually happens when you try.

 

[judy jones via AccessMonster.com]

Steve? thank you so much for responding!

As I mentioned, existing donation data shows up correctly in the subform
(ie. multiple donations in each subform as related to donor of that main
form), so I know the link is good.

But when I try to add a donation in 6 of the 7 subform fields (including
the donorID field) I get a bell tone. The 1 field that does not give me a
bell tone gives me an error explanation of:

Cannot add record(s); join key of table ?tblDonations? not in recordset.

This field is a donationType field that is actually from a table linked to
tblDonations as a definition table giving a drop down list for
donationType. And now that I get to looking better, this drop down list is
NOT working on the 'existing' donations in this subform either. I hope
that last comment made sense. Anyway, that may be a secondary problem, but
makes me a little suspicious. Maybe I should remove that definition table
link for now, then retest.

judy

 

[judy jones via AccessMonster.com]

I apologize for online analysis, but I'm seeing that my query that works
great for listing existing donations, may not be the right query for
entering donations? (also you will see I actually have 2 dropdown fields
defined by linked tables; DonationAppealType & DonationType)

SELECT tblDonations.DonorID, tblDonations.[donation#],
tblDonationType.DonationType, tblDonations.DonationMemo,
tblDonations.DonationDate, tblDonations.DonationAmount,
tblDonationAppealType.DonationAppealType, tblDonations.[UnitedWay Donation]
FROM ((tblDonors INNER JOIN tblDonations ON tblDonors.DonorID =
tblDonations.DonorID) INNER JOIN tblDonationAppealType ON
tblDonations.DonationFromAppealType =
tblDonationAppealType.DonationAppealTypeID) INNER JOIN tblDonationType ON
tblDonations.DonationType = tblDonationType.DonationTypeID
ORDER BY tblDonations.DonorID, tblDonations.[donation#] DESC;

I'll go back and play around with the query. But glad to hear your
thoughts if you read this tonight.

 

[Steve Schapel]

Judy,

I think at a quick glance that the meaning of this error message is it
wants the tblDonations.DonationType field in the query that the subform
is based on, as this is the field that the form control needs to be
bound to in order for data entered on the form to go into the correct
place. Make sense? Sorry about the cursory answer, I will be able to
look at it more closely later.

 

[judy jones via AccessMonster.com]

at a glance... you're pretty good! You're right, the query that the
subform is based on was using the field in the definition table; which
meant the drop down was not even showing up in the query.

So now the drop down shows up in the query. And like you said, the donorID
shows up as the default number in the subform. However...
in the subform, when I get to the donationType field - still no drop down,
PLUS I have to key in the numeric match rather than the choosing from the
text list (which is why I 'assumed' I needed the definition table field)

progressing,
judy

 

[John Vinson]

However...
in the subform, when I get to the donationType field - still no drop down,
PLUS I have to key in the numeric match rather than the choosing from the
text list (which is why I 'assumed' I needed the definition table field)

It appears you're yet another victim of Microsoft's misdesigned,
misleading, and all but useless so-called Lookup Field.

Using a Lookup field in a table makes it about two mouseclicks easier
to add a Combo Box to a form or subform.

Not using a lookup field does NOT mean that you are stuck typing in a
numeric match.

Open your subform in form design view; add a Combo Box using the
toolbox wizard, bound to the donationType field, but displaying the
text value.


John W. Vinson[MVP]

 

[judy jones via AccessMonster.com]

in the main (donation) form I have functioning drop downs created by
properties data tab:
Control Source : DonorAffiliationType
Row Source Type: Table/Query
Row Source : SELECT tblDonorAffiliation.DonorAffiliationID,
tblDonorAffiliation.DonorAffiliation FROM tblDonorAffiliation;
(actually I used the wizard that dropped this info in)

BUT...
in the subform (donations) I only have
Control Source : DonationType

there is not a Row Source Type or Row Source listed in the Data tab of the
subform. Are combo lists not allowed in subforms?

If that is true... do I create a button for New Donation Entry that takes
the user to a donation entry MAIN form?

Did I miss the 'Rules of Subforms' somewhere? Or is it just trial and
error to learn these things?

Fun maybe, but time to 'get on with it'.

 

[John Vinson]

there is not a Row Source Type or Row Source listed in the Data tab of the
subform. Are combo lists not allowed in subforms?

Yes, of course they are.

Use a Continuous view subform (view the Form's Properties and set the
Default View) rather than a Datasheet subform. You can then use
textboxes, listboxes, combos, option groups, any control that you
wish.

John W. Vinson[MVP]

 

[judy jones via AccessMonster.com]

Many thanks to both John & Steve for taking the time to help. I don't know
why I didn't try the wizard on that field already. I've been using the
wizard to learn what I have.

So as it turned out. I used the wizard to connect the subform to the form.
Then, used the wizard for the 2 combo box fields.

And... I continue to go back and check properties and code to try and make
sense of it all.

thanks again,
judy