Update Two Tables from one form

[russell576]

I wrote a database for my daughter's dance school. There are two tables, one for personal details, the other for exam results. The data is separated this way because when I was given it all, I got it from two people in an Excel file.

When I programmed it, I had the Primary Key in the Master table auto-generated (an Integer) which I also used as the Primary Key in the ExamResults table. All the data manipulation is handled in code by Event Procedures.

This is the code to update the Master Table when a new pupil's details has been entered and the 'Submit' button on the form clicked:

Private Sub btnSubmit_Click()
On Error GoTo btnSubmit_Error

    Dim curDatabase As Database
    Dim rstNameAddress As Recordset
        
    If txtNameFirst = "" Then
        MsgBox "You must enter the member's first name to proceed.", _
               vbOKOnly Or vbInformation, "F L Toppers"
        txtNameFirst.SetFocus
        Exit Sub
    End If
    
    If txtNameLast = "" Then
        MsgBox "You must enter the member's last name to proceed.", _
               vbOKOnly Or vbInformation, "F L Toppers"
        txtNameFirst = ""
        txtNameFirst.SetFocus
        Exit Sub
    End If
    
    If cbxDOBDay <> "" Then
        If cbxDOBMonth = "" Then
            MsgBox "There is a problem" & vbCrLf & _
            "with the details you have" & vbCrLf & _
            "selected for the Date of Birth." & vbCrLf & vbCrLf & _
            "Please check and try again.", _
            vbOKOnly Or vbInformation, "F L Toppers"
            cbxDOBDay.Value = ""
            cbxDOBMonth.Value = ""
            txtDOBYear.Value = ""
            cbxDOBDay.SetFocus
            Exit Sub
        End If
    Else
        If cbxDOBDay = "" Then
            If cbxDOBMonth <> "" Then
                MsgBox "There is a problem" & vbCrLf & _
                "with the details you have" & vbCrLf & _
                "selected for the Date of Birth." & vbCrLf & vbCrLf & _
                "Please check and try again.", _
                vbOKOnly Or vbInformation, "F L Toppers"
                cbxDOBDay.Value = ""
                cbxDOBMonth.Value = ""
                txtDOBYear.Value = ""
                cbxDOBDay.SetFocus
                Exit Sub
            End If
        End If
    End If
    
    If cbxDOBDay <> "" Then
        If txtDOBYear = "" Then
            MsgBox "There is a problem" & vbCrLf & _
            "with the details you have" & vbCrLf & _
            "selected for the Date of Birth." & vbCrLf & vbCrLf & _
            "Please check and try again.", _
            vbOKOnly Or vbInformation, "F L Toppers"
            cbxDOBDay.Value = ""
            cbxDOBMonth.Value = ""
            txtDOBYear.Value = ""
            cbxDOBDay.SetFocus
            Exit Sub
        End If
    Else
        If cbxDOBDay = "" Then
            If txtDOBYear <> "" Then
                MsgBox "There is a problem" & vbCrLf & _
                "with the details you have" & vbCrLf & _
                "selected for the Date of Birth." & vbCrLf & vbCrLf & _
                "Please check and try again.", _
                vbOKOnly Or vbInformation, "F L Toppers"
                cbxDOBDay.Value = ""
                cbxDOBMonth.Value = ""
                txtDOBYear.Value = ""
                cbxDOBDay.SetFocus
                Exit Sub
            End If
        End If
    End If
    
    Set curDatabase = CurrentDb
    Set rstNameAddress = curDatabase.OpenRecordset("Master")
    
    With rstNameAddress
        .AddNew
        .Fields("NameFirst").Value = txtNameFirst
        .Fields("NameLast").Value = txtNameLast
        .Fields("NameFull").Value = .Fields("NameLast").Value & ", " & .Fields("NameFirst").Value
        .Fields("ClassName1").Value = cbxClass1
        .Fields("ClassName2").Value = cbxClass2
        .Fields("Address1").Value = txtAddress1
        .Fields("Address2").Value = txtAddress2
        .Fields("Address3").Value = txtAddress3
        .Fields("TownCity").Value = txtTownCity
        .Fields("Postcode").Value = txtPostcode
        .Fields("PhoneHome").Value = txtPhoneHome
        .Fields("MobileHome").Value = txtPhoneMobile
        .Fields("EmailHome").Value = txtEmail
        .Fields("ParentDetails").Value = tglParent
        .Fields("ParentTitle").Value = txtParentTitle
        .Fields("ParentNameFirst").Value = txtParentNameFirst
        .Fields("ParentNameLast").Value = txtParentNameLast
        .Fields("EmailParent").Value = txtParentEmail
        .Fields("EmergencyContactName").Value = txtEmergencyContactName
        .Fields("EmergencyRelationship").Value = txtEmergencyRelationship
        .Fields("EmergencyContactNumber").Value = txtEmergencyContactNumber
        .Fields("DOBDay").Value = cbxDOBDay
        .Fields("DOBMonth").Value = cbxDOBMonth
        .Fields("DOBYear").Value = txtDOBYear
        If txtDOBYear <> "" Or Null Then
            .Fields("DOB").Value = DateOfBirth(.Fields("DOBDay").Value, _
                               .Fields("DOBMonth").Value, .Fields("DOBYear").Value)
            .Fields("DayDOB").Value = CInt(Day(rstNameAddress("DOB").Value))
            .Fields("MonthDOB").Value = CInt(Month(rstNameAddress("DOB").Value))
            If Not IsDate(txtDOB) Then
                MsgBox "There is a problem" & vbCrLf & _
                    "with the details you have" & vbCrLf & _
                    "selected for the Date of Birth." & vbCrLf & vbCrLf & _
                    "Please check and try again.", _
                vbOKOnly Or vbInformation, "F L Toppers"
                cbxDOBDay = ""
                cbxDOBMonth = ""
                txtDOBYear = ""
                cbxDOBDay.SetFocus
                txtDOB.Visible = False
                txtAge = ""
                DoCmd.CancelEvent
            Else
                txtAge.Visible = True
                .Fields("Age").Value = Agenow(.Fields("DOB").Value)
            End If
        End If
        
        If cbxClass1 = "Chaps" Then
            .Fields("ClassName1").Value = cbxClass1
            .Fields("ClassName2").Value = cbxClass2
        Else
            .Fields("ClassName2").Value = cbxClass1
            .Fields("ClassName1").Value = ""
        End If
        .Update
    End With
    
    rstNameAddress.Close
    curDatabase.Close
    Set rstNameAddress = Nothing
    Set curDatabase = Nothing
    
    
    MsgBox "The details for this member have been added.", _
           vbOKOnly Or vbInformation, "F L Toppers"
           
    btnReset_Click

