Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31

Add data to a table by "on click" action from a report

  1. #16
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376

    Just tested, the sample worked as expected. Did you enable code? Do you want to post your version of it so we can see what went wrong? I chose a couple of random items, entered 123 in the Other Value textbox and clicked on the button.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    maybe this could work? I have to play

    https://docs.microsoft.com/en-us/off...stbox.selected

  3. #18
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    My sample uses that, indirectly. You can loop it testing for selected being true or the simpler loop I used.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    Did you enable code?
    What do you mean?

    Where is the append records form saving? I selected Baldy and entered 1 in "other" don't see any change

  5. #20
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    You would open tblOtherTable and hopefully see the record(s) that got added. I think it starts out empty.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #21
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    Oh, and by enabling code I mean the option below the ribbon if the app is being run from an untrusted location.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #22
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    I see, I have the warning disabled so it did not warn me but it blocked it indeed, now it works.

    It's more or less what I want to do, I like the extra "other value" field, that can be useful.

    It needs a deselect button.
    Say you first pick several values and save them without "other value" and now you want to select a new one and add the "other value" to it, if you don't unslect the other ones first it will duplicate the EmpID and add the "other value" to all the new records. Can be cumbersome to click on the previously selected items to deselect if there are many of them.

    I'll play with it and let you know how it went, probably next week. Thanks for your help.

  8. #23
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    reset something like this maybe? Replacing combo with the list box

    Code:
    Private Sub cmdReset_Click()
    'Reset Form button
    
    
    Me.cboPurchaseCat.Value = Null
    Me.cboReportsNames.Value = Null
    
    
    End Sub

  9. #24
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    You can add a simple loop to deselect everything after adding records. Something like:

    Code:
        For i = 0 To ctl.ListCount - 1
          ctl.Selected(i) = False
        Next i
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #25
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    Quote Originally Posted by geotrouvetout67 View Post
    reset something like this maybe? Replacing combo with the list box

    Code:
    Private Sub cmdReset_Click()
    'Reset Form button
    
    
    Me.cboPurchaseCat.Value = Null
    Me.cboReportsNames.Value = Null
    
    
    End Sub
    No, that won't work with a multiselect listbox. Its value is always Null anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    You can add a simple loop to deselect everything after adding records. Something like:

    Code:
        For i = 0 To ctl.ListCount - 1
          ctl.Selected(i) = False
        Next i
    that sounds like a good option

  12. #27
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    No problem, post back if you get stuck.
    I've created my queries and form

    Unfortunately I can't just use a unique identifier like you did "EmpID", I need to save all my fields from the query to the new table and I added two "other" text fields.

    My ListBox is called lstRequisitionItems and shows 10 columns I need to save to the table tbl_Manual_Requisitions.

    I started debugging and have two issues for starter (I quoted out the text validation for now).

    #1
    Code:
    Dim db As DAO.Database
    is returning a "User-defined type not defined" error

    #2 the multi selection from the list does not work (but it could be because the script bugs)

    Code:
    Private Sub CmdAddRequisitions_Click()
    
    Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
      Dim ctl           As Control
      Dim varItem       As Variant
    
    
      On Error GoTo ErrorHandler
    
    
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("tbl_Manual_Requisitions", dbOpenDynaset, dbAppendOnly)
    
    
      'make sure a selection has been made
      'If Me.lstRequisitionItems.ItemsSelected.Count = 0 Then
       ' MsgBox "Please select at least 1 requisition item"
        'Exit Sub
      'End If
    
    
      'If Not IsNumeric(Me.txtQtyOverride) Then
       ' MsgBox "Please enter a numeric Qty"
        'Exit Sub
      'End If
      
       'If Not IsDate(Me.txtDueDate) Then
        'MsgBox "Please enter a Date MM/DD/YYYY"
        'Exit Sub
      'End If
    
    
      'add selected value(s) to table
      Set ctl = Me.lstRequisitionItems
      For Each varItem In ctl.ItemsSelected
      
        
        rs.AddNew
        
        'Query results
        rs!Component_ID = ctl.ItemData(varItem)
        rs!Description = Me.Description
        rs!Vendor = Me.Vendor
        rs!MOQ = Me.MOQ
        rs!Multi = Me.Multi
        rs!Lead_Time = Me.Lead_Time
        rs!Planned_Date = Me.Planned_Date
        rs!Suggested_Due_Date = Me.Suggested_Due_Date
        rs!Balance = Me.Balance
        rs!Suggested_Order_Qty = Me.[Order Qty]
        
        'Open Fields
        rs!Manual_Order_Qty = Me.txtQtyOverride
        rs!Manual_Due_Date = Me.txtDueDate
        
        
        rs.Update
      Next varItem
    
    
    ExitHandler:
      Set rs = Nothing
      Set db = Nothing
      Exit Sub
    
    
    ErrorHandler:
      Select Case Err
        Case Else
          MsgBox Err.Description
          DoCmd.Hourglass False
          Resume ExitHandler
      End Select
    
    
    
    
    End Sub

  13. #28
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    I think I understand the problem, I just don't know how to fix it using your script.

    Your script is adding new records to the original table (table A tblEmployees) as well as to the "other" table (table B tblOtherTable).

    What I'm looking at doing is pulling records from table A (query --> List box) and insert the selected records to table B only, including with calculated fields from the query and manual entries (text boxes).

    In my application table A is temporary (gets deleted and re-populated on refresh) and shows me items that will become negative within a defined period so it is a moving target, table B will be saved accumulating selections from table A which are items to be ordered.

    The approach I am thinking about now is to add a checkbox and enter a yes/no in each line I want to save, then create an append query using the yes/no selection but it is not as pretty as your application of click to select.

  14. #29
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    My code should only be saving values to the "other" table. It only adds data to whatever table is specified here:

    Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)

    I just tested that in the file from the website. You don't have the form bound to the employee table, do you?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #30
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    My code should only be saving values to the "other" table. It only adds data to whatever table is specified here:

    Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)

    I just tested that in the file from the website. You don't have the form bound to the employee table, do you?
    You're right, I forgot that I manually populated the employee's table before so it appeared it was adding to the employee table.

    This being said I found a work around not needing coding by embedding a subform in a form then sending it to the "save table" with an append query. Actually I like it better because that allows me to enlarge the data selection range and then filter the dates as needed right on the form and I can input all my dates and quantities on 1 page all at once before saving, it's just not as nice but I'll try working on your option more when I'm done just for fun.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. How to suppress "Right-Click" action on a text box
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 11-19-2016, 02:41 AM
  3. Fill in data into table upon "on click"
    By teekc in forum Forms
    Replies: 4
    Last Post: 04-01-2014, 08:27 PM
  4. saves data with out click "Save" button in forms
    By terrythomasvda in forum Forms
    Replies: 4
    Last Post: 01-14-2013, 01:31 PM
  5. "Close form" action does not release table
    By TFisher in forum Programming
    Replies: 7
    Last Post: 07-22-2010, 06:33 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
  •  
Tech Forums: Microsoft Office Forums