Reference a table field

[Eljefegeneo]

I have two tables, Table1 and Table2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then
And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.
What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then
But I get an error. Is there a way to reference the Table instead of the Form in both the VBA and the query?
The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).

I've attached a simple DB to show what I mean.

 

[JHB]

You can do it by using recordset and a module:

Function CheckIt(IDNo As Long)
  Dim dbs As Database, rstTable1 As Recordset, rstTable2 As Recordset
 
  Set dbs = CurrentDb
  Set rstTable1 = dbs.OpenRecordset("Select DoNotchange From Table1 WHERE DoNotchange = False AND [Table1ID]=" & IDNo)
  Set rstTable2 = dbs.OpenRecordset("Select Active From Table2 WHERE Active = True AND [Table2ID]=" & IDNo)
  CheckIt = Not rstTable2.EOF And Not rstTable1.EOF
End Function

To use the the function:

Private Sub Command24_Click()
  Dim dbs As Database, rst As Recordset
 
  If CheckIt(Me.Table1IDA) Then
    DoCmd.OpenQuery "Qry2a", acViewNormal
  End If
End Sub

 

[Eljefegeneo]

Thank you. I will try it later.

 

[boblarson]

You can do it by using recordset and a module:

Function CheckIt(IDNo As Long)
  Dim dbs As Database, rstTable1 As Recordset, rstTable2 As Recordset
 
  Set dbs = CurrentDb
  Set rstTable1 = dbs.OpenRecordset("Select DoNotchange From Table1 WHERE DoNotchange = False AND [Table1ID]=" & IDNo)
  Set rstTable2 = dbs.OpenRecordset("Select Active From Table2 WHERE Active = True AND [Table2ID]=" & IDNo)
  CheckIt = Not rstTable2.EOF And Not rstTable1.EOF
End Function

To use the the function:

Private Sub Command24_Click()
  Dim dbs As Database, rst As Recordset
 
  If CheckIt(Me.Table1IDA) Then
    DoCmd.OpenQuery "Qry2a", acViewNormal
  End If
End Sub

You can also check for record count instead

CheckIt = (rstTable2.RecordCount > 0 And rstTable1.RecordCount > 0)

If there are any records, the record count will not be 0, even if you would normally have to do a MoveLast to get the actual count, the count will still be 1 to start with and not 0. I just personally don't like using BOF's and EOF's unless absolutely necessary. But it is a personal choice so it isn't wrong and either way can be used.

 

[Eljefegeneo]

Thanks both of you. I tried it and it worked, now have to figure out how to put it in my regular DB.

 

[Galaxiom]

I have two tables, Table1 and Table2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data.

This requirement sounds like the result of a normalization error.