Here's my situation.  I have two tables (costcenter, and validate)
I'm trying to create a recordset of the costcenter table and then validate 
the "costcenter" field and the "wbselement" field based on the data in 
the "Validate" table.  I'm doing this from a query by calling the Function 
Validate.
My problem is that my function is writing the same answer in every field 
of the query.  




Option Compare Database
Option Explicit

                                                                           
                                                                           
                                          
Public Function Validate(CostCenter As String, WBSElement As String) As 
String
   ' Declare DAO object variables.
   Dim rstCostCenter As Recordset
   Dim rstValidate As Recordset
   ' Set object variables to recordsets.
   Set rstCostCenter = CurrentDb. _
   OpenRecordset("ProjectUpdate", dbOpenDynaset)
   Set rstValidate = CurrentDb. _
   OpenRecordset("tblStrategicMktgWBSElements&CostCenters", dbOpenDynaset)
    rstCostCenter.MoveFirst
    
rstValidate.FindFirst "[NewCostCenter]= '" & rstCostCenter!NEWCostCenter 
& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"
        
 
             Do Until rstCostCenter.EOF
                    
                If Not rstValidate.NoMatch Then
                    rstCostCenter.MoveNext
                End If
                If rstValidate.NoMatch Then
                    Validate = "Invalid WBS Element"
                    rstCostCenter.MoveNext
                End If
                
            Loop
          
  rstValidate.FindFirst "[NewCostCenter]= '" & rstCostCenter!NEWCostCenter 
& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"
      
   
   
  
    
         

   
End Function

Create a new query and add both tables to it.  Link the tables on the
common fields and create a left join where you want all the records in
the costcenter table.  Bring the constcenter field and wbselement field
from the validate table to the QBE and in the criteria row enter Is
Null.  Also bring down the validate field from the validate table and in
the Update To row enter ""Invalid WBS Element"

This should work:

UPDATE costcenter LEFT JOIN validate ON costcenter.costcenter 
validate. Costcenter AND costcenter.wbselement = validate.wbselement 
SET validate.Validate = "Invalid WBS Element"
WHERE (((validate.costcenter) Is Null AND (validate.wbselement) Is
Null));

Before you try this, make sure you have backups of each table.

You can run the update query thru code by using the following

' Docmd.SetWarnings False	' Turn Access warnings off
Docmd.openquery ("TheUpdateQueryName")	' Run the query
' Docmd.SetWarnings True	' Turn Access warnings back on

John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity

xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 


-----Original Message-----
From: malik.al-amin@w... [mailto:malik.al-amin@w...] 
Sent: Thursday, May 23, 2002 11:23 PM
To: Access
Subject: [access] Looping through records in a recordset

Here's my situation.  I have two tables (costcenter, and validate)
I'm trying to create a recordset of the costcenter table and then
validate 
the "costcenter" field and the "wbselement" field based on the data in 
the "Validate" table.  I'm doing this from a query by calling the
Function 
Validate.
My problem is that my function is writing the same answer in every field

of the query.  




Option Compare Database
Option Explicit

 

 

                                          
Public Function Validate(CostCenter As String, WBSElement As String) As 
String
   ' Declare DAO object variables.
   Dim rstCostCenter As Recordset
   Dim rstValidate As Recordset
   ' Set object variables to recordsets.
   Set rstCostCenter = CurrentDb. _
   OpenRecordset("ProjectUpdate", dbOpenDynaset)
   Set rstValidate = CurrentDb. _
   OpenRecordset("tblStrategicMktgWBSElements&CostCenters",
dbOpenDynaset)
    rstCostCenter.MoveFirst
    
rstValidate.FindFirst "[NewCostCenter]= '" & rstCostCenter!NEWCostCenter

& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"
        
 
             Do Until rstCostCenter.EOF
                    
                If Not rstValidate.NoMatch Then
                    rstCostCenter.MoveNext
                End If
                If rstValidate.NoMatch Then
                    Validate = "Invalid WBS Element"
                    rstCostCenter.MoveNext
                End If
                
            Loop
          
  rstValidate.FindFirst "[NewCostCenter]= '" &
rstCostCenter!NEWCostCenter 
& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"
      
   
   
  
    
         

   
End Function

Mal..

Your function has a basic floor in that you are using two If statements
instead of if..else. If the first If is true, a move next is executed, but
the function has not finnished - the second if is applied to the next
record.

I rather suspect you have not found this to be the problem yet.
You function appears to be validating every record of the first table,
against a second table. As a function I find it confusing. Try breaking the
job up into smaller steps, a function that is passed one cost centre,
validates it against another table and returns a boolean result. Your
wording " writing the same answer in every field of the query." suggests you
are calling the function continually in a query. If this is true Access will
optimize it to be executed only once and then apply the result to every
record in the query. There is a msdn article about it. The way around it is
to pass a value to your function from each record in your query.

Have fun

David
-----Original Message-----
From: malik.al-amin@w... [mailto:malik.al-amin@w...]
Sent: Thursday, May 23, 2002 11:23 PM
To: Access
Subject: [access] Looping through records in a recordset


Here's my situation.  I have two tables (costcenter, and validate)
I'm trying to create a recordset of the costcenter table and then validate
the "costcenter" field and the "wbselement" field based on the data in
the "Validate" table.  I'm doing this from a query by calling the Function
Validate.
My problem is that my function is writing the same answer in every field
of the query.




Option Compare Database
Option Explicit




Public Function Validate(CostCenter As String, WBSElement As String) As
String
   ' Declare DAO object variables.
   Dim rstCostCenter As Recordset
   Dim rstValidate As Recordset
   ' Set object variables to recordsets.
   Set rstCostCenter = CurrentDb. _
   OpenRecordset("ProjectUpdate", dbOpenDynaset)
   Set rstValidate = CurrentDb. _
   OpenRecordset("tblStrategicMktgWBSElements&CostCenters", dbOpenDynaset)
    rstCostCenter.MoveFirst

rstValidate.FindFirst "[NewCostCenter]= '" & rstCostCenter!NEWCostCenter
& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"


             Do Until rstCostCenter.EOF

                If Not rstValidate.NoMatch Then
                    rstCostCenter.MoveNext
                End If
                If rstValidate.NoMatch Then
                    Validate = "Invalid WBS Element"
                    rstCostCenter.MoveNext
                End If

            Loop

  rstValidate.FindFirst "[NewCostCenter]= '" & rstCostCenter!NEWCostCenter
& "' " & "And [wbselement] = '" & rstCostCenter!WBSElement & "'"








End Function