Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA - Passing form controls values to saved query (Access 97)

    I'm trying to set up a form from which to run an update program. I've got the form set up so that the user can select which period (i.e. 2002 02 = Feb 2002) to update.

    The SQL of the saved query that I'd like to use the control values is...

    SELECT [New Current Def Acct Code].[USER ID], Credit.PathwayID, [New Current Def Acct Code].[CARD HOLDER], [New Current Def Acct Code].[GL UNIT], [New Current Def Acct Code].CARD, [New Current Def Acct Code].AMT, [New Current Def Acct Code].[RULE TABLE], [New Current Def Acct Code].[MCC BLOCKING TABLE], [New Current Def Acct Code].[GL INDICATOR], Right([Credit]![StatementDt],4)+" "+Left([Credit]![StatementDt],2) AS StmtDtPeriod INTO 01_Non_Filers_Data
    FROM [New Current Def Acct Code] LEFT JOIN Credit ON [New Current Def Acct Code].[USER ID] = Credit.PathwayID
    WHERE ((([New Current Def Acct Code].[RULE TABLE])<>"TERPEST" Or ([New Current Def Acct Code].[RULE TABLE])<>"0969SRC" Or ([New Current Def Acct Code].[RULE TABLE])<>"SPRTECH") AND (([New Current Def Acct Code].[MCC BLOCKING TABLE])="SEARSX" Or ([New Current Def Acct Code].[MCC BLOCKING TABLE])="760SLS") AND ((Right([Credit]![StatementDt],4)+" "+Left([Credit]![StatementDt],2))=[Forms]![frm_NFTDupdate]![cBox_transYr]));

    I haven't trued to do this before, so I'm not sure what I'm doing wrong: the query refuses to run.

    I've looked in online & Application help, but can't find something that looks like an answer - could be MY inexperience keeping me from seeing the answer...I honestly don't know.

    So, could I get - or be pointed to - a step-by-step for what you have to have to enable separately saved query "A" take in control values from form "B" as criteria.

    Thanks.

    <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA - Passing form controls values to saved query (Access 97)

    Is this an Access form or a VBA UserForm? The syntax at the end of your query looks pretty alien to me, so I'm thinking Access. But you're here in the VBA forum, so maybe you mean UserForm. If it is a UserForm, try using dotted notation such as UserForm.ControlName.Value to retrieve the year or, if you need to examine a number of controls, assign the value to a variable in advance of the SQL statement.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA - Passing form controls values to saved query (Access 97)

    When you say the query does not work, do you mean it does not return any records?
    Tell me, is the field StatementDt a Date or a text field? If it's a text field then I am stumped, however, if it is a date field you cannot use Left and Right functions on it.
    What you would probably do is use the format function in your query,
    eg:
    AND Format(StatementDt,"mm yyyy") = forms![frm_NFTDupdate]![cBox_transYr]

    HTH
    Pat

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Passing form controls values to saved query (Access 97)

    To clarify the questions, thus far:
    (1) It is an Access form, not a user form.
    (2) It isn't even successfully getting to Datasheet view when I test it. I get the error "The text file specification 'New Current Def Acct Code Link Specification' does not exist. You can't import, export, or link using the specification."

    Thanks for the questions thus far and also for the help to come.

    malkor <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Passing form controls values to saved query (Access 97)

    To clarify the questions, thus far:
    (1) It is an Access form, not a user form.
    (2a) It isn't even successfully getting to Datasheet view when I test it. I get the error "The text file specification 'New Current Def Acct Code Link Specification' does not exist. You can't import, export, or link using the specification."
    (2b) it is a text-type field.

    Thanks for the questions thus far and also for the help to come.

    malkor <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA - Passing form controls values to saved query (Access 97)

    Take the bangs (!) out of your references to [Credit] and replace them with dots. You use bangs when referring to controls on a form in a SQL statement, but you use dots when referring to the fields in a table.

    What happens if you paste this into the SQL view of a new query?

    SELECT C.[USER ID], Credit.PathwayID, C.[CARD HOLDER], C.[GL UNIT], C.CARD, C.AMT, C.[RULE TABLE], C.[MCC BLOCKING TABLE], C.[GL INDICATOR], Right([Credit].[StatementDt],4)+" "+Left([Credit].[StatementDt],2) AS StmtDtPeriod INTO 01_Non_Filers_Data
    FROM [New Current Def Acct Code] AS C LEFT JOIN Credit ON C.[USER ID] = Credit.PathwayID
    WHERE (((C.[RULE TABLE])<>"TERPEST" Or (C.[RULE TABLE])<>"0969SRC" Or (C.[RULE TABLE])<>"SPRTECH") AND ((C.[MCC BLOCKING TABLE])="SEARSX" Or (C.[MCC BLOCKING TABLE])="760SLS") AND ((Right([Credit].[StatementDt],4)+" "+Left([Credit].[StatementDt],2))=[Forms]![frm_NFTDupdate]![cBox_transYr]));

    Do you still get the same error? The particular message your seeing suggestes that 'New Current Def Acct Code' is a linked file, and not necessarily an Access table. Is that the case?
    Charlotte

Posting Permissions

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