Solved

Use form list box to create multiple records in table

Posted on 2003-11-21
29
538 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:tgerman10
  • 15
  • 12
  • 2
29 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9799808
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
 

Author Comment

by:tgerman10
ID: 9800073
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
 
LVL 7

Expert Comment

by:donpricejr
ID: 9800119
tgerman10,

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

-Don
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 32

Expert Comment

by:jadedata
ID: 9800192
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
 
LVL 7

Expert Comment

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

-Don
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9803468
I gotta refresh more often...
sorry...
0
 

Author Comment

by:tgerman10
ID: 9808248
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9810847
Is txtComment bound to a field on a table?  or where is the data coming from that txtComment presents from?
0
 

Author Comment

by:tgerman10
ID: 9811176
txtComment is bound to the field "Comment" in tblCLog.  lstCustomers is bound to the field "CustomerName" in tblCLog.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9811254
Let me see YOUR version of the code
0
 

Author Comment

by:tgerman10
ID: 9811360
Jake,

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

Accepted Solution

by:
jadedata earned 250 total points
ID: 9811401
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
 

Author Comment

by:tgerman10
ID: 9811488
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9811609
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
 

Author Comment

by:tgerman10
ID: 9813205
I tried that code and it is still adding that extra record.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9813227
do you know how to "walk" thru the code to check values of references while the code is "hot"?
0
 

Author Comment

by:tgerman10
ID: 9814064
no.... sorry
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9814166
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
 

Author Comment

by:tgerman10
ID: 9817652
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9818880
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
 

Author Comment

by:tgerman10
ID: 9820360
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9820484
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
 

Author Comment

by:tgerman10
ID: 9820584
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9820612
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
 

Author Comment

by:tgerman10
ID: 9820675
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9820732
see profile,...send to ptc address
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9820736
send in zip file named tgerman10.zip
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9821113
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
 

Author Comment

by:tgerman10
ID: 9824734
Everything works perfect now!  Thank you very much for all of your help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now