btnSubmit_Exit:
    Exit Sub
    
btnSubmit_Error:
    MsgBox "There was a problem when submitting this form.", _
           vbOKOnly Or vbInformation, "F L Toppers"
    Resume btnSubmit_Exit
End Sub

The woman who uses it told me the other day that she can't update the exam results for any pupil she has put into the database since I gave it her - records of pupils entered by me are OK.

When I checked the code I realised that when a new record is created, there isn't a corresponding one in ExamResults.

So my question is, when is the Primary Key first generated and how do I program the form to update the new pupil's details in the ExamResults form when the Master table is first written to?

 

[Ranman256]

This is not how you update tables. you dont assign values in VB, you must use queries.
Use queries to update. To delete, append , etc.
Queries.

 

[vbaInet]

russell576 please use Code tags when posting code here. It makes it legible and easy to follow. Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=200247

Set rstNameAddress = curDatabase.OpenRecordset("Master")

When I checked the code I realised that when a new record is created, there isn't a corresponding one in ExamResults.

I've had a quick glance at your code and I see no place where you're adding to the ExamResults table.

If you want a corresponding ID value in the ExamResults table then you need to have code to do just that.

In a similar vein to Ranman's comments, you mustn't use action queries but you would have been better off firing off queries or sql statements to save your data or using a bound form, but I guess you have your reasons for using a recordset.

 

