changes in 2003

S

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
 
A

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
 
Top