DoCmd.OpenForm Problem

[Mike]

I am trying to open a search results form based on the input from a
prompt form. I am using the following code:

--- Begin Code ---
Private Sub btnSearch_Click()
'Dim Variable and assign data
Dim srce As String, fstnme As String, lstnme As String, bidnum As String
srce = Me.Source_Control

If Not IsNull(Me.Text18) Then
lstnme = Me.Text18
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[last_name] = '" & lstnme & "'"
'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub

ElseIf Not IsNull(Me.Text16) Then
fstnme = Me.Text16
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[first_name] = '" & fstnme &"'"
'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub

ElseIf Not IsNull(Me.Text20) Then
bidnum = Me.Text20
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[last_bidder_number] = " &
bidnum 'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub
End If

End Sub

--- End Code ---

Sorry for the word wrapping. All the lines are single lines no wraping
in the program.

The primary search field is the last name. This is why it is checked
first. The problem is when I open the search results form, it opens with
the previous query results not the current request, unless I set a break
point and the code pauses and I then hit the 'RUN' key (F5) to contine
the code execution.

How can I get the form to open with the correct results without stopping
or pausing the code run?

I am using MS-Access 2003 as a front end to a MS-SQL MSDE backend. This
is an Access Project.

Thansk for any help!!

Mike Charney

[MacDermott]

It sounds as if your Results form is not getting properly closed when you
are finished with it.
If there's no other obvious place to close it, you could do this just before
you re-open it.

"Mike" <[e-mail]> wrote in message
news:II***************@newssvr17.news.prodigy.com. ..

I am trying to open a search results form based on the input from a
prompt form. I am using the following code:

--- Begin Code ---
Private Sub btnSearch_Click()
'Dim Variable and assign data
Dim srce As String, fstnme As String, lstnme As String, bidnum As String
srce = Me.Source_Control

If Not IsNull(Me.Text18) Then
lstnme = Me.Text18
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[last_name] = '" & lstnme & "'"
'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub

ElseIf Not IsNull(Me.Text16) Then
fstnme = Me.Text16
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[first_name] = '" & fstnme &"'"
'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub

ElseIf Not IsNull(Me.Text20) Then
bidnum = Me.Text20
'Open Results Form
DoCmd.OpenForm "frmSearch_Results", , , "[last_bidder_number] = " &
bidnum 'Assign Source Control Data
Forms![frmSearch_Results].[Source_Control] = srce
'Close Search Form
DoCmd.Close acForm, "frmCustomer_Search", acSaveNo
Exit Sub
End If

End Sub

--- End Code ---

Sorry for the word wrapping. All the lines are single lines no wraping
in the program.

The primary search field is the last name. This is why it is checked
first. The problem is when I open the search results form, it opens with
the previous query results not the current request, unless I set a break
point and the code pauses and I then hit the 'RUN' key (F5) to contine
the code execution.

How can I get the form to open with the correct results without stopping
or pausing the code run?

I am using MS-Access 2003 as a front end to a MS-SQL MSDE backend. This
is an Access Project.

Thansk for any help!!

Mike Charney

[cjb_kjb]

Something that might help you.

You'll probably have to modify this but your problem is somewhat
similar to a situation I have where I
want to open forms from another form and may :
only want a subset of the data
want the data in a particular sequence
and want to open the form with a particular record showing

My circumstances are different to yours in that I always use convoluted
keys for records (I.e. Ids) not lastname, firstName etc and I always
know the key of the record I want showing

I open the form I want and pass in Openargs on the open statement 3
pieces of information; key field value ^ criteria and sequence

eg 267^"Where City = 'Adelaide' Order by lastName"

The on open event on the called form calls a function which appears
below that parses this info from the openargs parameter (openargs can
only be 1 string value - thats why the data I want is all concantenated
as shown).

It gets the key Value, the where clause and orderby clause (if they
exist) and applies them to the form's filter and orderby properties
then does a find on the form's recordset clone for the key value and
positions the form's recordset to the key using the bookmark property.

The ApplyOpenArgs function requires 2 pieces of data - frm = pointer to
the form calling it (i.e. Me) and the name of the keyfield that the
passed keyvalue applies to. This is where you may need to change it a
bit to maybe pass this from the calling form also, as you seem to be
using different keys depending on circumstances where as I'm always
passing the primary key value of the table the form is displaying.

Also - I have modified the code from something that is more complicated
than I have described - I think it should work but is possible there
will be errors if you just copy it.

Calling Form code:

DoCmd.OpenForm "formyouwanttoOpen", acNormal, , , acFormEdit, , _
& KeyValue & "^" & strCriteria & " " & strSequence

Called form code:
================================================== =====
Private Sub Form_Open(Cancel As Integer)

If ApplyOpenArgs(Me, "[BPAId]") = False Then
MsgBox "Error in applying Filter or Orderby on opening of this
form", vbOKOnly
End If

End Sub

'************************************************* ***********************************
Function ApplyOpenArgs(frm As Form, strKeyFldName As String) As Boolean
'************************************************* ***********************************

On Error GoTo Err_ApplyOpenArgs

Dim strCrit As String
Dim strFilter As String
Dim strOrder As String
Dim j As Integer
Dim varKeyValue As Variant
Dim i As Integer

varKeyValue = 0
strCrit = ""
ApplyOpenArgs = True
frm.OrderBy = ""
frm.OrderByOn = False

If Not (Nz(frm.OpenArgs)) = "" Then
j = InStr(1, frm.OpenArgs, "^")
If j = 0 Then
j = Len(frm.OpenArgs) + 1
strCrit = ""
Else
strCrit = Trim(Right(frm.OpenArgs, Len(frm.OpenArgs) - j))
End If
varKeyValue = Mid(frm.OpenArgs, 1, j - 1)

j = InStr(1, strCrit, "ORDER BY")
If j = 0 Then
strFilter = Replace(strCrit, "WHERE ", "")
strOrder = ""
Else
strFilter = Replace(left(strCrit, j - 1), "WHERE ", "")
strOrder = Right(strCrit, Len(strCrit) - (j + 8))
End If
If Trim(strFilter) <> "" Then
DoCmd.ApplyFilter , strFilter
End If
frm.OrderByOn = True
frm.OrderBy = Replace(strOrder, ";", "")
End If

Call frm.RecordsetClone.FindFirst(strKeyFldName & " = " & varKey)
frm.Bookmark = frm.RecordsetClone.Bookmark

Exit_ApplyOpenArgs:
Exit Function

Err_ApplyOpenArgs:
MsgBox Err.Description
ApplyOpenArgs = False
Resume Exit_ApplyOpenArgs

End Function