indexing fields 2

[spruceni]

The search function does not seem to be working at the moment. I would like to work my way through a record selected from a query. Taking each field at a time and comparing it with an equivalent field in another selected record from the same query.
Now I can do that by specifing the field name each time and ending up with 20 comparison statements but I am sure that there is a better way. Using a for next loop and a index to identify the field.
Just guessing but would rst!field(i).value work where i is the index?

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[spruceni]

strFilterm = " [ContactID] LIKE '" & MasterID & "'"
Set rstm = db.OpenRecordset("SELECT * FROM qryAllClients WHERE " & strFilter)
If rstm.RecordCount = 0 Then
'Found nothing Clean up recordset
rstm.Close
Set rstm = Nothing
Else
' There is a master record step through the slave records and transfer data if necessary

For intCounter = LBound(aSearch) To UBound(aSearch)

strFilter = " [ContactID] LIKE '" & Trim(aSearch(intCounter)) & "'"

Set rsta = db.OpenRecordset("SELECT * FROM qryAllClients WHERE " & strFilter)

If rsta.RecordCount = 0 Then
'Found nothing Clean up recordset
rsta.Close
Set rsta = Nothing
Else
rsta.MoveFirst
' move back to the first record step through the records transfering to master as required
While Not rsta.EOF
rstm.Edit

xxxxxxxxxxxxxxxxxxxxxxxx
Here is the bit where I do the comparason of the two records and transfer data between records if the master field is empty.
There are 45 fields to check in the record.

If IsNothing(Nz(rstm!Title)) Then rstm!Title = rsta!Title

xxxxxxxxxxxxxxxxxxxxxxxxx



rstm.Update
rsta.MoveNext
Wend
'Clean up recordset
rsta.Close
Set rsta = Nothing
End If

Next intCounter
' Close the master record and clean up
rstm.Close
Set rstm = Nothing
End If

 

[PHV]

Replace this:
If IsNothing(Nz(rstm!Title)) Then rstm!Title = rsta!Title
with this:
For i = 0 To rsta.Fields.Count - 1
If Trim(rstm.Fields(i).Value & "") = "" Then
rstm.Fields(i).Value = rsta.Fields(i).Value
End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gammachaser]

I beleive the code you are looking for is:

For i = 0 to rstm.fields.count -1 ' fields is 0 based
    If isnull(rstm.fields(i)) then rstm.fields(i) = rsta.fields(i)
Next

Notice it is fields not field since it is a collection.

You should not need the IsNothing(NZ()) part... Isnull works just fine.

Also note that if you have an autonumber index in the first field of your table, start with fields(1) instead of 0 or you will waste one step in each cycle since fields(0) will never be null and does not need to be checked.

 

[Gammachaser]

OK, PHV... you beat me while I was typing.

 

[spruceni]

Thanks guys for this information I will try it out. It should save a lot of hand codeing

 

[spruceni]

Hi Just a thought some of these fields are flags (logical) an example deceased. will the detection of empty ( false ) still work or will I have to deal with these fields separately?

 

[Gammachaser]

The boolean fields will never be considered Null, so each 'Master' record will be set to match the 'Slave.'

The same holds true for 'IsEmpty' and for the method described by PHV.