Adding data to a table from a multiselect list box

[lfarnsworth]

Hi all,

First, let me tell you that I'm new to VBA progamming and learning as go. I've reach a point where I don't know what I'm doing wrong and I'm hoping you folks can help.

What I'm trying to do is this: I'm building a database to track employee training. I'm trying to build a form that will allow the data entry folks to add new records in batches. What I want to be able to do is select a group of employees (based on the sign in sheet for the training), select the name of the training and enter the date of the training. Then I want to permanently add that data to an existing table, which is then queries for various reports.

What I have is a list box -set to simple multiselect- to select employees, a combo box to select training name and a text box to input date. I've tried to build code using an Append query and using the AddNew method but haven't gotten it to work. I'm sure I'm making some critical mistake but I don't know what.

Here is my attempt at doing it with an Append Query:

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim mySGI As String
Dim myCar As String

Set frm = Forms!Form1
Set ctl = frm!List0

mySGI = List0.BoundColumn
myCar = frm!Text5.Value

For Each varItm In ctl.ItemsSelected
DoCmd.RunSQL "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) SELECT Forms!Form1!List0.ItemData(varItem) AS [ID#], Forms!Form1!Text5 AS [TrainingType]"
Next varItm
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

Here is my attempt using AddNew:

Private Sub Command7_Click()
Dim db As DAO.Database
Dim tbl As DAO.Recordset
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Set db = CurrentDb()
Set tbl = db.OpenRecordset("employee-data", dbOpenDynaset)
Set frm = Forms!Form1
Set ctl = frm!List0
For Each varItem In ctl.ItemsSelected
tbl.AddNew
tbl!ID# = Forms!Form1!List0.ItemData(varItem)
tbl!TrainingType = Forms!Form1!Text5
tbl.Update
Next varItem
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

Any advice would be appreciated.

 

[MarkK]

Hey. Welcome to the forum. Understanding someone else's code is not trivial and it'll offer you an advantage in getting help with your problem if you offer a more detailed description of what isn't working.
Do you get an error? What error, by description, not number? Does the debugger highlight a particular line of code? Which line? Do you get wrong results? Etc...
Cheers,

 

[lfarnsworth]

lagbolt, thanks...

For the AddNew code, I get the run time error: "Item not found in this collection", highlighting this part of the code: "tbl!ID# = Forms!Form1!List0.ItemData(varItem)"

For the Append Query, I either get syntax errors related to the SQL part of the code or instead of treating the List0.ItemData as a value, it treats it as a parameter and asks me for a value.

 

[MarkK]

One possibility here is that you need to evaluate the value of the referenced form property outside of the SQL string. Consider...

...
set frm = forms!form1
set ctl = frm.list0 
...

DoCmd.RunSQL _
  "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) " & _
  "SELECT " & ctl.ItemData(varItem) & ", " & frm.Text5

I don't think you don't need the 'AS' clause in the SELECT portion of the insert, and notice that the value references are now 1) evaluated and then 2) concatenated to the SQL string.

Does that make a difference?

 

[lfarnsworth]

logbolt, I gave that a try. Unfortunately it didn't work.

Instead of appending the values to the table, it treated them as parameters and asked me for their value. Also, interestingly, it did not give me the selected values from the list box as parameters. Rather, it gave me the value of the first item in the list box as a parameter.

 

[MarkK]

1) Not sure if this is an issue, but 'varItm' in your For...Next loop is not the same as 'varItem' in the SQL statement. If it doesn't say Option Explicit at the top of your module, you're actually dealing with two different variables.
2) What I commonly do with an SQL statement that I build on the fly is set a breakpoint before the statement is used and when code stops, copy the sql into the immediate window and prefix it with a '?', so something like...

? "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) " & _
  "SELECT " & ctl.ItemData(varItem) & ", " & frm.Text5

This evaluates and prints the SQL, and you can ascertain whether the SQL string contains the values you expect.
3) Is Training Type a string value in the table? Your SQL may need to enclose it in quotes like...

...", '" & frm.text5 & "'"

4) Is employee-data a query or a table?

 

[lfarnsworth]

1) oops
2) I'll try that.
3) It is a string value, so I'll try that.
4) It's a table.

 

[lfarnsworth]

Latest version of code:

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
    Dim frm As Form
    Dim ctl As Control
    Dim varItem As Variant
    Dim mySGI As String
    Dim myCar As String
 
   Set frm = Forms!Form1
   Set ctl = frm!List0
 
   mySGI = List0.BoundColumn
   myCar = frm!Text5.Value
 
    For Each varItem In ctl.ItemsSelected
    DoCmd.RunSQL "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) " & "SELECT " & ctl.ItemData(varItem) & ", " & frm.Text5 
    Next varItem
Exit_Command7_Click:
    Exit Sub
Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click
 
End Sub

Putting frm.Text5 in quotes results in an error message: syntax error (missing operator) in query expression '&frm.Text5&'.

If I remove the quote marks, the error goes away but it still treats the results as parameters not values. So, why is it seeing them as parameters and (more importantly) how do I convince it to recognize them as values.

 

[MarkK]

Are you sure the [ID#] field is not a string? I can generate the error you describe if I put an unknown string without enclosing quotes in the Select clause...

  "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) " & _
  "SELECT someValue, " & frm.Text5

vs

  "INSERT INTO [employee-data] ( [ID#], [TrainingType] ) " & _
  "SELECT 'someValue', " & frm.Text5

 

[lfarnsworth]

That worked! Thank you lagbolt.