lfarnsworth
Registered User.
- Local time
- Today, 10:25
- Joined
- Jul 15, 2008
- Messages
- 25
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:
Here is my attempt using AddNew:
Any advice would be appreciated.
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.