Adding multiple records based on a list box

[sherriross81]

Hello,

I have a form that I am testing out a scenario to see if I am apply it to
other forms in my project. The form is called frmTest.

The form is bound to the table TestTable and has columns:
Primary Key , autonumber,
MonitorTknNbr, Number
ComputerTknNbr,Number

The form has a combo box that lists all the ComputerTknNbr and the List box
contains all of the MonitorTknNbrs. The user can select multiple values from
the list box of monitors but only one computer. If the user selects multiple
monitors I want it to insert a separate record in TestTable for each
computer/monitor combination.

I have looked through the discussion boards and found a few examples but I
am having trouble getting them to work so I thought I would post my code here
for some input. I am not sure if this is on the right track or not.....

Private Sub cboSave_Click()
Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.lstMonitors
For Each varItm In ctl.ItemsSelected
DoCmd.GoToRecord acNewRec
Me.txtMonitorID = ctl.ItemData(varItm)
Me.txtComputerID = Me.cboComputerID.Value
Next varItm
Set ctl = Nothing
End Sub

When I run it I get a run time error 2487 The object type argument for the
action or method is blank or invalid.

 

[Beetle]

Stop!

If one computer can have multiple monitors associated with it then
those two entities shuold be in separate tables in a 1:m relationship.
Access is a relational database, not a spreadsheet. What you're doing
now is basically trying to feed it spreadsheet data.

Once you have your tables correctly designed, you can set up a
main form/sub form where the users can select as many monitors as they
need for any given computer. No code will be necessary.

 

[sherriross81]

Ok maybe I was not specific enough.

I have a table called Computer. This table holds all computers that we
have. I have a combo box on my form called cboComputerID. Then I have
another table called Monitor which holds all the monitors that we have. The
list box on my form contains all the monitors that we have available. When
the user selects a computer from the computerID combo box and a monitorid
from the monitor list box these values get inserted into a new table called
ComputerMonitorAttach which is a primary key auto unqiue number and then the
id of the computer and the monitor. I have my database set up
relationally......

computer
comp1
comp2

monitor
monitorA
monitorB

computermonitorattach
comp1, monitorA
comp1,monitorB

I created a test table to test out this new technique and that is what I am
referring to when I talk about TestTable and the two columns that it has.

I just want to be able to select one computer from the combo box and
multiple monitors from the list box so the ComputerMOnitorAttach or in my
example Test Table would look as I showed above:

computermonitorattach
comp1, monitorA
comp1,monitorB

How do I use a loop to add based on the different items selected??

 

[Beetle]

Sorry, misunderstood your post.

You can use an insert query to do what you want. Something like this
(I have added line continuation characters in the SQL statement to
adjust for the news group line wrap);

Private ub cboSave_Click()

Dim strSQL As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstMonitors

For Each varItem in ctl.ItemsSelected
strSQL = "Insert Into TestTable (MonitorTknMbr, ComputerTknNbr)" _
& " Values (" & ctl.ItemData(varItem) & ", " _
& Me.cboComputerID & ")"
CurrentDb.Execute, dbFailOnError
Next varItem

Me.Requery

End Sub

 

[sherriross81]

I put the code you provided into my click event. When I compile it gives me
an error that says argument not options and it highlights the line:
CurrentDb.Execute, dbFailOnError

Any ideas?

Thanks in advance!

 

[Beetle]

Open your code window and go to Tools/References. Make sure that the
Microsoft DAO 3.x Object Library is checked.

If that doesn't solve the problem you can try replacing that line with

DoCmd.RunSQL strSQL

I prefer the CurrentDb.Execute method because it doesn't generate the
standard system warning message that you get with the DoCmd method.
If you do end up using the DoCmd method, you can turn off the Access
warning message with

DoCmd.SetWarnings False

but you need to make sure you turn them back on again before you
exit the procedure. You'll want to make sure you have error handling
and turn the warnings back on in the exit_procedure event (or similar)
so that the system warnings will get turned back on even if there is an
error and your procedure doesn't run. In that case the code might
look like;

Private Sub cboSave_Click()
On Error GoTo HandleError

Dim strSQL As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstMonitors

DoCmd.SetWarnings False

For Each varItem in ctl.ItemsSelected
strSQL = "Insert Into TestTable (MonitorTknMbr, ComputerTknNbr)" _
& " Values (" & ctl.ItemData(varItem) & ", " _
& Me.cboComputerID & ")"
DoCmd.RunSQL strSQL
Next varItem

Me.Requery

Exit_Procedure:
DoCmd.SetWarnings True
Exit Sub

HandleError:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_Procedure

End Sub

 

[sherriross81]

Bettle,

Thanks so much for your help! I ended up having to use the section option.
My DAO Object Library was checked and still had the problem. The second
option runs perfectly though. I really appreciate your help. Thanks again!

Sherri