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.
tgerman10Asked:
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.

jadedataMS Access Systems CreatorCommented:
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
0
tgerman10Author Commented:
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
0
donpricejrCommented:
tgerman10,

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

-Don
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jadedataMS Access Systems CreatorCommented:
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.
0
donpricejrCommented:
Jack, didn't I just write that??? No doubt you know your stuff, but come on!!!

-Don
0
jadedataMS Access Systems CreatorCommented:
I gotta refresh more often...
sorry...
0
tgerman10Author Commented:
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?
0
jadedataMS Access Systems CreatorCommented:
Is txtComment bound to a field on a table?  or where is the data coming from that txtComment presents from?
0
tgerman10Author Commented:
txtComment is bound to the field "Comment" in tblCLog.  lstCustomers is bound to the field "CustomerName" in tblCLog.
0
jadedataMS Access Systems CreatorCommented:
Let me see YOUR version of the code
0
tgerman10Author Commented:
Jake,

Please see my previous post for the code.  That is what I am using.
0
jadedataMS Access Systems CreatorCommented:
oops sorry,...(I'm claiming lack of coffee....)

The code is assigned to the listbox click.  It should be assigned to a command button that will run after all selections have been made between the comment selection and all the selected customer.  This code is running each time you click on lstcustomers, which is premature.
0

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
tgerman10Author Commented:
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
0
jadedataMS Access Systems CreatorCommented:
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
0
tgerman10Author Commented:
I tried that code and it is still adding that extra record.
0
jadedataMS Access Systems CreatorCommented:
do you know how to "walk" thru the code to check values of references while the code is "hot"?
0
tgerman10Author Commented:
no.... sorry
0
jadedataMS Access Systems CreatorCommented:
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.
0
tgerman10Author Commented:
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?
0
jadedataMS Access Systems CreatorCommented:
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.
0
tgerman10Author Commented:
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  
0
jadedataMS Access Systems CreatorCommented:
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?

0
tgerman10Author Commented:
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.

0
jadedataMS Access Systems CreatorCommented:
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
0
tgerman10Author Commented:
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.
0
jadedataMS Access Systems CreatorCommented:
see profile,...send to ptc address
0
jadedataMS Access Systems CreatorCommented:
send in zip file named tgerman10.zip
0
jadedataMS Access Systems CreatorCommented:
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.

0
tgerman10Author Commented:
Everything works perfect now!  Thank you very much for all of your help.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.