a problem opening a form with a specific record being displayed!

[martin.ryan]

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.


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

Yours truly,
Martin R

 

[Marshall Barton]

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.

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

The WhereCondition argument needs to use the name
of the field in the spares table on the left side of the
comparison and the value you want to filter by on the right
side

You have completely lost me as to what is named what. Is
Number the name of a text box control on the form or is it
the name of a field in a table? For the moment, I will
assume that Number is the name of the text box on the form
that is trying to open the spares form. I think Index is
the name of a field in the spares table. If those are valid
assumptions, then the code would be:
stLinkCriteria = "Index = " & Me.Number

In situations like this, it is important to make a clear
distinction between fields in a table/query and controls on
a form.

Get rid of the SetFocus. It's redundant because OpenForm
takes care of that for you.

 

[MartinR]

the name of the field in the table spares is called "No". the name of
the control text box on the sub form is "index" and the name of the
control text box on the sparesform is called "Number".

This above still does not open the correct record. Should i just use
the where condition criteria or should i add in a filter condition also
to the DoCmd. expression.

 

[Rick Brandt]

the name of the field in the table spares is called "No". the name of
the control text box on the sub form is "index" and the name of the
control text box on the sparesform is called "Number".

This above still does not open the correct record. Should i just use
the where condition criteria or should i add in a filter condition also
to the DoCmd. expression.

The where clause has to reference a field name in the RecordSource of the form
you are opening, not the name of a control on the form. Try...

Private Sub index_DblClick(Cancel As Integer)

Dim stDocName As String, stLinkCriteria As String

stDocName = "Sparesform"
stLinkCriteria = "[No]= " & Me![index]

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

One of the options from the command button wizard would write this code for you.
Then you could copy it to your double-click event.

 

[MartinR]

You just solved it for me!
I copied that code in and it worked!!!
Thats great, it works perfect.
I have spent so long on this i am really happy now.

thank you so much