Recordset and NoMatch 2

[eclipse33]

Hi,

I am trying to test a recordset for the NoMatch and pop up a message box if there is no match. But rst.NoMatch always equal False even when there is no match?? Code below...

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT longProjectName FROM Project WHERE projectName= '" & globalResort & "';&quot

If rst.NoMatch = True Then
Dim retValue
retValue = MsgBox("Contact the administrator.", vbExclamation, "Out of Records&quot
Else
comboResort.value = rst![longProjectName]
End If

rst.Close
Set rst = Nothing
Set db = Nothing



Thanks
eclipse33

 

[JohnLowell]

You should use NoMatch with the FindFirst method.

You can still use the Openrecordset, just test for EOF and BOF:

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT longProjectName FROM Project WHERE projectName= '" & globalResort & "';&quot

If rst.EOF and rst.BOF Then
Dim retValue
MsgBox "Contact the administrator.", vbExclamation, "Out of Records"
Else
comboResort.value = rst![longProjectName]
End If

rst.Close
Set rst = Nothing
Set db = Nothing

 

[eclipse33]

Hi John,

Thanks...I tried that before I tried the NoMatch and the same thing is happening. rst.BOF and rst.EOF is always false no matter if there a match or not.

question...If there is no value in the longProjectName table field, Is the recordset actually empty??

ID projectName longProjectName
1 RiverView RiverView Lodge
2 RedWolf
3 SunnyHill SunnyHill Resort

 

[JohnLowell]

How does globalResort get filled?

 

[eclipse33]

It is set on the login screen (first screen) it holds the name of the project the agent logs into. Its is a global variable.

 

[JohnLowell]

I really can't see anything wrong.
If you want, I could take a look at the project.

 

[eclipse33]

I am using Access 97...if that matters??

And...

If there is no value in the longProjectName table field, Is the recordset actually empty?? If I ask for longProjectName WHERE ID=2 will I get a true or false EOF??

ID projectName longProjectName
1 RiverView RiverView Lodge
2 RedWolf
3 SunnyHill SunnyHill Resort


Thanks for your time John

 

[JohnLowell]

EOF will be false, but the rst![longProjectName] will equal Null.

 

[eclipse33]

So if I say...

If rst![longProjectName] = null Then
msgbox "message"
Else

End If

then that should work right? But that doesn't either!

When I go to the debug screen and mouse over the value of rst![longProjectName] is null but it goes to the else??

 

[cmmrfrds]

There was a bug in Access 97 on the BOF and EOF being set properly in Access 2000 it works as expected. In Access 97 you need to do a MoveFirst and MoveLast before testing the Properties.


rst.MoveFirst
rst.MoveLast
If rst.EOF and rst.BOF then
no records
End if

 

[JohnLowell]

Try:
If isnull(rst![longProjectName]) or len(rst![longProjectName]) < 1 Then
msgbox &quot;message&quot;
Else

End If

 

[eclipse33]

Hey John

You are too good!

Thank you very much for your help!!
eclipse33