Page 1 of 5 12345 LastLast
Results 1 to 15 of 63
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Auto-generate queries via VBA (need extended capability)

    Experts, Vlad:



    I previously posted a thread where expert Vlad Cucinschi (aka Gicu) provided a top-notch solution. Please allow me to emphasize that Vlad deserves **all credit** for existing (working) solution.

    At this time, however, I need some additional help w/ modifying the existing (working) solution IOT achieve an extended capability.

    For details on the existing capability though, please see https://www.accessforums.net/showthread.php?t=83606.

    ***************

    Please find attached two (2) DB files. Allow me to first share modifications I have made:

    "Testing v02A" -- this version works fine and includes the following modification:
    1. Added field [AFSAS_MAPPED] in table [01_tbl_ProductTables].
    2. Thus, when executing the command button in form "F01_MainMenu", only the field where [01_tbl_ProductTables].[AFSAS_MAPPED]= TRUE are added to the 2 products tables (tbl_Demographics & tbl_Other).
    3. Again, this version **works fine** and does NOT require any modification whatsover. I merely included this version to demonstrate the working baseline.

    "Testing v02B" -- now, for purposes of this question, I copied the working version "v02A" but version "v02B" includes the following changes:
    a. In table [01_tbl_ProductTables], I added records "PERS_NEW_FIELD1", "PERS_NEW_FIELD2", and "PERS_NEW_FIELD3" linked to products tables "tbl_Demographics" and "tbl_Other", respectively.
    b. Also added new field [DATA_TYPE_NEW_FIELDS] to specify the data type
    c. Now, these three new fields do NOT exist in data source tables [00_tbl_Master_PK] & [00_tbl_Master_String].
    d. Thus, based on c., none of the products tables are created when clicking the command button via the form.

    Here's what I need some help with:
    - How can the VBA be tweaked so that all product tables are generated BUT also include (blank/empty) fields [PERS_NEW_FIELD1] & [PERS_NEW_FIELD2] & [PERS_NEW_FIELD3]?
    - If the latter is possible, I would want to want to insert these 3 fields with the specified **data type ** as listed in [01_tbl_ProductTables].[DATA_TYPE_NEW_FIELDS].

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Here you go, please try it out and let me know. You might also want to add a field to the product table to flag fields to be indexed and\or set as primary keys. This can\should be done in the new sub I added (have a look at https://www.databasejournal.com/feat...On-The-Fly.htm).
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    **AS ALWAYS**, your solution is brilliant and did **EXACTLY** what I was looking for. I'm super-excited!!!

    The moment I ran your file, I realized though that I should have included one additional component. It's totally my fault for not thinking it all the way through.

    That said, if all possible, maybe there's an easy "fix" to include that one "nice-to-have" option (I don't want to call it "requirement").

    Ok, here it is:
    - Per the attachment, I added one additional field [NEW_FIELD_DEFAULT_VALUE]
    - Ideally, I would want, e.g., all 30 example records (tbl_Demographics, tbl_Other) to be populated with such value.
    - Now, per [01_tbl_ProductTables], I have now included six (6) new fields where all new fields (for "Demographics") do have a default value (e.g., "XYZ", "01/01/2021", and "9999999").
    - However, for the last 3 example new fields, I did NOT include a default value as I may NOT always know that value. For such scenario, I want to include NULL as its default. Naturally, if NULL does not have to be specified if not known, the value would be left blank.

    My question:
    Is it easy to plug in those default value (where known e.g., for PERS_NEW_FIELD1, PERS_NEW_FIELD2, & PERS_NEW_FIELD3?

    Thank you in advance,
    Tom
    Attached Thumbnails Attached Thumbnails DefaultValues.JPG  
    Attached Files Attached Files

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Please check this out, should do what you want. It populates the empty (new) records with the default value and sets that as the default value for the field. I also added the indexing option I was mentioning.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- I just got back to this. I'm excited to learn about this capability.

    However, upon downloading the file, I'm running into some VBA errors (see attached).
    Do you also receive them? If so, how can they be fixed?
    Attached Thumbnails Attached Thumbnails Errors.JPG  

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't SET variables to anything unless they are objects. A string is not an object (nor is a date, integer, long etc.).
    If you had declared fld as a Field, that would be different. The message is hiding your code so if that is about defining a table field, then declare it as a field.

    You should look at how to name things. fld certainly suggests the variable is a field but you declared it a string so I'm not sure if your issue is about naming or not.

    http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html
    Last edited by Micron; 05-24-2021 at 06:12 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Micron -- thanks for chiming in. Below is the entire function.

    Code:
    Public Sub fnCreateFields(sTable As String)
    Dim sField As String, sDataType As String
    Dim rs As DAO.Recordset, sProductTable As String, sIndex As String, vDefault
    Dim db As DAO.Database
    
    
    On Error GoTo fnCreateFields_Err
    
    sProductTable = Replace(sTable, "tbl_", "")
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
        sField = rs("AFSAS_FIELDNAME")
        sDataType = rs("NEW_FIELD_DATA_TYPE")
        vDefault = rs("NEW_FIELD_DEFAULT_VALUE")
        CurrentDb.Execute "ALTER TABLE [" & sTable & "] ADD COLUMN [" & sField & "] " & sDataType & ";" 'add field
        CurrentDb.TableDefs.Refresh
        If Not IsNull(vDefault) Then
            Set db = CurrentDb
            Set fld = db.TableDefs(sTable).Fields(sField)
            
            Select Case fld.Type
            Case 10, 12
                'lets set the default value for the field
                fld.DefaultValue = vDefault
                'populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = '" & vDefault & "';"
            Case 1, 3, 4, 5, 6, 7, 11, 16, 19, 20, 21
                'lets set the default value for the field
                fld.DefaultValue = vDefault
                 'populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = " & vDefault
            Case 8, 22, 23
                'lets set the default value for the field
                fld.DefaultValue = "#" & vDefault & "#"
                 'populate existing rows
                CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = #" & vDefault & "#;"
            End Select
            
        
            
        End If
    rs.MoveNext
    Loop
    '      MyText       TEXT(50),
    '      MyMemo       MEMO,
    '      MyByte       BYTE,
    '      MyInteger    INTEGER,
    '      MyLong       LONG,
    '      MyAutoNumber COUNTER,
    '      MySingle     SINGLE,
    '      MyDouble     DOUBLE,
    '      MyCurrency   CURRENCY,
    '      MyReplicaID  GUID,
    '      MyDateTime   DATETIME,
    '      MyYesNo      YESNO,
    '      MyOleObject  LONGBINARY,
    '      MyBinary BINARY(50)
    
    'reset recordset to include all fields
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND PRODUCT_TABLE = '" & _
                sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
    If rs.RecordCount = 0 Then Exit Sub
    Do Until rs.EOF
        sField = rs("AFSAS_FIELDNAME")
        sIndex = Nz(rs("INDEX"), "NO")
        Select Case sIndex
        Case "UNIQUE"
            CurrentDb.Execute "CREATE UNIQUE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
        Case "YES"
            CurrentDb.Execute "CREATE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
        Case "PRIMARY"
            CurrentDb.Execute "CREATE UNIQUE INDEX PK_" & sTable & " ON [" & sTable & "]([" & sField & "])WITH PRIMARY"
        End Select
    rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set fld = Nothing
    Set db = Nothing
    fnCreateFields_Exit:
    Exit Sub
    
    fnCreateFields_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume Next
    End Sub

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So rather than take what I said which basically was 'if it's a field, declare it as a field but if it's a string don't Set it' you just posted your un-altered code? Or am I missing something?

    What you seem to be missing in that code is the declaration you show in post 5. Where is fld declared in that code? I did a ctrl+find to be sure yet could not find it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Micron -- I don't want to speak out of turn. Expert Vlad's solution have always, always been perfect. Not sure as to why it didn't execute on my machine. Looking at the table generated in Vlad's latest ACCDB, it appears it executed for him.

    Again, I will have to deter to Vlad on your question. Thank you!

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All yours Vlad.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi guys,
    Micron, your absolutely right of course, I had declared the fld as DAO.Field but somehow I managed to delete it in the final version I uploaded. And yet that version works for me (it is compiled), I run the code and the fld variable gives me the right type in the immediate window... Very strange. Anyway, here is an updated one that should work.
    Sorry for the mishap!
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    And yet that version works for me (it is compiled)
    If a person was curious they might go through the decompile/compile process because the code you see isn't always the version that's compiled. It might have compiled for you, but not for me:

    Click image for larger version. 

Name:	VladDb.jpg 
Views:	37 
Size:	24.9 KB 
ID:	45301

    EDIT- BTW, I jumped in because I saw that you were off line. Since I thought the answer was obvious (fld was a string) I tried to pitch in but post 7 turned out to be a real a head-scratcher. All of a sudden there was no declaration for fld at all and I didn't realize the code was yours as I really haven't been following this one.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hey no worries, that is why it is a forum, we all cover up for each other .

    Regarding the decompiling, I do that mainly when I get obvious errors\corruption, do you do it with all the forum downloads? Do you have a trick to do it faster? I have a shortcut on my desktop with the decompile flag but I still have to rename the file, move it to that shortcut's location, open it ( and fix compile errors), then copy it back and upload it. The issue with this one today is that I used a laptop with tough screen and I think somehow while I had the VBE window open I must have deleted the fld declaration. And yet it did compile...But maybe because I compiled it just before? I save and compile and save and compile.....

    Anyway, I hope the latest version works for both of you, please let me know otherwise!

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Amazing... I love the integration of the default values into the *products* table. This will most definitely save some time as part of data integration.

    At this time, I have swapped the tables [00_tbl_Master_Pk], [00_tbl_Master_String], and [01_tbl_ProductTables] to test everything with my *actual* data (vs. sample data = age, gender, last name, etc.).

    Test #1 with 67,000+ records in [00_tbl_Master_Pk/String]:
    ================================================== ========
    1. Total # of records in table [01_tbl_ProductTables] = 573
    2. Total # of fields where [FIELDNAME_STANDARDIZED] is null = 417. As you know, these are the fields that exist in target system but for which I don't have values [00_tbl_Master_Pk/String].
    3. For all of the 417 (new) fields the respective data types were added.
    4. When executing the PK or String version, all executes successfully. GREAT!!!


    Test #2 with same/actual data set but now with INDEX information:
    ================================================== ===============
    Now, I wanted to utilize the INDEX field in [01_tbl_ProductTables]. Thus, I made the following modifications:
    - There are 9 product tables.
    - For each of these 9 records, I added "PRIMARY" into field [INDEX].
    - Also, I added "1" into field [NEW_FIELD_DEFAULT_VALUE] where [NEW_FIELD_DATA_TYPE] = DOUBLE
    - Lastly, all of these 9 records are new fields so [FIELDNAME_STANDARDIZED] are blank.

    Ultimately, I double-checked all information (data types, default values, and index) and all information appears to follow the same logic and we utilized in the sample database (with age, last name, etc.)

    Two different type of errors:
    1. When including "1" as the default value... results in error "3022 The changes you requested to the table were not successful..."
    2. When removing the default value... resuls in error "3058 index or primary key cannot contain a NULL value."

    So for now, I have removed the PRIMARY reference in the [INDEX] and it processes w/o issues. However, I really liked your concept to identify the primary key. Any thoughts on how I need to set the information in table [01_tbl_ProductTables]?

    Thank you,
    Tom
    Attached Thumbnails Attached Thumbnails Capture1.JPG   Capture2.JPG  

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Glad to hear it (mostly) works for you. The errors you describe with the primary key are to be expected. As the primary key by definition cannot be Null or cannot have duplicate values both errors make perfect sense. Usually the primary keys are autonumbers (long integer) not double. And populating them with new values like this has to be done very carefully in order to maintain whatever relationships they are part of in the source applications or the new one. But if you want the ability to add autonumber fields as I described and set them as PK please let me know and I can modify the sample as I already have that code.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Auto-generate queries via VBA
    By skydivetom in forum Programming
    Replies: 26
    Last Post: 05-24-2021, 08:56 AM
  2. Use a Query to generate auto ID
    By banpreet in forum Queries
    Replies: 2
    Last Post: 07-28-2016, 01:28 PM
  3. Auto generate records
    By RokitRod in forum Database Design
    Replies: 1
    Last Post: 10-02-2012, 10:45 AM
  4. Auto Generate Record
    By mjhopler in forum Forms
    Replies: 2
    Last Post: 02-10-2010, 03:40 PM
  5. Auto Fill-In Capability?
    By bbarrene in forum Access
    Replies: 3
    Last Post: 01-15-2010, 08:35 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