By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Access 2003 - opening a form to show specific record

P: n/a
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

Jul 5 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----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
Jul 5 '06 #2

P: n/a
"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
Jul 5 '06 #3

P: n/a
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?

Jul 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.