comparing data in specific fields from two tables ???
Dim MyControlRs as recordset
Table1 has fields F1, F2,F3,F4
Table2 has fields F1A,F2A,F3A,F4A
You want to verify if F2 = F2a AND IF F3 = F3A
You have a table CTRLFields that has a table1.fieldX to table2.FieldX check
relation ship
1 record contains example
Field1 = Table1
Field2 = F2
Field3 = Table2
Field4 = F2A
The second record contains same thing but for fields F3, F3A
To obtain all the comparaisons that need to be done between these two tables
is a recordset based on a select statement
In Code
DIM SQL as string
SQL = "Select * from CtrlFields where Field1 = " & """" &
CtrFields("table1") & """" & " and Field3 = " & """" & CtrlFields("Table2")
& """"
To see if any records in the tables match you can simply create a query
SQL = "Select " & CtrlFierld("field1") & "." & CtrFields("Field2) & "," &
CtrlFierld("field3") & "." & CtrFields("Field4) "from " & CtrFields.Field1
& " Inner join " & CtrlFields("Field3") & " ON " & CtrlField("Field2") & "
= " & CtrField("Field4")
Use this query to open a recordset
Set MyNewRs = MyDb.openrecordset(SQL)
If not MyNewRs.BOf and Not MyNewRs.EOF then
'there are records so some match
else
'there are no records in the record set so you don't have a match
end if
Of course you can add other fields to the SQL statement to help you in
manipulating the result
Note :Code not tested by the way but should work :-)
Quote:
>I've exhausted my limited knowledge...... any help will be greatly
>appreciated....
>I need to compare some ( not all ) fields of records in two tables. I am
>trying to write some code that I can reuse for several operations of this
>kind.
>I created a table to hold the tablenames and fieldnames so I can select
them
>on the way in.
>I was trying to use a variant to pass the field names to the string
>assignment for the two tables.
>Is there a better way to do this????
>Here's what I did >>>>
>I create a table to hold the fieldnames I want to use
>tblMatch contains tlbname and fldname fields
>(fldname contains !field1,!field2, !field3)
>rstMatch contains fldnames for a particular tblname
>With rstSMatch
>.MoveFirst
>Set strFlds = !fldname
>.MoveNext
>Do
>Set strFlds = strFlds & " & " & !fldname
>.MoveNext
>Loop Until .EOF
>End With
>This produces a string "!field1 & !field2 & field3"
>I try to use this string with another recordset to create a string
>containing the contents of the three fields but this won't work.
>with rst2
>str2=strflds
