Open Form To Specific Record

[bmcgree1]

Hello Everyone,
So I have 2 forms, nearly identical to each other. On the first form the user finds the record they want by the field "LoanID" and if they wish to edit the loan information they click a button which opens up the second form.
What I need is for the second form to open up to the same record that the first form is still on (first form does not close when button is clicked) based on its LoanID. How do I do that?
Below is some code I was trying to get to work, but couldn't. Any help is much appreciated, thank you.

Dim strFieldName As String
Dim strForm As String
strForm = "frm_FormTwo"
strFieldName = ([Me].[LoanID] = [frm_FormOne].[LoanID])
DoCmd.OpenForm strForm, , , strFieldName

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/wherecondition.htm

 

[bmcgree1]

Paul,
That worked thank you. One more question though, is there anyway that you know of to do this without filtering? For example if FormOne is on record 23 of 50 when you click the button to open FormTwo, FormTwo opens to record 23 of 50 as well.

The reason I ask is because I have an If Else stmt set up on the button that doesn't allow FormTwo to be opened up to certain records based on the date, but if a user was able to simply click "Toggle Filter" it would defeat the purpose of my If Else stmt.

Thank you

 

[DanG]

I just got through getting an answer on that one...
http://www.access-programmers.co.uk/forums/showthread.php?t=175777

 

[pbaldy]

I should probably add this to my site. Using the same forms, this code should do what you're asking:

  Dim rs            As Object
  Dim lngBookmark   As Long

  'set a variable to the current record
  lngBookmark = Me.txtEmpID
  'open the new form
  DoCmd.OpenForm "frmEmployeesDetail"

  'take it to the selected record
  Set rs = Forms!frmEmployeesDetail.RecordsetClone
  rs.FindFirst "EmpID = " & lngBookmark
  Forms!frmEmployeesDetail.Bookmark = rs.Bookmark

  Set rs = Nothing

 

[bmcgree1]

Paul,
I'm doing something wrong, access says "Object doesn't support this property or method" when I try and run this code. Here is how I changed your code to fit for me:

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.LoanID
'open the new form
DoCmd.OpenForm "FormTwo"
'take it to the selected record
Set rs = Forms!FormTwo.LoanID
rs.FindFirst "LoanID = " & lngBookmark
Forms!FormTwo.Bookmark = rs.Bookmark
Set rs = Nothing

 

[pbaldy]

Which line is highlighted? What is the data type of LoanID?

 

[MSAccessRookie]

Paul,
I'm doing something wrong, access says "Object doesn't support this property or method" when I try and run this code. Here is how I changed your code to fit for me:

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.LoanID
'open the new form
DoCmd.OpenForm "FormTwo"
'take it to the selected record
Set rs = Forms!FormTwo.LoanID
rs.FindFirst "LoanID = " & lngBookmark
Forms!FormTwo.Bookmark = rs.Bookmark
Set rs = Nothing

I know that Paul has been helping you, but I noticed that the highlighted code above was incorrect. I think it should be as fopllows:

Set rs = Forms!FormTwo.RecordsetClone

 

[bmcgree1]

I guess I tried to change too much. Paul, Dan and Rookie thank you all so much.

For anyone else looking at this, I tried Dan's method and it worked also. Thanks again!