access 2007: entering multiple entries in a single bound text box

Question

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.

Fionnuala Fionnuala · Accepted Answer · 2010-11-10T21:26:17

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 example:

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

access 2007: entering multiple entries in a single bound text box

1 Answers