Lost-Need Help with Data Entry Form(s)

[Larry G]

I am a relatively new user to Access and I’m not really familiar with how to
write VBA Code and pretty much the same for macros.

The following comes from an Access 2000 dbase that I’m working on for an
organization that I belong to.

I’m currently stuck in the forest and can’t see the trees.

Tables:

tblDues:
Fields: [duesID] = Autonumber and primary key, [duesYear]=text,
[duesDescription] = text and [duesAmount]=currency

TblDuesRecipients
Fields: [duesID] = number and [MemberCertificateID]=text / to avoid
duplicate entries the Intent is to set both as Primary keys

tblMemberCertificates:
Fields: [CertificateIdPrefix]=text, [CertificateIdNumber]=number / Both are
set as primary keys
[CerticateMainContact]text look up to tblMembers
There is a total of 91 members, 80 Original members with a Prefix of M plus
11 Associate members with a prefix of E

TblMembers:
Fields: [MemberID]=autonumber, and primary key
[MemberCertificateID] = text that is a look up field, selects/stores the
concatenation of the two primary keys in tblMemberCertificates the use of
this field associates an individual to a specific Membership Certificate
This table also has other fields that concern the individual (i.e.: name;
address etc etc.)

Now to the issue:

Each record in tblDues will ALWAYS be charged/assessed to all of the 80
Original members and the dues may or may not be charged to the 11 Associate
members.

For the purpose of recording in tblDuesRecipients the recipients of each
record in tblDues, I need to implement a form that will allow the user to
display a record from the tblDues including its [duesID] that subsequently
can be and ideally auto entered into [tblDuesRecipients].[Duesid] whereas the
data for [tblDuesRecipients]. [MemberCertificateID] would be derived from
some type of user interaction or selection.

I’m not sure if it is the most efficient way to achieve the above,
nonetheless thus far I have been trying to work with a main form
“DuesRecipients†set as a Data Entry form, which has the two fields from
tblDuesRecipients and then I added two subforms.
Subform 1 “ MemSubform†displays the fields from tblMembers and Subform 2 “
DuesToAddToDuesRecipients†displays the fields from tblDues.

I have tried several combinations of macros or event procedures that I’ve
placed on command buttons or in field or form events to come up with a smooth
quick and efficient way for the user to select a record from tblDues then to
get that record recorded with each [memberCertificateID] that received the
assessment. However since I suck at code and/or macros I seem to be having
real difficulty in getting anything to work the way I think it should.

If anyone can help me to see the trees in the forest by offering suggestions
on how I can accomplish the above it would be greatly appreciated. I’m not
opposed to re-design, etc.

Since I’m lamb at code or macros should you suggest any, please also include
the all the exact/specific key strokes I’ll need and exactly where it/they
need to be placed.

Thank in advance for any/all comments, suggestions or replies.

Larry G.

 

[halfbodyguy]

My attention span is short, But on a quick once over, my first thought
is that you could setup 2 Seperate queries, and have a trigger that
appends the queries differently. The queries would have All your
Associates and Original members respectively, then when you apply the
(most likely APPEND) Query, it will only add to the records where the
criteria is met. IE. They are associates or original members
respectively.