From one form, open another form, find record (no filter)

S

Song Su

In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will find the
record in
'frmOffice' if it is already opened as well as it has not opened before
clicking cmdOffice on frmPO?
 
N

NKTower

Here's one way to do it. It will work whether the form was already open or
not.

---- in the "calling form", put this code in the button or what ever other
event you want to use to position the other form -----

Private Sub btn_TheButton_Click()
Dim frm_theOtherForm As Form
Dim ctrl_HiddenControl As Control

' Open the form if it isn't already open
If Not CurrentProject.AllForms("theOtherForm").IsLoaded Then
DoCmd.OpenForm "theOtherForm" ' add options as needed,
End If

' Now that it's open, pass a value in to a hidden control
Set frm_theOtherForm = Forms![theOtherForm].Form
Set ctrl_HiddenControl = frm_theOtherForm.Controls("hiddenControl")
ctrl_HiddenControl.Text = str_TheThingYouWant

' set the timer in that form to a short fuse
frm_theOtherForm.TimerInterval = 10 ' milliseconds

' and clean up
Set ctrl_HiddenControl = Nothing
Set frm_theOtherFOrm = Nothing

End Sub


----- in the FORM "theOtherFOrm" which is top be positioned to the requested
record

a) create a text box control, named "hiddenControl"
b) set the VISIBLE property to FALSE
c) in the form's TIMER event
d) in my example, I want to find the record that has a matched value in the
column that I call "something".

Private Sub Form_Timer()
Dim RSC As DAO.RecordSet
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[something] = '" & Me.hiddenControl & "'"

' Note: equalsign, space, apostrophe, quote, blank, ampersand
' blank, Me.hiddenControll, blank, ampersand, blank,
' quote, apostrophe, quote


RSC.FindFirst str_where
If Not RSC.NoMatch
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
End Sub

I'm sure that there are other ways, but I like this one. It is very
flexible. For example, with two hidden fields you could support choice of
selection - one field would hold the name of the column to search in the
"FindFirst", the other the value to find. Just be sure to handle wrapping
the value to find in quotes or # (if date) or nothing if numeric.



Song Su said:
In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will find the
record in
'frmOffice' if it is already opened as well as it has not opened before
clicking cmdOffice on frmPO?
Click to expand...
 
S

Song Su

Almost there.
If the 'otherForm' is already open, from calling form, click the button, the
otherForm does not come to the front.
How to modify it so 'otherForm' which is already open, come to the front?

Thanks.

NKTower said:
Here's one way to do it. It will work whether the form was already open
or
not.

---- in the "calling form", put this code in the button or what ever other
event you want to use to position the other form -----

Private Sub btn_TheButton_Click()
Dim frm_theOtherForm As Form
Dim ctrl_HiddenControl As Control

' Open the form if it isn't already open
If Not CurrentProject.AllForms("theOtherForm").IsLoaded Then
DoCmd.OpenForm "theOtherForm" ' add options as needed,
End If

' Now that it's open, pass a value in to a hidden control
Set frm_theOtherForm = Forms![theOtherForm].Form
Set ctrl_HiddenControl = frm_theOtherForm.Controls("hiddenControl")
ctrl_HiddenControl.Text = str_TheThingYouWant

' set the timer in that form to a short fuse
frm_theOtherForm.TimerInterval = 10 ' milliseconds

' and clean up
Set ctrl_HiddenControl = Nothing
Set frm_theOtherFOrm = Nothing

End Sub


----- in the FORM "theOtherFOrm" which is top be positioned to the
requested
record

a) create a text box control, named "hiddenControl"
b) set the VISIBLE property to FALSE
c) in the form's TIMER event
d) in my example, I want to find the record that has a matched value in
the
column that I call "something".

Private Sub Form_Timer()
Dim RSC As DAO.RecordSet
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[something] = '" & Me.hiddenControl & "'"

' Note: equalsign, space, apostrophe, quote, blank, ampersand
' blank, Me.hiddenControll, blank, ampersand, blank,
' quote, apostrophe, quote


RSC.FindFirst str_where
If Not RSC.NoMatch
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
End Sub

I'm sure that there are other ways, but I like this one. It is very
flexible. For example, with two hidden fields you could support choice of
selection - one field would hold the name of the column to search in the
"FindFirst", the other the value to find. Just be sure to handle wrapping
the value to find in quotes or # (if date) or nothing if numeric.



Song Su said:
In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will find
the
record in
'frmOffice' if it is already opened as well as it has not opened before
clicking cmdOffice on frmPO?
Click to expand...
Click to expand...
 
N

NKTower

