Results 1 to 5 of 5
  1. #1
    MykeRuiz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Location
    Saudi Arabia
    Posts
    5

    how to update two tables using single form?

    Fellow programmers please help me on my code. Thank you!

    Private Sub cmdUpdate_Click()

    Dim db As Database
    Dim employ As Recordset
    Dim asset As Recordset



    Set db = OpenDatabase("C:\Users\MIKE\Documents\bonyan_asset s.mdb")
    Set employ = db.OpenRecordset("Employees", dbOpenTable)
    Set asset = db.OpenRecordset("Assets", dbOpenTable)


    'update the status of employee from active to inactive in table employees
    With employ
    .Index = "EmpID"
    .Seek "=", Val(cboEmployee.Value)
    If Not .NoMatch Then
    .Edit
    !Active = IIf(chkActive.Value = -1, True, False)
    .Update
    End If
    End With

    'update the status of assigned asset to employee from active to available in table assets
    While Not .EOF
    With asset
    .Index "EmployeeID"
    .Seek "=", Val(cboEmployee.Value)
    If Not .NoMatch Then
    .Edit
    !StatusID = "1"
    .Update
    End If
    MsgBox !EmployeeID
    End With
    Wend


    employ.Close
    asset.Close
    db.Close
    clearFields
    cboEmployee.Requery
    cmdUpdate.Enabled = False
    cboEmployee.SetFocus
    End Sub

  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
    Help with what exactly? This is outside any With block so presumably errors:

    While Not .EOF

    Running update SQL would be more efficient, or at least opening the record sets on filtered SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MykeRuiz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Location
    Saudi Arabia
    Posts
    5
    Sir, updating the employee table alone, the code works. but if i add the code to update the status of assigned asset it doesn't work at all.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by MykeRuiz View Post
    Sir, updating the employee table alone, the code works. but if i add the code to update the status of assigned asset it doesn't work at all.
    I think this statement suggests that the code is the same for both situations, when in fact, it is not. In one case, you attempt to loop through a recordset; in the other, you do not. Here are my observations, for whatever they're worth:

    1. You should be using Do While...Loop statements in favour of the old While...Wend of BASIC if only for the reasons that you can test conditions within the loop, plus exit the loop when conditions warrant.

    2. I think you are assuming that any search is going to start at the beginning of your table because that's what you see in a table view. A recordset has no such guarantee, and without a .Move First, where you will start is a crap shoot. Where you will go by using = with .Seek is forward from where you are. Do not expect the search to begin at the beginning of the recordset when it reaches the end. Thus if you start past the point where the sought value is found, you will not find it.

    3. you are closing the recordsets, but not destroying them (they will continue to occupy system memory). My rule is, anything that gets Set to anything gets Set to Nothing before the procedure reaches the end (Set rs = Nothing) and after .Close if that is needed first.

    .4 I agree with Paul. This While Not .EOF is outside of the block that references the recordset. You are trying to refer to a property of it (.EOF) without referring to it first. I'm also surprised an error is not presented. Or perhaps it is, which leads me to #5...

    .5 Doesn't work doesn't help (liking that one so much it is part of my signature). Your original post should have at least stated what happens and what (if any) error messages are encountered. Anything beyond that minimum, such as what calls a procedure, what it should do (it is seldom obvious to us what a poster has in their head) which line it fails on (when errors are encountered), the error number and text - is a bonus and can only help us to help you.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    MykeRuiz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Location
    Saudi Arabia
    Posts
    5
    Thank you sir! I will try to re-code it again.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-03-2014, 05:39 AM
  2. Form with single record but multiple tables
    By thegooser in forum Forms
    Replies: 1
    Last Post: 10-10-2012, 01:48 PM
  3. Can I have two tables in a single form?
    By Ash1402 in forum Access
    Replies: 6
    Last Post: 10-02-2012, 08:56 PM
  4. Replies: 10
    Last Post: 08-09-2012, 01:07 PM
  5. Single Record Update Query from a Form
    By Steven.Allman in forum Access
    Replies: 0
    Last Post: 03-30-2011, 09:34 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