Automatically populate a form using a query

Question

I have created a payments form that uses a query that is based on 2 tables, Payments (the many side) and the Members (the one side). I want to automatically populate the payments form once the member's SSN or EIN or MBID is entered. The Payments and Members table both have the following common fields, MemberID and SSN.

I want the Payments form to automatically populate from the Members table, LastName, FirstName, MI, EIN and MBID if SSN is entered, memberid, address, city, state, and zip code.

I have continually had a problem getting this work. We use to get which union members paid their dues based on their SSN. Now they base it on their memberid.

What fields should be in this query so it will automatically populate this form?

Thanks, Robert

Answer 1

Let me also say that it is not a good idea to be storing people's SSN in an Access Database.  Access does not have the security strength to protect such sensitive information.  Just FYI...

Answer 2

First, I recommend that you visit the Microsoft Training pages to understand a little more about what you are doing and how to build a database using MS Access.

http://office.microsoft.com/en-us/access-help/CH010372755.aspx

Next, you might consider the following to normalize your table structure before beginning on forms.

Table:

Members 

MemberID	AutoNumber (PK)
MemberNameLast	Text
MemberNameFirst	Text
MemberNameMid	Text

Acctounts

AcctNbr	Text (PK)
Acct_FK_MemberID	Number (FK)
AcctOpenDate	Date/Time
AcctCloseDate	Date/Time

Transactions (Credits and Debits) AKA (Payments and Witdrawls)

TransID	AutoNumber (PK)
Trans_FK_AcctNbr	Text (FK)
TransAmt	Number – Double
TransDate	Date/Time
TransMethod	Number – Long
TransEntry	Date/Time
TransEnteredBy_FK_EmployeeID	Number – Long

So here you have your Members, joined as 1 to many to Accounts, joined as 1 to many to Transactions.  One member can have more than one account.  One account can have more than one transaction.

Now you can begin to create a form that looks up transactions based on a member's account.  If you only want to see credits (Payments), use a query that looks up transaction values greater than 0.  For debits, look up transactions less than 0.

You might start with a query as follows:

SELECT Transactions.*

FROM Transactions INNER JOIN Accounts ON Transactions.Trans_FK_AcctNbr = Accounts.AcctNbr

WHERE Accounts.AcctNbr = [Input AcctNbr] AND Transactions.TransAmt > 0

ORDER BY Transactions.TransDate;

Answer 3

I want the Payments form to automatically populate from the Members table, LastName, FirstName, MI, EIN and MBID if SSN is entered, memberid, address, city, state, and zip code.

I do believe you mistated the above.   The Members table should already have the member but each payment record needs the MemberID added.

I set a one-to-many relationship with referential integerity and cascade update.  Use a form/subform for member/payments setting the Master/Child links. 

Scroll form to member and when a new record is added to subform (payments) the MemberID is automatically inserted.

---

Build a little, test a little.

Answer 4

First, I do not recommend using a multi-table query as the Recordsource of a form. This can often result in an uneditable query. A better option is to use a main form /subform combination.  So if you are entering Payment information and you want to DISPLAY the member info, the best way is to use a Subform. You select the member from a combobox that returns their memberID. The subform is then linked on MemberID so the subform will display the member info.

I've written a blog that describes this in greater detail and offers alternatives to display data from a related table. see my Blog address in my Sig.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 5

"rdw1260" wrote in message

news:*** Email address is removed for privacy ***...

>I have created a payments form that uses a query that is based on 2 tables,

>Payments (the many side) and the Members (the one side). I want to

>automatically populate the payments form once the member's SSN or EIN or

>MBID is entered. The Payments and Members table both have the following

>common fields, MemberID and SSN.

>

> I want the Payments form to automatically populate from the Members table,

> LastName, FirstName, MI, EIN and MBID if SSN is entered, memberid,

> address, city, state, and zip code.

>

> I have continually had a problem getting this work. We use to get which

> union members paid their dues based on their SSN. Now they base it on

> their memberid.

>

> What fields should be in this query so it will automatically populate this

> form?

>

> Thanks, Robert

Unless there is something extremely unusual about your case, there is no

good reason to have both the MemberID and the SSN in the Payments table.

Presumably one of these fields -- most likely MemberID -- is the primary key

of the Members table.  That is the field that you should use as the foreign

(linking) key field in the Payments table.

What you have described is an autolookup query, but with a complication --

you want to be able to enter *either* the MemberID *or* the SSN, and in

either case get the rest of the fields from the Members table to fill in

automatically.  To do that, you're going to have to put make your query be

the recordsource of a continuous form, and bind two different combo boxes to

the same MemberID field.  Here's what I mean:

Suppose your query looks like this:

SELECT

Payments.MemberID, Payments.Amount, Payment.DatePaid,

Members.LastName, Members.FirstName, Members.MI,

Members.Address, Members.City, Members.State, Members.Zip

FROM

Payments

INNER JOIN

Members

ON Payments.MemberID = Members.MemberID;

(I've only guessed at field names, of course.)

Now you make this query the recordsource of a continuous form.  On that

form, you place controls to represent all the fields in the query.  For

MemberID, you put a combo box named "cboMemberID", with these properties:

Row Source:  SELECT MemberID FROM Members ORDER BY MemberID;

Control Source: MemberID

Bound Column: 1

Column Count:  1

The put *another* combo box on the form, named "cboSSN", *also* bound to

MemberID.  Make the properties of this combo box like this:

Row Source:  SELECT MemberID, SSN FROM Members ORDER BY SSN;

Control Source: MemberID

Bound Column: 2

Column Count:  2

Column Widths:  0"; 1"

That combo box will now display the SSN of each member, but store the

MemberID in the underlying query field, MemberID.

With this setup, you can enter the MemberID for a new payment in

cboMemberID, and the SSN and all the other member-related fields will fill

in automatically.  Or you can enter the SSN in cboSSN, and the MemberID and

all the other member-related fields will fill in.  I believe that is what

you were trying to accomplish.

Incidentally, I've described this method with the understanding that you've

purposely decided not to use a main form/subform arrangement to enter your

data -- main form based on Members, subform based on Payments -- for the

purpose of speeding data entry.  You could certainly take the main/subform

approach quite easily, and have combo boxes on the main form designed to let

you look up a member record by MemberID or by SSN, and probably also by

name.  The drawback to this is that the main form would have to be in

single-form view, so while you could easily see a single member's records

and all his payments at a glance, it would be a little more cumbersome to

enter payments for lots of different members at one time.

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html