Object Doesn't Support this Property or Method Error Message (1 Viewer)

Pauline123

Registered User.
Local time
Today, 20:30
Joined
Apr 1, 2013
Messages
69
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

Super Moderator
Staff member
Local time
Today, 16:30
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 20:30
Joined
Apr 1, 2013
Messages
69
Hi have now attached the database in question.
 

Attachments

  • PersonalDatabase - Theory.zip
    597.2 KB · Views: 211

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
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

Super Moderator
Staff member
Local time
Today, 16:30
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 20:30
Joined
Apr 1, 2013
Messages
69
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

Registered User.
Local time
Today, 20:30
Joined
Apr 1, 2013
Messages
69
Hey Jdraw I will take your comments on board and make the adjustments - thank you for your insight :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:30
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 20:30
Joined
Apr 1, 2013
Messages
69
Ah so I had referenced to the label and not the field lol :banghead:
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:30
Joined
Jan 23, 2006
Messages
15,364
10-4

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

Attachments

  • TablesInDatabase.jpg
    TablesInDatabase.jpg
    19.2 KB · Views: 210

Users who are viewing this thread

Top Bottom