Greenhorn needs Help On Recordset Parameter Query 
Author Message
 Greenhorn needs Help On Recordset Parameter Query

I have a function with 2 recordsets. The second Recordset  does a
parameter query based on the results of the first recordset, then based
on the results of the parameter query a specific field in the first
recordset is updated. This cycles through about 11,000 records.

Everything is working as expected until I hit the situation where the
results of the first recordset cause the parameter query produce zero or
Null  results.

I thought I could resolve by putting in the following IF statement
below: I've highlighted the IF statement that I'm having trouble with.

    ' The parameter value from the 2nd recordset is =
    qdMyQuery.Parameters("txtProj") = txtProjNum

    Set rsMyRS1 = qdMyQuery.OpenRecordset()

    rsMyRS1.MoveFirst

        With rsMyRS2
        .Edit
        If rsMyRS1.Fields("Ty") = "DE" Then
        !Type = "Z222" ' Demand
        ElseIf rsMyRS1.Fields("Ty") = "PL" Then
        !Type = "Z333" ' Planned
        End If

        .Update
        End With

        ' Now cycle through the rest of the records.
        Do While Not rsMyRS2.EOF
        rsMyRS2.MoveNext
        txtWBS = rsMyRS2.Fields(1)
        txtProjNum = Mid(txtWBS, 3, 7)
        qdMyQuery.Parameters("txtProj") = txtProjNum

        ' Requery the Project Type for the latest record
        rsMyRS1.Requery qdMyQuery
        Debug.Print rsMyRS1.Fields("Proj")

        ' If the parameter query response does not have a match then
move to the next record
        If IsNull(rsMyRS1.Fields("Ty")) Then
        rsMyRS2.MoveNext
        End If

        With rsMyRS2
        .Edit
        If rsMyRS1.Fields("Ty") = "DE" Then
        !Type = "Z222" ' Demand
        ElseIf rsMyRS1.Fields("Ty") = "PL" Then
        !Type = "Z333" ' Planned
        End If

        .Update
        End With

        Loop

    ' Close the Recordset object.

I'd sure appreciate it if someone out there could help me out !!



Tue, 29 Oct 2002 03:00:00 GMT  
 Greenhorn needs Help On Recordset Parameter Query

From help

A Recordset or TableDef object with no records has a RecordCount
property
setting of 0.

So you could do

If recordset.recordcount=0 then
    do something
end if

--
Hope this helps

Terry Wickenden

Quote:

> I have a function with 2 recordsets. The second Recordset  does a
> parameter query based on the results of the first recordset, then
> based on the results of the parameter query a specific field in the
> first recordset is updated. This cycles through about 11,000 records.

> Everything is working as expected until I hit the situation where the
> results of the first recordset cause the parameter query produce zero
> or Null  results.

> I thought I could resolve by putting in the following IF statement
> below: I've highlighted the IF statement that I'm having trouble with.

>     ' The parameter value from the 2nd recordset is =
>     qdMyQuery.Parameters("txtProj") = txtProjNum

>     Set rsMyRS1 = qdMyQuery.OpenRecordset()

>     rsMyRS1.MoveFirst

>         With rsMyRS2
>         .Edit
>         If rsMyRS1.Fields("Ty") = "DE" Then
>         !Type = "Z222" ' Demand
>         ElseIf rsMyRS1.Fields("Ty") = "PL" Then
>         !Type = "Z333" ' Planned
>         End If

>         .Update
>         End With

>         ' Now cycle through the rest of the records.
>         Do While Not rsMyRS2.EOF
>         rsMyRS2.MoveNext
>         txtWBS = rsMyRS2.Fields(1)
>         txtProjNum = Mid(txtWBS, 3, 7)
>         qdMyQuery.Parameters("txtProj") = txtProjNum

>         ' Requery the Project Type for the latest record
>         rsMyRS1.Requery qdMyQuery
>         Debug.Print rsMyRS1.Fields("Proj")

>         ' If the parameter query response does not have a match then
> move to the next record
>         If IsNull(rsMyRS1.Fields("Ty")) Then
>         rsMyRS2.MoveNext
>         End If

>         With rsMyRS2
>         .Edit
>         If rsMyRS1.Fields("Ty") = "DE" Then
>         !Type = "Z222" ' Demand
>         ElseIf rsMyRS1.Fields("Ty") = "PL" Then
>         !Type = "Z333" ' Planned
>         End If

>         .Update
>         End With

>         Loop

>     ' Close the Recordset object.

> I'd sure appreciate it if someone out there could help me out !!



Wed, 30 Oct 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help needed: Setting parameters in parameter queries

2. Help needed: Setting parameters in parameter queries

3. HELP with Editing Recordset Fields while using a Parameter Query

4. Help! Recordsets and Parameter Queries

5. Open recordset on parameter query - who can help?

6. Need help passing query parameters in a url

7. Need help with this WHERE clause in parameter query

8. Help with SQL parameter query needed!

9. Need Help passing Parameters to SQL query in a VB dataenviroment

10. homework help needed: passing a recordset as a parameter

11. calling parameter query from a parameter query

12. Use Parameter Queries in VBA- Then open as Recordset- collect values

 

 
Powered by phpBB® Forum Software