MS Access 2010

techsolve1 used Ask the Experts™

I have a question on how to design a form that will populate 8 values for a single field into an access table
ie the "customer" field will have 8 "Transactions" for that one customer over a number of dates

I want to be able to create a form where I can select the customer and add 8 transactions to that customer form a dropdown list on the form over a number of dates

I have the form setup with the fields and drop down values, but when I select a transaction field and change it, it changes all transaction fields to the same value that I select from the drop down list

any help appreciated

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
first off, you should probably have two tables for this, maybe 3.

Cust_ID -autonumber
other fields as needed to define the customer

CT_ID - autonumber
Cust_ID - long (FK to Cust_ID in tbl_Customers)
Trans_ID - long (FK to Trans_ID in tbl_TransTypes)
other fields as needed

Trans_ID - autonumber
Trans_Desc - text
Sort_Order - integer (determines order to use when adding)

Then your form should be based on the customers table, with a subform for customer transactions.  The main form and the subform would be linked in the Cust_ID field so that when a new customer transaction is added to the subform, it will automatically add the Cust_ID associated with the main form.

If every record in the customer transactions table should have all 8 transactions and they should be in a particular order and a particular number of days from either the previous entry or from the first date, then you could automate that process as well.


Hi Dale

Thanks for the info, much appreciated, I'm also trying to implement if possible the below

I have a status field, which has closed or open, how do I hide closed items from view on a form?

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Several ways to do this.

1.  My preferred method would be to use a query as the RecordSource of the form, as set the [status] field to "Open"

2.  Another method, which would allow you to toggle between all records, open, or closed records would be to put a combo box in the forms header for status codes, and then use the AfterUPdate event of that combo box to set the forms Filter property, something like:
Private sub cbo_ViewStatus_AfterUpdate

    if me.cbo_ViewStatus = "All" then
        me.Filter = ""
        me.FilterOn = false
        me.Filter = "[Status] = '" & me.cbo_ViewStatus & "'"
        me.FilterOn = True
    End If

End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial