Results 1 to 6 of 6
  1. #1
    ikolwon is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    3

    update table after combo box search

    Hi,


    I Have a form with 1x combo box, 10 test fields and a button. Combo box is used to search for the data in the table and the data is displayed on the text boxes. Once I edit the data, i should be able to click on the button to update the record in the database. The issue i have is every time i click the run button , it keeps on defaulting to updating a single row while other rows in the table can't be update. Can you advice where i went wrong? Below is my query.. Any assistance will be much appreciated



    Private Sub cmdEdit_click()
    Dim db As Database
    Dim rec As Recordset
    Dim strMessage As String
    Set db = CurrentDb
    Set rec = db.OpenRecordset("SELECT * From ChurchMembers")
    If rec.RecordCount > 0 Then
    rec.Edit
    rec("Last_Name") = Forms("frmMembersMaintenance").LastName
    rec("Gender") = Forms("frmMembersMaintenance").Gender
    rec!DOB = Forms("frmMembersMaintenance").DOB
    rec("BAPDate") = Forms("frmMembersMaintenance").BAPDate
    rec("Employment_status") = Forms("frmMembersMaintenance").Employment
    rec("Position") = Forms("frmMembersMaintenance").Position
    rec("Status") = Forms("frmMembersMaintenance").Status
    rec("HomeProvince") = Forms("frmMembersMaintenance").HomeProvince
    rec("HomeDistrict") = Forms("frmMembersMaintenance").HomeDistrict
    rec("HomeLLG") = Forms("frmMembersMaintenance").HomeLLG
    rec("HomeVillage") = Forms("frmMembersMaintenance").HomeVillage
    rec("ResProvince") = Forms("frmMembersMaintenance").ResProvince
    rec("ResDistrict") = Forms("frmMembersMaintenance").ResDistrict
    rec("ResLLG") = Forms("frmMembersMaintenance").ResLLG
    rec("ResVillage/Surbub") = Forms("frmMembersMaintenance").ResVill
    rec("ChurchID") = Forms("frmMembersMaintenance").ChurchID
    rec.Update
    rec.Close
    End If
    End Sub


    Private Sub cmbFirstname_AfterUpdate()


    End Sub


    Private Sub cmbFirstname_Click()
    Me.LastName = Me![cmbFirstname].Column(2)
    Me.Gender = Me![cmbFirstname].Column(3)
    Me.DOB = Me![cmbFirstname].Column(4)
    Me.BAPDate = Me![cmbFirstname].Column(5)
    Me.Employment = Me![cmbFirstname].Column(6)
    Me.Position = Me![cmbFirstname].Column(7)
    Me.Status = Me![cmbFirstname].Column(8)
    Me.HomeProvince = Me![cmbFirstname].Column(9)
    Me.HomeDistrict = Me![cmbFirstname].Column(10)
    Me.HomeLLG = Me![cmbFirstname].Column(11)
    Me.HomeVillage = Me![cmbFirstname].Column(12)
    Me.ResProvince = Me![cmbFirstname].Column(13)
    Me.ResDistrict = Me![cmbFirstname].Column(14)
    Me.ResLLG = Me![cmbFirstname].Column(15)
    Me.ResVill = Me![cmbFirstname].Column(16)
    Me.ChurchID = Me![cmbFirstname].Column(17)
    End Sub

  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
    If you want to edit a record, then open bound form and either go to or filter for record. Type edits into bound controls and the edits will be committed to table when form is closed.

    All this code to populate controls and save values would go away.
    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
    ikolwon is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    3
    Thank you. What you mean is to manually edit the table want the editing to be done when button is clicked

  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
    You want user to select item in combobox then use combobox columns to create a new record or edit existing? If you want to edit multiple records then need to loop through recordset. But why update all member records with same info?
    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
    ikolwon is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    3
    Thank you. That is where my confusion is. Is it necessary to update member records with same info?.. Thank you for your reply

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You tell me if it is necessary. If you do loop through recordset and update each record with combobox info, every record in table will end up with same name. As is, code is opening a recordset of members table and updating only the first record.

    Need to describe what you actually want to accomplish.

    What do you mean by "other rows in the table can't be update" - why should they?
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-13-2019, 06:00 PM
  2. Replies: 2
    Last Post: 10-10-2014, 05:33 AM
  3. Replies: 25
    Last Post: 03-19-2013, 10:08 PM
  4. Combo box update field in a different table
    By deadringer86 in forum Forms
    Replies: 3
    Last Post: 12-28-2012, 05:10 PM
  5. Replies: 7
    Last Post: 09-21-2012, 11:09 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