Goto Record in an open form

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 & """"
 
Hi This works fine thanks.

Regards
Paul

Allen Browne said:
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.

Paul Cram said:
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
Click to expand...
Click to expand...
 
Back
Top