Unable to get values from a list box

[terraservio]

Hi,

I am trying to use selected values in a list box on a form to create records in a table using a runSQL method. The list box is not bound to a field since i want to make multiple records from the selection. However, for some reason the VBA code below won't return the selected record. Only blank values which appear in the MsgBox. The function results in the invalid SQL syntax error "Syntax error in INSERT INTO statement". The statement works when I substitute text for the variables. So I am fairly confident the SQL statement is correct. Can someone look this over to see if I am missing something? Thanks.

Private Sub btnAddSample_Click()
On Error GoTo Err_btnAddSample_Click


    DoCmd.GoToRecord , , acNewRec
    Dim varItem As Variant
    Dim sampleName, analysisName, SQLstr As String
    Dim analyList As Control
    Set analyList = Forms![Enter Sample Information]![Analyses]
    sampleName = Me.Sample_Name.Value
    For Each varItem In analyList.ItemsSelected
        analyisName = analyList.ItemData(varItem)
        MsgBox (analysisName)
        SQLstr = "INSERT INTO tblSampleAnalyses (Sample_Name,Analysis_Name) VALUES (" & sampleName & "," & analysisName & ");"
        DoCmd.RunSQL SQLstr
    Next varItem

Exit_btnAddSample_Click:
    Exit Sub

Err_btnAddSample_Click:
    MsgBox Err.Description
    Resume Exit_btnAddSample_Click
    
End Sub

[ADezii]

Hi,

I am trying to use selected values in a list box on a form to create records in a table using a runSQL method. The list box is not bound to a field since i want to make multiple records from the selection. However, for some reason the VBA code below won't return the selected record. Only blank values which appear in the MsgBox. The function results in the invalid SQL syntax error "Syntax error in INSERT INTO statement". The statement works when I substitute text for the variables. So I am fairly confident the SQL statement is correct. Can someone look this over to see if I am missing something? Thanks.

Private Sub btnAddSample_Click()
On Error GoTo Err_btnAddSample_Click


    DoCmd.GoToRecord , , acNewRec
    Dim varItem As Variant
    Dim sampleName, analysisName, SQLstr As String
    Dim analyList As Control
    Set analyList = Forms![Enter Sample Information]![Analyses]
    sampleName = Me.Sample_Name.Value
    For Each varItem In analyList.ItemsSelected
        analyisName = analyList.ItemData(varItem)
        MsgBox (analysisName)
        SQLstr = "INSERT INTO tblSampleAnalyses (Sample_Name,Analysis_Name) VALUES (" & sampleName & "," & analysisName & ");"
        DoCmd.RunSQL SQLstr
    Next varItem

Exit_btnAddSample_Click:
    Exit Sub

Err_btnAddSample_Click:
    MsgBox Err.Description
    Resume Exit_btnAddSample_Click
    
End Sub

This code should work quite nicely for you:[CODE=vb]
Dim varItem As Variant
Dim sampleName As Variant
Dim analysisName As Variant
Dim SQLstr As String
Dim analyList As ListBox

DoCmd.GoToRecor d , , acNewRec

Set analyList = Forms![Enter Sample Information]![Analyses]
sampleName = Me.Sample_Name. Value

If analyList.Items Selected.Count > 0 Then
For Each varItem In analyList.Items Selected
analysisName = analyList.ItemD ata(varItem)
DoCmd.SetWarnin gs False
SQLstr = "INSERT INTO tblSampleAnalys es ([Sample_Name], [Analysis_Name]) VALUES ('" & _
sampleName & "', '" & analysisName & "')"
DoCmd.RunSQL SQLstr
DoCmd.SetWarnin gs True
Next varItem
End If[/CODE]

[terraservio]

This code should work quite nicely for you:[CODE=vb]
Dim varItem As Variant
Dim sampleName As Variant
Dim analysisName As Variant
Dim SQLstr As String
Dim analyList As ListBox

DoCmd.GoToRecor d , , acNewRec

Set analyList = Forms![Enter Sample Information]![Analyses]
sampleName = Me.Sample_Name. Value

If analyList.Items Selected.Count > 0 Then
For Each varItem In analyList.Items Selected
analysisName = analyList.ItemD ata(varItem)
DoCmd.SetWarnin gs False
SQLstr = "INSERT INTO tblSampleAnalys es ([Sample_Name], [Analysis_Name]) VALUES ('" & _
sampleName & "', '" & analysisName & "')"
DoCmd.RunSQL SQLstr
DoCmd.SetWarnin gs True
Next varItem
End If[/CODE]

Worked like a charm! Thanks!

[ADezii]

Worked like a charm! Thanks!

You are quite welcome.