I have a single form, linked to a single table. The form has three bound text boxes, each allow for data entry to the table. In the text boxes you enter a date, a dollar amount, and a client to bill for the postage. My problem is what if I have multiple clients, billed to a single expense.
I want to alter the client entry text box so that i can type in multiple clients, seperated by a comma, and access will know to take the amount entered and divide it between the number of clients entered.
Is this possible? Any help would be appreciated! I taught myself access for the most part and am not very savvy.
Let me start by saying this is not a good idea, however, if it is for your personal use, you can use VBA to split the text string and get a count, you are likely to run into a number of problems, depending on how the bound field is set up.
What I would suggest is a listbox, your two text boxes, and a subform. Using a muti-select listbox you can choose one or several clients. A little code will allow you to iterate through the list and insert a record into a table for each client, with the date and the amount divided by list count. The subform will show the inserted records for checking.
For Each itm In Me.ListBox.ItemsSelected sSQL = "INSERT INTO MyTable (ClientID, Amount, InvDate ) " _ & "VALUES ( " & Me.ListBox.Column(0, itm) & "," _ & Me.txtAmount / Me.ListBox.ItemsSelected.Count & ",#" _ & Me.txtDate & "#)" CurrentDB.Execute sSQL, dbFailOnError Next Me.SubformControlName.Form.Requery