This is probably elementary (showing my newbie status)...

When trying to run (the full version of) the following code, the 
variable 'strGrp' is not recognised. The variable is declared as a string 
(I have also tried it as a variant)and passed by reference to the 
procedure containing the code. I have also tried various formulations (but 
maybe not the right one!!) to ensure that quotation marks are present if 
needed. I have checked to ensure that the correct value has been passed to 
the procedure.  All records are deleted! However if I replace 'strGrp' 
with an actual Group name, all works perfectly ...

I have also tried to get round the problem by redefining 'strGrp' to take 
the full phrasing ( e.g. rst!ShipGroup <> "Home"), omitting the 'rst!
ShipGroup =' from the code. I then get a 'Run time Error 13: Type 
mismatch' message.

Have I got something wrong with the syntax? Is it not possible to pass a 
variable into a code when looping through a recordset, using the 'With 
Statement' (Help files haven't helped.) 

Code:

Set rst = dbs.OpenRecordset("tblShip")

With rst     
    .MoveLast
    .MoveFirst

    Do While Not rst.EOF
    ...
    ElseIf (rst!Change > 0 And rst!ShipGroup <> strGrp) Then
        .Delete
    ...
    Loop
    End If
    .MoveNext
End With

Include the whole subroutine.  It sounds like you are doing it correctly but
without seeing the subroutine definition

Private Sub ProcedureName(byval strGrp as String)

it is hard to tell.

The real question is why not do this with a delete query?  It would be much
faster and simpler to code.

Private Sub DeleteGroup(byval GroupName as string)
   Dim db as DAO.Database
   Dim qdf as DAO.Querydef

   Set db = Currnetdb()
   Set qdf=db.CreateQueryDef("DELETE * FROM [tblShip] " & _
                             "WHERE ([Change] > 0) AND " & _
                             "[ShipGroup] = '" & GroupName & "'"
   qdf.Execute
   Set qdf = Nothing
   Set db = Nothing
End Sub

|-----Original Message-----
|From: Martin Debenham [mailto:martin.debenham@b...]
|Sent: Thursday, July 18, 2002 8:34 PM
|To: Access
|Subject: [access] Using variable when looping through recordset
|
|
|This is probably elementary (showing my newbie status)...
|
|When trying to run (the full version of) the following code, the
|variable 'strGrp' is not recognised. The variable is declared as a string
|(I have also tried it as a variant)and passed by reference to the
|procedure containing the code. I have also tried various formulations (but
|maybe not the right one!!) to ensure that quotation marks are present if
|needed. I have checked to ensure that the correct value has been passed to
|the procedure.  All records are deleted! However if I replace 'strGrp'
|with an actual Group name, all works perfectly ...
|
|I have also tried to get round the problem by redefining 'strGrp' to take
|the full phrasing ( e.g. rst!ShipGroup <> "Home"), omitting the 'rst!
|ShipGroup =' from the code. I then get a 'Run time Error 13: Type
|mismatch' message.
|
|Have I got something wrong with the syntax? Is it not possible to pass a
|variable into a code when looping through a recordset, using the 'With
|Statement' (Help files haven't helped.)
|
|Code:
|
|Set rst = dbs.OpenRecordset("tblShip")
|
|With rst
|    .MoveLast
|    .MoveFirst
|
|    Do While Not rst.EOF
|    ...
|    ElseIf (rst!Change > 0 And rst!ShipGroup <> strGrp) Then
|        .Delete
|    ...
|    Loop
|    End If
|    .MoveNext
|End With

Thank you! (But perhaps there's still something strange ...)

I had not included 'ByVal' in the subroutine - as in your example below:

Private Sub ProcedureName(byval strGrp as String)

Adding this has caused everything to work (so far).  But strangely it also 
works if I now remove 'ByVal'. But the 'ByVal' is staying in ...

And why not a delete query? It's a long story ... Perhaps enough to say 
that the earlier part of the subroutine is a 'Make Table' query (itself 
drawing from a recalculated table) and the latter part is an 'Append' 
query.