Goto Record in an open form

[Guest]

Hi

I have two forms Contatcts and Jobs
On the Contacts form there is a list box showing associated jobs
I want to be able to double click a Job in contacts and go to the job in Jobs.
I have the following code in the double click event:
If IsLoaded("frmJobList") Then
DoCmd.FindRecord Me.lstJobs.Value, , , , True
Else
DoCmd.OpenForm strForm, , "[Job No] = " & Me.lstJobs.Value
' DoCmd.OpenForm "Work_Orders", , , "WO_ID = " & Me.WO_ID
End If

If the form is loaded I get an error saying "The command or action Find
Record isn't available now".

Any ideas as to what is wrong.

Regards
Paul

 

[Allen Browne]

Find the record in the form's RecordsetClone.
Then set the form's Bookmark to the found record.

This kind of thing:

Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.lstJobs) Then
If IsLoaded("frmJobList") Then
.SetFocus 'Bring forward
If .Dirty Then 'Save any edits
.Dirty = False
End If
Else
DoCmd.OpenForm "frmJobList"
End If

With Forms("frmJobList")
Set rs = .RecordsetClone
strWhere = "[Job No] = " & Me.lstJobs
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
Set rs = Nothing

Note that if [Job No] is a Text type field (not a Number type), you need
extra quotes:
strWhere = "[Job No] = """ & Me.lstJobs & """"

 

[Guest]

Hi This works fine thanks.

Regards
Paul

Find the record in the form's RecordsetClone.
Then set the form's Bookmark to the found record.

This kind of thing:

Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.lstJobs) Then
If IsLoaded("frmJobList") Then
.SetFocus 'Bring forward
If .Dirty Then 'Save any edits
.Dirty = False
End If
Else
DoCmd.OpenForm "frmJobList"
End If

With Forms("frmJobList")
Set rs = .RecordsetClone
strWhere = "[Job No] = " & Me.lstJobs
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found. Filtered?"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
Set rs = Nothing

Note that if [Job No] is a Text type field (not a Number type), you need
extra quotes:
strWhere = "[Job No] = """ & Me.lstJobs & """"

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

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

Hi

I have two forms Contatcts and Jobs
On the Contacts form there is a list box showing associated jobs
I want to be able to double click a Job in contacts and go to the job in
Jobs.
I have the following code in the double click event:
If IsLoaded("frmJobList") Then
DoCmd.FindRecord Me.lstJobs.Value, , , , True
Else
DoCmd.OpenForm strForm, , "[Job No] = " & Me.lstJobs.Value
' DoCmd.OpenForm "Work_Orders", , , "WO_ID = " & Me.WO_ID
End If

If the form is loaded I get an error saying "The command or action Find
Record isn't available now".

Any ideas as to what is wrong.

Regards
Paul