[russell576]

In response to the first couple of negative responses, I used the format for updating the records I learned from this website:
http://www.functionx.com/vbaccess/

I had hoped to get some positive feedback - it isn't helpful being told how not to do something, I thought this was a place to come for help!!

No, I do not update the ExamResults table- my question was when does the Primary Key (auto generated integer) get generated? When I know when, I would be in a position to add code to update the other table. At the moment, I still get error messages if I try to do it from this procedure.

Meanwhile, I have tagged the code:

Private Sub btnSubmit_Click()
On Error GoTo btnSubmit_Error

'Declare the database and table as a Recordset
Dim curDatabase As Database
Dim rstNameAddress As Recordset

'If one of the fields is empty, warn to user then proceed
If txtNameFirst = "" Then
MsgBox "You must enter the member's first name to proceed.", _
vbOKOnly Or vbInformation, "F L Toppers"
txtNameFirst.SetFocus
Exit Sub
End If

'If one of the fields is empty, warn the user then proceed
If txtNameLast = "" Then
MsgBox "You must enter the member's last name to proceed.", _
vbOKOnly Or vbInformation, "F L Toppers"
txtNameFirst = ""
txtNameFirst.SetFocus
Exit Sub
End If

'Error checking for Date of Birth - makes sure there is an entry in all fields
If cbxDOBDay <> "" Then
If cbxDOBMonth = "" Then
MsgBox "There is a problem" & vbCrLf & _
"with the details you have" & vbCrLf & _
"selected for the Date of Birth." & vbCrLf & vbCrLf & _
"Please check and try again.", _
vbOKOnly Or vbInformation, "F L Toppers"
cbxDOBDay.Value = ""
cbxDOBMonth.Value = ""
txtDOBYear.Value = ""
cbxDOBDay.SetFocus
Exit Sub
End If
Else
If cbxDOBDay = "" Then
If cbxDOBMonth <> "" Then
MsgBox "There is a problem" & vbCrLf & _
"with the details you have" & vbCrLf & _
"selected for the Date of Birth." & vbCrLf & vbCrLf & _
"Please check and try again.", _
vbOKOnly Or vbInformation, "F L Toppers"
cbxDOBDay.Value = ""
cbxDOBMonth.Value = ""
txtDOBYear.Value = ""
cbxDOBDay.SetFocus
Exit Sub
End If
End If
End If

If cbxDOBDay <> "" Then
If txtDOBYear = "" Then
MsgBox "There is a problem" & vbCrLf & _
"with the details you have" & vbCrLf & _
"selected for the Date of Birth." & vbCrLf & vbCrLf & _
"Please check and try again.", _
vbOKOnly Or vbInformation, "F L Toppers"
cbxDOBDay.Value = ""
cbxDOBMonth.Value = ""
txtDOBYear.Value = ""
cbxDOBDay.SetFocus
Exit Sub
End If
Else
If cbxDOBDay = "" Then
If txtDOBYear <> "" Then
MsgBox "There is a problem" & vbCrLf & _
"with the details you have" & vbCrLf & _
"selected for the Date of Birth." & vbCrLf & vbCrLf & _
"Please check and try again.", _
vbOKOnly Or vbInformation, "F L Toppers"
cbxDOBDay.Value = ""
cbxDOBMonth.Value = ""
txtDOBYear.Value = ""
cbxDOBDay.SetFocus
Exit Sub
End If
End If
End If

'Opens the Master table as a Recordset in the current database
Set curDatabase = CurrentDb
Set rstNameAddress = curDatabase.OpenRecordset("Master")