In the timer event, after you have done your repositioning with the bookmark,
select a control (I'll call it txtFirst) and "SetFocus".

Me.txt_First.SetFocus


Song Su said:
Almost there.
If the 'otherForm' is already open, from calling form, click the button, the
otherForm does not come to the front.
How to modify it so 'otherForm' which is already open, come to the front?

Thanks.

NKTower said:
Here's one way to do it. It will work whether the form was already open
or
not.

---- in the "calling form", put this code in the button or what ever other
event you want to use to position the other form -----

Private Sub btn_TheButton_Click()
Dim frm_theOtherForm As Form
Dim ctrl_HiddenControl As Control

' Open the form if it isn't already open
If Not CurrentProject.AllForms("theOtherForm").IsLoaded Then
DoCmd.OpenForm "theOtherForm" ' add options as needed,
End If

' Now that it's open, pass a value in to a hidden control
Set frm_theOtherForm = Forms![theOtherForm].Form
Set ctrl_HiddenControl = frm_theOtherForm.Controls("hiddenControl")
ctrl_HiddenControl.Text = str_TheThingYouWant

' set the timer in that form to a short fuse
frm_theOtherForm.TimerInterval = 10 ' milliseconds

' and clean up
Set ctrl_HiddenControl = Nothing
Set frm_theOtherFOrm = Nothing

End Sub


----- in the FORM "theOtherFOrm" which is top be positioned to the
requested
record

a) create a text box control, named "hiddenControl"
b) set the VISIBLE property to FALSE
c) in the form's TIMER event
d) in my example, I want to find the record that has a matched value in
the
column that I call "something".

Private Sub Form_Timer()
Dim RSC As DAO.RecordSet
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[something] = '" & Me.hiddenControl & "'"

' Note: equalsign, space, apostrophe, quote, blank, ampersand
' blank, Me.hiddenControll, blank, ampersand, blank,
' quote, apostrophe, quote


RSC.FindFirst str_where
If Not RSC.NoMatch
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
End Sub

I'm sure that there are other ways, but I like this one. It is very
flexible. For example, with two hidden fields you could support choice of
selection - one field would hold the name of the column to search in the
"FindFirst", the other the value to find. Just be sure to handle wrapping
the value to find in quotes or # (if date) or nothing if numeric.



Song Su said:
In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will find
the
record in
'frmOffice' if it is already opened as well as it has not opened before
clicking cmdOffice on frmPO?
Click to expand...
Click to expand...
Click to expand...
 
S

Song Su

I added the SetFocus line at end. It still would not bring the form to the
front. Is it because I use Access 2007 tabbed document format (under Access
option, current database)? Here is my code:

Private Sub Form_Timer()
Dim RSC As DAO.Recordset
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[Office] = '" & Me.hiddencontrol & "'"

RSC.FindFirst str_Where
If Not RSC.NoMatch Then
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
Me.PO_Summary.SetFocus
End Sub

NKTower said:
In the timer event, after you have done your repositioning with the
bookmark,
select a control (I'll call it txtFirst) and "SetFocus".

Me.txt_First.SetFocus


Song Su said:
Almost there.
If the 'otherForm' is already open, from calling form, click the button,
the
otherForm does not come to the front.
How to modify it so 'otherForm' which is already open, come to the front?

Thanks.

NKTower said:
Here's one way to do it. It will work whether the form was already
open
or
not.

---- in the "calling form", put this code in the button or what ever
other
event you want to use to position the other form -----

Private Sub btn_TheButton_Click()
Dim frm_theOtherForm As Form
Dim ctrl_HiddenControl As Control

' Open the form if it isn't already open
If Not CurrentProject.AllForms("theOtherForm").IsLoaded Then
DoCmd.OpenForm "theOtherForm" ' add options as needed,
End If

' Now that it's open, pass a value in to a hidden control
Set frm_theOtherForm = Forms![theOtherForm].Form
Set ctrl_HiddenControl = frm_theOtherForm.Controls("hiddenControl")
ctrl_HiddenControl.Text = str_TheThingYouWant

' set the timer in that form to a short fuse
frm_theOtherForm.TimerInterval = 10 ' milliseconds

' and clean up
Set ctrl_HiddenControl = Nothing
Set frm_theOtherFOrm = Nothing

End Sub


----- in the FORM "theOtherFOrm" which is top be positioned to the
requested
record

a) create a text box control, named "hiddenControl"
b) set the VISIBLE property to FALSE
c) in the form's TIMER event
d) in my example, I want to find the record that has a matched value in
the
column that I call "something".

Private Sub Form_Timer()
Dim RSC As DAO.RecordSet
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[something] = '" & Me.hiddenControl & "'"

' Note: equalsign, space, apostrophe, quote, blank, ampersand
' blank, Me.hiddenControll, blank, ampersand, blank,
' quote, apostrophe, quote


RSC.FindFirst str_where
If Not RSC.NoMatch
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
End Sub

