Code to go to a specific record in another form

[Guest]

Hi,

I have a form that lists all Outstanding Jobs in our department. Only a few
fields have been selected to appear in this form. It's format is Continuous
Forms so the jobs appear in list format. The form's source is a query that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of each job.
We identify each job using both the Job Number field and the Date Job
Submitted field. This is because the form restarts the Job Number at "1" at
the start of each day.

I would like to place a command button on the Outstanding Jobs form that,
when clicked, takes you to that specific job on the main form (the caption
will be "See Job Details"). How can I code the command button to accomplish
this?

Thanks!

 

[Allen Browne]

This example assumes that the other form is already open, and you need to to
show all records. (If not, you could build the same Where string, and use it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If

 

[Guest]

Hi Allen,

Thank you!! However I am getting a compile error -- the "rs As
DAO.RecordsetClone" is highlighted, and the error says "user-defined type not
defined". How would I fix this?

Thanks again,
Rosemary

This example assumes that the other form is already open, and you need to to
show all records. (If not, you could build the same Where string, and use it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a form that lists all Outstanding Jobs in our department. Only a
few
fields have been selected to appear in this form. It's format is
Continuous
Forms so the jobs appear in list format. The form's source is a query
that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of each
job.
We identify each job using both the Job Number field and the Date Job
Submitted field. This is because the form restarts the Job Number at "1"
at
the start of each day.

I would like to place a command button on the Outstanding Jobs form that,
when clicked, takes you to that specific job on the main form (the caption
will be "See Job Details"). How can I code the command button to
accomplish
this?

Thanks!

 

[Allen Browne]

Open the code window.
Choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

Access 1, 2, 95, 97, and 2003 all have this reference by default.
Access 2000 and 2002 missed out.

More info about references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you!! However I am getting a compile error -- the "rs As
DAO.RecordsetClone" is highlighted, and the error says "user-defined type
not
defined". How would I fix this?

Thanks again,
Rosemary

This example assumes that the other form is already open, and you need to
to
show all records. (If not, you could build the same Where string, and use
it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If

Hi,

I have a form that lists all Outstanding Jobs in our department. Only
a
few
fields have been selected to appear in this form. It's format is
Continuous
Forms so the jobs appear in list format. The form's source is a query
that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of each
job.
We identify each job using both the Job Number field and the Date Job
Submitted field. This is because the form restarts the Job Number at
"1"
at
the start of each day.

I would like to place a command button on the Outstanding Jobs form
that,
when clicked, takes you to that specific job on the main form (the
caption
will be "See Job Details"). How can I code the command button to
accomplish
this?

Thanks!

 

[Guest]

Hi Allen,

I'm sorry -- don't know what I'm doing wrong -- I checked the Microsoft DAO
3.6 Object Library box, but I'm still getting the same error message. . .

Rosemary

Open the code window.
Choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

Access 1, 2, 95, 97, and 2003 all have this reference by default.
Access 2000 and 2002 missed out.

More info about references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you!! However I am getting a compile error -- the "rs As
DAO.RecordsetClone" is highlighted, and the error says "user-defined type
not
defined". How would I fix this?

Thanks again,
Rosemary

This example assumes that the other form is already open, and you need to
to
show all records. (If not, you could build the same Where string, and use
it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If


Hi,

I have a form that lists all Outstanding Jobs in our department. Only
a
few
fields have been selected to appear in this form. It's format is
Continuous
Forms so the jobs appear in list format. The form's source is a query
that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of each
job.
We identify each job using both the Job Number field and the Date Job
Submitted field. This is because the form restarts the Job Number at
"1"
at
the start of each day.

I would like to place a command button on the Outstanding Jobs form
that,
when clicked, takes you to that specific job on the main form (the
caption
will be "See Job Details"). How can I code the command button to
accomplish
this?

Thanks!

 

[Guest]

P.S. Does it mean something that when I try to type "RecordsetClone" after
the DAO, it doesn't appear in the list of properties? Recordset appears, but
not RecordsetClone.

Hi Allen,

I'm sorry -- don't know what I'm doing wrong -- I checked the Microsoft DAO
3.6 Object Library box, but I'm still getting the same error message. . .

Rosemary

Open the code window.
Choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

Access 1, 2, 95, 97, and 2003 all have this reference by default.
Access 2000 and 2002 missed out.

More info about references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you!! However I am getting a compile error -- the "rs As
DAO.RecordsetClone" is highlighted, and the error says "user-defined type
not
defined". How would I fix this?

Thanks again,
Rosemary


:

This example assumes that the other form is already open, and you need to
to
show all records. (If not, you could build the same Where string, and use
it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If


Hi,

I have a form that lists all Outstanding Jobs in our department. Only
a
few
fields have been selected to appear in this form. It's format is
Continuous
Forms so the jobs appear in list format. The form's source is a query
that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of each
job.
We identify each job using both the Job Number field and the Date Job
Submitted field. This is because the form restarts the Job Number at
"1"
at
the start of each day.

I would like to place a command button on the Outstanding Jobs form
that,
when clicked, takes you to that specific job on the main form (the
caption
will be "See Job Details"). How can I code the command button to
accomplish
this?

Thanks!

 

[Allen Browne]

Sorry, the declaration line should be:
Dim rs As DAO.Recordset

The RecordsetClone bit comes later in the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

P.S. Does it mean something that when I try to type "RecordsetClone"
after
the DAO, it doesn't appear in the list of properties? Recordset appears,
but
not RecordsetClone.

Hi Allen,

I'm sorry -- don't know what I'm doing wrong -- I checked the Microsoft
DAO
3.6 Object Library box, but I'm still getting the same error message. . .

Rosemary

Open the code window.
Choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

Access 1, 2, 95, 97, and 2003 all have this reference by default.
Access 2000 and 2002 missed out.

More info about references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you!! However I am getting a compile error -- the "rs As
DAO.RecordsetClone" is highlighted, and the error says "user-defined
type
not
defined". How would I fix this?

Thanks again,
Rosemary


:

This example assumes that the other form is already open, and you
need to
to
show all records. (If not, you could build the same Where string,
and use
it
in the WhereCondition of OpenForm.)

Dim frm As Form
Dim rs As DAO.RecordsetClone
Dim strWhere As String

Set frm = Forms![Whatever the target form is called here]
Set rs = frm.RecordsetClone

strWhere = "([Date Job Submitted] = " & _
Format(Me.[Date Job Submitted], "\#mm\/dd\/yyyy\#") & _
") AND ([Job Number] = " & Me.[Job Number] & ")"

rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not Found"
Else
If frm.Dirty Then frm.Dirty = False
frm.Bookmark = rs.Bookmark
frm.SetFocus
End If


Hi,

I have a form that lists all Outstanding Jobs in our department.
Only
a
few
fields have been selected to appear in this form. It's format is
Continuous
Forms so the jobs appear in list format. The form's source is a
query
that
filters out all jobs that have been completed.

On the Main Form, with its tabbed subforms, are all the details of
each
job.
We identify each job using both the Job Number field and the Date
Job
Submitted field. This is because the form restarts the Job Number
at
"1"
at
the start of each day.

I would like to place a command button on the Outstanding Jobs
form
that,
when clicked, takes you to that specific job on the main form (the
caption
will be "See Job Details"). How can I code the command button to
accomplish
this?

Thanks!