Object Doesn't Support this Property or Method Error Message

[Pauline123]

Hey hope someone can help.

I have an unbound form that I have a multi-select list and unbound fields to enter data into the Theory Training Table.

I thought I had got the bugs out but now keep getting the "Object Doesn't Support..." error message.

The details are:
Forms Name: TheoryTraining
Unbound List Box Name: EmployeeList (EmployeeNo)
Three Combo Boxes: TxtTheoryCategory / TxtTheoryCourse / TxtProvider

VBA Code on Command Button - Click:
Private Sub Command38_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo ErrorHandler

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

'make sure a selection has been made
If Me.EmployeeList.ItemsSelected.Count = 0 Then
MsgBox "You Must Select an Employee"

Exit Sub

End If

'add selected value(s) to table
Set ctl = Me.EmployeeList
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!EmployeeNo = ctl.ItemData(varItem)
rs!TheoryStartDate = Me.txtTheoryStartDate
rs!TheoryExpiryDate = Me.txtTheoryExpiryDate
rs!TheoryCategory = Me.txtTheoryCategory
rs!TheoryCourse = Me.txtTheoryCourse
rs!Provider = Me.txtProvider
rs!ThCertificateNo = Me.txtThCertificateNo
rs!TheoryNotes = Me.txtTheoryNotes
rs!TheoryRenew = Me.txtTheoryRenew

rs.Update
Next varItem

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

End Sub

When I have got it to work it enters a row in the table with the training details, then a further row / rows with the Employee Number. I need the training and the Employee number to be entered on to the same row - can anyone help - Thanks Pauline

 

[jdraw]

Suggest you post a copy of your database in zip format. Also include instructions to get to the problem area. Only include enough data to show/highlight the problem.
Don't post anything private/confidential.

 

[Pauline123]

Hi have now attached the database in question.

 

[pbaldy]

Let's learn how to fish. Temporarily comment out the "On Error GoTo..." line and run the code. Go into Debug and note which line is highlighted. See why?

 

[jdraw]

Further to Paul's advice, I would recommend Not using Lookups at the table field level.
Also, every relational table should have a Primary Key to uniquely identify each record in that table.

Also, it would be helpful for you and readers if there was a description of the key points of the database/application to put the tables and form into context.

Good luck with your project.

 

[Pauline123]

Hi I get this piece of code highlighted

rs!TheoryRenew = Me.txtTheoryRenew

This is a Tick box - which I removed then deleted this field from the Theory Table and it seems to be working fine - thanks for the help - I can't believe it was that simple

 

[Pauline123]

Hey Jdraw I will take your comments on board and make the adjustments - thank you for your insight

 

[pbaldy]

Hi I get this piece of code highlighted

rs!TheoryRenew = Me.txtTheoryRenew

This is a Tick box - which I removed then deleted this field from the Theory Table and it seems to be working fine - thanks for the help - I can't believe it was that simple

No, it isn't a tick box.

That was the label next to the tick box. Labels don't have a value.

 

[Pauline123]

Ah so I had referenced to the label and not the field lol :banghead:

 

[jdraw]

10-4

These tables are in your database (see jpg).
Good luck.