Start Free Trial

asked on

Use form list box to create multiple records in table

Hello Experts! This is my first time using this site. I am also new to Access.

Here’s what I am trying to do:

I would like to create a form that contains a multi select list box (or combo box) and a text box. The list box (CustomerNumber) will contain a list of customers and the text box (Comment) will be a comment field. I would like to have the ability to select multiple customers from the list box, enter a comment in the text box, and have a separate record created in my table for each selected customer using the comment entered. For example, if I selected customer1, customer5, and customer7 from the list box and entered “sample comment” into the text field, I would like for the following records to be created in my table:

ID      CustomerNumber      Comment

1      customer1            sample comment
2      customer5            sample comment
3      customer7            sample comment

I hope that makes sense…

Any advice you can give me will be greatly appreciated (please keep in mind that I have limited knowledge of Access and writing code). Thanks.

Hey tgerman10!

I'm thinking it goes something like this....

dim rs as recordset
set rs = currentdb.openrecordset("blName",dbopendynaset)
for each Itm in me("lstCustomers").ItemsSelected
rs.addnew
rs("CustomerNumber") = me("lstCustomers").ItemData(itm)
rs("CustomerCOmment") = me("txtSampleComment")
rs.update
next itm

this needs tuned up to system specs,... but..

regards
Jack

ASKER

Jack,

I am having a bit of difficulty getting your solution to work(please keep in mind that I know very very little about code). The problem is with the "set rs = currentdb.openrecordset("blName",dbopendynaset)" line. I changed your "blName" to the name of my table, "CLog", (is that the correct thing to do?) and I get a 3001: invalid argument error. What am I doing wrong?

Also, I placed this under the On Click event of my listbox. Is that where it should be?

Thanks,
Tim

tgerman10,

Did you make sure that you declare the db and rs As DAO.Database and DAO.Recordset?

-Don

from the VBA Editor window, select the menu Tools/References
Is the Microsoft DAO 3.6 Object library in the selected list of references? (add please...)

Change:
dim rs as recordset
To:
dim rs as DAO.recordset

Then Compile your project of see if you still get the error,.. if you do,.. post Your code here as the working copy.

Jack, didn't I just write that??? No doubt you know your stuff, but come on!!!

-Don

I gotta refresh more often...
sorry...

ASKER

Okay... the Microsoft DAO 3.6 Object library was not selected. It is now and things are working better, but not correctly. Multiple records are being created in the table, but they are either missing the comment or the customer name. For example, if I select three customers and enter a comment, four records are created. The first of these records contains the comment, but no customer name. The other three records contain the customer names, but no comment. This is an example of what is being created in my table:

CustomerName Comment

test
customer1
customer5
customer7

Here is my code:

Private Sub lstCustomers_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCLog", dbOpenDynaset)
For Each itm In Me("lstCustomers").ItemsSelected
rs.AddNew
rs("CustomerName") = Me("lstCustomers").ItemData(itm)
rs("Comment") = Me("txtComment")
rs.Update
Next itm

End Sub

Any Suggestions?

Is txtComment bound to a field on a table? or where is the data coming from that txtComment presents from?

ASKER

txtComment is bound to the field "Comment" in tblCLog. lstCustomers is bound to the field "CustomerName" in tblCLog.

Let me see YOUR version of the code

ASKER

Jake,

Please see my previous post for the code. That is what I am using.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Jake,

Thank you. That makes a huge difference. It is working much better now. However, there is still one minor thing... The customer and the comment are being added to the table correctly, but one additional record is being added that contains the comment and no customer name. Any ideas? Here is my updated code:

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCLog", dbOpenDynaset)
For Each itm In Me("lstCustomers").ItemsSelected
rs.AddNew
rs("CustomerName") = Me("lstCustomers").ItemData(itm)
rs("Comment") = Me("txtComment")
rs.Update
Next itm

End Sub

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblCLog", dbOpenDynaset)
For Each itm In Me("lstCustomers").ItemsSelected
if len(Me("lstCustomers").ItemData(itm))>0 then '< test the value of the lstbox before adding record.
rs.AddNew
rs("CustomerName") = Me("lstCustomers").ItemData(itm)
rs("Comment") = Me("txtComment")
rs.Update
endif
Next itm

End Sub

ASKER

I tried that code and it is still adding that extra record.

do you know how to "walk" thru the code to check values of references while the code is "hot"?

ASKER

no.... sorry

right above the line:
Set rs = CurrentDb.OpenRecordset("tblCLog", dbOpenDynaset)

Insert:
Stop

The code will stop there.
the {F8} key will run one line of code at a time (step)

"inch" your way thru the code and along the way you can check the value of references like variables and form control values and properties to make sure everything is coming along nicely or to see where problems are occuring.

Our problem at this point is finding out why if you select Three records in the listbox, the routine enters Four records.

"step" thru the code and see if you can detect why the extra record is being placed on the table.

ASKER

Okay... I "walked" through the code (thanks for the instructions). I didn't detect anything that would be adding the extra record. However, I did notice that the extra record is only created when I close the form or click the new record button. Does that mean anything?

Also, when I click on the new record button, the list box is not reset (still has the selections from the previous record). Is that normal?

do you have code running in Form_Close event?

Below this line:
Next itm

add this:
Me("lstCustomers").rowsource = Me("lstCustomers").rowsource

I think this will serve to Unselect the items in the current list.

ASKER

Jake,

No, there is nothing running on the Form_Close event.

I added that line and it does unselect the items. Thanks.

I found a rough work-around that I can use until I figure out something better. If I add an undo command at the end of the code the extra record is no longer created. I know that's not a good solution, so I am open to any other suggestions you may have.

Thanks for all your help (and patience with my lack of knowledge) on this.

Tim

No,.. this is not a workaround!!! this is cancelling something that would otherwise trigger the extra record creation.

Does the re-triggering of the listbox rowsource work to clear the selection on the current list?

ASKER

Oops... I didn't think it was a good idea...

When I click the command button, the records are written correctly. Once this is done, the form is still displaying the record number that I began on (i.e. if I began on record 1 of 1 it still displays record 1 of 1 after I click the command button). When I close the form or create a new record it writes whatever was last on the form (after the command button click) as the extra record. That's why I stuck the undo command in there - to delete whatever record was remaining after the command button click. What is the proper way to handle this?

Yes, the re-triggering of the listbox rowsource does work to clear the selection on the current list.

are there two buttons that will add record to the table from the listbox?

is there any code remaining assigned to the listbox events"

i'm concerned about the form triggeringjunk record add on close.... something must be causing this

ASKER

There is only one button on the form and no other code. I have been using a test db that only has two tables and one form. The form has nothing but a list box, a text box, and a command button. The only code is what you have helped me with. I can send you the database if you would like to take a look at it.

see profile,...send to ptc address

send in zip file named tgerman10.zip

got it.

Changes include:
released form recordsource
release field bindings on controls
change form properties:
allow adds = false
allow datasheetview = false
allow pivotview(s) = false
allow deletes = false
scrollbars = none
record navigators = false
record selectors = false

Added test for null comment to code.

The problem was that the form was bound to the same table we were populating with the listbox code.
The natural tendency for a form is to save a record if changes are made to the form when it closes.

ASKER

Everything works perfect now! Thank you very much for all of your help.