Microsoft Access Forms

Posted on 2014-07-13
Last Modified: 2014-08-07
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.
Question by:gacto
    LVL 4

    Accepted Solution

    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,
    LVL 84
    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.

    Author Comment

    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.

    Author Comment

    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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now