Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    stLink Criteria (A2k2)

    Good Morning everyone,

    I'm using the following code to open a form where MaxMCNo, SSN, and ReviewTypes in the recordsource of the opening form are equal to controls on the existing form. When the MaxMCNo is clicked the code runs and opens a form to the respective record.

    <hr> stLinkCriteria = "[MaxMCNo]='" & Me![txtMaxMCNo] & "' And [SSN]='" & Me![txtSSN] & "' And [ReviewType] = " & Me![txtReviewType]<hr>

    However, I want to add another criteria to this code "EpisodeNo" which is numeric, so I changed the code to:
    <hr>stLinkCriteria = "[MaxMCNo]='" & Me![txtMaxMCNo] & "' And [SSN]='" & Me![txtSSN] & "' And [ReviewType] = " & Me![txtReviewType] & "' And [EpisodeNo] ='" & [iEpisodeNo] & "'"<hr>

    but I get the attached error msg. All the values that are noted in the msg. are the correct values for the associated record.

    What have I done wrong with the code.

    Thanks in advance,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stLink Criteria (A2k2)

    There is an apostrophe missing after [ReviewType] = and if EpisodeNo is a number field, you shouldn't put single quotes around the value:
    <code>
    stLinkCriteria = "[MaxMCNo]='" & Me![txtMaxMCNo] & "' And [SSN]='" & Me![txtSSN] & "' And [ReviewType] = '" & Me![txtReviewType] & "' And [EpisodeNo] =" & [iEpisodeNo]</code>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stLink Criteria (A2k2)

    Thanks Hans,

    I now get a run time error 2501 -- The Open Form was canceled ....and when I select "Debug" the following line is highlighted:
    DoCmd.OpenForm strCM, , acNormal, stLinkCriteria
    If I highlight the code above (the one you suggested), all fields are the correct values...so why don't the form open?

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: stLink Criteria (A2k2)

    Hans has told you the answer, but the way to find these things for yourself is to look at the value inStLinkCriteria after all the values are put into your expression.

    You can do that by putting in

    msgbox stLinklCriteria

    (or debug.print stLinklCriteria)

    immediately after the line that sets it up, but before anything is done with it. You will see little mistakes there, that you can't see when you write out complicated expressions like the one you have written.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: stLink Criteria (A2k2)

    I think you need another comma

    DoCmd.OpenForm strCM, , acNormal, ,stLinkCriteria
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stLink Criteria (A2k2)

    Hans,

    I did the following and all is well....as always....THANKS a million:
    stLinkCriteria = "[MaxMCNo]='" & Me.txtMaxMCNo & "' And [SSN]='" & Me.txtSSN & "' And [ReviewType] = " & Me.txtReviewType & " And [EpisodeNo] = " & iEpisodeNo

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stLink Criteria (A2k2)

    Are the other fields in strLinkCriteria all text fields?

    Oops, your instruction has a comma in the wrong place. It should be

    DoCmd.OpenForm strCM, acNormal, , stLinkCriteria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •