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

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?

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" wrote:

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?

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" wrote in message
...
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" wrote:

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?

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" wrote:

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" wrote in message
...
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" wrote:

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?

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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?

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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?