New Record Question

[Larry Salvucci]

I have a continuous subform where I store order details. When I finish adding
a new record it shows a new blank control below it. How do I stop it from
doing this and only using the "create new record" command button that I have
on my forms header? I only want a new record to be created when I hit the
button, not after a new record is entered.

 

[Duane Hookom]

A new record does not get added until you have entered something into the
blank row at the bottom.

If you don't want to display the blank row then you may need to set the
subform to not allow additions. Then use code in the On Click event of your
command button to append a row with the appropriate foreign key value and
then requery the subform.

 

[Larry Salvucci]

That's what I meant. I don't want the blank row to appear until I click the
command button. How would I set up the code on my command button to append a
new record to my table?

 

[Duane Hookom]

The code might look like:
Dim strSQL as String
strSQL = "INSERT INTO tblChild (fieldA, fieldB, ....) Values (" & _
Me.MasterID & ",....)"
Currentdb.Execute strSQL, dbfailonerror
Me.sfrmCtrlName.Form.Requery

 

[Larry Salvucci]

I modified the code to my field names but I get an error that says "You
entered an expression that has no value".

Here is what I put in the event:

Dim strSQL As String
strSQL = "INSERT INTO tblChild (GroupsID, RecordID, Material) Values (" & _
Me.RecordID & ",)"
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery

 

[Duane Hookom]

Is your table from the subform actually named "tblChild"? You list three
fields in the "INTO" fields and only 1 in the "VALUES". Is the primary key of
your main table actually named "RecordID"?

I don't think you want to requery the current form unless the code is
running in the subform.

 

[Larry Salvucci]

I forgot to change the table name. Those are the three fields that are in my
table. Should they be in the "INTO" statement? The primary key of my main
table is called "RecordID".

 

[Duane Hookom]

You generally don't need to insert values into more than a single field since
the user will edit the added record in the subform. You might be able to get
by with
strSQL = "INSERT INTO tblChildName (ForeignKeyFieldName) Values(" &
Me.RecordID & ")"

 

[Larry Salvucci]

I was tinkering a bit with the "AllowAdditions" function and came up with an
idea. I put this code in the "On Click" event of my button:

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec

Then in the "After Update" event of my control I put this:

Me.AllowAdditions = False

This way it will toggle it off after a record is entered but the only way to
create a new record is to fire the command button code. Do you think this is
an ok way to do it?

 

[Duane Hookom]

Your solution is as good or better than mine.