linking data on a form

[Guest]

I have a form that has the location name in a combo box. When the user
selects the location it is linked to all forms.

The issue is when the user adds a record, the location number, which is the
key for the location in the combo box is needed. How do I get the location
number (key) to be automatically added to the new record being added without
the user having to enter the location name or location number.

Below is how I am keeping the location name to get to the new form.


Private Sub Command86_Click()
On Error GoTo Err_Command86_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_Route"

stLinkCriteria = "[BRANCH]=" & "'" & Me![cmboLocation] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command86_Click:
Exit Sub

Err_Command86_Click:
MsgBox Err.Description
Resume Exit_Command86_Click

End Sub

Thanks
Rick

 

[Marshall Barton]

I have a form that has the location name in a combo box. When the user
selects the location it is linked to all forms.

The issue is when the user adds a record, the location number, which is the
key for the location in the combo box is needed. How do I get the location
number (key) to be automatically added to the new record being added without
the user having to enter the location name or location number.

Below is how I am keeping the location name to get to the new form.

Private Sub Command86_Click()
On Error GoTo Err_Command86_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_Route"
stLinkCriteria = "[BRANCH]=" & "'" & Me![cmboLocation] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

[]


Since the form you are opening is filtered to a single
location, You can just set the DefaultValue property of the
location text box when the form opens.

While the link criteria is available in the form's Filter
property, I think you should explictly pass the location ID
using the OpenArgs argument.

With all that in mind, the OpenForm line could be:
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
OpenArgs:= Me!cmboLocation

And the code in the f_route form's Open or Load event
procedure would be like:
Me.txtLocation.DefaultValue = Me.OpenArgs