Code to Open a Form to a specific record

[moore020]

I am using a datasheet view with dbl click code to open a form to a selected record.

I was able to use pbaldy's code and it worked perfectly.

DoCmd.OpenForm "Asset Status", , , "[Project Number] = '" & Me.Project_Number & " ' "

Then I realized I really want to be able to go to other records after I have gone to this form and tried this:

Dim rs As Object
Dim lngBookmark As Long
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form
DoCmd.OpenForm "Asset Status"
'take it to selected record
Set rs = Forms![Asset Status].RecordsetClone
rs.FindFirst "[Project Number] = '" & lngBookmark & " ' "
'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
' If rs.NoMatch Then 'optional - if no match, go to a new record
' DoCmd.GoToRecord acForm, "frmEmployeesDetail", acNewRec 'optional
' Forms!frmEmployeesDetail.txtEmpID = Me.txtEmpID 'optional - copy the employee ID from this form
' Else 'optional
Forms![Asset Status].Bookmark = rs.Bookmark
'End If 'optional
Set rs = Nothing

But to no avail. Project_Number is a text field.

If anyone can help me figure out where I am missing it I would appreciate it.

 

[vbaInet]

Have a look at the following from pbaldy:

http://baldyweb.com/Bookmark.htm

 

[moore020]

Thank you for the quick reply. That is where I got the second set of code that is not currently working for me. I suspect this is because that code was written for a numeric data type but the fields I am referencing are text data types.

 

[vbaInet]

Replace this:

& " ' "

... with this

& "'"

 

[moore020]

Replaced as suggested on line 9 with no luck.

I am getting an error in the code that pops up and highlights "lngBookmark = Me.Project_Number" when I try to double click. Does LNG indicated long integer or something when I need it to be bookmarking text?

 

[steve21nj]

For a text field it would look like this on the open form:

DoCmd.OpenForm "Asset Status", , , "[Project Number] = " & Me.Project_Number

For you code, I believe it would look like this if its a text field:

rs.FindFirst "[Project Number] = " & lngBookmark

I am using a datasheet view with dbl click code to open a form to a selected record.

I was able to use pbaldy's code and it worked perfectly.

DoCmd.OpenForm "Asset Status", , , "[Project Number] = '" & Me.Project_Number & " ' "

Then I realized I really want to be able to go to other records after I have gone to this form and tried this:

Dim rs As Object
Dim lngBookmark As Long
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form
DoCmd.OpenForm "Asset Status"
'take it to selected record
Set rs = Forms![Asset Status].RecordsetClone
rs.FindFirst "[Project Number] = '" & lngBookmark & " ' "
'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
' If rs.NoMatch Then 'optional - if no match, go to a new record
' DoCmd.GoToRecord acForm, "frmEmployeesDetail", acNewRec 'optional
' Forms!frmEmployeesDetail.txtEmpID = Me.txtEmpID 'optional - copy the employee ID from this form
' Else 'optional
Forms![Asset Status].Bookmark = rs.Bookmark
'End If 'optional
Set rs = Nothing

But to no avail. Project_Number is a text field.

If anyone can help me figure out where I am missing it I would appreciate it.

 

[moore020]

Thank you for the response but I am still getting a type mismatch error.

 

[steve21nj]

Can you post a sample database? WinZip so we can take a look.

 

[vbaInet]

Replaced as suggested on line 9 with no luck.

I am getting an error in the code that pops up and highlights "lngBookmark = Me.Project_Number" when I try to double click. Does LNG indicated long integer or something when I need it to be bookmarking text?

Change the variable declaration from Long to String.

And steve, Text is as the poster already has it. What you describe is for Numbers.

 

[moore020]

Not sure I know how to do that. Is there a way to save the DB so it removes information, or is there a way to save a sample of the DB?

 

[vbaInet]

Not sure I know how to do that. Is there a way to save the DB so it removes information, or is there a way to save a sample of the DB?

Take your time and re-read my post again and then look at your code. It will make sense.

 

[moore020]

Aha. Thank you so much.

Below is what worked.

Private Sub Project_Number_DblClick(Cancel As Integer)
Dim rs As Object
Dim lngBookmark As String
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form
DoCmd.OpenForm "Asset Status"
'take it to selected record
Set rs = Forms![Asset Status].RecordsetClone
rs.FindFirst "[Project Number] = '" & lngBookmark & " ' "
'the lines marked as optional can be included if there's a chance the record won't exist in the form being opened
' If rs.NoMatch Then 'optional - if no match, go to a new record
' DoCmd.GoToRecord acForm, "frmEmployeesDetail", acNewRec 'optional
' Forms!frmEmployeesDetail.txtEmpID = Me.txtEmpID 'optional - copy the employee ID from this form
' Else 'optional
Forms![Asset Status].Bookmark = rs.Bookmark
'End If 'optional
Set rs = Nothing
End Sub

 

[vbaInet]

Good to hear!

By the way, lng usually represents Long so since you've changed it to String you might want to change that prefix to str.