How do I: Create New Records based on 3 Multi-Select List Boxes?

[Guest]

I have a form with 3 Multi-Select list Boxes, each based off of a table:
lstVehicle, lstTechnology & lstPosition.

The mainform recordsource is another table that has the following fields:
fldQuoteID
fldVehicleID
fldTechID
fldPosID.

What's the best way to insert/append new records into the recordsource table
using all of the values selected in the list box?

I would need to add every combination of values selected in all the list
boxes combined.

So, if I have the following values selected in the list boxes:
Vehicle1, Vehicle2; Tech1,Tech5; Pos3;Pos4

I would have the following Records Added:
QuoteID; Vehicle1;Tech1;Pos3
QuoteID; Vehicle2;Tech1;Pos3
QuoteID; Vehicle1;Tech1;Pos4
QuoteID; Vehicle2;Tech1;Pos4
QuoteID; Vehicle1;Tech5;Pos3
QuoteID; Vehicle2;Tech5;Pos3
QuoteID; Vehicle1;Tech5;Pos4
QuoteID; Vehicle2;Tech5;Pos4

My other thought would be to use 3 subforms instead of listboxes, add a
Check Box field to each of the child tables. Then just have a query insert
based off of the records that are checked.

After switching records, reset all the checks to un-checked.

Thanks,
MikeZz

 

[Nikos Yannacopoulos]

Mike,

Your code should be something like:


Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = Me.RecordsetClone
For each vid in Me.lstVehicle.ItemsSelected
For each tid in Me.lstTechnology.ItemsSelected
For each pid in Me.lstPosition.ItemsSelected
rst.AddNew
rst.Fields(0) = Me.txtQuoteID
rst.Fields(1) = vid
rst.Fields(2) = tid
rst.Fields(3) = pid
rst.Update
Next
Next
Next
rst.Close
Set rst = Nothing
Set db = Nothing


where I have assumed that the value for QuoteID is read from a control
on the same box, named txtQuoteID; change as required.

Note: To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools

References; check if a Microsoft DAO reference is present among the

ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos

 

[Guest]

I forgot to mention one thing....
The recordset I want to update is not the Main Form Record Set, but the
Recordset of the subform called "fsubQuoteContent".

So, I haven't tried it but I'm guessing this code will udpate the main form
table. How do I tell it to use the recordset of the subform?

Thanks!
MikeZz

 

[Nikos Yannacopoulos]

Mike,

Change line:

Set rst = Me.RecordsetClone

to:

Set rst = Me.fsubQuoteContent.Form.RecordsetClone

It should do the job.

HTH,
Nikos

 

[Guest]

Nikos,
I did what you recommended and I get "Type 13 Mismatch error" and it stops
Does it matter that both the cmdButton, 3 lists & subform are all on a
multi-page object called: "pagExtras"? Here is my exact code now:

Thanks for the help.

Private Sub cmdInsertList_Click()
Dim db As Database
Dim rst As Recordset
Dim vid As Variant
Dim tid As Variant
Dim PID As Variant
Set db = CurrentDb

Set rst = Me.fsubQuoteContent.Form.RecordsetClone

For Each vid In Me.lstVehicles.ItemsSelected
For Each tid In Me.lstTech.ItemsSelected
For Each PID In Me.lstPosition.ItemsSelected
rst.AddNew
rst.Fields(0) = Me.txtQuoteID
rst.Fields(1) = vid
rst.Fields(2) = tid
rst.Fields(3) = PID
rst.Update
Next
Next
Next
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[Guest]

Nikos,
I got the code to start to work by changing the line to:
Dim rst As DAO.Recordset
instead of just Dim rst As Recordset.... not sure why.

Now I always get error '3421 Data Type Conversion Error' on this line:
rst.Fields(0) = Me.txtQuoteID

The control field for txtQuoteID is a GUID, and the field I'm trying to
insert is of Type Replicatin ID.

When I put some code to msgbox the value of txtQuoteID, I only get a single
boolean value. It's like it considers that field to be a 16 part array, each
part with a boolean value instead of 1 alpha-numeric number.

Any Ideas on what could be going on?

Thanks

 

[Nikos Yannacopoulos]

Mike,

Dim rst as Recordset defines rst as an ADO recordset (ADO is the
default), whereas the code is for a DAO recordset; that's why it works
when you specifically declare it as such, but doesn't otherwise.

Now, to your new problem, I'm afraid I haven't had any similar
experience, so I can't be of much help... suggest you start a new thread
on this particular issue.

Nikos

 

[Guest]

After doing some research, I found it was easier to just change from a GUID
to a text field with some constraints based on which user was creating the
record...thereby eliminating the chance of creating duplicate keys.

Thanks for the feedback,
Mike