Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16

    Set Public Variable from Form Input and use in Access Query?

    Ok, so I've got a situation where there are maybe 6 to 8 queries that have to be manually adjusted in SQL each time the workflow is run, entering new names for two or three different input and output tables; ends up about 20 manual edits.



    I was wondering if there was a way that I could add three text boxes and perhaps an "Apply button" on a Form, which would update variables for the three different table names. Then, from within the queries (some are Action Queries) I could place the variable in SQL instead of the table name and never have to manually change the queries again, just type the new values into the text boxes, hit the apply button, and run the queries.

    I've read something about not being able to directly call variables from within SQL, but that if you build a function that defines the variable you can call the function. A bit over my head at this point, but I thought I'd mention it.

    Is this doable in Access or am I looking for something that Access can't do?

    Thanks for any replies and info,

    Joe

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To my knowledge you can't have a dynamic table name in SQL (frankly wanting to implies a design problem). You'd have to use a QueryDef to manipulate the SQL. The technique you mention is used for the criteria, not the source table. More detail about your situation might lead to suggestions of a better way to accomplish it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you tell us more info about your workflow - especially about the issue(s) that require these manual changes?

  4. #4
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    This is a continuing project, as a friend has inherited this Access 'program' (BTW, what do you call the collection of all tables, queries, forms, reports...etc?... as a collective?) and I told her I'd see what I could do to make it easier to work with.

    Every time her customer sends in new data (in Excel) she has to rename a bunch of columns (to match what was originally in the Access program), change data types, do some text-to-column operations, and add a couple of columns BEFORE she can then import it into Access. I should mention that this was created and still running on Access 2000, though I'm doing all the work in 2016 and they are going to update to a modern Access when I'm done.

    So... all of that manual stuff in Excel I've got down to just un-hiding all columns and changing one column to numeric (didn't seem to be a quick way to do that in Access thru VBA), everything else I've gotten done in VBA.

    Once she imports the data, she saves it to a new table named CB-MMMYYmf (month and year), and runs the VBA from a button on a form to make all the other changes needed to the data. This table is then called by other queries that create other tables, like CB-MMMYYml and MMMYYImprint. These files in turn are referenced to make some tables that overwrite others of the same name (no problem there) or do data validation (like checking this month's data against what should be the same in last month's data.

    But what she has to do is to open each of these queries in SQL view and then manually change the name of the table used as input and/or the table being created. At first I thought that it was a stupid setup, and that I should just name everything with generic names and overwrite the previous month's data, but then I realized that a couple of queries access stuff from the previous month, and that it might be nice in general to have a few months (at least) of historical data.

    I could reinvent the wheel entirely, but I'm just doing this as a favor... and I'm also doing this as an opportunity to learn some stuff, but don't have the free time to completely redo the entire thing; I'm too slow!

    Here are some examples (I put in hard returns to make it easier to read on the forum):

    Code:
    SELECT MONTHLYIMPORT.UID, MONTHLYIMPORT.Imprintcode, MONTHLYIMPORT.Postalcode, 
    MONTHLYIMPORT.Subno, MONTHLYIMPORT.Field3, MONTHLYIMPORT.Field4, MONTHLYIMPORT.Field5, 
    MONTHLYIMPORT.[End Date], MONTHLYIMPORT.Qty, MONTHLYIMPORT.[First Name], MONTHLYIMPORT.[Middle Name],
    MONTHLYIMPORT.[Last Name], MONTHLYIMPORT.Companyname, MONTHLYIMPORT.Address1,
    MONTHLYIMPORT.Address2, MONTHLYIMPORT.Address3, MONTHLYIMPORT.Address4, MONTHLYIMPORT.City,
    MONTHLYIMPORT.State, MONTHLYIMPORT.Country, MONTHLYIMPORT.[Country Code], MONTHLYIMPORT.[Email Address],
    MONTHLYIMPORT.[Phone Number], MONTHLYIMPORT.County, MONTHLYIMPORT.Ite, MONTHLYIMPORT.Number,
    MONTHLYIMPORT.[Person Status], MONTHLYIMPORT.[Order Date], MONTHLYIMPORT.[Order Amount],
    MONTHLYIMPORT.[Order Number], MONTHLYIMPORT.PriceList, MONTHLYIMPORT.PMApma_type, MONTHLYIMPORT.pma_org_name,
    MONTHLYIMPORT.pma_address1, MONTHLYIMPORT.pma_address2, MONTHLYIMPORT.pma_address3, MONTHLYIMPORT.pma_address4,
    MONTHLYIMPORT.pma_city, MONTHLYIMPORT.pma_state, MONTHLYIMPORT.pma_postal_code, MONTHLYIMPORT.pma_country,
    MONTHLYIMPORT.Field42, MONTHLYIMPORT.[Date List Pulled] INTO july18mlFROM CBjuly18mf AS MONTHLYIMPORT
    WHERE (((MONTHLYIMPORT.Imprintcode)="LF" Or (MONTHLYIMPORT.Imprintcode)="FI"));
    Code:
    SELECT uidsearch.UID, uidsearch.subno, uidsearch.Field3, uidsearch.Field4, uidsearch.Field5, uidsearch.[End Date], uidsearch.Qty,
    uidsearch.[First Name], uidsearch.[Middle Name], uidsearch.[Last Name], uidsearch.COMPANYNAME, uidsearch.Address1, uidsearch.Address2,
    uidsearch.Address3, uidsearch.Address4, uidsearch.City, uidsearch.State, uidsearch.PostalCode, uidsearch.Country, uidsearch.[Country Code],
    uidsearch.IMPRINTCODE, uidsearch.Number, uidsearch.[Person Status], uidsearch.[Order Date], uidsearch.[Order Amount],
    uidsearch.[Order Number], uidsearch.PriceList, uidsearch.PMApma_type, uidsearch.pma_org_name, uidsearch.pma_address1,
    uidsearch.pma_address2, uidsearch.pma_address3, uidsearch.pma_address4, uidsearch.pma_city, uidsearch.pma_state,
    uidsearch.pma_postal_code, uidsearch.pma_country, uidsearch.Field42
    FROM july18ml AS uidsearch LEFT JOIN IMPRINT ON uidsearch.UID=[IMPRINT].[uid]
    WHERE ((([IMPRINT].[uid]) Is Null));
    Code:
    SELECT MailCurrent.Qty, [IMPRINT].[address8], [IMPRINT].[olduid], MailCurrent.UID, MailCurrent.imprintcode, [IMPRINT].[Field15],
    [IMPRINT].[Qty], [IMPRINT].[UID], [IMPRINT].[MailCurrent_subno], [IMPRINT].[Subscribers_subno], [IMPRINT].[littlepic], [IMPRINT].[address1],
    [IMPRINT].[address2], [IMPRINT].[address3], [IMPRINT].[address4], [IMPRINT].[address5], [IMPRINT].[address6], [IMPRINT].[address7]
    INTO julyImprint FROM july18ml AS MailCurrent, IMPRINT
    WHERE ((MailCurrent.UID)=[IMPRINT].[UID]) Or ((MailCurrent.UID)=[IMPRINT].[OLDUID]);
    Code:
    SELECT monthimprint.MailCurrent_UID, monthimprint.imprintcode, monthimprint.Field15, monthimprint.IMPRINT_Qty,
    monthimprint.IMPRINT_UID, monthimprint.littlepic, monthimprint.address1, monthimprint.address2, monthimprint.address3,
    monthimprint.address4, monthimprint.address5, monthimprint.address6, monthimprint.address7
    FROM julyimprint AS monthimprint
    WHERE (((monthimprint.imprintcode)<>[Field15]));


    Thanks for any ideas,

    Joe

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Create a new code module, basGlobals.
    In it define your global variables like:
    Code:
    Option Compare Database
    Option Explicit
    
    Global glMonthlyImportINTO As String
    Global glMonthlyImportFROM as string
    Global gluidSearch As String
    Global glImprint As String
    Global glMonthlyImprint as string
    ... and so on
    Then create a form with unbound textboxes to set the values of these variables.

    Then your queries can use the variables...
    BUT NOT NAMED QUERIES.
    You'll have to build and execute the SQL in code like this:

    Code:
    Dim sSQL as string
    sSQL = " _
    & "SELECT MONTHLYIMPORT.UID, MONTHLYIMPORT.Imprintcode, MONTHLYIMPORT.Postalcode, " _
    & "MONTHLYIMPORT.Subno, MONTHLYIMPORT.Field3, MONTHLYIMPORT.Field4, MONTHLYIMPORT.Field5, " _ 
    & "MONTHLYIMPORT.[End Date], MONTHLYIMPORT.Qty, MONTHLYIMPORT.[First Name], MONTHLYIMPORT.[Middle Name], " _
    & "MONTHLYIMPORT.[Last Name], MONTHLYIMPORT.Companyname, MONTHLYIMPORT.Address1, " _
    & "MONTHLYIMPORT.Address2, MONTHLYIMPORT.Address3, MONTHLYIMPORT.Address4, MONTHLYIMPORT.City, " _
    & "MONTHLYIMPORT.State, MONTHLYIMPORT.Country, MONTHLYIMPORT.[Country Code], MONTHLYIMPORT.[Email Address], " _
    & "MONTHLYIMPORT.[Phone Number], MONTHLYIMPORT.County, MONTHLYIMPORT.Ite, MONTHLYIMPORT.Number, " _
    & "MONTHLYIMPORT.[Person Status], MONTHLYIMPORT.[Order Date], MONTHLYIMPORT.[Order Amount], " _
    & "MONTHLYIMPORT.[Order Number], MONTHLYIMPORT.PriceList, MONTHLYIMPORT.PMApma_type, MONTHLYIMPORT.pma_org_name, " _
    & "MONTHLYIMPORT.pma_address1, MONTHLYIMPORT.pma_address2, MONTHLYIMPORT.pma_address3, MONTHLYIMPORT.pma_address4, " _
    & "MONTHLYIMPORT.pma_city, MONTHLYIMPORT.pma_state, MONTHLYIMPORT.pma_postal_code, MONTHLYIMPORT.pma_country, " _
    & "MONTHLYIMPORT.Field42, MONTHLYIMPORT.[Date List Pulled] INTO " & glMonthlyImportINTO & " FROM " & glMonthlyImportFROM & " AS MONTHLYIMPORT " _
    & "WHERE (((MONTHLYIMPORT.Imprintcode)= ""LF"" Or (MONTHLYIMPORT.Imprintcode)= ""FI""));"
    CurrentDB.Execute sSQL, dbFailOnError
    Last edited by davegri; 07-12-2018 at 07:58 AM. Reason: format/ adjust for literal strings

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you should import the data to one table with a date field indicating the year and month it was imported, then you don't need to do anything except query the correct period.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Thank you for the response.

    Then create a form with unbound textboxes to set the values of these variables.
    How does one link the text boxes to the variables? Never done that before.

    Joe

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    How does one link the text boxes to the variables? Never done that before.
    Use the after_update of the textboxes to set the value:
    Code:
    sub txtBox1_afterUpdate()
        glMonthlyImportINTO = txtBox1
    end sub

  9. #9
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    AWESOME Dave! Thank you so much. Ok, now to try to put it all together and see what I can achieve.

    Thanks everyone for the the continued help. It's so hard trying to do things while you teach yourself, because you run into all sorts of roadblocks or cases of knowing what you want to do, but no idea of how to actually do it. These forums are a HUGE help... thanks for being around. If I ever become proficient, I'll try to do my part to help others on here.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    OK, good luck. I think your user will like filling out the form much better than modifying the queries.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    Thank you all again!

  13. #13
    Netopia is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    16
    I just had a thought. All of these could just use the month/yr as a varaible and have in SQL something like:

    Code:
    "CB & glMonYear & ml " FROM "CB & glMonYear & mf"
    And in the module:

    Code:
    Global glMailList As String
    Would that work? If so, then there would only be a singe textbox to enter the new data in?

    I'm not sure about the syntax of how I wrote that statement either, is that correct?

    One other thing, can a global variable be called into a report?

    Joe

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't the variables contain table names? Just make sure you have all the table names needed in separate variables. Much easier to do and debug setting them up in the form instead of in the sql syntax.

    And yes a global variable can be used in a report.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by davegri View Post
    And yes a global variable can be used in a report.
    Directly (outside VBA)? I think a function would need to be called to use in a textbox or something.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-05-2016, 02:36 PM
  2. Displaying a Public Variable on a form
    By swenger in forum Programming
    Replies: 1
    Last Post: 06-23-2016, 01:56 PM
  3. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  4. Replies: 1
    Last Post: 03-28-2013, 07:54 AM
  5. Replies: 5
    Last Post: 05-18-2012, 07:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums