Microsoft Access Forms

I am creating a db to track client data and payment information. Currently my db contains three tables:

1. ClientList
2. AccountInformation
3. PaymentInformation

I have set relationships between all three tables using AccountNumber as the relationship field. I have created a New Client form that allows me to enter client and account information. Once completed all of the information is recorded in both the ClientList and AccountInformation tables with the same AccountNumber. Now I need to create a form that will allow me to:

1. Choose a client from the ClientList table based on the Name and AccountNumber.
2. Enter payment information.
3. Have data recorded in the PaymentInformation table including that AccountNumber from the choice made in step 1.

My questions are:

1. Can I do this with a form?
2. What type of form do I need to use?
3. Can I do this without writing any special Macros? I am not comfortable with writing macros.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jack LeachProprietorCommented:
What type of relationships?  Can one client have more than one Account for which you're providing information for, or is it a one-to-one relationship between those two for the purpose of keeping the column count down on the Clients table?

Payment Information sounds like a transaction log to me, in which case you'd have a One to Many relationship set up between clients and their payment information.  As such, the Payment Information table would have a Foreign Key that tracks the Client by ID (Account number, if that's what you're using as a Primary Key for the clients - I'm not clear on what you currently have).

That said, the requirements for your form are easily attainable, provided you have the correct table/relationship structure in place (a normalized table structure is perhaps the most important aspect of a database design, and heavily influences how easy it is to handle forms, reports, etc).

A common scenario for entering payment information for a given client is to have a form that's bound to the PaymentInformation table.  In your form, provide all of your standard fields, and also provide a listbox or combobox whose ControlSource property is set to the Foreign Key of the PaymentInformation (eg, the Client ID).  Using this as a control source saves the selection that's in the list/combo to the appropriate field automatically.

Listboxes and Comboboxes both have a RowSource property as well: typically, you'll use a simple query to select the records to display in the list.  Clicking the "..." button at the right side of the RowSource property will open the query editor where you can define the query for which records to show.  Using other formatting properties, you can hide and size columns accordingly, allowing you to track hidden information without having to display it to the user (an example would be if you were going to store the Client ID in the ControlSource, but only wanted to show the ClientName to the user... include the ClientID in the query, and hide that column via formatting so it's still accessible to the bound form).

With a proper relationship/table structure and with setting the the listbox/combobox properties correctly, you should not require any code or macros.

As for what type of form, it's more up to you.  For list formats, use Datasheet or Continuous Form (note that listboxes aren't applicable here, so you've have to use a combobox instead), or if you want more detail information  use a standard Form view.

hope that helps,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In addition to Jack's excellent advice:
Once completed all of the information is recorded in both the ClientList and AccountInformation tables with the same AccountNumber.
You generally don't store the same information in two different tables. If a Client "owns" an Account, then you would simply add the ClientID to the AccoutInformation table in a Foreign Key field, and this would "relate" the Client to their Account.

You may also not need separate tables to hold Client and Account information, depending on exactly what you consider to be a "Client". Unless you can have multiple Clients be associated with the same Account, then there may be no reason to break out your data in that manner. But we'd have to know more about the real-world processes and attributes of your business before we could advise further.
gactoAuthor Commented:
Ok sorry for the much delayed response, this project got trumped for a while but now I am back on it. Here are responses to the questions I have seen:

1. Technically a client can, and does, have more than one account. However for the way this system is currently operating the client to account relationship is one-to-one. The clients who do have more than one account are set up as separate clients each time an account is opened. Albeit not the most efficient process it is already in place and I don't plan to change that.

2. Primary key is the account number. For every instance when a new account is added for a client, a unique account number is assigned and that is what will tie all transactions and client information back together.

Jack - If I understood your post correctly, I believe the task is something I can accomplish. My remaining question is when I set up my form, bound to my PaymentInformation table, how will I get the list or combo box to display the ClientID from my client/account tables? Is that by using the query in the Rowsource that you referred to? So in other words I will query my ClientList table for ClientID?

Scott - the only data that I am storing across multiple tables is the Account Number. This is the key I am using to reference all the activity. I do have my client information and account information stored in two separate tables. Based on the fact this is a one-to-one relationship I realize it is not a definite requirement, but it felt like a more logical approach at the time.
gactoAuthor Commented:
Jack - actually scratch that last question, I believe I what you provided has gotten me to the point of finishing this question. I believe I have a bigger learning curve on the forms than I had anticipated. So I will likely be posting many more questions here. I appreciate the help so far.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.