adding items from a multiselect list

[bigwillno2 via AccessMonster.com]

Hello, i am trying to add items from a listbox called MyList into a table
called MSM. This listbox contains query with unmatching items between MSM and
LinkedTable.
fields in MSM are ModelNumber(text), Desc, SpringID, BorderID etc......
fields in LinkedTable are ID_1, OrderNo, Comment.

the fields that are being compared are, ModelNumber and ID_1 the are similar
data. over a click, this is what i have:

Private Sub addParts_Click()
Dim varItem As Variant
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("MSM", dbOpenDynaset)
For Each varItem In Me.MyList.ItemsSelected
With rst
.AddNew
!ID_1 = Me.MyList(varItem)
.update
End With
Next
rst.close
Set rst = Nothing
Set db = Nothing
End Sub

this code gives me an unmatching datatype and pointing at this line: !ID_1 =
Me.MyList(varItem)
my question is where is my systax wrong and how can i flag or get rid of the
items once they already added into the table.

 

[Douglas J. Steele]

What's the data type of ID_1? What values are being returned by
Me.MyList(varItem)?

Assuming ID_1 is a Long Integer, you could try:

!ID_1 = CLng(Me.MyList(varItem))

 

[bigwillno2 via AccessMonster.com]

thanks for the response Steele,
i got it to work the way i wanted to only that it is only passing one field
to the form.
the fields that i want to pass is ModelNumber and Description. this is what i
have and i hope you could help me with this.

Private Sub addParts_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSel As String
Dim strWhere As String
Dim msg As String

If Me.MyList.ItemsSelected.Count = 0 Then
msg = "No items selected."
typ = vbExclamation
ttl = "Can't add records"
MsgBox msg, typ, ttl
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("MSM")
For Each itm In Me.MyList.ItemsSelected
rst.AddNew
rst.Fields("ModelNumber") = Me.MyList.ItemData(itm)
rst.update
strSel = "'" & Me.MyList.ItemData(itm) & "',"
Next
rst.close
Set rst = Nothing
Set db = Nothing
strSel = Left(strSel, Len(strSel) - 1)
strWhere = "ModelNumber In (" & strSel & ")"
DoCmd.OpenForm "MSM", , , strWhere
End Sub

What's the data type of ID_1? What values are being returned by
Me.MyList(varItem)?

Assuming ID_1 is a Long Integer, you could try:

!ID_1 = CLng(Me.MyList(varItem))

Hello, i am trying to add items from a listbox called MyList into a table
called MSM. This listbox contains query with unmatching items between MSM

[quoted text clipped - 30 lines]

the
items once they already added into the table.

 

[Douglas J. Steele]

Assuming that ModelNumber is the first column in the listbox, and
Description is the second column, you'll need something like:

rst.AddNew
rst.Fields("ModelNumber") = Me.MyList.Column(0, itm)
rst.Fields("Description") = Me.MyList.Column(1, itm)
rst.update

Note that the Column collection starts counting at 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thanks for the response Steele,
i got it to work the way i wanted to only that it is only passing one
field
to the form.
the fields that i want to pass is ModelNumber and Description. this is
what i
have and i hope you could help me with this.

Private Sub addParts_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSel As String
Dim strWhere As String
Dim msg As String

If Me.MyList.ItemsSelected.Count = 0 Then
msg = "No items selected."
typ = vbExclamation
ttl = "Can't add records"
MsgBox msg, typ, ttl
Exit Sub
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("MSM")
For Each itm In Me.MyList.ItemsSelected
rst.AddNew
rst.Fields("ModelNumber") = Me.MyList.ItemData(itm)
rst.update
strSel = "'" & Me.MyList.ItemData(itm) & "',"
Next
rst.close
Set rst = Nothing
Set db = Nothing
strSel = Left(strSel, Len(strSel) - 1)
strWhere = "ModelNumber In (" & strSel & ")"
DoCmd.OpenForm "MSM", , , strWhere
End Sub

What's the data type of ID_1? What values are being returned by
Me.MyList(varItem)?

Assuming ID_1 is a Long Integer, you could try:

!ID_1 = CLng(Me.MyList(varItem))

Hello, i am trying to add items from a listbox called MyList into a
table
called MSM. This listbox contains query with unmatching items between
MSM

[quoted text clipped - 30 lines]

the
items once they already added into the table.

 

[bigwillno2 via AccessMonster.com]

Hey Steele,

this helped me with solve the problem, you are great.........thanks. now i am
wondering if you could give me some hint on this following problem. i like to
set my result base on two criteria. RequiredDate AND OrderNO. This is what i
have. OrderNo is one of the items on the list

On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String

strDelim = "#" 'Delimiter appropriate to field type.
strDoc = "rptProduction"

'Loop through the ItemsSelected in the list box.
With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible column.

strDescrip = strDescrip & """" & .Column(1, varItem) & ""","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Desc" & Left$(strDescrip, lngLen)
End If
End If
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Assuming that ModelNumber is the first column in the listbox, and
Description is the second column, you'll need something like:

rst.AddNew
rst.Fields("ModelNumber") = Me.MyList.Column(0, itm)
rst.Fields("Description") = Me.MyList.Column(1, itm)
rst.update

Note that the Column collection starts counting at 0.

thanks for the response Steele,
i got it to work the way i wanted to only that it is only passing one

[quoted text clipped - 48 lines]

 

[Douglas J. Steele]

You're passing the concatenated string of Descriptions as an OpenArgs
parameter. If you want it as a criteria, you need to add it to strWhere.

Note that there's no need to check whether varItem is Null: it's coming from
the ItemsSelected collection, so you know it's not null.

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") And " & _
"Desc IN (" & Left$(strDesc, Len(strDesc) - 1) & ")"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hey Steele,

this helped me with solve the problem, you are great.........thanks. now i
am
wondering if you could give me some hint on this following problem. i like
to
set my result base on two criteria. RequiredDate AND OrderNO. This is what
i
have. OrderNo is one of the items on the list

On Error GoTo Err_Handler
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim strLabel As String

strDelim = "#" 'Delimiter appropriate to field type.
strDoc = "rptProduction"

'Loop through the ItemsSelected in the list box.
With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column.

strDescrip = strDescrip & """" & .Column(1, varItem) &
""","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 4
If lngLen > 0 Then
strDescrip = "Desc" & Left$(strDescrip, lngLen)
End If
End If
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Assuming that ModelNumber is the first column in the listbox, and
Description is the second column, you'll need something like:

rst.AddNew
rst.Fields("ModelNumber") = Me.MyList.Column(0, itm)
rst.Fields("Description") = Me.MyList.Column(1, itm)
rst.update

Note that the Column collection starts counting at 0.

thanks for the response Steele,
i got it to work the way i wanted to only that it is only passing one

[quoted text clipped - 48 lines]

the
items once they already added into the table.

 

[bigwillno2 via AccessMonster.com]

Hey Steele,

it gives me an error now. is it the same syntax if i wanted to make a number
field the second criteria. or do i have to change it. would it go something
like this

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") AND" & _"
[OrderNo] IN ( " & Me.sched & ")
this also gives me an error
any other suggestion....thanks again

You're passing the concatenated string of Descriptions as an OpenArgs
parameter. If you want it as a criteria, you need to add it to strWhere.

Note that there's no need to check whether varItem is Null: it's coming from
the ItemsSelected collection, so you know it's not null.

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") And " & _
"Desc IN (" & Left$(strDesc, Len(strDesc) - 1) & ")"

Hey Steele,

[quoted text clipped - 62 lines]

 

[Douglas J. Steele]

What's the data type of OrderNo? If it's Text, you need quotes around the
value you're passing:

"[OrderNo] = """ & Me.sched & """"

It's also not clear to me (due to word wrap) if you've got a space between
the keyword AND and the field name [OrderNo].

In general, the best approach is to print out strWhere (using Debug.Print)
and make sure that it looks correct.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey Steele,

it gives me an error now. is it the same syntax if i wanted to make a
number
field the second criteria. or do i have to change it. would it go
something
like this

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") AND" &
_"
[OrderNo] IN ( " & Me.sched & ")
this also gives me an error
any other suggestion....thanks again

You're passing the concatenated string of Descriptions as an OpenArgs
parameter. If you want it as a criteria, you need to add it to strWhere.

Note that there's no need to check whether varItem is Null: it's coming
from
the ItemsSelected collection, so you know it's not null.

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") And " & _
"Desc IN (" & Left$(strDesc, Len(strDesc) - 1) & ")"

Hey Steele,

[quoted text clipped - 62 lines]

the
items once they already added into the table.

 

[bigwillno2 via AccessMonster.com]

Hey Steele;

the OrderNo is a number type of data. does go like this

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") AND "[OrderNo]
= " & Me.sched &
or am i wrong

What's the data type of OrderNo? If it's Text, you need quotes around the
value you're passing:

"[OrderNo] = """ & Me.sched & """"

It's also not clear to me (due to word wrap) if you've got a space between
the keyword AND and the field name [OrderNo].

In general, the best approach is to print out strWhere (using Debug.Print)
and make sure that it looks correct.

Hey Steele,

[quoted text clipped - 24 lines]

 

[Douglas J. Steele]

Not sure what you mean by "does go like this". Are you saying it works or
that it doesn't work?

Try sticking a Debug.Print strWhere immediately after where you've assigned
the value to the variable, and check the Immediate Window (Ctrl-G) to see
what's in the string.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey Steele;

the OrderNo is a number type of data. does go like this

strWhere = "[RequiredDate] IN (" & Left$(strWhere, lngLen) & ") AND
"[OrderNo]
= " & Me.sched &
or am i wrong

What's the data type of OrderNo? If it's Text, you need quotes around the
value you're passing:

"[OrderNo] = """ & Me.sched & """"

It's also not clear to me (due to word wrap) if you've got a space between
the keyword AND and the field name [OrderNo].

In general, the best approach is to print out strWhere (using Debug.Print)
and make sure that it looks correct.

Hey Steele,

[quoted text clipped - 24 lines]

the
items once they already added into the table.

 

[bigwillno2 via AccessMonster.com]

hey steele;
no it doesnt work and as for the debug.print, somehow, it gives me back the
result of a mudule i have debuging the tables i have and i dont find a way to
reset it or clear it so i could give me the value of of the strWhere. thanks
for your help. any other suggestion. i could tell you though that, the
RequiredDate by itself works perfectly, i just need to combine it with
OrderNo ,which is a number field on the list. thanks again.

Not sure what you mean by "does go like this". Are you saying it works or
that it doesn't work?

Try sticking a Debug.Print strWhere immediately after where you've assigned
the value to the variable, and check the Immediate Window (Ctrl-G) to see
what's in the string.

Hey Steele;

[quoted text clipped - 21 lines]