subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a subform with in it, they are linked by SSN and LastName.
when i create a new record in the main form i want the subform record to be
created automatically with out having to type anyting in it. even if it is
only hte SSN and LastName that is filled out. how do i do this?

thank you,
 
The big question is: Why would you want to have a child record that
redundantly duplicates the information from the parent. There is no need to
have children unless they contain information that isn't stored elsewhere.
Parent-Child relationships exist to elimanate redundancy.
 
I have the child form because it has other fields in it. all of the fields
have default values set. if the user doesnt need to change these values,
they end up just closing the form, but then the child form does not actually
enter teh record into the table it is mapped to. how do i set it so that
when the last name is typed into the parent form, the child form is "saved"
so to speak?

thank you,
russ
 
The last name field on the parent form has an event AfterUpdate method. In
this method you should write code to open a recordset on the sub-table and
use the .addnew function to create a new record in this table then write the
name and ssn (if already entered) from the form to the recordset. see
example below (using ADO recordsets):

Private Sub Name_AfterUpdate()
Dim rst As ADODB.Recordset

On Error GoTo HandleErr
Set rst = New ADODB.Recordset
rst.Open "tblMySubTableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
With rst
.AddNew
!SubName = Me.txtName
!SubSSN = Me.txtSSN
.Update
End With
rst.Close

ExitHere:
Set rst = Nothing
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Form_MyFormName.Name_AfterUpdate" 'ErrorHandler:
End Select
End Sub

just subtitute your sub-table name, sub-table field names and parent form
field names. The .update statement will write a record into your sub-table
with the Name and SSN primary keys from your parent form, with any default
values you have identified for the sub-table. Be sure to close the recordset
and use the set rst = nothing at the end of your routine to clear the memory
space used, whether or not you use the error-handling statements or not.
I have the child form because it has other fields in it. all of the fields
have default values set. if the user doesnt need to change these values,
they end up just closing the form, but then the child form does not actually

enter teh record into the table it is mapped to. how do i set it so that
when the last name is typed into the parent form, the child form is "saved"

so to speak?

thank you,
russ
 
I am getting the following error in visual basics:
User defined type not defined.

it is highliting the line:
rst As ADODB.Recordset

could you help me fix this problem. thank you
russ
 
Russ, your application version within access needs to be configured to use
ADO (the alternative is to use DAO) recordsets. I almost exclusively use
ADO over DAO since Access 2000 introduced it ( I assumed you are using
Access 2000 or greater). To configure your code for using ADO, you need to
select "References" from the "Tools" drop-down menu where you have the
source code module open and active. Within the list of References, you need
to find and select "Microsoft ActiveX Data Objects x.xx Library", where
x.xx is a version number such as 2.1. The importance of the version number
is dependent on the oldest version of Access installed on your users' PC's
(i.e., you don't want to select a higher version if one of your user's
configuration might not support it).

I will include the DAO equivalent code below, however you need to ensure a
reference for DAO has been selected (i.e., "Microsoft DAO 3.51 Object
Library"):


Private Sub Name_AfterUpdate()
Dim rst As DAO.Recordset

On Error GoTo HandleErr
set rst = currentDB.OpenRecordset("tblMySubTableName", dbOpenDynaset)
With rst
.AddNew
!SubName = Me.txtName
!SubSSN = Me.txtSSN
.Update
End With
rst.Close

ExitHere:
Set rst = Nothing
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Form_MyFormName.Name_AfterUpdate" 'ErrorHandler:
End Select
End Sub

I am getting the following error in visual basics:
User defined type not defined.

it is highliting the line:
rst As ADODB.Recordset

could you help me fix this problem. thank you
russ

Ron Kunce said:
The last name field on the parent form has an event AfterUpdate method. In
this method you should write code to open a recordset on the sub-table and
use the .addnew function to create a new record in this table then write the
name and ssn (if already entered) from the form to the recordset. see
example below (using ADO recordsets):

Private Sub Name_AfterUpdate()
Dim rst As ADODB.Recordset

On Error GoTo HandleErr
Set rst = New ADODB.Recordset
rst.Open "tblMySubTableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
With rst
.AddNew
!SubName = Me.txtName
!SubSSN = Me.txtSSN
.Update
End With
rst.Close

ExitHere:
Set rst = Nothing
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Form_MyFormName.Name_AfterUpdate" 'ErrorHandler:
End Select
End Sub

just subtitute your sub-table name, sub-table field names and parent form
field names. The .update statement will write a record into your sub-table
with the Name and SSN primary keys from your parent form, with any default
values you have identified for the sub-table. Be sure to close the recordset
and use the set rst = nothing at the end of your routine to clear the memory
space used, whether or not you use the error-handling statements or not.

I have the child form because it has other fields in it. all of the fields
Click to expand...
have default values set. if the user doesnt need to change these values,
Click to expand...
 
Back
Top