One Form and 2 tables?? (1 Viewer)

[txsondra]

I inherited a membership database. When a new member is added by using a checkbox (titled active) on the form, I need it to show the checkmark in 2 tables. I have a MasterListTbl that doesn't have names or addresses and I have a MembersTbl that shows the names and addresses. When I add a new member, in the subform member table, I check the active column. It only updates the MembersTbl . When I go to the MasterListTbl the active column does not have a check in the box for the new member.

When I added the active column in the MembrsTbl, it auto populated the active column in the subform.

 

[pbaldy]

It is typically a mistake to store the same piece of data in two places (search on normalization). Presuming the two tables are related by a member ID or something, you can always get the currently populated field in a query that joins the 2 tables. It's not hard code, but I'd suggest you rethink the need to do it.

 

[The_Doc_Man]

First, welcome to the forum. That appeared to have been your first post.

Second, Paul (pbaldy) is correct. Never store something twice unless you really have two of them. In Access-think, the idea is to store something in one place where it is uniquely applicable and then look it up any other place that needs it. Paul's suggestion to look into "Normalization" is a good one. I will add this advice. In this database-oriented forum, you can look up "Normalization" and get some information. If you do a general web search, you have to look up "Database Normalization" because "Normalization" applies to chemistry, politics, math, and a few other topics.

IF you do a web search for normalization articles, start with write-ups from .EDU sites before looking at .COM sites. That is only because the .COM sites usually have something to sell you AND/OR they are pushing a proprietary something-or-another that might be of no interest at this time. That is not to say the .COM sites are no good. They just aren't where you should start.

 

[arnelgp]

It only updates the MembersTbl .

if you want to keep your current setup (both tables having Active column), you add Code
to the Subform's AfterUpdate Event:

Private Sub Form_AfterUpdate()
    With Me.Parent.Form.RecordsetClone
        .FindFirst "[Membership #] = " & Me.Parent![Membership #]
        If Not .NoMatch Then
            .Edit
            !Active = (DCount("1", "MembersTbl", "[Membership #] = " & Me.Parent![Membership #] & " And [Active]=-1")<>0)
           .Update
        End If
     End With
End Sub

 

[Pat Hartman]

Three out of four experts say - change your method.