MS Access 2010

techsolve1
techsolve1 used Ask the Experts™
on
Hi

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

thanks
Comment
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
Commented:
first off, you should probably have two tables for this, maybe 3.

tbl_Customers:
Cust_ID -autonumber
other fields as needed to define the customer

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

tbl_TransTypes
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.

Author

Commented:
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?

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

Commented:
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
    else
        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