Jump To A Record

G

Guest

I am attempting to jump to a record, on a form open, using criteria supplied
by the previous form. I found an earlier discussion, on this topic, but was
unsuccessful in making the code work. Hopefully someone can help me out.

Here is the code I am trying to run:

DoCmd.OpenForm "frmsinglewrlookup"

DoCmd.GoToControl [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO]
DoCmd.FindRecord Me.WORK_REQUEST_NO, acEntire, , acSearchAll, , acCurrent,
True

I have tried putting the last 2 lines on the Open event, of the 2nd form,
with no luck. It kept looking for a control named the same as the value of
[Forms]![frmsinglewrlookup]![WORK_REQUEST_NO].

However, setting it after the DoCMD.FormOpen returns an Error 2046 - "The
Command or Action 'GoToControl' isn't available now."

If I comment the line, I get the same error, for 'FindRecord'.

Any suggestions? I think it is partly an event timing issue, but I think
there may be a second problem in that it isn't identifying the control, but
rather the value of that control.

Thanks in advance.

Sharkbyte
 
G

Guest

Let me see if I ubderstand, you have a form open and the you open a second
form, you want to show data related to the record of the first form?
If so have you tried a sub form ? you can do that by going in designe mode,
and select the subform option on the tools toolbar. When it promp about the
relashionship select the same field that you wnat to link on both options.
that will create a sub form that shows all the related records on both forms.
 
G

Guest

You can filter the second form by means of the WhereCondition argument of the
OpenForm method. If WORK_REQUEST_NO is a number data type:

Dim strCriteria as String

strCriteria = "WORK_REQUEST_NO = " & Me.WORK_REQUEST_NO
DoCmd.OpenForm "frmsinglewrlookup", WhereCondition:=strCriteria

If its text data type wrap the value in quotes characters:

strCriteria = "WORK_REQUEST_NO = """ & Me.WORK_REQUEST_NO & """"

Alternatively to navigate to the first matching record in the second form
rather than filtering the form first go to the record in a clone of the
second form's recordset and then synchronize the form's book mark with the
clone's bookmark:

Dim frm as Form
Dim rst As Object

DoCmd.OpenForm "frmsinglewrlookup"

Set frm = Forms("frmsinglewrlookup")
Set rst = frm.Recordset.Clone

With rst
.FindFirst "WORK_REQUEST_NO = " & Me.WORK_REQUEST_NO
If Not .NoMatch Then
frm.Bookmark = .Bookmark
Else
MsgBox "No matching record.", vbInformation, "Warning"
End If
End With

Again the above assumes the WORK_REQUEST_NO is a number data type.

Ken Sheridan
Stafford, England
 
Z

zionsaal

Let me see if I ubderstand, you have a form open and the you open a second
form, you want to show data related to the record of the first form?
If so have you tried a sub form ? you can do that by going in designe mode,
and select the subform option on the tools toolbar. When it promp about the
relashionship select the same field that you wnat to link on both options.
that will create a sub form that shows all the related records on both forms.
--
IT1957



Sharkbyte said:
I am attempting to jump to a record, on a form open, using criteria supplied
by the previous form. I found an earlier discussion, on this topic, but was
unsuccessful in making the code work. Hopefully someone can help me out.
Click to expand...
Here is the code I am trying to run:
Click to expand...
DoCmd.OpenForm "frmsinglewrlookup"
Click to expand...
DoCmd.GoToControl [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO]
DoCmd.FindRecord Me.WORK_REQUEST_NO, acEntire, , acSearchAll, , acCurrent,
True
Click to expand...
I have tried putting the last 2 lines on the Open event, of the 2nd form,
with no luck. It kept looking for a control named the same as the value of
[Forms]![frmsinglewrlookup]![WORK_REQUEST_NO].
Click to expand...
However, setting it after the DoCMD.FormOpen returns an Error 2046 - "The
Command or Action 'GoToControl' isn't available now."
Click to expand...
If I comment the line, I get the same error, for 'FindRecord'.
Click to expand...
Any suggestions? I think it is partly an event timing issue, but I think
there may be a second problem in that it isn't identifying the control, but
rather the value of that control.
Click to expand...
Thanks in advance.
Click to expand...
Sharkbyte- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


try this

DoCmd.OpenForm "FormName", , , "WORK_REQUEST_NO=" & Me.WORK_REQUEST_NO
 
J

JK

Hi, Sharkbyte,

In the second form you are trying to find WORK_REQUEST_NO to find itselef by
going to that record, albeit unsuccessfully, and making it the target of the
find. You better off using OpenArgs .

In the First form:

'++++++++ WORK_REQUEST_NO is *Number* ++

Private Sub Something
Dim strForm As String, numToFind As Long

strForm = "frmsinglewrlookup"
numToFind = Nz(FieldIntheForm, 0)

'Open the form
'numToFind is te open argument in the 2nd form
DoCmd.OpenForm strForm, acNormal, , , , , numToFind

'Move the focus to the second form
DoCmd.SelectObject acForm, strForm

End Sub


In the Second From (Open Event):

Private Sub Form_Open(Cancel As Integer)
Dim numArg as long

If IsNull(Me.Form.OpenArgs) Then
Exit Sub
Else
numArg = Me.Form.OpenArgs
DoCmd.GoToControl "WORK_REQUEST_NO"
DoCmd.FindRecord numArg
End If
DoCmd.GoToControl whatever
End Sub


'++++++++ WORK_REQUEST_NO is *String ++

Private Sub Something
Dim strForm As String, strToFind As String
strForm = "frmsinglewrlookup"
strToFind = Nz(FieldIntheForm, "")

'Open the form
'strToFind is te open argument in the 2nd form
DoCmd.OpenForm strForm, acNormal, , , , , strToFind

'Move the focus to the second form
DoCmd.SelectObject acForm, strForm

End Sub


In the Second From (Open Event):

Private Sub Form_Open(Cancel As Integer)
Dim opnArg as String

If IsNull(Me.Form.OpenArgs) Then
Exit Sub
Else
opnArg = Me.Form.OpenArgs
DoCmd.GoToControl "WORK_REQUEST_NO"
DoCmd.FindRecord opnArg
End If
DoCmd.GoToControl whatever
End Sub




|I am attempting to jump to a record, on a form open, using criteria
supplied
| by the previous form. I found an earlier discussion, on this topic, but
was
| unsuccessful in making the code work. Hopefully someone can help me out.
|
| Here is the code I am trying to run:
|
| DoCmd.OpenForm "frmsinglewrlookup"
|
| DoCmd.GoToControl [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO]
| DoCmd.FindRecord Me.WORK_REQUEST_NO, acEntire, , acSearchAll, , acCurrent,
| True
|
| I have tried putting the last 2 lines on the Open event, of the 2nd form,
| with no luck. It kept looking for a control named the same as the value
of
| [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO].
|
| However, setting it after the DoCMD.FormOpen returns an Error 2046 - "The
| Command or Action 'GoToControl' isn't available now."
|
| If I comment the line, I get the same error, for 'FindRecord'.
|
| Any suggestions? I think it is partly an event timing issue, but I think
| there may be a second problem in that it isn't identifying the control,
but
| rather the value of that control.
|
| Thanks in advance.
|
| Sharkbyte
 
G

Guest

JK:

The code was perfect. Thanks. I was beginning to wonder if I hadn't
explained myself correctly.

Much appreciated.

Sharkbyte




JK said:
Hi, Sharkbyte,

In the second form you are trying to find WORK_REQUEST_NO to find itselef by
going to that record, albeit unsuccessfully, and making it the target of the
find. You better off using OpenArgs .

In the First form:

'++++++++ WORK_REQUEST_NO is *Number* ++

Private Sub Something
Dim strForm As String, numToFind As Long

strForm = "frmsinglewrlookup"
numToFind = Nz(FieldIntheForm, 0)

'Open the form
'numToFind is te open argument in the 2nd form
DoCmd.OpenForm strForm, acNormal, , , , , numToFind

'Move the focus to the second form
DoCmd.SelectObject acForm, strForm

End Sub


In the Second From (Open Event):

Private Sub Form_Open(Cancel As Integer)
Dim numArg as long

If IsNull(Me.Form.OpenArgs) Then
Exit Sub
Else
numArg = Me.Form.OpenArgs
DoCmd.GoToControl "WORK_REQUEST_NO"
DoCmd.FindRecord numArg
End If
DoCmd.GoToControl whatever
End Sub


'++++++++ WORK_REQUEST_NO is *String ++

Private Sub Something
Dim strForm As String, strToFind As String
strForm = "frmsinglewrlookup"
strToFind = Nz(FieldIntheForm, "")

'Open the form
'strToFind is te open argument in the 2nd form
DoCmd.OpenForm strForm, acNormal, , , , , strToFind

'Move the focus to the second form
DoCmd.SelectObject acForm, strForm

End Sub


In the Second From (Open Event):

Private Sub Form_Open(Cancel As Integer)
Dim opnArg as String

If IsNull(Me.Form.OpenArgs) Then
Exit Sub
Else
opnArg = Me.Form.OpenArgs
DoCmd.GoToControl "WORK_REQUEST_NO"
DoCmd.FindRecord opnArg
End If
DoCmd.GoToControl whatever
End Sub




|I am attempting to jump to a record, on a form open, using criteria
supplied
| by the previous form. I found an earlier discussion, on this topic, but
was
| unsuccessful in making the code work. Hopefully someone can help me out.
|
| Here is the code I am trying to run:
|
| DoCmd.OpenForm "frmsinglewrlookup"
|
| DoCmd.GoToControl [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO]
| DoCmd.FindRecord Me.WORK_REQUEST_NO, acEntire, , acSearchAll, , acCurrent,
| True
|
| I have tried putting the last 2 lines on the Open event, of the 2nd form,
| with no luck. It kept looking for a control named the same as the value
of
| [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO].
|
| However, setting it after the DoCMD.FormOpen returns an Error 2046 - "The
| Command or Action 'GoToControl' isn't available now."
|
| If I comment the line, I get the same error, for 'FindRecord'.
|
| Any suggestions? I think it is partly an event timing issue, but I think
| there may be a second problem in that it isn't identifying the control,
but
| rather the value of that control.
|
| Thanks in advance.
|
| Sharkbyte
Click to expand...
 
J

JK

My pleasure SharkByte,

I Forgot to mention:
If you have other routines in Open event of the second form, make sure that
my routine is *the last* one, otherwise other routines will not run when you
open that form in other ways when OpenArgs is a Null value

Regards/JK



| JK:
|
| The code was perfect. Thanks. I was beginning to wonder if I hadn't
| explained myself correctly.
|
| Much appreciated.
|
| Sharkbyte
|
|
|
|
| "JK" wrote:
|
| > Hi, Sharkbyte,
| >
| > In the second form you are trying to find WORK_REQUEST_NO to find
itselef by
| > going to that record, albeit unsuccessfully, and making it the target of
the
| > find. You better off using OpenArgs .
| >
| > In the First form:
| >
| > '++++++++ WORK_REQUEST_NO is *Number* ++
| >
| > Private Sub Something
| > Dim strForm As String, numToFind As Long
| >
| > strForm = "frmsinglewrlookup"
| > numToFind = Nz(FieldIntheForm, 0)
| >
| > 'Open the form
| > 'numToFind is te open argument in the 2nd form
| > DoCmd.OpenForm strForm, acNormal, , , , , numToFind
| >
| > 'Move the focus to the second form
| > DoCmd.SelectObject acForm, strForm
| >
| > End Sub
| >
| >
| > In the Second From (Open Event):
| >
| > Private Sub Form_Open(Cancel As Integer)
| > Dim numArg as long
| >
| > If IsNull(Me.Form.OpenArgs) Then
| > Exit Sub
| > Else
| > numArg = Me.Form.OpenArgs
| > DoCmd.GoToControl "WORK_REQUEST_NO"
| > DoCmd.FindRecord numArg
| > End If
| > DoCmd.GoToControl whatever
| > End Sub
| >
| >
| > '++++++++ WORK_REQUEST_NO is *String ++
| >
| > Private Sub Something
| > Dim strForm As String, strToFind As String
| > strForm = "frmsinglewrlookup"
| > strToFind = Nz(FieldIntheForm, "")
| >
| > 'Open the form
| > 'strToFind is te open argument in the 2nd form
| > DoCmd.OpenForm strForm, acNormal, , , , , strToFind
| >
| > 'Move the focus to the second form
| > DoCmd.SelectObject acForm, strForm
| >
| > End Sub
| >
| >
| > In the Second From (Open Event):
| >
| > Private Sub Form_Open(Cancel As Integer)
| > Dim opnArg as String
| >
| > If IsNull(Me.Form.OpenArgs) Then
| > Exit Sub
| > Else
| > opnArg = Me.Form.OpenArgs
| > DoCmd.GoToControl "WORK_REQUEST_NO"
| > DoCmd.FindRecord opnArg
| > End If
| > DoCmd.GoToControl whatever
| > End Sub
| >
| >
| >
| >
| > | > |I am attempting to jump to a record, on a form open, using criteria
| > supplied
| > | by the previous form. I found an earlier discussion, on this topic,
but
| > was
| > | unsuccessful in making the code work. Hopefully someone can help me
out.
| > |
| > | Here is the code I am trying to run:
| > |
| > | DoCmd.OpenForm "frmsinglewrlookup"
| > |
| > | DoCmd.GoToControl [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO]
| > | DoCmd.FindRecord Me.WORK_REQUEST_NO, acEntire, , acSearchAll, ,
acCurrent,
| > | True
| > |
| > | I have tried putting the last 2 lines on the Open event, of the 2nd
form,
| > | with no luck. It kept looking for a control named the same as the
value
| > of
| > | [Forms]![frmsinglewrlookup]![WORK_REQUEST_NO].
| > |
| > | However, setting it after the DoCMD.FormOpen returns an Error 2046 -
"The
| > | Command or Action 'GoToControl' isn't available now."
| > |
| > | If I comment the line, I get the same error, for 'FindRecord'.
| > |
| > | Any suggestions? I think it is partly an event timing issue, but I
think
| > | there may be a second problem in that it isn't identifying the
control,
| > but
| > | rather the value of that control.
| > |
| > | Thanks in advance.
| > |
| > | Sharkbyte
| >
| >
| >
| >
 

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

Jump to record that satisfy some criteria 7
Create a Message Error when record not found 3
refering to field in data sheet view 2
Using bookmarks to select a record after form requery 4
Getting field data in data sheet view 12
Open Form Action from Continuous form 2
Find record in a listbox 1
How to go to last record? 1

Top