OpenForm vs. Form menu double click.

[John Keith]

Clicking an addAssignment button on a main form calls another popup modal
form via...

DoCmd.OpenForm "AddAssignment", , , "[SSN] = '" & Me.SSN & "'

and then fires off... the AddAssignment open event w/ the coded WHERE
condition:

Private Sub Form_Open(Cancel As Integer)

How can I detect with code here (in the open event) if the AddAssignment
form was opened from the form menu (I.E no code controlled Where condition
from the DoCmd.Openform?) When the form is run with out a Where or filter or
openarg... I need to cancel the open with a message (with no code... the form
opens as an empty frame).

I tried using the openarg parm of the DoCmd.OpenForm but once inside the
Open event... Me.OpenArg was null (even though viewing the value being passed
shows that it was a 9 dight ssn)

I tried using the filter parm, but me.filter in the open event was still a
null string (even though the form did display properly with the filtered
records.)

I need to detect:
1) Wide open recordset (form should not open and show a message why)
2) Records limited via the OpenForm (one of the 3 methods:
Filter/Where/OpenArg)
3) Records limited but none match.

Perhaps I need the Load event to check recordset conditions, but what is the
proper way to detect this in the Open (cancelable) event.

 

[Larry Linson]

I tried using the filter parm, but me.filter in the open
event was still a null string (even though the form did
display properly with the filtered records.)

Put your filter-checking code in the "Load" event -- works for me.

If you have the selection done in a Combo Box, with "Limit to List" property
set, you can simply bypass executing the DoCmd.OpenForm, and display an
appropriate MsgBox, or fill in a field on the Selection/Launch Form to
explain, but that should, in fact, prevent either of the situations you
describe, actually opening the form for all, or no, Records. In fact, if
you default the value of the Combo in the Selection/Launch Form to the first
item in its drop-down list (see OnCurrent code, below)

Private Sub Form_Current()
Me.Combo0 = Me.Combo0.ItemData(0)
End Sub

And, in the code behind the Command Button, use an

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkers"

If Not IsNull(Me.Combo0) Then
stLinkCriteria = "[WorkerID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
Me!Combo0.SetFocus
Me!Combo0 = Me!Combo0.ItemData(0)
End If

And, if your users need reminding, in the Else clause, you could pop up a
Message Box, or display a Control telling them that they must choose a
person or press the Cancel button.

Larry Linson
Microsoft Office Access MVP

 

[John Keith]

I didn't think my post had gone through, Last Friday when I clicked on "POST"
an error was returned stating that the post did not go through... So I
didn't check back here till much later, I have made a number of changes
trying to get this to work, but the form keeps coming up with out any records
showing...

I have attempted Larry's suggestion to put the filter assigns in the Load
event, but it still wont work.

