Check to see if a field on one table is on a second table

[Micron]

When you post something like "yeah, this exactly" - quote the post or refer to the responder. This forum does not alert a responder when he/she is composing a post and the thread has been updated. I left because I thought my interpretation was wrong and now I see I was not. Now you have two suggestions on how to handle this; 1 in post 6 and post 12.

[madpiet]

I have two tables. (Table A and Table B).

I want to check if each of the fields on A are on B.

my desired result is a message : -NameField, PhNum is on A but not B.
- Address, CarMake is on both A and B

I'm pretty sure that if you look up Error 3265 (field not found), you'll find an example of this...

you'd loop through the fields in table A

Public Sub ShowMissingFields(byval strTableA as string, strTableB as string)
On Error Goto ErrHandler

dim tdfA as tabledef
dim fldA as field
dim fldB as field

set tdfA = CurrentDb.tabledefs(strTableA)
for each fldA in tdfA.Fields
set fldB = tdfB.Fields(fldA.Name)
next fldA
Exit Sub

ErrHandler:
If Err.Number = 3265 Then
Msgbox "Field [" & fldA.Name & "] does not exist in " & tdfB.Name, vbokonly + vbinformation
Resume Next '-- exit the handler and return to the next line in the code
End If

End Sub

if the set statement fails, it means the field doesn't exist in table B, so you do something (like write the value to somewhere). Oh, and you need error handling on this, otherwise the error (3265) will cause your code to basically crash because of an unhandled exception. (this is aircode still... haven't tested it because I don't even know if that's the problem...)

[Micron]

Looks viable to me. Now there are 3 proposed solutions. I'm usually not a fan of using *raised errors to figure something out* but in this case it seems simpler.
* - an exception would be in dealing with commandbars or db properties such as bypass key.

[June7]

bastnpak, I tested your DCount code and it works for me.

[bastnpak]

I found my answer to the Dcount issue.
1. Needed the [ ] around the table and field names
2.Tried to use the equation to combine pieces of variable name
-Instead predefined the variables and then just used the variables in the equation
3.Realized did not need to put " " around variable name

'Before  - dcount field and table names used a more complex name set (had  " " around variable name')
         - missing [ ] around field and table name/variable


    TB_Cn = TB_C.Name             'table name ex: dc1_current
    TBF_B = TB_B.Fields(l).Name   'Field name ex: Component
    
    dc = DCount(" & TBF_B & ", " & TB_Cn & ")  ' attempt to use variables above in equation and add " manually
                


'After   - I predefined the dcount variables for field and table and added the [] in the predefined variables
'        - Realized I did not need to add the  " " around variable name
    
    TB_Cn = "[" & TB_C.Name & "]"             'table name ex: [dc1_current]
    TBF_B = "[" & TB_B.Fields(l).Name & "]"   'Field name ex: [Component]


    dc = DCount(TBF_B, TB_Cn)

then used the result of empty or a number to either add to error message or just go to next field

        If IsEmpty(dc) = True Then            
               Bf_message = Bf_message & vbCrLf & TBF_B
        End If

'then moved to next field in loop and displayed bfmessage after loop finished

Thank you for all the help

[Welshgasman]

1. Only if you have spaces, which is never recommended.

[Micron]

1. Only if you have spaces, which is never recommended.

Or begin a field name with a number. Stumbled across that in somebody's posted code, not because it's something I do.

[moke123]

you could try something like this

Dim dict1 As Scripting.Dictionary

Sub checkTables()

    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim tdf2 As TableDef
    Dim fld As Field
    
    Set db = CurrentDb
    Set dict1 = New Dictionary

    Set tdf = db.TableDefs("Table1")
    Set tdf2 = db.TableDefs("Table2")

    For Each fld In tdf.Fields
        dict1.Add fld.Name, fld.Name
    Next

    For Each fld In tdf2.Fields
        
        If dict1.Exists(fld.Name) Then Debug.Print fld.Name & " is in both tables"
        
    Next

    Set dict1 = Nothing
    Set db = Nothing
    Set tdf = Nothing
    Set tdf2 = Nothing
    
End Sub

You could also run it the other way to check flds in table1 to table2