help with syntax

[Guest]

My if stmt is not in the correct syntax and I've tried rst.Carrier; Carrier
and the following. It always empty and I know there is a record in the table.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb

Set rst = db.OpenRecordset("WorkTable", dbOpenForwardOnly)
rst.MoveFirst

'check to see if RRG policy exist in table and if so use existing policy
number.
If rst(Carrier) = "RRL" Then

 

[Douglas J. Steele]

If rst("Carrier") = "RRL" Then

or

If rst!Carrier = "RRL" Then

Of course, that will only look at the first row in the recordset, so if the
first row isn't "RRL", then the If statement will be false: you'd need to
loop through the recordset until you found the first RRL row.

What not only look for RRL rows?

 

[Stefan Hoffmann]

hi Leslie,

My if stmt is not in the correct syntax and I've tried rst.Carrier; Carrier
and the following. It always empty and I know there is a record in the table.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb

Set rst = db.OpenRecordset("WorkTable", dbOpenForwardOnly)

If Not rst.Bof And Not rst.Eof Then
If rst![Carrier] = "RRL" Then
End If
End If 'Not rst.Bof And Not rst.Eof


mfG
--> stefan <--

 

[John Spencer]

Why not just use DLookup or DCount in this scenario

If DCount("*","WorkTable","Carrier =""RRL""") =1 Then
...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

thanks to all. it worked great. Table in question only as 1 record at a time
then it's cleared for a new record so the loop thru code I was able to use
with a different file.

Again...Thanks

--
Leslie

hi Leslie,

My if stmt is not in the correct syntax and I've tried rst.Carrier; Carrier
and the following. It always empty and I know there is a record in the table.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb

Set rst = db.OpenRecordset("WorkTable", dbOpenForwardOnly)

If Not rst.Bof And Not rst.Eof Then
If rst![Carrier] = "RRL" Then
End If
End If 'Not rst.Bof And Not rst.Eof


mfG
--> stefan <--