access 2007: entering multiple entries in a single bound text box
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 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
精彩评论