DoCmd.FindRecord not sure what the best choice is.

[Phred]

Access 2007, back end SQL Server 2008. It is easier to show you my code then try to explain it.

Private Sub EntityID_DblClick(Cancel As Integer)

Dim intEntityID As Integer

Dim intNewID As Integer

intEntityID = [Forms]![Property]![PropertyEntityJoin].[Form]![EntityID] 'gets the primary key that you want to find in EntityFRM.

DoCmd.OpenForm "EntityFRM" 'Opens EntityFRM

Me.EntityID.SetFocus 'sets the focus on the field EntityID in the EntityFRM

intNewID = DLookup("EntityID", "dbo_entity", "EntityID = " & intEntityID & "") 'Finds and confirms that the EntityID that I want to find actually exists and returns it to the variable Dim intNewID As Integer

DoCmd.FindRecord Here is where I get in trouble. I have the primary key of the record I want to display but I don't know how to find and display it. This should be easy. 

End Sub

I can't seem to find the proper way to display the record.

Thanks,

Fred

[June7]

Options:

1. Provide arguments that fit your requirement, maybe: DoCmd.FindRecord intNewID, acEntire, , acSearchAll, , acAll

2. set the form Filter and FilterOn properties

3. use RecordsetClone and Bookmarks

[Phred]

I tried this. Each line seems to work correctly but I cannot get the form to show the found record. The FindRecord arguments seem correct. It does not error out at all.

Dim intEntityID As Integer
Dim intNewID As Integer
intEntityID = [Forms]![Property]![PropertyEntityJoin].[Form]![EntityID]
DoCmd.OpenForm "EntityFRM"
Me.EntityID.SetFocus
intNewID = DLookup("EntityID", "dbo_entity", "EntityID = " & intEntityID & "")
DoCmd.FindRecord intNewID, acEntire, , acSearchAll, , acAll
Me.Refresh

Should there be some other method besides Me.Refresh?

Thanks

Fred

[June7]

Shouldn't need the Me.Refresh

The FindRecord code works for me.

Are you trying to search a subform? Is the button on main form? Review https://www.accessforums.net/forms/f...ure-45926.html

[Phred]

There is no button. The user double clicks on a field in the sub form to launch the code. I have attached two JPGs.

Property Screen (directly below) has a subform called PropertyEntityJoin_FRM forms the join between the Property table via PropertyFRM and the Entity table. It is a parent child screen (1 to many) linked by Primary keys.



The user wants to find the corresponding Entity on the EntityFRM so..

The user double clicks on the EntityID (highlighted in yellow). The code grabs the EntityID field which happens to be the Primary key for the Entity form/table.

It opens the Entity form (see Entity JPG) and sets the focus on the EntityID field which is in the main form EntityFRM, sets focus on field EntityID, and then runs the findrecord code. It should bring up the matching record.

This allows the user to jump quickly from the Property screen to the entity screen and find the right Entity.



Thanks,

Fred

[June7]

Regardless of what event (button click or textbox double click) is used for the search code, the issue is the same.

Your images don't relate. Property image shows 20 was selected in the subform but shows 14 in the Entity image.

I don't understand why you want the FindRecord code.

Open the Entity form filtered to the EntityID selected in the Property subform.

DoCmd.OpenForm "Entity", , , "EntityID=" & Me.EntityID

[Phred]

Hi June7:

Sorry for the mismatch on the screen snaps. It was sloppiness on my part. I didn't necessarily want the "FindRecord" I was just looking for the correct way and it seemed like a good choice with my limited experience. Your code below did the trick.

DoCmd.OpenForm "Entity", , , "EntityID=" & Me.EntityID

Thanks for your patience.

Fred