MS Access 2010


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

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.

Dale FyeOwner, Dev-Soln LLCCommented:
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.

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
techsolve1Author 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?

Dale FyeOwner, Dev-Soln LLCCommented:
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

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.