New Record On Click

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

Guest

I have a main form with yes/no controls. I'd like Access to create a new
record in the subform of another form (in the background) when the user
clicks on one of the yes/no controls. Here's an if statement that I've tried
out thus far:
ConceptLayoutsLA = Name of yes/no control
Plan Status = Name of other form
Plan_Status = Control name of the subform on "Plan Status"

If ConceptLayoutsLA Then
DoCmd.OpenForm "Plan Status", acNormal, , , acFormEdit, acHidden
Forms("Plan Status").SetFocus
DoCmd.GoToRecord , Plan_Status, acNewRec
Forms("Plan Status").Plan_Status.Form.Combo126 = "Concept Layouts"
DoCmd.Close acForm, "Plan Status", acSaveYes
Else
Exit Sub
End If

This works, but on the click event you see the record for a second then it
closes. Is there a way to do this, so it happens all in the background.
Thanks in advance.

Neil Cash
 
Neil Cash said:
I have a main form with yes/no controls. I'd like Access to create a
new record in the subform of another form (in the background) when
the user clicks on one of the yes/no controls. Here's an if
statement that I've tried out thus far:
ConceptLayoutsLA = Name of yes/no control
Plan Status = Name of other form
Plan_Status = Control name of the subform on "Plan Status"

If ConceptLayoutsLA Then
DoCmd.OpenForm "Plan Status", acNormal, , , acFormEdit, acHidden
Forms("Plan Status").SetFocus
DoCmd.GoToRecord , Plan_Status, acNewRec
Forms("Plan Status").Plan_Status.Form.Combo126 = "Concept Layouts"
DoCmd.Close acForm, "Plan Status", acSaveYes
Else
Exit Sub
End If

This works, but on the click event you see the record for a second
then it closes. Is there a way to do this, so it happens all in the
background. Thanks in advance.

Neil Cash
Click to expand...

I think maybe you're losing sight of the fact that forms are only
windows on the data. The data is actually stored in a table, not a
form. If you can just add the record you want to the table that is
serving as the recordsource for the subform, with the appropriate values
in the various fields, then that record will appear on the subform the
next time the form is opened. You don't actually have to open the form
now to add the record.

When I want to add a record to a table, I usually just execute an append
query in code, using DAO; e.g.,

CurrentDb.Execute _
"INSERT INTO SomeTable (ThisField, ThatField) " & _
"VALUES(" & OneValue & ", '" & AnotherValue & "')", _
dbFailOnError

It's also possible to do this using the DoCmd.RunSQL method.

I can't tell from what you've posted what would be an appropriate append
query to execute, nor where the values to insert should come from, but I
think this approach will be much simpler and more efficient than opening
a form just to add a record to its subform.
 
Back
Top