Error with DAO.Recordset Method or database not found

[data59]

I'm having problems with this code and I'm getting the massage "Method or Database not found” I’m a newbie to VBA so any help would be great. Thanks

Option Compare Database
Option Explicit

Private Sub Calc_Click()

Dim dbs As DAO.Databases
Dim rst As DAO.Recordset
Dim wtb As Integer
Dim r As Integer
Dim sr As Integer
Dim x As Integer
Dim y As Integer
Dim w As Integer
Dim th As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("weektbl", dbOpenDynaset)

wtb = Format(Me.DesignComplete(), "ww") - Format(Me.ReadyToSample(), "ww")

Me.WeekToBuild = wtb

r = Me.TotalHr / Me.WeekToBuild
sr = Format(Me.ReadyToSample(), "ww")
Me.WeekNum = sr
Me.Result = r
Me.AutoIDTool


x = Format(Me.ReadyToSample(), "ww")
y = wtb + x
w = Format(Me.DesignComplete(), "ww")
th = Me.TotalHr / wtb
y = wtb + w


For x = w To y
  If x < y Then
   rst.AddNew
   rst("WeekNum") = x
   rst("JobHr") = th
   rst.Update
   rst.NextRecordset
End If

Next x


End Sub

 

[PHV]

Replace this:
Dim dbs As DAO.Databases
with this:
Dim dbs As DAO.Database

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[data59]

Thanks PHV that did the trick, eyes are not what they use to be. I have a another problem however, I'm getting an error due to referential integrity of "you can not change record because a related record is required in table.” If I remove the relationship the code works correctly, accept it will not tie the record to the tblMain IDTool. Thanks for any help

Here are my tables

tblMain

IDTool (KEY)
Year
ToolNum
DesignStart
StartDate
CompleteDate
WeeksToBuild
TotalHr


tbl_sub_Main


AutoIDTool (KEY)
IDTool (1 to many)
ToolNum
Year
WeekNum
JobHr
JobType

Option Compare Database
Option Explicit



Private Sub Enter_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim wtb As Integer
Dim r As Integer
Dim sr As Integer
Dim x As Integer
Dim y As Integer
Dim w As Integer
Dim th As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblsubMain", dbOpenDynaset)

wtb = Format(Me.CompleteDate(), "ww") - Format(Me.StartDate(), "ww")

Me.WeeksToBuild = wtb





x = Format(Me.CompleteDate(), "ww")
y = wtb + x
w = Format(Me.StartDate(), "ww")
th = Me.TotalHr / wtb
y = wtb + w


For x = w To y
  If x < y Then
   rst.AddNew
   rst("WeekNum") = x
   rst("JobHr") = th
   rst.Update
  
End If

Next x
End Sub