Here is what I am attempting to do...
1) On the "Employee" Form:
* A txtbox with the current record's SSN showing. (Me.SSN)
* A command button to add an assignment
2) On the "AddAssignment" Form:
* A popup and modal form
* A continuous form showing assignment history (multiple SSN's and
assignments with an effective date. The most recent entry for each SSN is
the current assignment (assignment is a location number) I.E. the Employee
is currently assigned to a location.
* Nothing is allowed to be changed in the detail
* In the form header section a place to select a location with a combo-box
(I will be adding code to add a row with the new data as the form is closed
(or skip it when a cancel button is pressed))
* In the form header section the SSN and a DLookup to get the full
formatted name from another query.

Code attached to the "Employees" Form:
Private Sub btnAddAssignment_Click()
'Where condition and OpenArg
DoCmd.OpenForm "AddAssignment", , , "[SSN] = '" & Me.SSN & "'", , , Me.SSN
End Sub

Code attached to the "AddAssignment" Form:
Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Or Len(Me.OpenArgs) = 0 Then
' no input SSN to point to
MsgBox "Can not directly open form:" & Me.Name & " with out a filter"
Cancel = True
Exit Sub
End If
End Sub

Private Sub Form_Load()
' I thought the Where condition from the OpenForm would handle this but before
' this Form_Load code was in place the the form was coming up empty too
Me.Filter = "[SSN] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
End Sub

I stopped the code in the Form_Load and have verified that Me.OpenArgs does
infact contain the SSN value. After execution the AddAssignment form is
empty (no records showing). Closing the form and opening it in design view
shows the filter to be blank. (I expected to see the string as assigned
above)

Any one know what might be the issue here?

--
Regards,
John

I tried using the filter parm, but me.filter in the open
event was still a null string (even though the form did
display properly with the filtered records.)

Put your filter-checking code in the "Load" event -- works for me.

If you have the selection done in a Combo Box, with "Limit to List" property
set, you can simply bypass executing the DoCmd.OpenForm, and display an
appropriate MsgBox, or fill in a field on the Selection/Launch Form to
explain, but that should, in fact, prevent either of the situations you
describe, actually opening the form for all, or no, Records. In fact, if
you default the value of the Combo in the Selection/Launch Form to the first
item in its drop-down list (see OnCurrent code, below)

Private Sub Form_Current()
Me.Combo0 = Me.Combo0.ItemData(0)
End Sub

And, in the code behind the Command Button, use an

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkers"

If Not IsNull(Me.Combo0) Then
stLinkCriteria = "[WorkerID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
Me!Combo0.SetFocus
Me!Combo0 = Me!Combo0.ItemData(0)
End If

And, if your users need reminding, in the Else clause, you could pop up a
Message Box, or display a Control telling them that they must choose a
person or press the Cancel button.

Larry Linson
Microsoft Office Access MVP

 

[John Keith]

Searching for "Openform Where condition" I found others having a similar
problem, and their problem was solved by making sure that the Where condition
was a string and the data type of the where variable matched the syntax...

In my situatiation the SSN on the input is text as well as the SSN field in
the AssignmentsHistory table (the source for AddAssignments detail)

Thus the where condition coded with me.ssn inside single quotes (') should
be correct.

Oh, this is on Access2003 incase that matters.

--
Regards,
John

I didn't think my post had gone through, Last Friday when I clicked on "POST"
an error was returned stating that the post did not go through... So I
didn't check back here till much later, I have made a number of changes
trying to get this to work, but the form keeps coming up with out any records
showing...

I have attempted Larry's suggestion to put the filter assigns in the Load
event, but it still wont work.

Here is what I am attempting to do...
1) On the "Employee" Form:
* A txtbox with the current record's SSN showing. (Me.SSN)
* A command button to add an assignment
2) On the "AddAssignment" Form:
* A popup and modal form
* A continuous form showing assignment history (multiple SSN's and
assignments with an effective date. The most recent entry for each SSN is
the current assignment (assignment is a location number) I.E. the Employee
is currently assigned to a location.
* Nothing is allowed to be changed in the detail
* In the form header section a place to select a location with a combo-box
(I will be adding code to add a row with the new data as the form is closed
(or skip it when a cancel button is pressed))
* In the form header section the SSN and a DLookup to get the full
formatted name from another query.

Code attached to the "Employees" Form:
Private Sub btnAddAssignment_Click()
'Where condition and OpenArg
DoCmd.OpenForm "AddAssignment", , , "[SSN] = '" & Me.SSN & "'", , , Me.SSN
End Sub

Code attached to the "AddAssignment" Form:
Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Or Len(Me.OpenArgs) = 0 Then
' no input SSN to point to
MsgBox "Can not directly open form:" & Me.Name & " with out a filter"
Cancel = True
Exit Sub
End If
End Sub

Private Sub Form_Load()
' I thought the Where condition from the OpenForm would handle this but before
' this Form_Load code was in place the the form was coming up empty too
Me.Filter = "[SSN] = '" & Me.OpenArgs & "'"
Me.FilterOn = True
End Sub

I stopped the code in the Form_Load and have verified that Me.OpenArgs does
infact contain the SSN value. After execution the AddAssignment form is
empty (no records showing). Closing the form and opening it in design view
shows the filter to be blank. (I expected to see the string as assigned
above)

Any one know what might be the issue here?

--
Regards,
John

I tried using the filter parm, but me.filter in the open
event was still a null string (even though the form did
display properly with the filtered records.)

Put your filter-checking code in the "Load" event -- works for me.

If you have the selection done in a Combo Box, with "Limit to List" property
set, you can simply bypass executing the DoCmd.OpenForm, and display an
appropriate MsgBox, or fill in a field on the Selection/Launch Form to
explain, but that should, in fact, prevent either of the situations you
describe, actually opening the form for all, or no, Records. In fact, if
you default the value of the Combo in the Selection/Launch Form to the first
item in its drop-down list (see OnCurrent code, below)

Private Sub Form_Current()
Me.Combo0 = Me.Combo0.ItemData(0)
End Sub

And, in the code behind the Command Button, use an

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmWorkers"

If Not IsNull(Me.Combo0) Then
stLinkCriteria = "[WorkerID]=" & Me![Combo0]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
Me!Combo0.SetFocus
Me!Combo0 = Me!Combo0.ItemData(0)
End If

And, if your users need reminding, in the Else clause, you could pop up a
Message Box, or display a Control telling them that they must choose a
person or press the Cancel button.

Larry Linson
Microsoft Office Access MVP