Object Required?

[HandJT]

I get a run-time error message 424 object required using the following code and I'm not sure why.

rs = db.OpenRecordset("Select * " & _
"FROM Plant_Employees " & _
"WHERE Plant_Employees.[Clock#] = " & Val(Employee_Scrap.[Clock#]) & ";")


Does anybody have any suggestions on how I can change it?
Thanks in advance!!

 

[CajunCenturion]

How are 'rs' and 'db' defined and initialized?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

Dim db As DAO.Database
Dim rs As DAO.Recordset


Is this what you're looking for?

 

[CajunCenturion]

Is 'db' being initialized to a specific database. Such as
Set db = CurrentDb
or something similar?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

Yes, it is:

Set db = OpenDatabase("my database.mdb")

That's why it's confusing me.

 

[CajunCenturion]

What version of Access are you using?
Could you please post the entire code?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TheAceMan1]

How are ya HandJT . . . . .

[blue]CajunCenturion[/blue] has already touched on it. If its the current database try

[purple]Set db = CurrentDB()[/purple]

See Ya! . . . . . .

 

[HandJT]

I'm using Access 2000. Here is the entire code. I still can't seem to figure it out. Thanks a million for your help.


Private Sub To_save_the_record_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim intClock As Integer

Dim test As String

Set db = OpenDatabase("E:\Documents and Settings\Honey\My Documents\Copy of Employee Scrap.mdb")

'On Error GoTo Err_To_save_the_record_Click
Expires = [DateNow] + 183

'For the Plant Employees table
'If it is a Repair then it needs to go to the R Points field
'with points (or a fraction of a point) added
'If it is a Scrap then it needs to go to the S Points field
'with points (or a fraction of a point) added
'According to Clock #

'For Repairs

If [Repair or Scrap].Value = 1 Then
If OperationName.Value = 8 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Plant_Employees " & _
"WHERE Plant_Employees.[Clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName.Value = 9 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName.Value = 10 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName.Value = 11 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName.Value = 12 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName.Value = 13 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.33
rs.Update

Else
If OperationName.Value = 14 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.33
rs.Update

Else
If OperationName.Value = 15 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 0.5
rs.Update

Else
If OperationName <> "" Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))


rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs![R Points] = rs![R Points] + 1
rs.Update
End If
End If
End If
End If
End If
End If
End If
End If
End If
Else '****************************************************
'For Scraps

If OperationName.Value = 8 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Plant_Employees " & _
"WHERE Plant_Employees.[Clock#] = " & Val(Plant_Employees.Clock#) & ":")

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value = 9 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value = 10 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value = 11 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value = 12 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value = 13 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.33
rs.Update

Else
If OperationName.Value = 14 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.33
rs.Update

Else
If OperationName.Value = 15 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 0.5
rs.Update

Else
If OperationName.Value <> "" Then
Set rs = db.OpenRecordset("Select * " & _
"FROM plant_employees " & _
"WHERE Plant_Employees.[clock#] = " & Val(Clock.Value))

rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs![S Points] = rs![S Points] + 1
rs.Update

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

'********************************************************
'For the Office SandR table

intClock = Val(Clock.Value)

If intClock = 10 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 10")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 10")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 11 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 11")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 11")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 20 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 20")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 20")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 21 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 21")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 21")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 22 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 22")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 22")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 23 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 23")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 23")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 24 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 24")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 24")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 25 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 25")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 25")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 30 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 30")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 30")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 31 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 31")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 31")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 32 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 32")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 32")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 40 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 40")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 40")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 50 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 50")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 50")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 51 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 51")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 51")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If

Else
If intClock = 52 Then
If [Repair or Scrap].Value = 2 Then
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 52")
rs.Edit
rs![Scraps] = rs![Scraps] + 1
rs.Update
Else
Set rs = db.OpenRecordset("Select * " & _
"FROM Office_SandR " & _
"WHERE Office_SandR.[clock#] = 52")
rs.Edit
rs![Repairs] = rs![Repairs] + 1
rs.Update
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End If

'*******************************************************
'*******************************************************


Exit_To_save_the_record_Click:
Exit Sub

Err_To_save_the_record_Click:
MsgBox Err.Description
Resume Exit_To_save_the_record_Click


End Sub

Private Sub Clear_the_form_for_new_entry_Click()
On Error GoTo Err_Clear_the_form_for_new_entry_Click


DoCmd.GoToRecord , , acNewRec

Exit_Clear_the_form_for_new_entry_Click:
Exit Sub

Err_Clear_the_form_for_new_entry_Click:
MsgBox Err.Description
Resume Exit_Clear_the_form_for_new_entry_Click


End Sub

 

[CajunCenturion]

You are using Access 2000. The default data model for A2K is ADO, and it is obviously your intention to use DAO. In order to use DAO, you must include a reference to the DAO object model. From the IDE, Tools->References, and then pick the latest DAO object library that you have.

You also need to Close the recordset before you try to re-open it. May I also suggest that you take a look at the Case statement to simply and reduce the overall code. For example, the Repairs section could be rewritten as follows:

If [Repair or Scrap].Value = 1 Then
   If (OperationName <> "") Then
      Set rs = Db.OpenRecordset("SELECT * " & _
                                "FROM   Plant_Employees " & _
                                "WHERE  Plant_Employees.[Clock#] = " & Val(Clock.Value))
      rs.Edit
      rs![Repairs] = rs![Repairs] + 1
      Select Case OperationName.Value
         Case 8, 9, 10, 11, 12, 15
            rs![R Points] = rs![R Points] + 0.5
         Case 13, 14
            rs![R Points] = rs![R Points] + 0.33
         Case Else
            rs![R Points] = rs![R Points] + 1
      End Select
      rs.Update
      rs.Close
   End If
Else

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

I rewrote the code using the example you provided. I already referenced the object model to DAO. And yet still the run-time error occurs in the "Set rs = ....". In the Where clause. Any other suggestions?

Thanks

 

[CajunCenturion]

Are you sure that the db is being opened successfully?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

I'm sure it is. Is there any way I can check in case it isn't? Sorry, kind of new at this.

 

[HandJT]

I reset the Database as "Set Db = Currentdb()"
This seems to be working just fine, but still I'm getting that message.

 

[KenReay]

Hi

A few days ago there was a similar post, problem was said to be the # in the column name, you have a # in the column name

"WHERE Plant_Employees.[Clock#] = " & Val(Employee_Scrap.[Clock#]) & ";")

could you try renaming [Clock#] to ClockNo


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[CajunCenturion]

With specific attention to the Where clause

WHERE Plant_Employees.[Clock#] = " & Val(Clock.Value))

Is [Clock#] a valid field within the Plant_Employees table?

Consider Clock.Value - How, where is Clock defined?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

Yes cajunCenturion, the Clock# is a valid field in Plant_Employees table. I'm not sure what you mean in how, where is Clock defined. If you mean in the table, design view. It's just a number field. I'm going to try doing what KenReay suggested and change the # to No and I'll let you know how that works out. And if you could let me know how, where Clock is defined, I'd really appreciate it. Thanks.



Also, KenReay, I can try using No instead of #, but like I said, I haven't had a problem with that before. I have to change some things, but I'll let you know how it works out. Hopefully it solves the problem. I'm getting more and more baffeled as to why I would get that error. Thanks.

 

[CajunCenturion]

If Clock is just a number field, then it doesn't have a .Value property. Only an object would have a .Value property. That could be your error. Clock is not an object, but be asking for it's .Value property the system is responding with ".Value property, I don't have an object call 'Clock' with a .Value property - An Object is required here."

I cannot possibly tell you where or how clock is defined, because I wasn't involved in the design or implementation of the application.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

Good point. I just came to that conclusion myself when I was changing # to No, although you summed it up pretty good. So, how would fix this problem? Would I change the Clock field from number to maybe text or would I have to rewrite that part of the code? Also, if the latter is the case, how would I do that?

 

[CajunCenturion]

It all depends on how Clock is declared, and only you can provide that information.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HandJT]

Thanks all for your help. It was very informative and helpful. I appreciate it greatly.