Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

problem displaying specific record

Discussion in 'Information Technology' started by MartinR, Jul 28, 2006.

  1. MartinR

    MartinR
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    MartinR wrote:
    > 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


    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]

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. MartinR

    MartinR
    Expand Collapse
    Guest

    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?
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    MartinR wrote:
    > 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?


    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?

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  6. MartinR

    MartinR
    Expand Collapse
    Guest

    When i click the filter by selection button and then go into design
    view, where do i view the code behind this??
     
  7. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    MartinR wrote:
    > When i click the filter by selection button and then go into design
    > view, where do i view the code behind this??


    Just look at the Filter property of the form on the {Data} tab of the
    property sheet.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  8. MartinR

    MartinR
    Expand Collapse
    Guest

    Opposite filter lookup on the data tab it says Database Default. Is
    this the setting i require?
     
  9. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    "MartinR" <martin.ryan@esbi.ie> wrote in message
    news:1152172057.102110.262860@m79g2000cwm.googlegroups.com...
    >
    > Opposite filter lookup on the data tab it says Database Default. Is
    > this the setting i require?


    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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  10. MartinR

    MartinR
    Expand Collapse
    Guest

    This is what it says in the filter part under the data tab.
    ((Spares.[No]=651))

    What way should i now change my code?
     
  11. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

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


    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

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  12. MartinR

    MartinR
    Expand Collapse
    Guest

    that worked perfect. I see my mistake now.
    again thank you.
     

Share This Page