Append queries in related tables (SOLVED)

[CarlosFGMG]

Dear mates,
I don't know where to post this feedback (Access or VBA), so may be a moderator will move it to the proper forum. I didn't find much information on Internet about my subject; or should I better say that I'm just a newbie who doesn't understand complex codes to do the same. I can copy and paste them however, but it's quite important to know what I'm typing in order to avoid malicious code. I also strive to make things as easy and intuitive as possible, for a better understanding and teaching.
So finally I want to share my solution when it comes to append records from a form onto related tables. Suppose that you have a primary table that have to feed a foreign one. Record source for combo boxes comes from primary tables and you need to insert values typed in combo boxes both to primary and foreign related fields. You can violate key rules because Access can't refresh data without the appropriate code, which was my concern. So here is my method to overcome this.

Code: Select all

Private Sub cmdAppend_Click()
    Person
    CurrentDb.Execute (" INSERT INTO Person (id, prefix_title, first_name, middle_name, last_name, suffix_title, signature, gender) " _
    & " VALUES ('" & Me.cboIdentityCard & "', '" & Me.cboPrefix & "', '" & Me.cboFirstName & "', '" & Me.cboMiddleName & "', '" & Me.cboLastName & "', '" & Me.cboSuffix & "', '" & Me.txtSignature & "', '" & Me.cboGender & "');")
End Sub

Private Function Person()
    If IsNull(DLookup("chrIdentityCard", "tblIdentityCard", "[chrIdentityCard] = '" & Me.cboIdentityCard & "'")) Then
        CurrentDb.Execute (" INSERT INTO tblIdentityCard (chrIdentityCard) VALUES ('" & Me.cboIdentityCard & "');")
    End If
    If IsNull(DLookup("chrGivenName", "tblGivenName ", "[chrGivenName] = '" & Me.cboFirstName & "'")) Then
        CurrentDb.Execute (" INSERT INTO tblGivenName (chrGivenName) VALUES ('" & Me.cboFirstName & "');")
    End If
    If IsNull(DLookup("chrSurname", "tblSurname ", "[chrSurname] = '" & Me.cboMiddleName & "'")) Then
        CurrentDb.Execute (" INSERT INTO tblSurname (chrSurname) VALUES ('" & Me.cboMiddleName & "');")
    End If
    If IsNull(DLookup("chrSurname", "tblSurname ", "[chrSurname] = '" & Me.cboLastName & "'")) Then
        CurrentDb.Execute (" INSERT INTO tblSurname (chrSurname) VALUES ('" & Me.cboLastName & "');")
    End If
End Function

Still needed simple message code to warn user when required, so please report any likely improvement or bug. Regards,
Carlos.

[HansV]

Welcome to Eileen's Lounge, and thanks for sharing.

Do you have a specific reason for storing lists of free text items such as given name and surname in tables?

[CarlosFGMG]

No, I must admit that it's a nonsense but just been an exercise for the improvement of my knowledge, and I found many cases on Internet on how to make use this of. I am pretty sure that many users could find it really helpful tailoring it to their needs. The good news is that this code works with as less code as possible. I received couple of answers with complex code which I'm not able to understand, which could lead onto unexpected or side behaviour. I think that this code is easy to assimilate.
Regards,
Carlos.

[HansV]

Yes, the code is clear and easy to understand.

[CarlosFGMG]

Let's say, e.g., that chrSurname from tblSurname is an Id for another purpose and related to a chrDescription in the same table (which is really a one-to-one relationship, all in one table). You can reach that description easily. Another example: chrSurname (appropriately named) is a combination of certain fields in its table tblSurname (appropriately named), such a serial number. Every field is meaningful by itself (4 four digits for the year, 6 digits for a serie, 3 characters for the manufacturer, 2 letters for the country, etc.), so you can understand what the whole serial number means.
And thanks for your welcome, dear Hans. I bet this is a great forum, it's nice to meet you.
Regards,
Carlos.