Add a new record to related table on opening subform

[Guest]

I'm not sure how to title this, but the problem is straightforward enough - I
have a main project table (with an autonumber field as the primary key) and
several related tables that are linked together referencing that project
number. I have forms that I use to enter data in these related tables - they
had been sub-forms, but I had to break them out as they were getting too
large to be usable in a tabbed format. I've got the forms opening now using
command buttons, and they are only opening related records, as they should -
but when there are no related records (i.e. I haven't added an evaluation to
the project yet) the new record that it shows doesn't have the project number
populated. This is a major usability issue, and so I've got to get it working
fast - it's been suggested by a user that 'maybe you can have it make a new
record if there isn't one' but if there are records, we don't want blank ones
created. Any suggestions??

 

[John Vinson]

I've got the forms opening now using
command buttons, and they are only opening related records, as they should -
but when there are no related records (i.e. I haven't added an evaluation to
the project yet) the new record that it shows doesn't have the project number
populated.

You can pass the calling form's value of the Project Number in the
Form's OpenArgs property; and then use the Open event of the second
form to set the DefaultValue property of the Project Number textbox to
the passed value. This won't affect any existing data (only new
records) and should get the effect that you want.

John W. Vinson[MVP]

 

[Guest]

John,

I understand the OnOPen function, but OpenArgs (open arguments??) isn't
familiar - some help would be appreciated. Thanks!

 

[John Vinson]

I understand the OnOPen function, but OpenArgs (open arguments??) isn't
familiar - some help would be appreciated. Thanks!

It's the last optional parameter in the OpenForm method. See the help
for OpenForm (the VBA method, in the VBA help file).

Basically, you'll have a line in the calling form

DoCmd.OpenForm strFormName, _
WhereCondition:="[ProductID] = " & Me.ProductID, _
WindowMode:=acDialog, _
OpenArgs:=Me.ProductID

Then in the Open event you'll have code like

Private Sub Form_Open(Cancel as Integer)
<other open checks as needed>
If Me.OpenArgs & "" <> "" Then ' was an arg passed?
Me.ProductID.DefaultValue = "'" & Me.OpenArgs & "'"
End If
End Sub

John W. Vinson[MVP]

 

[Guest]

John,

Thanks so much - haven't had time to get back to you until today. I've
gotten the following error on the "On Click" for the first form - can you see
what's wrong?

Private Sub Command94_Click()
On Error GoTo Err_Command94_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FRMEvalSumCM"

stLinkCriteria = "[Project number]=" & Me![Project number]
DoCmd.OpenForm stDocName, , , stLinkCriteria
OpenArgs: Me.[Project number]


Thanks again!

I understand the OnOPen function, but OpenArgs (open arguments??) isn't
familiar - some help would be appreciated. Thanks!

It's the last optional parameter in the OpenForm method. See the help
for OpenForm (the VBA method, in the VBA help file).

Basically, you'll have a line in the calling form

DoCmd.OpenForm strFormName, _
WhereCondition:="[ProductID] = " & Me.ProductID, _
WindowMode:=acDialog, _
OpenArgs:=Me.ProductID

Then in the Open event you'll have code like

Private Sub Form_Open(Cancel as Integer)
<other open checks as needed>
If Me.OpenArgs & "" <> "" Then ' was an arg passed?
Me.ProductID.DefaultValue = "'" & Me.OpenArgs & "'"
End If
End Sub

John W. Vinson[MVP]

 

[John Vinson]

Thanks so much - haven't had time to get back to you until today. I've
gotten the following error on the "On Click" for the first form - can you see
what's wrong?

You didn't indicate what error you're getting, but the syntax is
incorrect: it should be

stLinkCriteria = "[Project number]=" & Me![Project number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
OpenArgs:=Me.[Project number]

or count commas (I usually use the named arguments instead) and use
put Me.[Project Number] after the correct number of commas. I'd
probably have written this as

stLinkCriteria = "[Project number]=" & Me![Project number]
DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria, _
OpenArgs=:Me.[Project number]

Note that the syntax is Argumentname; a colon; an equals sign; and the
value for that argument.

John W. Vinson[MVP]

 

[Guest]

John, you're a genius! This has solved problems on so many things I can't
even begin to tell you. Thanks again!!

Thanks so much - haven't had time to get back to you until today. I've
gotten the following error on the "On Click" for the first form - can you see
what's wrong?

You didn't indicate what error you're getting, but the syntax is
incorrect: it should be

stLinkCriteria = "[Project number]=" & Me![Project number]
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
OpenArgs:=Me.[Project number]

or count commas (I usually use the named arguments instead) and use
put Me.[Project Number] after the correct number of commas. I'd
probably have written this as

stLinkCriteria = "[Project number]=" & Me![Project number]
DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria, _
OpenArgs=:Me.[Project number]

Note that the syntax is Argumentname; a colon; an equals sign; and the
value for that argument.

John W. Vinson[MVP]