Results 1 to 5 of 5
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Update recordset value with textbox.text

    I have a form that lists an individuals demographic data and a subform for entering the individuals donations to a charity. There are 2 linked tables (Contact info and Item Donation respectively)underlying these forms. When the primary form is opened, a combo box is used to find an individuals current record. Then the subform is opened to record a new contribution. The main form is opened in add mode so I assume the subform is as well. When a new donation is entered, the forms work properly- a new record is added to the "Item Donation" table for the individual. I am stumped on an additional feature I want to add to the subform. I have 4 checkboxes on the subform to allow the user to select one of 4 designations for their contribution. I want the user to check the box for the designation they want. There is a textbox next to the checkbox that contains a string denoting the designation. As an example, one of the designations is for "capital improvements". I want that string to be placed in the field "Restricted Use" of the underlying table "Item Donations". Since their are 4 options and one "restricted use" field in the table, I have been working with recordset.edit to try to add the string value to the field when the check box is clicked. I have tried multiple permutations of the following code. I can't tell if the record I am trying to edit is the same as the record that opens when the subform opens. The code runs, but the "restricted Use" field doesn't update. If it helps, I did not use a query to set up this form/subform. The record source for the main form is "Contact info", and the record source for the subform is "Item Donation"


    Code:
    Private Sub Check1_Click()Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Item_Donations", dbOpenTable)
      rst.Edit
        Text90.SetFocus
        rst.Fields("Restricted Use").Value = Nz((Me!Text90.Text), "")
       
     rst.Update
     rst.Close
    Set rst = Nothing
     
    End Sub
    What am I missing?

    Thanks

  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
    Why not just have the field on the form? Your code will update the first record in the table, not the record you're on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I didn't want the user to have to enter the text every time. I suppose a list box would work, but doesn't looks as nice. So my error is that the code doesn't open the record that I have open in my subform? That makes sense. Is there a way to select the record that I am currently working on? Should I use rst.findfirst on my primary key field to get a match for the record I am on?

  4. #4
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I think this will work. My key field is "Id_Number_FK". I made the following modification. It tested ok once. What do you think?

    Code:
    Private Sub Check1_Click()
    Set rst = Me.Recordset.Clone
        rst.FindFirst "[Restricted Use] = " & Str(Nz(Me![ID_number_FK], 0))
        If Not rst.EOF Then Me.Bookmark = rst.Bookmark
        Me.Refresh
        rst.MoveLast
        rst.Edit
        Text90.SetFocus
      rst.Fields("Restricted Use").Value = Nz((Me!Text90.Text), "")
        
        
    rst.Update
    rst.Close
    Set rst = Nothing
    
    
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd still use a field on the form, presuming it can be bound. You can populate it with code. It seems like to get to the record the form is on anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 02-04-2017, 07:34 AM
  2. Replies: 6
    Last Post: 02-26-2016, 05:28 AM
  3. Replies: 7
    Last Post: 01-01-2016, 11:17 PM
  4. Replies: 5
    Last Post: 04-30-2015, 01:50 AM
  5. Replies: 1
    Last Post: 06-18-2014, 09:39 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