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 2 6737
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" <me@you.com> 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: beowulfs |
last post by:
Here's what I've got:
I've got a form with combo boxes. you can select already existing
company names or type in new ones. if you type in a new one, it
prompts you to double click the combo...
|
by: Lyn |
last post by:
Hi,
I have a Search input form which collects from the user a person's name. I
am using LIKE with a "%" suffix in the SQL so that the user does not have to
type in the full name. When they hit...
|
by: cannen |
last post by:
I am wanting to use Docmd to open another form and set the newly opened
forms detail.visible = false to true.
stDocName = "frmVacRequest"
DoCmd.OpenForm stDocName, acNormal 'This opens my form...
|
by: Thelma Lubkin |
last post by:
formA determines some_where and some_value and issues
docmd.close ,Me
docmd.openform "formB", , ,some_where, , ,some_value
formB receives the correct some_where and some_value
After...
|
by: John Welch |
last post by:
I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssignments", , , "SearchAssignmentID = 1"
(SearchAssignmentID is the PK, auto number)
When it runs, the form opens but...
|
by: keliie |
last post by:
Hello (from Access novice),
I'm building a switchboard form (using a Treeview object). The treeview
is populated by two tables (tblSwitchboardParent and
tblSwitchboardChild). Within...
|
by: mark mestrom |
last post by:
hi, i have this problem with OpenForm and the stLinkCriteria. I have the following code:
Private Sub Knop22_Click()
On Error GoTo Err_Knop22_Click
Dim stDocName As String
Dim...
|
by: RAG2007 |
last post by:
Hi all,
Have a question I can't get around. I've done this before and for some reason can't get it to work this time.
I'm opening a form through docmd.openform, and trying to get it to open an...
|
by: silen |
last post by:
i am using Access2000. Currently i had a main form call "frmDownload" and sub form "fsubAdmmission". Under the fsubAdmmission, i do have another sub form "fsubHospital". Once i link my application to...
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |