update multiple tables from one form

I'm not sure the best way to go about this.  I have attached a document showing the relationships of my tables.  I need to build a form to update the tblReviewHistory table and the tblBranchReviewHistory.  On the form, the user should be able to select the client and then input the dates for the review.  Then if the client has multiple branch locations, the user can select which locations were visited for that review.  Please let me know if more information is needed.  Any suggestions are appreciated.  Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
You need to set up the appropriate relationships between your tables.  One-to-many between tblClients and tblBranchLocations to start with. It is not clear why you need both tblReviewHistory and tblBranchReviewHistory -- why not just add BranchID to tblReviewHistory?  Also, you could remove ClientID, since it can be obtained through tblBranchLocations.  Then set up a one-to-many relationship between tblBranchLocations and tblReviewHistory.

You could have a main form bound to tblClients with a subform bound to tblBranchLocations, or a form bound to tblClients with a listbox whose row source is tblBranchLocations, filtered by the current record's ClientID.  This would allow you to make multiple branch selections and do something with them, using the ItemsSelected collection of a multi-select listbox, as in the code sample below:
Private Sub cmdExportData_Click()

On Error GoTo ErrorHandler

   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   Set lst = Me![lstSelectContacts]
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   strTitle = "Information missing"
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
         GoTo NextItem
      End If
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
   Next varItem

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

nfstrongAuthor Commented:
I do have the one-to-many relationships between the tblClients and tblBranchLocations.  I have the tblReviewHistory and tblBranchReviewHistory as more than one branch can be reviewed at each review.  Also, each client can have multiple reviews.  So a client can have 200 branches and over the term of the loan we want to track which branches have been visited at each review.  Hopefully that makes sense.  I'll try your code with the listbox and see if I can get it to work.  Thanks!
nfstrongAuthor Commented:
I'm having a problem getting the list box to update with the correct client.  I've tried it on the main form and on the sub form and it either has all the locatoins for all the clients listed or none at all.  Is there a step that I'm missing to requery the listbox once a client is selected?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

nfstrongAuthor Commented:
Alright, I think I've almost got this figured out.  I'm just stuck on one thing.  I'm trying to get the BranchReviewHistory table to update from the information entered on the form.

I have a form with a combo box to select a client which then displays the branch locations in the list box.  In the subform is where the user can update the review information with the dates.  After the user fills in the review dates and selects the branches, the database updates the ReviewHistory table, but not the BranchReviewHistory table.  I'm not quite sure how to make that work.

Below is some sample data from my tables so you can understand why I have them.
ClientNumber       ClientName       ActiveStatus       FYE
ABC0                   ABC                                   A         12

ClientNumber       BranchID       BranchName       Address       City       State
ABC0                               1                                   121 West St  City           ST
ABC0                               2                                    234 East St   City2         AL
ABC0                               3                                    145 East St    City3        MS

ClientNumber       ReviewID       ReviewDate       InternalIssue       GlobalIssue       Lead
ABC0                                1             4/4/2011              4/4/2011              4/4/2011        SD
ABC0                                2             3/21/2011            3/31/2011            4/4/2011        AM

ReviewID       BranchID
             1                    1
             1                    2
             2                    2
             2                    3

Each Client can have many branch locations.  Each Client can have many reviews.  Each review can review many branches.  Each branch can have many reviews.  Let me know if that makes sense or if there is a better way to go about this.  Thanks for your help!
Can you upload your tables with sample data?
nfstrongAuthor Commented:
There is sample data in the above comment 35317958.  If you need more I can provide.
You can save us (and you also) a lot of time if upload your DB. If we will construct tables from your comment:
1. We need time for it
2. Fields type will be different from yours and you will be not able directly apply our exaples to your DB
nfstrongAuthor Commented:
Test this sample.
Open yor Switchboard form and select Add Review History
Fill fields, Fill ReviewDate in subform (or double click on it if it exists), mark reviewed Branches i popup form, close it.
I think you don't need ClientID in tblBranchReviewHistory and I don't fill it.
nfstrongAuthor Commented:
I do like how that works.  However, the Lead Analyst is not being updated in the tblReviewHistory table.  And when I try to run the query under Client Specific Reports on the Switchboard, the new review is not added.  I think it's because it looks for the ClientID.  

I'm also not quite sure how all those queries you added work, so if you could explain that it would be helpful.  Thanks!
nfstrongAuthor Commented:
I would like to understand how this works.  If you could please explain how the queries, tables, etc that you added work together, I would appreciate it.  Thanks.
Sorry, I've missed your previous comment.
All idea is in after update event for field
DoCmd.OpenQuery "QryTmpClear" - Clearing of temporary table for branches (tblTmp)
DoCmd.OpenQuery "QryTmpAddReviewed" - adding already reviewed branches to tblTmp
DoCmd.OpenQuery "QryTmpAddBranches" - adding not reviewed branches to tblTmp
DoCmd.OpenForm "FrmTmp" - form for working with tblTmp

in On Close event in form ftmTmp
DoCmd.OpenQuery "QryClearReviewed" - clear old statuses of reviewed branches
DoCmd.OpenQuery "QryBranchReviewAdd" - adding current statuses of reviewed branches.

There also was not added requery. New version is included.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nfstrongAuthor Commented:
That's great!  Can you help me on where to add the ClientNumber so it is updated in the tblBranchReviewHistory table.  Since it is not updated, it is causing the qryBranchReviewHistory query to not capture the new reviews that are entered.  I appreciate your help.
You don't need ClientNumber in  tblBranchReviewHistory. It is in table tblReviewHistory. Change table name for fields with ClientNumber in query qryBranches from  tblBranchReviewHistory to tblReviewHistory.
nfstrongAuthor Commented:
Got it.  Thanks for all your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.