locked
Update Fields Across Multiple Tables RRS feed

  • Question

  • I have created a database including two tables: the "Clients" table and the "JobPlacement" table.  There is a one-to-many relationship between the two, so that one entry in "tblClients" can be linked to multiple jobs in "tblJobPlacement."  I need to export an excel sheet each month (a "Roster") that will include each of the active clients.  One series of the columns will be the information for that client's current job.  

    My short-term solution has been to create a query ("qryRoster") that includes fields from the Clients table and fields from Job Placement table, with a criteria that selects only those entries from Job Placement that have a certain field ("Current Job") marked as "Yes."  I added this field to the Job Placement table just for this purpose.  This creates the result I want, but it is not user-friendly because the user has to make sure that only one job for each client has the "Current Job" field as Yes.

    I think that the better option would be to set up a macro so that when a new job is entered, a window appears that asks the user whether or not it is the current job.  If the user clicks yes, then the macro would be activated and a certain function would take the desired fields from the Job Placement table and enter them into fields in the Active Clients table.  Then, the "Roster" query would not have to pull fields from two different tables and there would be no possibility of the query yielding more than one instance of the same client from the Clients table.  I have tried using the DLookup function, but I haven't figured out how to write it in order to pull a value from one table and insert it into a field in another table.

    Is the DLookup function the best one for this purpose?  I'm still a VBA novice, but should I be using other VBA code?

    Sunday, May 18, 2014 3:15 AM

Answers

  • Hi,

    How about instead of having a "current job" field to just have a StartDate? Then, instead of looking for a Yes in your query, just look for the latest date.

    Just my 2 cents...


    Sunday, May 18, 2014 1:13 PM
  • Hi,

    How about instead of having a "current job" field to just have a StartDate? Then, instead of looking for a Yes in your query, just look for the latest date.

    Just my 2 cents...


    In addition to the DB guy, I use in a couple of applications both StrtDate and EndDate. An empty EndData means then the "current job".

    The advantage is that you have information on the time that someone had a job, but also on the time where that person had no job. Moreover, you can have more than one (parttime) current job.

    Imb.

    Sunday, May 18, 2014 2:16 PM
  • I think that the better option would be to set up a macro so that when a new job is entered, a window appears that asks the user whether or not it is the current job.  If the user clicks yes, then the macro would be activated and a certain function would take the desired fields from the Job Placement table and enter them into fields in the Active Clients table.

    I would advise strongly against such an approach.  It would create substantial redundancy, with the consequent risk of update anomalies.  In most situations I would adopt the approach used by Imb whereby a Null EndDate column indicates that a job is current, but I have adopted an approach similar to your CurrentJob Boolean column in other non-date contexts.  For example in one case I used a Boolean 'Primary' column to indicate a customer's main phone number of each category, and checked in the BeforeUpdate of a check box bound to this column in the relevant subform, to ensure that only one number is the primary one.  The code for the landline category for instance is:

        On Error GoTo Err_Handler

        Const conMESSAGE = _
            "Another number is set as primary landline " & _
            "number for this customer.  Do you wish to replace " & _
            "it with this number?"

        Dim dbs As DAO.Database
        Dim strCriteria As String
        Dim strSQL As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And PhoneType = ""LandLine"" And Primary = True"

        ' get user confirmation to change primary landline
        ' number for this customer
        If Me.Primary Then
            If Not IsNull(DLookup("CustomerID", "CustomerPhoneNumbers", strCriteria)) Then
                If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Warning") = vbYes Then
                    ' set all other landline numbers for this customer to non-primary
                    strSQL = "UPDATE CustomerPhoneNumbers " & _
                        "SET Primary = FALSE WHERE CustomerID = " & Me.CustomerID & _
                        " And PhoneType = ""LandLine"""
                    Set dbs = CurrentDb
                    dbs.Execute strSQL, dbFailOnError
                    Me.Refresh
                Else
                    Cancel = True
                End If
            End If
        End If

    Exit_Here:
        Exit Sub

    Err_Handler:
        MsgBox Err.Description, vbExclamation, "Error"
        Resume Exit_Here

    In the BeforeUpdate event procedure of the same subform a number is automatically made primary if it is the first to be entered for the customer in question, with the following code:

        On Error GoTo Err_Handler

        Dim strCriteria As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And PhoneType = ""LandLine"""

        ' make this primary landline number if none already exists
        ' for this customer
        If IsNull(DLookup("CustomerID", "CustomerPhoneNumbers", strCriteria)) Then
            Me.Primary = True
        End If

    Exit_Here:
        Exit Sub

    Err_Handler:
        MsgBox Err.Description, vbExclamation, "Error"
        Resume Exit_Here

    The restriction to only one 'primary' number per customer per phone category could also be enforced in the table definition by means of a CHECK CONSTRAINT.

        


    Ken Sheridan, Stafford, England

    Sunday, May 18, 2014 11:08 PM

All replies

  • Hi,

    How about instead of having a "current job" field to just have a StartDate? Then, instead of looking for a Yes in your query, just look for the latest date.

    Just my 2 cents...


    Sunday, May 18, 2014 1:13 PM
  • Hi,

    How about instead of having a "current job" field to just have a StartDate? Then, instead of looking for a Yes in your query, just look for the latest date.

    Just my 2 cents...


    In addition to the DB guy, I use in a couple of applications both StrtDate and EndDate. An empty EndData means then the "current job".

    The advantage is that you have information on the time that someone had a job, but also on the time where that person had no job. Moreover, you can have more than one (parttime) current job.

    Imb.

    Sunday, May 18, 2014 2:16 PM
  • I think that the better option would be to set up a macro so that when a new job is entered, a window appears that asks the user whether or not it is the current job.  If the user clicks yes, then the macro would be activated and a certain function would take the desired fields from the Job Placement table and enter them into fields in the Active Clients table.

    I would advise strongly against such an approach.  It would create substantial redundancy, with the consequent risk of update anomalies.  In most situations I would adopt the approach used by Imb whereby a Null EndDate column indicates that a job is current, but I have adopted an approach similar to your CurrentJob Boolean column in other non-date contexts.  For example in one case I used a Boolean 'Primary' column to indicate a customer's main phone number of each category, and checked in the BeforeUpdate of a check box bound to this column in the relevant subform, to ensure that only one number is the primary one.  The code for the landline category for instance is:

        On Error GoTo Err_Handler

        Const conMESSAGE = _
            "Another number is set as primary landline " & _
            "number for this customer.  Do you wish to replace " & _
            "it with this number?"

        Dim dbs As DAO.Database
        Dim strCriteria As String
        Dim strSQL As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And PhoneType = ""LandLine"" And Primary = True"

        ' get user confirmation to change primary landline
        ' number for this customer
        If Me.Primary Then
            If Not IsNull(DLookup("CustomerID", "CustomerPhoneNumbers", strCriteria)) Then
                If MsgBox(conMESSAGE, vbQuestion + vbYesNo, "Warning") = vbYes Then
                    ' set all other landline numbers for this customer to non-primary
                    strSQL = "UPDATE CustomerPhoneNumbers " & _
                        "SET Primary = FALSE WHERE CustomerID = " & Me.CustomerID & _
                        " And PhoneType = ""LandLine"""
                    Set dbs = CurrentDb
                    dbs.Execute strSQL, dbFailOnError
                    Me.Refresh
                Else
                    Cancel = True
                End If
            End If
        End If

    Exit_Here:
        Exit Sub

    Err_Handler:
        MsgBox Err.Description, vbExclamation, "Error"
        Resume Exit_Here

    In the BeforeUpdate event procedure of the same subform a number is automatically made primary if it is the first to be entered for the customer in question, with the following code:

        On Error GoTo Err_Handler

        Dim strCriteria As String

        strCriteria = "CustomerID = " & Me.CustomerID & _
            " And PhoneType = ""LandLine"""

        ' make this primary landline number if none already exists
        ' for this customer
        If IsNull(DLookup("CustomerID", "CustomerPhoneNumbers", strCriteria)) Then
            Me.Primary = True
        End If

    Exit_Here:
        Exit Sub

    Err_Handler:
        MsgBox Err.Description, vbExclamation, "Error"
        Resume Exit_Here

    The restriction to only one 'primary' number per customer per phone category could also be enforced in the table definition by means of a CHECK CONSTRAINT.

        


    Ken Sheridan, Stafford, England

    Sunday, May 18, 2014 11:08 PM