changes in 2003

[Smouch Com]

i am trying to call a VBscript that works in 2000 but not in 2003 because
its all changed and its saying that DAO.QueryDb and DAO.RecordSet is
undefined.

Private Sub Form_Current()
If Me.[Case ID] Then
Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef

Set qry = CurrentDb.CreateQueryDef("", "select Date, LastName, [Case
Id], from [Case table] where Case ID=" & Str(Me..[Case ID]))
Set rs = qry.OpenRecordset

With rs
.MoveFirst
Me.text15 = !LastDate
End With

End If

 

[RobFMS]

In a code module, look under Tools->References and make sure you have a
reference checked for DAO 3.5 (or higher). It sounds like you started clean
in an Access 2003 database. As such, the default references are set for ADO,
not DAO.

HTH

Rob Mastrostefano

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Allen Browne]

1. From the code window, choose References on the Tools menu.
Make sure the box is checked beside:
Microsoft DAO 3.6 Library.
More on references:
http://allenbrowne.com/ser-38.html

2. CurrentDb may not have a long enough lifetime for what you want. Declare
a Database variable. Alternatively, use the SQL statement itself to open the
recordset.

3. Add square brackets around the names that contain spaces.

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

If Not IsNull(Me.[Case ID]) Then
Set db = CurrenttDb()
strSql = "select LastDate, LastName, [Case Id], from [Case table]
where Case ID=" & Str(Me..[Case ID]) & ";"
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
Me.text15 = rs!LastDate
End If
rs.Close
Set rs = Nothing
End If
End Sub

BTW, you could try just this line:
Me.text15 = DLookup("LastDate", "Case table", "[Case ID] = " & Me.[Case
ID])

We are assuming that CaseID is a Number type field (not a Text type field.)

More help with DLookup():
http://allenbrowne.com/casu-07.html