I have used this forum anonymously for months now finding it highly informative and a great source of information that can be understood by VBA newbies like myself. I am posting for the first time due to a rush project. I inherited a database application that has one large main table (tblmaster) that holds customer information. I have recently added a second table to hold contract (tblcontract) information and tied the two tables together by adding a contract_id field to tblmaster.
Not all records in master will have a contract_id.
tblContract is populated through a form that has check box users will check if the contract and all documentation attached to the contract is complete and it's ready for the next department to review. When this box is checked, I would like to find all customers attached to that contract and update a date field in tblMaster with the current date, which will tell this department the contract is now in their hands as well as start the timetable on the aging of that contract.
The workflow is:
1. Find contract record to complete.
2. Check Complete Contract Check Box
3. msgBox "This will update....Are you Sure?
I'm looking for assistance writing the VB to run the query and make the update. The other pieces I think I have in this:
Dim LResponse As Integer
LResponse = MsgBox("This action will send all providers associated with this contract to PDM, are you sure you want to do this?", vbYesNo Or VbMsgBoxStyle.vbQuestion _
Or VbMsgBoxStyle.vbDefaultButton2, "Update Providers?")
If LResponse = vbYes Then
Form_Contract.Check31.Value = "Yes"
Else: Form_Contract.Check31.Value = "No"
End If
Thank you in advance.
Not all records in master will have a contract_id.
tblContract is populated through a form that has check box users will check if the contract and all documentation attached to the contract is complete and it's ready for the next department to review. When this box is checked, I would like to find all customers attached to that contract and update a date field in tblMaster with the current date, which will tell this department the contract is now in their hands as well as start the timetable on the aging of that contract.
The workflow is:
1. Find contract record to complete.
2. Check Complete Contract Check Box
3. msgBox "This will update....Are you Sure?
a. Yes. - Find the ID for all tblMaster records with this contract ID and enter today's date in recv_dt field. Prevent the check box field for this record from being updated in the form.
b. No - Close Message Box and do nothing.
b. No - Close Message Box and do nothing.
I'm looking for assistance writing the VB to run the query and make the update. The other pieces I think I have in this:
Dim LResponse As Integer
LResponse = MsgBox("This action will send all providers associated with this contract to PDM, are you sure you want to do this?", vbYesNo Or VbMsgBoxStyle.vbQuestion _
Or VbMsgBoxStyle.vbDefaultButton2, "Update Providers?")
If LResponse = vbYes Then
Form_Contract.Check31.Value = "Yes"
Else: Form_Contract.Check31.Value = "No"
End If
Thank you in advance.
