Solved

Use form list box to create multiple records in table

Posted on 2003-11-21
29
536 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

762 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

21 Experts available now in Live!

Get 1:1 Help Now