'Add a new record using the data entered in each of the fields on the form
With rstNameAddress
.AddNew
.Fields("NameFirst").Value = txtNameFirst
.Fields("NameLast").Value = txtNameLast
.Fields("NameFull").Value = .Fields("NameLast").Value & ", " & .Fields("NameFirst").Value
.Fields("ClassName1").Value = cbxClass1
.Fields("ClassName2").Value = cbxClass2
.Fields("Address1").Value = txtAddress1
.Fields("Address2").Value = txtAddress2
.Fields("Address3").Value = txtAddress3
.Fields("TownCity").Value = txtTownCity
.Fields("Postcode").Value = txtPostcode
.Fields("PhoneHome").Value = txtPhoneHome
.Fields("MobileHome").Value = txtPhoneMobile
.Fields("EmailHome").Value = txtEmail
.Fields("ParentDetails").Value = tglParent
.Fields("ParentTitle").Value = txtParentTitle
.Fields("ParentNameFirst").Value = txtParentNameFirst
.Fields("ParentNameLast").Value = txtParentNameLast
.Fields("EmailParent").Value = txtParentEmail
.Fields("EmergencyContactName").Value = txtEmergencyContactName
.Fields("EmergencyRelationship").Value = txtEmergencyRelationship
.Fields("EmergencyContactNumber").Value = txtEmergencyContactNumber
.Fields("DOBDay").Value = cbxDOBDay
.Fields("DOBMonth").Value = cbxDOBMonth
.Fields("DOBYear").Value = txtDOBYear

'Checks if valid date entered
If txtDOBYear <> "" Or Null Then
.Fields("DOB").Value = DateOfBirth(.Fields("DOBDay").Value, _
.Fields("DOBMonth").Value, .Fields("DOBYear").Value)
.Fields("DayDOB").Value = CInt(Day(rstNameAddress("DOB").Value))
.Fields("MonthDOB").Value = CInt(Month(rstNameAddress("DOB").Value))

'If there is an error with the date, cancel the event procedure
If Not IsDate(txtDOB) Then
MsgBox "There is a problem" & vbCrLf & _
"with the details you have" & vbCrLf & _
"selected for the Date of Birth." & vbCrLf & vbCrLf & _
"Please check and try again.", _
vbOKOnly Or vbInformation, "F L Toppers"
cbxDOBDay = ""
cbxDOBMonth = ""
txtDOBYear = ""
cbxDOBDay.SetFocus
txtDOB.Visible = False
txtAge = ""
DoCmd.CancelEvent

'Otherwise display the age today
Else
txtAge.Visible = True
.Fields("Age").Value = Agenow(.Fields("DOB").Value)
End If
End If

'Display another field for data entry if member in class 'Chaps'
If cbxClass1 = "Chaps" Then
.Fields("ClassName1").Value = cbxClass1
.Fields("ClassName2").Value = cbxClass2
Else
.Fields("ClassName2").Value = cbxClass1
.Fields("ClassName1").Value = ""
End If

'Update the record
.Update
End With

'Close the recordset and database
rstNameAddress.Close
curDatabase.Close
Set rstNameAddress = Nothing
Set curDatabase = Nothing


MsgBox "The details for this member have been added.", _
vbOKOnly Or vbInformation, "F L Toppers"

'Runs the Click event procedure to reset the form (clears all the fields)
btnReset_Click

btnSubmit_Exit:
Exit Sub

'Displays the error message when something goes wrong
btnSubmit_Error:
MsgBox "There was a problem when submitting this form.", _
vbOKOnly Or vbInformation, "F L Toppers"
Resume btnSubmit_Exit
End Sub

Until the record is written to the table, there is no ID (Primary Key) which is what I need for the other table (ExamResults)

I am open to suggestions as to how best write to the tables, and if that involves a query, then so be it. But I would appreciate details on how to do it. I already know that this way doesn't work so don't really need to be told.

 

[russell576]

Uncle Gizmo

Thank you for your help.

You are right, I had established a one to one relationship between the Master table and the ExamResults.

The reason why they are separate is because I was given the data separately. I had already created the Master table by the time I was given the spreadsheet with the exam results. The person who looked after the exam is in her 70s and not very computer literate. She had one table with students names down the left and the columns headed by the name of the exam e.g. Ballet grades 1-6, Tap grades 1-7.

