problem displaying specific record

  • Thread starter Thread starter MartinR
  • Start date Start date
M

MartinR

Hi,
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
 
MartinR said:
Hi,
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
Click to expand...

Your where clause does not need to refer to the form like that (the form
knows who it is). Just use...

stLinkCriteria = "[Number] = " & Me![index]
 
cheers for the advice but when i changed that, a message box comes up
prompting me to enter a number, when i put in a number it still does
not open the record with that index number but instead it opened the
first record! i dont want to have to enter in the number if i am
clicking on the one that i want to go to. Do you think my code is wrong?
 
MartinR said:
cheers for the advice but when i changed that, a message box comes up
prompting me to enter a number, when i put in a number it still does
not open the record with that index number but instead it opened the
first record! i dont want to have to enter in the number if i am
clicking on the one that i want to go to. Do you think my code is
wrong?
Click to expand...

Yep. When you are prompted for a field it usually means the field does not
exist or that you spelled it wrong.

Open the form normally to record one (no filter). Click into the [Number]
field and press the "Filter By Selection" button in the toolbar. That will
apply a filter that specifies the record that you are currently sitting on.
Now switch to design view and examine the filter string that was created by
what you just did. That is what the WHERE clause you are sending should
look like. Does it?
 
When i click the filter by selection button and then go into design
view, where do i view the code behind this??
 
MartinR said:
When i click the filter by selection button and then go into design
view, where do i view the code behind this??
Click to expand...

Just look at the Filter property of the form on the {Data} tab of the
property sheet.
 
Opposite filter lookup on the data tab it says Database Default. Is
this the setting i require?
 
MartinR said:
Opposite filter lookup on the data tab it says Database Default. Is
this the setting i require?
Click to expand...

No. Make sure the property sheet is showing properties for the form (not an
object on the form) by clicking on the small gray square in the upper left
corner of the form in design view. On the {Data} tab the second item listed
should be the Filter property.
 
This is what it says in the filter part under the data tab.
((Spares.[No]=651))

What way should i now change my code?
 
MartinR said:
This is what it says in the filter part under the data tab.
((Spares.[No]=651))

What way should i now change my code?
Click to expand...

That suggests that your RecordSource for the form has two fields both named
[No] so Access is appending the TableName-Dot in front of them to
distinguish. You either need to eliminate one of those from the query or
modify your code so it also includes the TableName-Dot notation...

Private Sub index_DblClick(Cancel As Integer)

Dim stDocName As String, stLinkCriteria As String

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

DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
 
Back
Top