locked
Change field in different table on click RRS feed

  • Question

  • I have a table called "Vehicles" in which there is a Yes/No field that is called "OnLoanStatus" I have a form called "DelWorkOrder" where I track mileage, time, etc. which is entered into the "DelWorkOrder" table. What I want to do is when I save the record into the "DelWorkOrder" table then the "OnLoanStatus" in the "Vehicles" table gets updated to Yes automatically.  I will also have RetWorkOrder that when entered into its own table it changes the "OnLoanStatus" in the "Vehicles" table back to "No"  I have everything working but I cannot figure out how to make this happen.   I do not have a "OnLoanStatus" on the "DelWorkOrder" form but if adding that can make this happen then great.  I have tried a query but cannot seem to get update to value listed.  Any help is appreciated as I am new to access.  Thanks, Troy

    Forgot to add that I need this update to only happen to the row containing a specific "ClientVehNum" that is also being entered in the form and is contained in both the "Vehicles" table and the "DelWorkOrder" table.  I hope this makes sense?  :)


    • Edited by Kaya4tw Monday, June 23, 2014 4:06 AM
    Monday, June 23, 2014 3:45 AM

Answers

  • Hi,

    According to your description, you want to update the field "OnLoanStatus" in table "Vehicles" automatically after you enter records to save into the table "DelWorkOrder" in the form "DelWorkOrder".

    Since you said "Change field in different table on click" in the title, I suspect you are saving the records into the table "DelWorkOrder" in the click event of a command button. If so, I think you just need to update the field "OnLoanStatus" in table "Vehicles" after inserting records in this click event. Here is a sample for your reference. It searches the records matched the entered value in the TextBox "ClientVehNum" of the form, then update the filed "OnLoanStatus".

    Option Compare Database Private Sub Command1_Click() Dim db As Dao.Database, rec As Dao.Recordset Dim strSQL As String 'save record into the table "DelWorkOrder"

    'Update field "OnLoanStatus" of table "Vehicles" based on ClientVehNum Set db = CurrentDb strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum=" & ClientVehNum.value Set rec = db.OpenRecordset(strSQL, dbOpenDynaset, dbEditAdd) With rec Do While Not .EOF .Edit ![OnLoanStatus] = True .Update .MoveNext Loop End With rec.Close db.Close End Sub

    If you are not saving the records into table "DelWorkOrder" in a click event, you could create a After Insert macro for the table "DelWorkOrder" to update the filed "OnLoanStatus" in table "Vehicles". In this way, it will automatically update the table "Vehicles" when inserting a new record into the table "DelWorkOrder".


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 24, 2014 3:37 AM
  • Hi,

    Since you never mentioned the field "ClientVehId" in the original post, we still talk about field "ClientVehNum".

    >>The ClientVehNum in the DelWorkOrder table is a "Number" data type as that table has a lookup to the "Vehicles" table but in the "Vehicles" table it is a "Text" data type.<<

    As you said, the field "ClientVehNum" in table "Vehicles" is a "Text" data type, I think we have to edit the SQL statement as followed.

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum='" & ClientVehNum.value & "'"

    If it cannot help you, I suggest you sharing your sample Access database through OneDrive and removing the private information so that we can troubleshoot.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 25, 2014 6:05 AM
  • I tried using the second vba code you gave me below

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum='" & ClientVehNum.value & "'"

    and that did the trick. It now writes Yes to the correct place in Vehicles table. T

    Thank you very much for your help.

    Troy

    <o:p></o:p>

    • Marked as answer by Kaya4tw Thursday, June 26, 2014 1:52 AM
    Wednesday, June 25, 2014 11:11 PM

