Help with a listbox variable

[mrfixit1170]

Hello All,
Was hoping to get alittle assistance, I have a listBox List68 on a form that has several different

table references. This list68 gets information from another form "sfrm1.List2".


The data from "List2 gets sent over in a Click event,

This is a Private Sub I modded from a form post I found on this site,

Private Sub btnMovetofrmAcft_Score_Click()
    Dim strItems As String
    Dim intItem As Integer
    For intItem = 0 To List2.ListCount - 1
        If List2.Selected(intItem) Then
            strItems = strItems & List2.Column(0, intItem) & ";"
            'strItems = strItems & List2.Column(1, intItem) & ";"
        End If
    Next intItem
    Forms.frm_Acft_Score.List68.RowSource = ""
    Forms.frm_Acft_Score.List68.RowSourceType = "Value List"
    Forms.frm_Acft_Score.List68.RowSource = strItems
        
End Sub

I then use the variable in the click_event to create a row source and value list and plug the

output of the variable(intItem) into the List68 on my original form.


My question is how do I reference this variable on my new form. On my original form, using another click event, I'd like to take specific fields
and transfer them into a table which I call tbl_Scored_Data. I have created the table with the specific fields however the information in the list68 listbox is preventing me from writing the records.

Here is the code sample.

Private Sub btnSubmitScore_Click()
Set db = CurrentDb
sSQL = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code)" _
    & "SELECT " _
    & Me.EI_ID & ", " _
    & Me.EVENT_DATE & ", " _
    & Me.EVENT_NO & ", " _
    & Me.SYS_CODE & ", " _
    & Me.List68 & ","


MsgBox "Records Inserted: " & db.RecordsAffected

I am not sure how to reference the variable to store in a table, now that it has Carried over to the list68 listbox.

Any assistance would be greatly appriciated.

[June7]

What are the form names? Which is 'original' and which is 'new'?

Don't see code that will 'plug the output of the variable(intItem) into the List68 on my original form'.

Why would information in List68 prevent writing to table?

Can't reference variables between forms unless declared as Global.

The SELECT in the INSERT sql is incomplete.

[mrfixit1170]

June7,
Thank you for your reply, Still new at posting questions I will try and clarify,

"What are the form names? Which is 'original' and which is 'new'?"
The "original" form name is frm_Acft_Score, the "new" form name is sfrm1.

sfrm1 is opened on a click event (button on frm_Acft_Score) to display a selection of tasks. When the

selections (on sfrm1) are made the btnMovetofrmAcft_Score_Click() is pressed and sends the selections back to frm_Acft_Score (via the Value List and Rowsource part of click event) and populates List68.

"Why would information in List68 prevent writing to table?"
I'm not really sure how to reference the data in the List68, I understand that the variable is strItems on the sfrm1.
You mentioned "Can't reference variables between forms unless declared as Global."
So do I have to loop through the list using something like a For,Next Loop to grab the values in the List68? I borrowed and modded this code sample from "stackoverflow"

Private Sub btnSubmitScore_Click()
Dim lngRow As Long
Dim strItem As String
Set db = CurrentDb

sSQL = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, strItem)" _

    & "SELECT " _

    & Me.EI_ID & ", " _

    & Me.EVENT_DATE & ", " _

    & Me.EVENT_NO & ", " _

    & Me.SYS_CODE & ", " _
    '& Me.List68 & ","_
With Me.List68
    For lngRow = 0 To List68.ListCount - 1
        If List68.Selected(lngRow) Then
            strItem = strItem & ", " & List68.Column(1, lngRow)
        End If
    Next lngRow
End With

MsgBox "Records Inserted: " & db.RecordsAffected
End Sub

Any thoughts......Still very new at all of this!

[RayMilhon]

I see a couple of problems. 1. In the sql Statement you are inserting 5 Fields. then in the Select portion you have 4 before the for next loop. but the last includes a comma after Then you build your strItem string with the for next loop adding a comma after each iteration of the loop. So your SQL Statement when fully built Looks like this INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, strItem) Select 1,9/12/2011,1,1,1,4,5,6 Assuming that Items 1,4,5,6 are the selected items in the list box. If no Items are selected then you are missing 1 field. Also on the Date there are no # signs to tell access it's a date.

Change the Select portion to " Values (" & me.EI_ID & ", #" & me.EVENT_DAte & "#, " & Me.Event_No & ", " & me.SYS_Code

Bear in mind that any of those values that are string format instead of numeric will also require the " around the Value so Assuming Me.SYS_Code is string It would look like &", "'' & me.sys_Code & """

Finally make sure your listbox only allows the user to select 1 value. and Add that to the string. Don't forget to include the ")" at the end.

The other issue I see is that in the sample you build the SQL but don't do anything with it. Also you don't include the strItem in the SQL Statement. I normally use the ADO Command to run the SQL you're showing.

[mrfixit1170]

RayMilhon,
Thank you for your input, I have made the correction that you have suggested. I'm not really understanding how to fix the VALUES part of the INSERT INTO statement.
Values (" & me.EI_ID & ", #" & Me.EVENT_Date & "#, " & Me.Event_No & ", " & Me.SYS_Code" ) is highlighted in red in my VBA environment, and I get a "Compile error: Expected:expression around the # symbols surrounding the #"& Me.EVENT_DATE & "#.

I added a part to Parse through the listbox and with every strItem in the listbox it would add the
EI_ID,EVENT_DATE, EVENT_No, SYS_Code and write to the table....I think?

Just to clarify, these (EI_ID,EVENT_DATE, EVENT_No, SYS_Code) are textboxes on the form frm_Acft_Score, the List68 holds the strItem's.

Option Compare Database

Private Sub btnSubmitScore_Click()
Dim lngRow As Long
Dim strItem As String
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
sSql = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, strItem) _"
Values (" & Me.EI_ID & ", #" & Me.EVENT_Date & "#, " & Me.Event_No & ", " & Me.SYS_Code" )

'Parses through the Listbox
With Me.List68
    For lngRow = 0 To List68.ListCount - 1
        For Each strItem In List68.ItemsSelected
            rs.AddNew
            rs!EI_ID = Me.EI_ID
            rs!EVENT_DATE = Me.EVENT_DATE
            rs!EVENT_NO = Me.EVENT_NO
            'rs!List68 = Me.List68.Column(0, strItem)
            rs.Update
            rs.Close
        Next strItem
        'Select the rows in the Listboxs
        'If List68.Selected(lngRow) Then
           'strItem = strItem & "," & .Column(0, lngRow)
        'End If
    Next lngRow
End With

'Strips off the leading comma and space
'If Len(strItem) > 2 Then
    'strItem = Mid(strItem, 3)
'End If

'Displays the selection held in variable strItem
MsgBox "Tasks: " & strItem

'Shows how many recoeds where created.
'MsbBox "Records Inserted: " & db.RecordsAffected

End Sub

Again any and all help is welcomed. Thanks!

[RayMilhon]

Ok I can see I wasn't clear enough.

sSql = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, strItem) _"
Values (" & Me.EI_ID & ", #" & Me.EVENT_Date & "#, " & Me.Event_No & ", " & Me.SYS_Code" )

Change To sSql = "INSERT INTO tbl_Scored_Date (EI_ID, Event_Date, Event_No,Sys_Code, strItem) Values (" & me.EI_ID & ", #" & me.Event_Date & "#, " & Me.Event_No & ", " & me.sys_Code "

Put a break point on the With Me.list68 (Line 14)and in the immediate Window put ?sSql You should see something like the following:

INSERT INTO tbl_Scored_Date (EI_ID, Event_Date, Event_No,Sys_Code, strItem) Values (2, #10/01/2011#, 3,1

All on one line.
Add the following after Dim strItem as string
Dim adocmd as New ADODB.command
adocmd.activeconnection = currentproject.connection

Change the next section
For lngRow = 0 To List68.ListCount - 1
For Each strItem In List68.ItemsSelected
adocmd.commandtext = sSQL & ", " & List68.Column(0,stritem)
adocmd.execute
Next strItem
Next lngRow

[mrfixit1170]

Is there a library I need to reference? 'cause I keep getting a compile error: User-define type not defined

Private Sub btnSubmitScore_Click()
Dim lngRow As Long
Dim strItem As String
Dim adocmd As New ADODB.Command
adocmd.activeconnection = CurrentProject.Connection
Dim db As Database
Dim rs As Recordset
 
Set db = CurrentDb
sSQL = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, IETM_ID)_"
Values = ("Me.EI_ID, Me.EVENT_Date, Me.Event_No, Me.SYS_Code, Me.List68")
 
'Parses through the Listbox
With Me.List68
    For lngRow = 0 To List68.ListCount - 1
        For Each strItem In List68.ItemsSelected
           adocmd.commandtext = sSQL & ", " & List68.Column(0, strItem)
           adocmd.Execute
        Next strItem
        'Select the rows in the Listboxs
        'If List68.Selected(lngRow) Then
           'strItem = strItem & "," & .Column(0, lngRow)
        'End If
    Next lngRow
End With
 
'Strips off the leading comma and space
'If Len(strItem) > 2 Then
    'strItem = Mid(strItem, 3)
'End If
 
'Displays the selection held in variable strItem
MsgBox "Tasks: " & strItem
 
'Shows how many records where created.
'MsbBox "Records Inserted: " & db.RecordsAffected
 
End Sub

Thank you for your help....

[June7]

Which line is causing error?

You will need reference to Microsoft ActiveX Data Objects 2.8 Library

Also, the Values list in the SQL will not save the values of the controls, but the control names. If you want the values, must concatenate.

sSQL = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, IETM_ID) " & _
"Values(" & Me.EI_ID & ", #" & Me.EVENT_Date & "#, " & Me.Event_No & ", " & Me.SYS_Code & ", " & Me.List68 & ")"

You almost had the SQL string correct in the first code. If any are text values, use apostrophe delimiter, as I did with the # for the date value.

Is IETM_ID misspelled?

[mrfixit1170]

From classicasp.aspfaq dot com
"You can use ' as a delimiter in Access as long as you know your code will always run against a newer version. If there is a chance you will connect to legacy Access versions, you might be safer continuing to use the # delimiter."

sql = "INSERT INTO Test(dt) VALUES('2004-07-20')" 
conn.execute(sql) 
 
sql = "INSERT INTO Test(dt) VALUES(#2004-07-20#)" 
conn.execute(sql)

Man that was painful to figure out.....anyway I found the reference for the ActiveXData Objects 2.8 Library...thanks! The error came from the adocmd Dim.

IETM_ID actuallys stands for Interactive Electronic Technical Manual

Here is my fixed code

Private Sub btnSubmitScore_Click()
Dim lngRow As Long
Dim strItem As String
Dim adocmd As New ADODB.Command
adocmd.activeconnection = CurrentProject.Connection
Dim db As Database
Dim rs As Recordset
  
Set db = CurrentDb
sSql = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, IETM_ID)" & _
"Values = ('" & Me.EI_ID & "',#& Me.EVENT_Date&#, " & Me.EVENT_NO & ",'" & Me.SYS_CODE & "','" & strItem & "')"
  
'Parses through the Listbox
With Me.List68
    For lngRow = 0 To List68.ListCount - 1
        For Each strItem In List68.ItemsSelected
           adocmd.commandtext = sSql & ", " & List68.Column(0, strItem)
           adocmd.Execute
        Next strItem
        'Select the rows in the Listboxs
        'If List68.Selected(lngRow) Then
           'strItem = strItem & "," & .Column(0, lngRow)
        'End If
    Next lngRow
End With
  
'Strips off the leading comma and space
'If Len(strItem) > 2 Then
    'strItem = Mid(strItem, 3)
'End If
  
'Displays the selection held in variable strItem
MsgBox "Tasks: " & strItem
  
'Shows how many records where created.
'MsbBox "Records Inserted: " & db.RecordsAffected
  
End Sub

I do need the values of those controls so I'll post what I have finished in the morning I'm done for the day...Thanks for all your help....

[June7]

The code to compile the SQL string is still wrong. Review my previous post. Look at the Me.Event_Date concatenation. Missing quotes and spaces. Also should be Values( without = sign. Might also need a space between ) and Values

[mrfixit1170]

Hello Everyone,
I keep getting an error "For Each control variable must be Variant or Object" and the strItem in line

For Each strItem In List68.ItemsSelected

is highlighted. I looked at the msdn library to find the definiton but all it says is "make sure

the group part of the For Each...Next is a Variant type variable." I understand that this maybe

easy programming stuff, its still not clicking for me yet....

Private Sub btnSubmitScore_Click()
Dim lngRow As Long
Dim strItem As String
Dim adocmd As New ADODB.Command
adocmd.activeconnection = CurrentProject.Connection
Dim db As Database
Dim rs As Recordset

   
Set db = CurrentDb
sSql = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, IETM_ID) " & _
"Values(" & Me.EI_ID & ", # & Me.EVENT_DATE & #, # & Me.EVENT_NO & #, " & Me.SYS_CODE & ", " & 

Me.List68 & ")"
   
'Parses through the Listbox
With Me.List68
    For lngRow = 0 To Me.List68.ListCount - 1
        For Each strItem In List68.ItemsSelected
           adocmd.commandtext = sSql & ", " & List68.Column(0, strItem)
           adocmd.Execute
        Next strItem
        'Select the rows in the Listboxs
        'If List68.Selected(lngRow) Then
           'strItem = strItem & "," & .Column(0, lngRow)
        'End If
    Next lngRow
End With
   
'Strips off the leading comma and space
'If Len(strItem) > 2 Then
    'strItem = Mid(strItem, 3)
'End If
   
'Displays the selection held in variable strItem
MsgBox "Tasks: " & strItem
   
'Shows how many records where created.
'MsbBox "Records Inserted: " & db.RecordsAffected
   
End Sub

The table this information is all getting written to is setup like:

tbl_Scored_Data

EI_ID (Text)
Event_Date (Date/Time)
Event_No (Number)
Sys_Code (Text)
IETM_ID (Number)


Any help would be greatly appreciated.

[June7]

Missing quote marks:

"Values(" & Me.EI_ID & ", #" & Me.EVENT_DATE & "#, #" & Me.EVENT_NO & "#, " & Me.SYS_CODE & ", " & Me.List68 & ")"

[RayMilhon]

# should only go around date values not numeric and " should go around string values

If break on that line and in the immediate window type ?sSQL the Values section should look like this Assuming EI_ID, EVENT_NO and SYS_CODE are all numeric values
EVENT_DATE is a date value and the value from List68 is a string value.

Values (12345,#01/01/2011#,1,12345,"Some other value")

That's not what your code is getting.

Change the sSQL to
sSql = "INSERT INTO tbl_Scored_Data (EI_ID, Event_Date, Event_No, Sys_Code, IETM_ID) " & _
"Values(" & Me.EI_ID & ", # & Me.EVENT_DATE & #, # & Me.EVENT_NO & #, " & Me.SYS_CODE & ", "

In the Loop change the adocmd.command text to
adocmd.commandtext = sSql & ", """ & List68.Column(0, strItem) & """)"

[mrfixit1170]

June7,
Thank you for the reply. However whenever I added those quotes inside the pound symbols Access kept having a problem with it....I actually found a site that helped me with the delimiter. I decided to change tack with RayMilhon recommendation, and found a solution that worked for me.

Private Sub btnSubmitScore_Click()
Dim ctl As Control
Dim varItem As Variant
Dim intItem As Long
Dim strItem As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
   
On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Scored_Data", dbOpenDynaset, dbAppendOnly)

'Make sure a selection has been made
If Me.List68.ItemsSelected.Count = 0 Then
 MsgBox "Must select at least 1 Task"
 Exit Sub
End If

'add selected values to table
Set ctl = Me.List68
For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!EI_ID = Me.EI_ID
    rs!EVENT_DATE = Me.EVENT_DATE
    rs!EVENT_NO = Me.EVENT_NO
    rs!SYS_CODE = Me.SYS_CODE
    rs!IETM_ID = ctl.ItemData(varItem)
    rs.Update
Next varItem

'Shows how many records where created.
MsgBox "Records Inserted: " & db.RecordsAffected


ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err
        Case Else
        MsgBox Err.Description
        DoCmd.Hourglass False
        Resume ExitHandler
    End Select

'Displays the selection held in variable strItem
'MsgBox "Tasks: " & intItem
   
End Sub

Thank you RayMilhon and June7 for your advice and recommendations, I will keep the other script to store in my script vault.
How do I mark this thread solved?

[June7]

I should have noticed value was not a Date type. I use dates in SQL frequently. Just as the apostrophe delimiter must be within the quotes, so must the #. If EVENT_NO and List68 are numeric, then no delimiters. As:
"Values(" & Me.EI_ID & ", #" & Me.EVENT_DATE & "#, " & Me.EVENT_NO & ", " & Me.SYS_CODE & ", " & Me.List68 & ")"

Excluding the red highlighted quotes results in a literal string , # & Me.EVENT_DATE & #, & Me.EVENT_NO & , not the values of the controls and one Value short so of course the SQL crashes.

Glad you found satisfactory solution.