Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7

    Unhappy Form Based on Select Query - Need results to update a table

    Guys - I have a simple form that queries an underlying Table. This form is called from a combo-box based in the "NOT IN LIST" event, which is done from another rather large data entry form. The query simply returns the max value in the table based in a parameter in the query. When the user types in a value that is not in the list, this form is opened, and returns the new value which is the max value based upon the parameter the user entered, and returns the result to the user in the form...as shown below (hopefully my screen print will post) All is good in the world at this point.

    Problem statement 1: I as a user need to have this same value returned in the form/query added to the underlying table from which the query producing the result below is based.



    Maybe I'm having brain freeze, but I cannot figure out how to get this value to the table. I cannot base a form on an Append Query. And if I use an append query as an action on the form, then I'm simply creating a duplicate process.



  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No image in post.

    Not sure what you mean by 'returned in the form/query' - need combobox list updated with new value? NOT IN LIST event coding is a common topic. Show your code or provide db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7
    The combo box "not in list event" opens a simple form used for display purposes only and calls the query to return the max value which is displayed in the form. I do this so the user can see the new value and choose to use it or not. To use it, I next need the user to click on an OK button to add it to the original table that the query was based upon.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Still not clear what you want.

    Which record on which form needs this accepted value? Use of form names can be helpful. If you are using LimitToList Yes and NotInList event then value must be in combobox list in order for the combobox and field to get this new value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7
    The combo box[RUN ID LOOKUP] which displays a list of values from table [RUN ID], "not in list event" opens a simple form [NEW RUN ID] used for display purposes only and calls a query [LAST RUN ID] to return the max value which is then displayed in the form [NEW RUN ID]. I do this so the user can see the new value in the form [NEW RUN ID]and choose to use it or not. To use it, I next need the user to click an OK button to add it to the original table [RUN ID] that the query was based upon. If the user chooses to press the OK button which (and this is what I'm trying to do) adds the new value to the data table called [RUN ID], I now need to see this new value in the combo box [RUN ID LOOKUP]

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you're not having the process right. If the query and form display the max value from the [RUN ID] table that means the value is already there and it shouldn't be "used" or "added" again. Now if your [NEW RUN ID] = DMax("[ID]","[RUN ID]") + 1 then I think is just a matter of adding Me.[RUN ID LOOKUP].Requery in the Enter event of the combo (after you added the value in the click event of the button as you say you do).

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

  7. #7
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7
    The query does returns the max value, and I then create a new field in the query to add +1 to get the new ID. The issue is adding this new value back to the table. This is where I'm stuck. How do I get this new value added to the [RUN ID] table. I have tried to use the following in the ONCLICK event. And keep getting a syntax error. Note: This is an inherited database. The table I'm trying to insert does have spaces as does most of the field values.

    DoCmd.RunSQL "INSERT INTO [RUN ID] [([RUNID], [TID], [NUMBER]),VALUES ([NEWRUNID], [TID], [NEXTNBR])]"

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    And the error is.... You said initially the issue was with
    see this new value in the combo box [RUN ID LOOKUP]
    . What are the other two values (TID and NEXTNBR) and where do you define them. Enclosing them in square brackets makes them appear as fields/controls and not variables as I think they should be. Can you show us all the code and/or a sample db?

    You SQL statement also seems a bit wrong:
    DoCmd.RunSQL "INSERT INTO [RUN ID] [([RUNID], [TID], [NUMBER]),VALUES ([NEWRUNID], [TID], [NEXTNBR])]"
    Try:
    DoCmd.RunSQL "INSERT INTO [RUN ID] ([RUNID], [TID], [NUMBER]),VALUES ([NEWRUNID], [TID], [NEXTNBR]);"

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

  9. #9
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7
    First....thanks for taking the time to assist. I'm still getting a syntax error with this. Any other suggestions? RUNID is the TABLE, the values are the fields in the query.

    Private Sub Command26_Click()
    DoCmd.RunSQL "INSERT INTO [RUN ID] ([RUNID], [TID], [NUMBER]),VALUES ([NEWRUNID], [TID], [NEXTNBR]);"


    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Values() must come from variables in VBA code using concatenation or parameters or be static data. There should not be a comma in front of VALUES. Examples:

    DoCmd.RunSQL "INSERT INTO [RUN ID] ([RUNID], [TID], [NUMBER]) VALUES (" & NEWRUNID & ", " & TID & "," & NEXTNBR & ")"

    DoCmd.RunSQL "INSERT INTO [RUN ID] ([RUNID], [TID], [NUMBER]) VALUES (1, 1, 1)"

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7

    almost there

    I'm almost there. No more syntax error. However I don't get why parameter entry are popping up only for me to re-enter the value that it is display. However, when I do, it does indeed write to the database.

    The Not In List event calls the form which opens the Parameter entry dialog. User enters the TID (terminal Identifyiner). In this example JAX. The form returns the value for the user to confirm or cancel. Confirmation done by pressing Add button which calls the on click event which performs the INSERT INTO statement. Again...I just want to take the value returned from the query, which I'm displaying to the user, and write it to the same file the query is built upon.

    See the attached video

    I'm sorry....I'm very much a novice in DB.

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show all the code you have on this form and the pop up one to add? The easiest would be to prepare a small sample of a db with just the objects involved (forms, queries, tables) and a sample "dummy" records to show the problem.

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

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Popups because Access cannot find objects referenced. You need to provide your code or the db itself.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    DuluthGa770 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Location
    Atlanta, GA
    Posts
    7
    This is the query that the form calls when opened and returns the values.

    SELECT Max(Format([TID],"@@@") & Format([NUMBER]+1,"000")) AS NEWRUNID, [POST RUN NUMBER].TID, Max([Number]+1) AS NextNbr
    FROM [POST RUN NUMBER]
    GROUP BY [POST RUN NUMBER].TID
    HAVING ((([POST RUN NUMBER].TID)=[Enter TID to create new Run No]));




    ======================below is the VB code for the form

    Option Compare Database
    Option Explicit


    Private Sub Command4_Click()
    On Error GoTo Err_Command4_Click




    DoCmd.CLOSE



    Exit_Command4_Click:
    Exit Sub


    Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click

    End Sub
    Private Sub Erase_Current_Record_Click()
    On Error GoTo Err_Erase_Current_Record_Click




    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


    Exit_Erase_Current_Record_Click:
    Exit Sub


    Err_Erase_Current_Record_Click:
    MsgBox Err.Description
    Resume Exit_Erase_Current_Record_Click

    End Sub


    Private Sub Command26_Click()
    DoCmd.RunSQL "INSERT INTO [POST RUN NUMBER] ([Run No], [TID], [Number]) VALUES (" & NEWRUNID & ", " & TID & "," & NextNbr & ")"




    End Sub

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code is much easier to read when posted within CODE tags.

    That query is form RecordSource? If NEWRUNID, TID, NextNbr are fields in form RecordSource then referencing them in the concatenation should work. Try Me.NEWRUNID or Me!NEWRUNID.

    DoMenuItem is deprecated method (not the best way to code an action). Consider DoCmd.RunCommand acCmdDeleteRecord.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-30-2020, 01:27 PM
  2. Replies: 8
    Last Post: 01-01-2020, 11:09 PM
  3. Replies: 4
    Last Post: 02-01-2019, 12:41 AM
  4. Replies: 3
    Last Post: 02-22-2016, 03:38 PM
  5. Replies: 7
    Last Post: 11-24-2014, 02:11 PM

Tags for this Thread

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