Results 1 to 3 of 3
  1. #1
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31

    Dynamically creating form fields?

    I have vb script that builds a query with a sql stmt, the first field is the current month.

    msqlbldREF = <A sql stmt that defines the query fields>
    Set qdf = CurrentDb.CreateQueryDef("Where Used", msqlbldREQ)
    DoCmd.OpenQuery qdf.Name


    But now I need to make this into a form, because I want to highlight fields < 0.
    So the query is built like:
    User Input month/year: 09/2020
    Item 09/2020 10/2020 11/2020 12/2020
    XX-1 10 50 30 15

    But the form, that I created a few months ago with the form builder:

    Item 07/2020 08/2020 09/2020 10/2020 11/2020 12/2020
    XX-1 Name? Name? 10 50 30 15

    So Ideally, I would like to programattically build the form so that it starts at the selected month.

    Any Ideas?



    Thanks!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    What is the design of the table for the query. What fields does it have.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    markcrobinson is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    New Hampshire, US
    Posts
    31
    SELECT MARSReport.itemId, MARSReport.use, Entry.[Item Description], Entry.category, MARSReport.qStk AS [Current STock],
    '|' AS z, MARSReport.B092020, MARSReport.R092020, MARSReport.U092020,
    '|' AS 1, MARSReport.B102020, MARSReport.R102020, MARSReport.U102020,
    '|' AS 2, MARSReport.B112020, MARSReport.R112020, MARSReport.U112020,
    '|' AS 3, MARSReport.B122020, MARSReport.R122020, MARSReport.U122020,
    '|' AS 4, MARSReport.B012021, MARSReport.R012021, MARSReport.U012021, ....
    FROM (MARSReport LEFT JOIN EntryItems ON MARSReport.itemId = EntryItems.Itemid) LEFT JOIN Entry ON EntryItems.OItemid = Entry.Item;

    The problem is that in October,
    MARSReport.B092020, MARSReport.R092020, MARSReport.U092020,
    will no longer exist.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamically Centering Form Fields
    By DarthBrute in forum Forms
    Replies: 8
    Last Post: 07-06-2019, 05:16 PM
  2. Replies: 2
    Last Post: 12-26-2013, 02:50 PM
  3. Replies: 1
    Last Post: 01-31-2013, 02:58 PM
  4. Replies: 2
    Last Post: 06-01-2011, 09:32 AM
  5. Replies: 1
    Last Post: 08-13-2010, 01:06 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