Data Entry Issue...

[SherryLynn]

I run a database that tracks families and their children along with other data. I have a main form which records the family's last name, file number, etc. There is a sub form on the main form which records the children's first names and dates of birth. There are two tables that this information is stored in - Family table (records last name, file number, etc.) and a Child table which records the children's first names, last names and dates of birth. The problem I am having is that the last name of the child is not being recorded in the Child table. I want to have the last name automatically be recorded in the Child table without having to re-type this information on the subform of the report. How can I go about this? I don't have much experience with Visual Basic. Any help is much appreciated.

Thanks, Sherry

 

[FancyPrairie]

How do you relate the child table to the family table? If you have a way to link the 2 tables, then, by default, you know what the child's last name is. However, there are cases where the child's last name is not the same as the parent. So, if you want to assume that the child's last name is the same as the parent's, then in the OnCurrent event of the subform, check to see if the child's last name is Null. If it is null then let the child's last name equal the parent's last name. Something like this.

Private Sub Form_Current()

If (IsNull(strChildLastName)) Then strChildLastName = Forms!frmFamilyForm!strFamilyLastName

End Sub

 

[SherryLynn]

Ok, I'm going to try that out. The second part of my problem now is how do I get the last names of the existing records to show the correct last name instead of a blank column? Any ideas? Do I have to somehow do a query?

Sherry

 

[FancyPrairie]

The OnCurrent event will do that for your. However, the records will only be changed if you goto that record. To do it programmatically, create a new module and enter something like the following. Note that I'm using a workspace so if there are any errors, all changes all rolled back. If everything is ok, then the transaction is committed aand all changes are kept. To run this module, in Debug's Immediate window, simply type ChangeLastName. The function will then execute and tell you when it is done.

NOTE that you must set a Reference to Microsoft DAO 3.6 Object Library. To set a Reference, goto TOOLS|REFERENCES...

Function ChangeLastName()

    Dim dbs As DAO.Database
    Dim rstF As DAO.Recordset   'Family
    Dim rstC As DAO.Recordset   'Child
    Dim wsp As DAO.Workspace
    
    Dim bolBeginTrans As Boolean
    
    bolBeginTrans = False
    
    Set dbs = CurrentDb
    Set rstF = dbs.OpenRecordset("Select strLastName from tblFamily;")
    Set rstC = dbs.OpenRecordset("Select strLastName from tblChild Where ((strLastName) Is Null);")
    Set wsp = DBEngine.Workspaces(0)
    
    wsp.BeginTrans
    bolBeginTrans = True
    
    While Not rstF.EOF
    
        rstC.Edit
        rstC!strLastName = rstF!strLastName
        rstC.Update
        
        rstF.MoveNext
        
    Wend
    
    wsp.CommitTrans
    bolBeginTrans = False
    
    rstF.Close
    rstC.Close
    
    Msgbox "done"

ExitProcedure:

    Exit Function
    
ErrHandler:

    MsgBox Err.Number & vbCrLf & Err.Description
    
    If (bolBeginTrans) Then
        wsp.Rollback
        bolBeginTrans = False
        rstF.Close
        rstC.Close
    End If
    
    Resume ExitProcedure
End Function