Problem Creating New Record in Sub Form

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I need to create a new record in a sub form when I enter details in a field
in the main form.

The sub form is in Datasheet view and is linked by two fields. Details are
as follows.

The main Record is in a form called High_Level_Project and I am using 2
fields "Key" & "txtProject_Title" to link to the Subform "Key2 &
"strProject_Title".

The table names are "tblHigh_Level_Project" (Main Form) &
"tblProject_Grades_Required" (Sub From)

Now I know I need to create a new record in the Table
"tblProject_Grades_Required" but I can't figure out how to do this.

I need this to be trigered when the operator tabs out of a field in the Main
Form called "strProject_title"

I understand that this should be quite simple but I can't find the way to do
it.

Thanks

Les
 
Les said:
I need to create a new record in a sub form when I enter details in a field
in the main form.

The sub form is in Datasheet view and is linked by two fields. Details are
as follows.

The main Record is in a form called High_Level_Project and I am using 2
fields "Key" & "txtProject_Title" to link to the Subform "Key2 &
"strProject_Title".

The table names are "tblHigh_Level_Project" (Main Form) &
"tblProject_Grades_Required" (Sub From)

Now I know I need to create a new record in the Table
"tblProject_Grades_Required" but I can't figure out how to do this.

I need this to be trigered when the operator tabs out of a field in the Main
Form called "strProject_title"
Click to expand...

Les,

first, please let me make a few comments.

I would assume, you want to create a new record in the subform only
when you also create a new record in the main form, and not everytime
the project title or some other data is modified, say, by correcting a
typo in an existing record. Not to speak of tabbing out of the title
field simply by tabbing through all controls.

This would mean, you would need to create the new subform record in
the AfterInsert event procedure of the main form.

You mention two key fields linking the subform records to the main
form record. Is 'Key' by any chance an AutoNumber or some other unique
key? If so, you don't need the project title as a second key. On the
contrary, storing a relatively long and free text in two places and
using it for record identification is asking for big trouble - just
think on all those typos.

This said, here is some code to insert a new record:

Private Form_AfterInsert()

Dim strSSQL As String

'Put together the INSERT statement
'I assume, Key and Key2 are numeric.
'If they are strings, they have to be enclosed in quotes.
strSQL = "INSERT INTO tblProject_Grades_Required (Key2)" _
& VALUES (" & Me!Key & ")"

'This is for testing
MsgBox strSQL

'Run the statement
CurrentDB.Execute strSQL, dbFailOnError

'Update the subform so it displays the new record
Me![NameOfSubformControlOnMainForm].Form.Requery

End Sub

Post back for further help.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Thanks Emilia

I had to adjust it a little but it works fine.

For some reason I could not use the option .Form.Requery but it works with
just .Requery

I found your other comments useful also.

Les

Emilia Maxim said:
Les said:
I need to create a new record in a sub form when I enter details in a field
in the main form.

The sub form is in Datasheet view and is linked by two fields. Details are
as follows.

The main Record is in a form called High_Level_Project and I am using 2
fields "Key" & "txtProject_Title" to link to the Subform "Key2 &
"strProject_Title".

The table names are "tblHigh_Level_Project" (Main Form) &
"tblProject_Grades_Required" (Sub From)

Now I know I need to create a new record in the Table
"tblProject_Grades_Required" but I can't figure out how to do this.

I need this to be trigered when the operator tabs out of a field in the Main
Form called "strProject_title"
Click to expand...

Les,

first, please let me make a few comments.

I would assume, you want to create a new record in the subform only
when you also create a new record in the main form, and not everytime
the project title or some other data is modified, say, by correcting a
typo in an existing record. Not to speak of tabbing out of the title
field simply by tabbing through all controls.

This would mean, you would need to create the new subform record in
the AfterInsert event procedure of the main form.

You mention two key fields linking the subform records to the main
form record. Is 'Key' by any chance an AutoNumber or some other unique
key? If so, you don't need the project title as a second key. On the
contrary, storing a relatively long and free text in two places and
using it for record identification is asking for big trouble - just
think on all those typos.

This said, here is some code to insert a new record:

Private Form_AfterInsert()

Dim strSSQL As String

'Put together the INSERT statement
'I assume, Key and Key2 are numeric.
'If they are strings, they have to be enclosed in quotes.
strSQL = "INSERT INTO tblProject_Grades_Required (Key2)" _
& VALUES (" & Me!Key & ")"

'This is for testing
MsgBox strSQL

'Run the statement
CurrentDB.Execute strSQL, dbFailOnError

'Update the subform so it displays the new record
Me![NameOfSubformControlOnMainForm].Form.Requery

End Sub

Post back for further help.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Click to expand...
 
Back
Top