I'm sure that there are other ways, but I like this one. It is very
flexible. For example, with two hidden fields you could support choice
of
selection - one field would hold the name of the column to search in
the
"FindFirst", the other the value to find. Just be sure to handle
wrapping
the value to find in quotes or # (if date) or nothing if numeric.



:

In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice
on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will
find
the
record in
'frmOffice' if it is already opened as well as it has not opened
before
clicking cmdOffice on frmPO?
Click to expand...
Click to expand...
Click to expand...
 
N

NKTower

How about this: Let's call the form with the button "First Form" and the one
that it opens "Second Form". How do you OPEN "First Form"? If you open it
with 'acDialog' then that could be the problem. A form opened as a dialog
will stay on top no matter what you do. Put another way:
Form_A opens Form_B which opens Form_C

If Form_A opens Form_B with "acDialog", then yes, Form_C can be opened, but
it will stay in the background and you will be locked in Form_B until you
close it.

Song Su said:
I added the SetFocus line at end. It still would not bring the form to the
front. Is it because I use Access 2007 tabbed document format (under Access
option, current database)? Here is my code:

Private Sub Form_Timer()
Dim RSC As DAO.Recordset
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[Office] = '" & Me.hiddencontrol & "'"

RSC.FindFirst str_Where
If Not RSC.NoMatch Then
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
Me.PO_Summary.SetFocus
End Sub

NKTower said:
In the timer event, after you have done your repositioning with the
bookmark,
select a control (I'll call it txtFirst) and "SetFocus".

Me.txt_First.SetFocus


Song Su said:
Almost there.
If the 'otherForm' is already open, from calling form, click the button,
the
otherForm does not come to the front.
How to modify it so 'otherForm' which is already open, come to the front?

Thanks.

Here's one way to do it. It will work whether the form was already
open
or
not.

---- in the "calling form", put this code in the button or what ever
other
event you want to use to position the other form -----

Private Sub btn_TheButton_Click()
Dim frm_theOtherForm As Form
Dim ctrl_HiddenControl As Control

' Open the form if it isn't already open
If Not CurrentProject.AllForms("theOtherForm").IsLoaded Then
DoCmd.OpenForm "theOtherForm" ' add options as needed,
End If

' Now that it's open, pass a value in to a hidden control
Set frm_theOtherForm = Forms![theOtherForm].Form
Set ctrl_HiddenControl = frm_theOtherForm.Controls("hiddenControl")
ctrl_HiddenControl.Text = str_TheThingYouWant

' set the timer in that form to a short fuse
frm_theOtherForm.TimerInterval = 10 ' milliseconds

' and clean up
Set ctrl_HiddenControl = Nothing
Set frm_theOtherFOrm = Nothing

End Sub


----- in the FORM "theOtherFOrm" which is top be positioned to the
requested
record

a) create a text box control, named "hiddenControl"
b) set the VISIBLE property to FALSE
c) in the form's TIMER event
d) in my example, I want to find the record that has a matched value in
the
column that I call "something".

Private Sub Form_Timer()
Dim RSC As DAO.RecordSet
Dim str_Where As String
Dim str_Bookmark As String

Me.TimerInterval = 0 ' turn it off
Set RSC = Me.Form.RecordsetClone

str_Where = "[something] = '" & Me.hiddenControl & "'"

' Note: equalsign, space, apostrophe, quote, blank, ampersand
' blank, Me.hiddenControll, blank, ampersand, blank,
' quote, apostrophe, quote


RSC.FindFirst str_where
If Not RSC.NoMatch
str_Bookmark = RSC.Bookmark
Me.Bookmark = str_Bookmark
End If
End Sub

I'm sure that there are other ways, but I like this one. It is very
flexible. For example, with two hidden fields you could support choice
of
selection - one field would hold the name of the column to search in
the
"FindFirst", the other the value to find. Just be sure to handle
wrapping
the value to find in quotes or # (if date) or nothing if numeric.



:

In my frmPO, I have cmdOffice. On click:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOffice"
stLinkCriteria = "[OfficeID]=" & Me!OfficeID
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

On my frmOffice, on open event, I have:
Dim rst As DAO.Recordset
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
Set rst = Nothing

This works fine if 'frmOffice' is not open before clicking cmdOffice
on
frmPO. If frmOffice is already open, code
will not run as it is in open event. How to modify code so it will
find
the
record in
'frmOffice' if it is already opened as well as it has not opened
before
clicking cmdOffice on frmPO?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A2000 form ceases working under A2002 1
Mimic Split Form with Two Subforms 0
Form Opens to record if Like 6
Open for to Edit or Add Record 6
stlinkcriteria syntax. multiple fields and wildcards 5
Datashhet View from another Form wont work 2
Open different form based on combo box selection 2
Edit a specific searched record on a form 0

Top