Re: OpenForm vs. Form menu double click.

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


"John Keith" wrote:

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


"Larry Linson" wrote:

"John Keith" <JohnKeith@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote

> 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



.

Loading