Sample Calculator

[vipersmind]

I have recently changed to 2000 and am experiencing probs with my code

This code calclulates Samples and sample intervals, well it used to but now it prompts the message:

User defined type not defined

Im' guessing it is something 97 did and now 2000 does it differently.
The red shows where I'm getting the probs.


Sub SampCal()
On Error GoTo ErrorHandler
Dim MyDb As Database
Dim SampleT As Table

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set SampleT = MyDb.OpenTable("SampleCalc")
Top = Forms!SamCalc![Top]
Bottom = Forms!SamCalc![Bottom]
Interval = Forms!SamCalc![SamInt]
NumSamp = Int((Bottom - Top) / Interval) - 1
Holeid = Forms!SamCalc![Holeid]
SampNum = Forms!SamCalc![SamFirst]
SampPre = Forms!SamCalc![SPrefix]
DepFrom = Top - Interval
SampNum = SampNum - 1
For x = 0 To NumSamp Step 1
DepFrom = DepFrom + Interval
DepTo = DepFrom + Interval
SampNum = SampNum + 1
SampleT.AddNew
SampleT![Holeid] = Holeid
SampleT![From] = DepFrom
SampleT![To] = DepTo
SampleT![SAMPLEID] = SampPre & Format$(SampNum, "00")
SampleT.Update
Next x
SampleT.Close
MsgBox "Completed!" & vbCrLf _
& vbCrLf _
& "View and Check Before Appending", vbInformation
Exit Sub
ErrorHandler:
Select Case Err
Case 3022: MsgBox "This Sample Range is already calculated!.", vbExclamation
Case 13: MsgBox "What the hell are you doing to me ?"
Case Else
MsgBox " Error " & Err & " Occurred - Easy Tiger.....what are you doing out there? !"
End Select
SampleT.Close
Exit Sub
Resume Next
End Sub


Hope you get what it does from this
Cheers

 

[Rich]

Dim MyDb As DAO Database

 

[dcx693]

Don't forget the . in between DAO and Database like this:
Dim MyDb As DAO.Database

But I'm confused, "Table" is not an object type, at least in DAO 3.6, TableDef is.

 

[vipersmind]

First problem solved, created another

Thanks Rich and dcx693

It seems that my code it becoming defunct

When I wrote this most of it I got running by trail and error and with the help of a couple of thick books (mainly used to bash the monitor). Since it ran I didn't question if it was valid...

I don't know how I was allowed to use 'Table' as an object type either but it ran with no problems in 97???

I have changed:

Dim Mydb as DAO.Database
Dim SampleT As TableDef

But am now prompting the error

Method or Data member not found

for

For x = 0 To NumSamp Step 1
DepFrom = DepFrom + Interval
DepTo = DepFrom + Interval
SampNum = SampNum + 1
SampleT.AddNew
SampleT![Holeid] = Holeid
SampleT![From] = DepFrom
SampleT![To] = DepTo
SampleT![SAMPLEID] = SampPre & Format$(SampNum, "00")
SampleT.Update

This is not supported either.......

Grrr

 

[Rich]

Something like
Dim db As DAO. DATABASE
Dim rst As Recordset

Dim I As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SampleT")
rst.MoveFirst 'orLast as the case may be
For x = 0 To NumSamp Step 1

rst.AddNew
rst![Holeid] = Holeid etc

 

[dcx693]

vipersmind, I don't know how your code ever ran! Use Rich's code sampe. Of course, don't forget to have an rst.update in there.

 

[vipersmind]

Thanks Guys beats me how it ran, just did.

I'll put your suggestions into action and see how we go.

Thanks heaps