As each person progressed through the school, their record was updated.

When the user starts the runtime version of the file, they get an opening menu where they can add a new member, change the details of a member, re-instate someone who has left, record the exam results and current exam class, choose various print options and close.

I programmed the database this way as it followed the examples given on an Access teaching website as reported earlier. I used queries, created on the fly, to produce the reports for printing.

What I'm looking for, I suppose, is some code that updates the ExamResults table with the ID number (Primary key) when a new member is added.

 

[vbaInet]

I programmed the database this way as it followed the examples given on an Access teaching website as reported earlier. I used queries, created on the fly, to produce the reports for printing.

When I (or at least most of us) see a problem that involves a significant amount of code, I would assume that the poster has a well grounded knowledge of Access and VBA for Access and has a reason for using code in an attempt to solve the problem. If you had indicated your level of knowledge of Access, that you got the code from elsewhere and that was the only way you knew how to tackle the problem then my suggestions would have been slightly different. It was because of this reason I mentioned that you would have been better off using a query to accomplish your task rather than using a recordset in code; obviously without realising that this was the only way you knew how.

So how it works when you're learning Access is that you first learn how to work with tables, queries, forms, reports, macros and finally code but it looks like you were misguided by the tutorial and jumped straight into code. The website FunctionX is for the more advanced aspects of Access, i.e. VBA (or code).
What one would normally do in a more simple way is bind a table or query to a form and Access will automatically handle the saving, updating and inserting of data. A form can be bound using its Record Source property but if had built the form using the Form Wizard it would automatically do the binding bit for you. So I would advise that you look into using the Form Wizard to build your forms and let Access do the rest.

To answer your question about when an auto number is generated, it is generated as soon as you type the first character into any control, but this requires the form to be bound to the table or query.

I hope this all makes sense! Perhaps Uncle Gizmo could help with links to some good Access Forms tutorials that explains how bound forms work.

 

[russell576]

vbaInet

Thanks for your reply - I do know how to use forms bound to tables but I wanted greater control, for example updating the 'Age' value when the database is first opened. Having said that, I hadn't taken into account making changes later should the need arise.

Anyway, I am where I am so I was hoping for an easy fix without having to rethink the whole project.

 

[russell576]

Let's go down the rabbit hole. I'm always up for a challenge

 

[vbaInet]

I wouldn't want to go down the rabbit hole, I prefer to stay safe on solid grounds

You are right, I had established a one to one relationship between the Master table and the ExamResults.

The reason why they are separate is because I was given the data separately. I had already created the Master table by the time I was given the spreadsheet with the exam results.

Developing an Access database follows certain rules and different stages of thought process. First of all, and most importantly, the tables and structure. From your comments I can gather that you've created the two tables using the same structure as you were given, but that's unfortunately not how it works in Access or any enterprise database such as SQL Server or Oracle. There's a process of normalisation which must be followed. What you currently have is much like an Excel spreadsheet like Uncle Gizmo has highlighted, and in terms of data structure, Access and Excel are totally different.

Now, I would imagine that there are different exam periods and if this is the case then 2 tables won't suffice. You would need more tables to indicate the exam periods and perhaps a junction table for recording the results against the student and exam period.

I would advise that you re-think your table structure and we could help go through it with you.

Thanks for your reply - I do know how to use forms bound to tables but I wanted greater control,...

You can have the same amount of control, much less code and less fuss using bound forms. Developers who use unbound forms do so in much bigger enterprise applications. When I use unbound forms I would mostly likely have Classes (another advanced topic altogether) to bind certain parts to the form.

Going back to your code, the autonumber is generated right after the Update line:

    With rstNameAddress
        .AddNew
        ...
[B][COLOR="Blue"]        .Update[/COLOR][/B]
    End With

Following this, if you want to know what the number generated is, then you can use the method described here:
http://stackoverflow.com/questions/8839377/access-get-newly-created-auto-number-in-dao