click on datasheet to open a specific record in a form

[janeyg]

Hi

I am a novice when it come to codes and macros that are more than basic.

I have two forms. One is a datasheet. One is a form with a default
view of Single Form (which contains combo boxes).

I want to have the user select (click) on a record in the
datasheet and have the second form open to that record. The datasheet form acts as an advanced search on two title fields.

I tried the open form macro but I dont know how to have to second form open to a specific record, I don't know how to set the "where condition" as some post suggested.

I have searched the forum and though I find some answers, none that give me a step by step on what to go into to do this. I am stumped.

Any ideas? Any help would be much appreciated.
thanks
Janeyg

 

[DavidAtWork]

I've always had to resort to having the form open in continuous view (not datasheet), then you can use the double-click event of a certain field, usually the field that is primary key for the record you want to open and use the docmd:
DoCmd.OpenForm "frmMyForm", acNormal, , "[PKrec_ID] = " & Me.PKrec_ID

PKrec_ID is your primary key

David

 

[janeyg]

Hi David

Thanks for your reply. I have added the code and done as you have suggested but I get the error message" run time error 2102" The form name FRM_Resources by Step is misspelled or does not exist ?

I dont know how to get rid of this? And don't understand why it says my form does not exist? Any idea's?
thanks
Jane

 

[DavidAtWork]

it may be that [FRM_Resources by Step] needs to be enclosed by square brackets. Is this the form you're trying to open or the form you're double-clicking. Post the code you're using to open one form the other

 

[janeyg]

This is the code I have in the double click event in the form I am using to search for a record.

The form I am trying to open is FRM_Resources by Step, as a specific record that matches the found Title (which is my primary key)

Private Sub Title_DblClick(Cancel As Integer)
DoCmd.OpenForm "FRM_Resources by Step", acNormal, , "[Title] = " & Me.Title
End Sub

 

[DavidAtWork]

if Title is a string, then the syntax needs to be changed to:
DoCmd.OpenForm "FRM_Resources by Step", acNormal, , "[Title] = ' " & Me.Title & " ' "

 

[janeyg]

No, I seem to get the run time error 2102 again. The form I am opening from is split form - even if I change the form I am opening to continuous instead of single - this error still pops up?

 

[janeyg]

ok, I changed the name of my form, as that seemed to be bring up errors. So my code on click is now

Private Sub Title_Click()
DoCmd.OpenForm "frmResourcesbyStep", acNormal, , "[Title] = ' " & Me.Title & " ' "
End Sub

This brings up the form but takes me to a blank record and not the specific record I clicked on? I can't figure what I am doing wrong here.

Any ideas?

 

[DavidAtWork]

would need to debug this by putting a break on the line "Docmd.OpenForm... etc to see if there is a value in Me.Title
BTW when you open "frmResourcesbyStep" normally, does it show all the records or does it take you to a new blank record? it may be that the DataEntry property is set to True
David

 

[janeyg]

Thanks for your reply - I will check the property