INSERT INTO statement - insert list? 1

[cutestuff]

hi,

I have the following statement:

   stSQL = "INSERT INTO tblVendorDeliverableLog(CommodityCode, DiscLetter, TagNumber, DocTypeID) VALUES ('" & Nz(Me![txtCommodityCode], 0) & "', '" & Nz(Me![txtDiscLetter], 0) & "', '" & Nz(Me![txtTagNumber], 0) & "'"
     CurrentDb.Execute (stSQL)

where DocTypeID is a list (I have the user select checkboxes and I want to log their selections into the table). I do not know how to insert a list. The commodity code, discletter, tagnumber will be the same for say 5 DocTypeIDs (if the list contains 5 selections).

Can anyone help?

Thanks so much in advance.

 

[Andrzejek]

If you have a list of Items in your list box:
Item1
Item2
Item3
Item4

Do you want the Items to go into your DB like:
Item1
Item2
Item3
Item4 (one item per record)

or
Item1, Item2, Item3, Item4
(all Items from the list into one record)



Have fun.

---- Andy

 

[cutestuff]

hi Andy,

I would like to have it as one item per record.
Basically, for DocTypeID I have the user choose from a number of checkboxes and I want their selections to be inserted into the table - one item per record.
So if the user chose: 1251, 1253, 1469, 1218, then the table should look something like?

CommodityCode DiscLetter TagNumber DocTypeID
CC P 5CC11 1251
CC P 5CC11 1253
CC P 5CC11 1469
CC P 5CC11 1218

Many thanks in advance...

 

[PHV]

user choose from a number of checkboxes
How are the checkboxes related to DocTypeID values (like 1251,1253,...) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

How about:

[blue]For i = 0 To DocTypeID.ListCount - 1[/blue]

stSQL = "INSERT INTO tblVendorDeliverableLog(CommodityCode, DiscLetter, TagNumber, DocTypeID) VALUES ('" & Nz(Me![txtCommodityCode], 0) & "', '" & Nz(Me![txtDiscLetter], 0) & "', '" & Nz(Me![txtTagNumber], 0) & "'[blue], '" & DocTypeID.List(i) & "')[/blue]
     CurrentDb.Execute (stSQL)
[blue]Next i[/blue]

Have fun.

---- Andy

 

[cutestuff]

hi Andy,

Many thanks for your reply.
I have another question though.
DocTypeID is actually a part of my subform (but the rest - CommodityCode, DiscLetter, etc. are not).

How do I reference DocTypeID so the code recognizes it?
I tried this:

Me![qryVendorDeliverables subform].Form!DocTypeID

but it still wouldn't recognize it.

Any suggestions?
Thanks again...

 

[Andrzejek]

So close....:

FormName.DocTypeID.List

So if your sub from is named frmMyForm2, you would have:

frmMyForm2.DocTypeID.List

Have fun.

---- Andy

 

[cutestuff]

hi Andy,

Sorry to bother you, but it's still giving me the "Method or Data member not found" error.

here is the code:

     Dim i As Integer
        Dim stSQL As String

      For i = 0 To qryVendorDeliverables_subform.DocTypeID.ListCount - 1

      stSQL = "INSERT INTO tblVendorDeliverableLog(CommodityCode, DiscLetter, TagNumber, DocTypeID) VALUES ('" & Nz(Me![txtCommodityCode], 0) & "', '" & Nz(Me![txtDiscLetter], 0) & "', '" & Nz(Me![txtTagNumber], 0) & "', '" & qryVendorDeliverables_subform.DocTypeID.List(i) & "')"
      CurrentDb.Execute (stSQL)
      Next i

 

[Andrzejek]

What is the name of the Form that your list DocTypeID is on?

If you are on Form1 and have a text box named txtTagNumber, you use its text this way (I guess):

Me![txtTagNumber]

Or you could say:

Me.txtTagNumber.Text or Form1.txtTagNumber.Text or just txtTagNumber.Text

But if you have a control on another Form, you need to state where the control is, ie Form.Control.Property


Have fun.

---- Andy