Welcome to SPN

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

Sign Up Now!

multiple link criteria in form

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

  1. BLTibbs

    BLTibbs
    Expand Collapse
    Guest

    What is wrong with this code below? It works with either the category field
    or the subcategory field but not with them both. What I am trying to do is
    open GroupDetailsSubFrm and only show the records in that query for the
    category and the subcategory on the original form... What am I doing wrong?

    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "GroupDetailsSubFrm"
    stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    Me![SubCategory]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit_Command19_Click:
    Exit Sub
    Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click
    End Sub
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    The And should be inside the quote

    Yours
    stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    Me![SubCategory]

    Should be
    stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
    Me![SubCategory]

    --
    Good Luck
    BS"D


    "BLTibbs" wrote:

    > What is wrong with this code below? It works with either the category field
    > or the subcategory field but not with them both. What I am trying to do is
    > open GroupDetailsSubFrm and only show the records in that query for the
    > category and the subcategory on the original form... What am I doing wrong?
    >
    > Private Sub Command19_Click()
    > On Error GoTo Err_Command19_Click
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    > stDocName = "GroupDetailsSubFrm"
    > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > Me![SubCategory]
    > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > Exit_Command19_Click:
    > Exit Sub
    > Err_Command19_Click:
    > MsgBox Err.Description
    > Resume Exit_Command19_Click
    > End Sub
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    The "And" needs to be part of the SQL string and therefore inside the
    quotes:

    stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]="
    & Me![SubCategory]
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    "BLTibbs" <BLTibbs@discussions.microsoft.com> wrote in message
    news:6808E616-12AF-4965-BED1-4B8A33EE502E@microsoft.com...
    > What is wrong with this code below? It works with either the category
    > field
    > or the subcategory field but not with them both. What I am trying to do
    > is
    > open GroupDetailsSubFrm and only show the records in that query for the
    > category and the subcategory on the original form... What am I doing
    > wrong?
    >
    > Private Sub Command19_Click()
    > On Error GoTo Err_Command19_Click
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    > stDocName = "GroupDetailsSubFrm"
    > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > Me![SubCategory]
    > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > Exit_Command19_Click:
    > Exit Sub
    > Err_Command19_Click:
    > MsgBox Err.Description
    > Resume Exit_Command19_Click
    > End Sub
     
  5. Cilla

    Cilla
    Expand Collapse
    Guest

    BLTibbs wrote:
    > What is wrong with this code below? It works with either the category field
    > or the subcategory field but not with them both. What I am trying to do is
    > open GroupDetailsSubFrm and only show the records in that query for the
    > category and the subcategory on the original form... What am I doing wrong?
    >
    > Private Sub Command19_Click()
    > On Error GoTo Err_Command19_Click
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    > stDocName = "GroupDetailsSubFrm"
    > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > Me![SubCategory]
    > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > Exit_Command19_Click:
    > Exit Sub
    > Err_Command19_Click:
    > MsgBox Err.Description
    > Resume Exit_Command19_Click
    > End Sub


    Not sure what your working from and too.

    Are you opening one form from another form?

    Is "Me" Form 1 and Category field 1 and subCategory field 2 on the Me
    form?

    Is the GroupDetailsSubFrm on a different form that the "Me" form is to
    open?

    Is both Category and SubCategory fields on the GroupDetailsSubFrm as
    seperate Fields?

    If all these are true then I would do it a little differently:

    Dim xxx As String
    Dim zzz As String
    xxx = [Text0]
    zzz = [Combo3]
    DoCmd.OpenForm "GroupDetailsSubFrm"
    DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
    DoCmd.ShowAllRecords
    Dim SrcSQL As String
    SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
    WHERE
    (((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
    ((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
    DoCmd.ApplyFilter SrcSQL
    Exit Sub

    Note: Replace GroupDetailSubFrmsTbl with the table name of the
    GroupDetailSubFrm thorugh the sql string

    Replace the MeFromName with the Form name for "Me" in your above code.
     
  6. Cilla

    Cilla
    Expand Collapse
    Guest

    Cilla wrote:
    > BLTibbs wrote:
    > > What is wrong with this code below? It works with either the category field
    > > or the subcategory field but not with them both. What I am trying to do is
    > > open GroupDetailsSubFrm and only show the records in that query for the
    > > category and the subcategory on the original form... What am I doing wrong?
    > >
    > > Private Sub Command19_Click()
    > > On Error GoTo Err_Command19_Click
    > > Dim stDocName As String
    > > Dim stLinkCriteria As String
    > > stDocName = "GroupDetailsSubFrm"
    > > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > > Me![SubCategory]
    > > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > > Exit_Command19_Click:
    > > Exit Sub
    > > Err_Command19_Click:
    > > MsgBox Err.Description
    > > Resume Exit_Command19_Click
    > > End Sub

    >
    > Not sure what your working from and too.
    >
    > Are you opening one form from another form?
    >
    > Is "Me" Form 1 and Category field 1 and subCategory field 2 on the Me
    > form?
    >
    > Is the GroupDetailsSubFrm on a different form that the "Me" form is to
    > open?
    >
    > Is both Category and SubCategory fields on the GroupDetailsSubFrm as
    > seperate Fields?
    >
    > If all these are true then I would do it a little differently:
    >
    > Dim xxx As String
    > Dim zzz As String
    > xxx = [Text0]
    > zzz = [Combo3]
    > DoCmd.OpenForm "GroupDetailsSubFrm"
    > DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
    > DoCmd.ShowAllRecords
    > Dim SrcSQL As String
    > SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
    > WHERE
    > (((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
    > ((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
    > DoCmd.ApplyFilter SrcSQL
    > Exit Sub
    >
    > Note: Replace GroupDetailSubFrmsTbl with the table name of the
    > GroupDetailSubFrm thorugh the sql string
    >
    > Replace the MeFromName with the Form name for "Me" in your above code.


    Opps: Didn't replace my text0 and combo 3. Try this one

    Dim xxx As String
    Dim zzz As String
    xxx = [Category]
    zzz = [SubCategory]
    DoCmd.OpenForm "GroupDetailsSubFrm"
    DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
    DoCmd.ShowAllRecords
    Dim SrcSQL As String
    SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
    WHERE
    (((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
    ((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
    DoCmd.ApplyFilter SrcSQL
    Exit Sub

    Note: Replace GroupDetailSubFrmsTbl with the table name of the
    GroupDetailSubFrm thorugh the sql string

    Replace the MeFromName with the Form name for "Me" in your above code.
     
  7. BLTibbs

    BLTibbs
    Expand Collapse
    Guest

    You were right on the form operation! Thanks. I tried your same format for
    a dlookup in a field control dealing with the same fields, but it gives me
    back a result of 0.00 every time regardless of what is should come out to. Do
    I have something wrong in the following dlookup string?

    =DLookUp("Ingresos","InQry","[category]= " &
    [Forms]![GroupsubCategorySubFrm]![category] And "[subcategory]= " &
    [Forms]![GroupsubCategorySubFrm]![subcategory])

    I tried putting the and inside the quotes like you said for the form, but it
    tells me that is invalid syntax and won't allow it. What now?

    Brian

    "Ofer Cohen" wrote:

    > The And should be inside the quote
    >
    > Yours
    > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > Me![SubCategory]
    >
    > Should be
    > stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
    > Me![SubCategory]
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "BLTibbs" wrote:
    >
    > > What is wrong with this code below? It works with either the category field
    > > or the subcategory field but not with them both. What I am trying to do is
    > > open GroupDetailsSubFrm and only show the records in that query for the
    > > category and the subcategory on the original form... What am I doing wrong?
    > >
    > > Private Sub Command19_Click()
    > > On Error GoTo Err_Command19_Click
    > > Dim stDocName As String
    > > Dim stLinkCriteria As String
    > > stDocName = "GroupDetailsSubFrm"
    > > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > > Me![SubCategory]
    > > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > > Exit_Command19_Click:
    > > Exit Sub
    > > Err_Command19_Click:
    > > MsgBox Err.Description
    > > Resume Exit_Command19_Click
    > > End Sub
     
  8. BLTibbs

    BLTibbs
    Expand Collapse
    Guest

    Scratch that last post - I found the obvious error - thanks again!

    "Ofer Cohen" wrote:

    > The And should be inside the quote
    >
    > Yours
    > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > Me![SubCategory]
    >
    > Should be
    > stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
    > Me![SubCategory]
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "BLTibbs" wrote:
    >
    > > What is wrong with this code below? It works with either the category field
    > > or the subcategory field but not with them both. What I am trying to do is
    > > open GroupDetailsSubFrm and only show the records in that query for the
    > > category and the subcategory on the original form... What am I doing wrong?
    > >
    > > Private Sub Command19_Click()
    > > On Error GoTo Err_Command19_Click
    > > Dim stDocName As String
    > > Dim stLinkCriteria As String
    > > stDocName = "GroupDetailsSubFrm"
    > > stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
    > > Me![SubCategory]
    > > DoCmd.OpenForm stDocName, , , stLinkCriteria
    > > Exit_Command19_Click:
    > > Exit Sub
    > > Err_Command19_Click:
    > > MsgBox Err.Description
    > > Resume Exit_Command19_Click
    > > End Sub
     

Share This Page