Open Recordset

[Mark]

I am not sure what you are trying to do, but you cannot print a Method:

Debug.Print rst.MoveNext

i.e. MoveNext is a Method of the Recordset Object.

To print the value of a variable to the immediate window:

Debug.Print rst![Field1]

If you are just trying to ensure that the value entered into Me!Fieldname
exist in the table then a loop is not necessary. Try

Dim rst As Recordset
Dim strSQLText as String
strSQLText= "Select [field1]" _
& " From Tablename" _
& " Where [field1] = '" & Me!Fieldname & "'"
Set rst = CurrentDB.Openrecordset (strSQLText)
If Not rst.EOF then
DoCmd.OpenForm ("New Form")
Else
MsgBox "Value Not Found"
End if
rst.Close


HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/



From: "ttthello" <[email protected]>
Subject: Open Recordset
Date: 05 March 2004 01:21


I am trying to write an "onClick" function for a button that will check
whether a field in the form is a valid record in a table.

There are some error during compilation and I'm not sure if the
following is correct:

Private sub cmdSubmit_Click ()
Dim rst As Recordset
Set rst = CurrentDB.Openrecordset ("Select [field1] from
Tablename")
rst.MoveFirst
Do
Debug.Print rst.MoveNext
Loop Until rst![Field1] = Me!Fieldname
DoCmd.OpenForm ("New Form")

End Sub

 

[ttthello]

I have tried to execute the following but there is some error,
especially with the 1st statement below.

Set rst=CurrentDB.OpenRecordset("select [Staff_ID] From Staff Where
[Status]='Inactive'"& Me![Staff_ID]"'")

rst.MoveFirst

While (Not rst.EOF)
If (rst!Staff_ID <> Me!Staff_ID) Then
MsgBox ("Staff is inactive")
rst.MoveNext
Wend

 

[Mark]

The Where clause is not correct in the SQL statement.

Try

Dim rst as DAO.Recordset
Dim strSQLText as String
strSQLText ="Select [Staff_ID]" _
& " From Staff " _
& " Where [Status]='Inactive' And [Staff_ID] = " &
Me![Staff_ID]
Set rst=CurrentDB.OpenRecordset(strSQLText)

Note if Staff_ID is a number then it does not need single quotes.