All replies

  • Hi,

    According to your description, you want to update the field "OnLoanStatus" in table "Vehicles" automatically after you enter records to save into the table "DelWorkOrder" in the form "DelWorkOrder".

    Since you said "Change field in different table on click" in the title, I suspect you are saving the records into the table "DelWorkOrder" in the click event of a command button. If so, I think you just need to update the field "OnLoanStatus" in table "Vehicles" after inserting records in this click event. Here is a sample for your reference. It searches the records matched the entered value in the TextBox "ClientVehNum" of the form, then update the filed "OnLoanStatus".

    Option Compare Database Private Sub Command1_Click() Dim db As Dao.Database, rec As Dao.Recordset Dim strSQL As String 'save record into the table "DelWorkOrder"

    'Update field "OnLoanStatus" of table "Vehicles" based on ClientVehNum Set db = CurrentDb strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum=" & ClientVehNum.value Set rec = db.OpenRecordset(strSQL, dbOpenDynaset, dbEditAdd) With rec Do While Not .EOF .Edit ![OnLoanStatus] = True .Update .MoveNext Loop End With rec.Close db.Close End Sub

    If you are not saving the records into table "DelWorkOrder" in a click event, you could create a After Insert macro for the table "DelWorkOrder" to update the filed "OnLoanStatus" in table "Vehicles". In this way, it will automatically update the table "Vehicles" when inserting a new record into the table "DelWorkOrder".


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 24, 2014 3:37 AM
  • Your description of what I need is exactly correct.  I put in the top code you gave and get a runtime error saying object required on this line.

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum=" & ClientVehNum.Value

    So just to make sure you are clear.   When I hit the command button on the form to enter a record into the DelWorkOrder table which has one of my fields as ClientVehNum, I need to update the Vehicles table OnLoanStatus field where the ClientVehNum value I just entered matches the ClientVehNum in the vehicles table.  The logic seems correct in the line with the error but I do not know all the proper syntax so maybe that is an issue?

    Thanks,

    Troy




    • Edited by Kaya4tw Wednesday, June 25, 2014 3:25 AM
    Wednesday, June 25, 2014 3:19 AM
  • Hi,

    >>I put in the top cope you gave and get a runtime error saying object required on this line.

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum=" & ClientVehNum.Value<<

    Since you said "I need this update to only happen to the row containing a specific "ClientVehNum" that is also being entered in the form and is contained in both the "Vehicles" table and the "DelWorkOrder" table", so we have to search the records matched the entered value in the TextBox "ClientVehNum" of the form firstly.

    In the line above of my sample, the ClientVehNum represents the Text Box where I entered values for field "ClientVehNum". I suspect you may not have this Text Box or you have such a Text Box with another name, or you have other control to enter the value, you have to edit it based on your form.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 25, 2014 3:30 AM
  • You are correct.  With all my messing around that field became VehicleID.  I am not sure this matters but that field has a lookup to get the VehicleID from a dropdown when entering the information into the DelWorkOrder form.  Also it is a "Number" as a data type.  So I changed ClientVehId to VehicleID and I no longer get a runtime error but the checkbox remains unchecked in the Vehicle table and I get no confirmation that a record has been changed. (Not sure that I should but thought I would mention it)   Also the field in Vehicles table is a Yes/No data type in table design View but shows as a checkbox in the layout view.  Thank you for all of your help by the way, I do appreciate it.  

    Troy

    Wednesday, June 25, 2014 3:43 AM
  • Some more info for you.  I changed the VehicleId back to ClientVehNum as it made more sense in the flow of things and that is what it should have been anyhow.   The ClientVehNum in the DelWorkOrder table is a "Number" data type as that table has a lookup to the "Vehicles" table but in the "Vehicles" table it is a "Text" data type.  Since changing things back the runtime error has resurfaced.  I think part of the problem is this lookup in the DelWorkOrder table but not really sure.

    Troy


    • Edited by Kaya4tw Wednesday, June 25, 2014 3:54 AM
    Wednesday, June 25, 2014 3:53 AM
  • Hi,

    Since you never mentioned the field "ClientVehId" in the original post, we still talk about field "ClientVehNum".

    >>The ClientVehNum in the DelWorkOrder table is a "Number" data type as that table has a lookup to the "Vehicles" table but in the "Vehicles" table it is a "Text" data type.<<

    As you said, the field "ClientVehNum" in table "Vehicles" is a "Text" data type, I think we have to edit the SQL statement as followed.

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum='" & ClientVehNum.value & "'"

    If it cannot help you, I suggest you sharing your sample Access database through OneDrive and removing the private information so that we can troubleshoot.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 25, 2014 6:05 AM
  • Okay so I started over and took all lookup fields I had at the Table level away and did the lookups at the form level (which as I understand it is the way to go) in a completely new database.  I entered and ran the very first code you sent me and now I get an too few parameters, expected 1 on this line

    Set rec = db.OpenRecordset(strSQL, dbOpenDynaset, dbEditAdd)

    I made sure that all field names are exactly as my first post.   two tables, Vehicles and DelWorkOrder and one form, DelWorkOrderform.   the ClientVehNum field is of same data type in both tables which is text and they are the same size.   So once again just to reiterate, I am trying to set the Yes/No value of the OnLoanStatus field to Yes in the vehicles table automatically when I save the DelWorkOrderform entering data into the DelWorkOrder table.  I have tried an after insert also.  when I enter the setfield information, access gives me what I want from the pull down menu namely, Vehicles.OnLoanStatus but it does not change the value in the Vehicles table either.  Not sure this matters or not but the ClientVehNum name on my DelWorkOrder form is txtClientVehNum and that was so the combo box would auto populate a few fields for me.

    Thanks,
    Troy




    • Edited by Kaya4tw Wednesday, June 25, 2014 11:04 PM
    Wednesday, June 25, 2014 8:12 PM
  • I tried using the second vba code you gave me below

    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum='" & ClientVehNum.value & "'"

    and that did the trick. It now writes Yes to the correct place in Vehicles table. T

    Thank you very much for your help.

    Troy

    <o:p></o:p>

    • Marked as answer by Kaya4tw Thursday, June 26, 2014 1:52 AM
    Wednesday, June 25, 2014 11:11 PM
  • I am not sure if I should start a new thread as you may know this answer easily.  What I realized in my table layout is that I shouldn't have a table for both DelWorkOrder and RetWorkOrder as those two tables are holding very similar information.  I thought it would be designed and work better if I just have a WorkOrder table with a selection of worker order type being Delivery or Return.  Of course this affects what you helped me with but I was thinking that all we needed to add was an if then else statement.  So if the WorkOrderType field = DelWorkOrder then change the same OnLoanStatus to true based on the ClientVehNum.  If WorkOrderType field = RetWorkOrder then OnloanStatus is false again based on ClientVehNum.  I think this will work but I have no idea how to implement that into what you sent me.  By the way here is what I used that worked for me.  

    Private Sub SaveDelWorkOrder_Click()
    Dim db As Dao.Database, rec As Dao.Recordset
    Dim strSQL As String

    'save record into the table "DelWorkOrder"
    'Update field "OnLoanStatus" of table "Vehicles" based on ClientVehNum
    Set db = CurrentDb
    strSQL = "SELECT * FROM Vehicles WHERE ClientVehNum='" & ClientVehNum.Value & "'"
    Set rec = db.OpenRecordset(strSQL, dbOpenDynaset, dbEditAdd)

    With rec
        Do While Not .EOF
            .Edit
            ![OnLoanStatus] = True
            .Update
       
            .MoveNext
        Loop
    End With

    rec.Close
    db.Close

    End Sub

    Sunday, June 29, 2014 10:16 PM
  • Hi,

    Since your original issue of this thread has been resolved, in order to involve more partners of this community to share their knowledge or learn from your interaction with us, I suggest you posting a new thread in the forum.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 30, 2014 1:06 AM