Hi Scott,
Got your database
First thing I did was look at your relationship diagram.
Unless you have a specific reason not to, you should enforce
Referential Integrity (RI) on all relationships
I find it very useful to lay out the diagram so it flows as
records must be created. For instance, before you can use
ContributorID in the Pledges table, the Contributor record
must be created -- so move it to the left of Pledges. Same
with your Categories ... and so on
In the table designs, remove the DefaultValue of 0 on your
numeric foreign keys (FK). For instance,
Pledges.Contribution_CategoryID has a default value of zero
which will not match to any record in ContributionCatagories
-- and even if it did, it would probably not be right. Read
about this in Access Basics (link in my siggy -- and you
should also pay close attention to the Combobox example)
frmCamPledgeList
RecordSource: tblDonationCampaignSetup
subform Name: frmCamPledgeListSub
subform RecordSource:
SELECT tblPledges.DatePledged, tblPledges.AmountPledged,
tblPledges.Paid, tblPledges.PaymentDate
, tblContributors.ContributorID,
tblContributors.DonorFirstName,
tblContributors.DonorLastName, ([DonorFirstName] & " " &
[DonorLastName]) AS Name, tblPledges.Campaign_ID
FROM tblContributors
INNER JOIN tblPledges
ON tblContributors.ContributorID = tblPledges.ContributorID;
do not use NAME as a name of any of your objects (fields,
tables, aliases, etc), it is a reserved word
Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html
Contributors is in a 1:many relationship with Pledges -- you
will have trouble adding records and changing contributor
information on this form. Best to base each form on just
one table.
Instead of pulling in the Contributors table to get the
name, base the RecordSource on Pledges and do this:
make ContributorID a combobox
RowSource:
SELECT ContributorID, (DonorFirstName + " ") & DonorLastName
AS Donor
FROM tblContributors
ORDER BY DonorFirstName, DonorLastName
ColumnCount --> 2
columnWidths --> 0;2
Listwidth --> 2.2 (sum of column widths + 0.2 for scrollbar)
here is the code you have to open the next form:
DoCmd.OpenForm "frmViewDonor", , ,
"tblContributors.[ContributorID] =" &
Forms!frmCamPledgeList!frmCamPledgeListSub.Form.idContributorID
firstly, you should have just one ContributorID in the
RecordSource (and you do) -- so you should not be
specifying the tablename
second, you are behind the form with the ContributorID so
you can use a relative reference instead of an absolute one
Forms!frmCamPledgeList!frmCamPledgeListSub.Form.idContributorID
--> Me.ContributorID
third, if you drop the combo in the ControlSource for
idContributorID on the Property sheet, you will find that
'tblContributors.ContributorID' doesn't match anything in
the list so it is not available as a value to use in the
code. Change the ControlSource to --> ContributorID
... and actually, THIS IS THE REASON your form is not
opening with the right data <smile>
If you intend to let the user edit data in the second form,
you should not include information from PositionList in the
RecordSource -- you can show the Position in a combobox and
hide the ID as I suggested you do for Donor.
You should not use * to get fields from more than one table
in the RecordSource -- otherwise, the key fieldnames will be
duplicated (and that is not necessary). Instead, on the
occasion that you really do need to join fields from another
table in a RecordSource, use * on the main table the form is
based on and specifically choose fields from other tables.
POSITION, btw, is also a reserved word -- so change the
field name to something else like 'Posit' or 'Positn'
Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com
free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal
Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access
*
have an awesome day
*
