Access 2003 - opening a form to show specific record

[MartinR]

Hi,
I'm still new to writing code in vba as I've only been introduced to
access three weeks ago. I have written this code below and it executes
but does not do what I want it to do. What I want is the form
"Sparesform" to open when I double click on the "index" field for the
record I wish to view. The index field is part of a subform. What is
happening at the moment is the form "Sparesform" is opening but does
not go to the correct record but instead goes to, what appears to be a
new record. My where condition of the code appears to be assigning the
correct index number to "forms![sparesform]![number]" but this does not
seem to be carried out when the sparesform is opened.

Index is a numeric data type (ie its an auto number)
Can you shine some light on my problem.
Hereis my code:

Private Sub index_DblClick(Cancel As Integer)

Dim stDocName As String, stLinkCriteria As String

stDocName = "Sparesform"
stLinkCriteria = "forms![sparesform]![Number] = " & Me![index]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![sparesform]![Condition].SetFocus

End Sub

Please help

Martin R

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the Sparesform's Open event (or the Load event) you have to set the
Filter or the RecordSource to the index number you have passed in the
OpenArgs parameter of the OpenForm method. E.g.:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "Index = " & Me.OpenArgs
Me.FilterOn = True
Else
MsgBox "No Index Passed", vbCritical
Cancel = True
End If

End Sub

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKwaLYechKqOuFEgEQL0vwCcD4KcZtS1OGsAbNNhoKcLaL 17L10AnjlE
zJgWQbdJ9pzjz+iUBxCvS+Ez
=0nqk
-----END PGP SIGNATURE-----
MartinR wrote:

Hi,
I'm still new to writing code in vba as I've only been introduced to
access three weeks ago. I have written this code below and it executes
but does not do what I want it to do. What I want is the form
"Sparesform" to open when I double click on the "index" field for the
record I wish to view. The index field is part of a subform. What is
happening at the moment is the form "Sparesform" is opening but does
not go to the correct record but instead goes to, what appears to be a
new record. My where condition of the code appears to be assigning the
correct index number to "forms![sparesform]![number]" but this does not
seem to be carried out when the sparesform is opened.

Index is a numeric data type (ie its an auto number)
Can you shine some light on my problem.
Hereis my code:

Private Sub index_DblClick(Cancel As Integer)

Dim stDocName As String, stLinkCriteria As String

stDocName = "Sparesform"
stLinkCriteria = "forms![sparesform]![Number] = " & Me![index]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![sparesform]![Condition].SetFocus

End Sub

[Larry Linson]

"MartinR" wrote

I'm still new to writing code in vba as I've only been introduced to
access three weeks ago. I have written this code below and it executes
but does not do what I want it to do. What I want is the form
"Sparesform" to open when I double click on the "index" field for the
record I wish to view. The index field is part of a subform. What is
happening at the moment is the form "Sparesform" is opening but does
not go to the correct record but instead goes to, what appears to be a
new record. My where condition of the code appears to be assigning the
correct index number to "forms![sparesform]![number]" but this does not
seem to be carried out when the sparesform is opened.

Index is a numeric data type (ie its an auto number)
Can you shine some light on my problem.
Hereis my code:

Private Sub index_DblClick(Cancel As Integer)

Dim stDocName As String, stLinkCriteria As String

stDocName = "Sparesform"
stLinkCriteria = "forms![sparesform]![Number] = " & Me![index]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![sparesform]![Condition].SetFocus

End Sub

Your stLinkCriteria is in error... you appear to be trying to set a Control
on "sparesform" to the value of the Index, but you need to set a criteria
for the pertinent Field in the underlying RecordSource of that Form. If the
Field is named "Number", then it should read:

stLinkCriteria = "[Number] = " & Me![index]

If the Field and the TextBox Control in which it is displayed have the same
name, you may need to rename the TextBox something like txtNumber to prevent
confusion. The WhereCondition only provides a Filter for the SQL or Query or
Table that is the RecordSource of a Form... you can't use it to set a value
into a Control.

And, as a matter of fact, the Forms Collection only includes _open_ forms,
so I think you should be getting an error on that DoCmd.OpenForm. And, by
the way, you should not need to SetFocus, just make sure that Condition is
the first Control in the Tab Order (with the Form open in Design View), on
the menu, View | Tab Order and follow the dialog box.

Larry Linson
Microsoft Access MVP

[MartinR]

I'm sorry but i couldn't follow what i was suppose to do. I put your
code into the database but it didn't seem to have any effect. is there
an easier way of writing code that opens a form and displays the same
record that i clicked on in the sub form?