Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2011
    Posts
    19

    Unanswered: Aauto fill bassed on 2 other fields

    Hi Everyone,

    I'm trying to have a field autofill based on the data in 2 other fields, I'm fairly new to access so if you need more info don't be shy.

    I have 3 tables

    Table 1
    Name: (Region)
    Field: "Region Name" [key field]


    Table 2
    Name: (Tax)
    Fields: "Id" (autonumber) [key field], "Region" (relation ship to table 1), "Tax Rate", "Tax Type", "Start date", "End Date"

    Table 3
    Name: (Invoices)
    Fields: "Id" (autonumber) [key field], "Region" (relationship to table 1), "Delivery Date"... and several others

    I have a form linked to table 3 for the invoice entry. For display purposes, I want to have a field display the tax rate in order for the system to also calculate taxes. Normally I'd do it with a =combo#.column(#) but I can't do it that simply.

    Here's the problem.

    In recent years we've had several tax modifications. The database already contains lots of historical data. So table 1 contains the name of 12 regions, but each region has had 3-5 tax rates over the last decades, those are detailed in table 2 and include an effective date and end date for that rate. To do the data entry I'd like the user to be able to see the tax rate used by the system and the amount. The amount isn't a problem but to get the rate out I need an autofill field that looks at the "region" and the then looks at the date to find the correct line to call up.

    While I can reason the process, I can’t figure how to write the formula or query, HELP!

    Anthony

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I guess that the row to be retrieved is the one with the most recent [Start date] for a given Region. If it's the case, you can build a dynamic query:
    Code:
    Private Sub Form_Current()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT TOP 1 Tax.* " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' " & _
                 "ORDER BY Tax.[Start date] DESC;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.EOF Then Me.Text_TaxRate = rst![Tax Rate]
        rst.Close
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    19
    Unfortunately that's not the case, this database gets populated several times a year (5000record/year). What happens is the regional offices forward their invoices to the head office, in some case it takes them a very long time (3-6 months)to do so causing the end user to have to go back to the previous tax codes (and in some cases even 2 previous). I thought since the invoice has a field for the "delivery date" that I could have the query look that dates falls with in what tax rate, this complicates things but ensure that the correct rate can be selected.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then change the SQL statement to:
    Code:
        strSQL = "SELECT TOP 1 Tax.* " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                 "Tax.[Start date] >= " & Me.[delivery date].Value & " AND " & _
                 "Tax[End Date] <=  " & Me.[delivery date].Value
    You'll perhaps use the Format function to convert the date from [delivery date]; it must be "mm/dd/yyyy", whatever the settings of the configuration pannel of Windows can be. In such a case, use:
    Code:
    ... & Format(Me.[delivery date].Value, "mm/dd/yyyy")...
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    19
    Like I said I'm very new to this, so I want to make sure i do this correctly.

    1. So this should be the code:

    Code:
     
    
    Private Sub Form_Current()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
       strSQL = "SELECT TOP 1 Tax.* " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                 "Tax.[Start date] >= " & Me.[delivery date].Value & " AND " & _
                 "Tax[End Date] <=  " & Me.[delivery date].Value    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.EOF Then Me.Text_TaxRate = rst![Tax Rate]
        rst.Close
        Set rst = Nothing
        
    End Sub
    Correct? If so, where do I add the query (what field and what event?)?

    2. The format I'm using is YYYY/MM/DD, I'm guessing the code is simply:

    Code:
    ... & Format(Me.[delivery date].Value, "yyyy/mm/dd")...
    Can I place that directly at the end of the query? (right after "Set rst = Nothing")?

    Thank you so much for you time Sinndho

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by AnthonyICU2 View Post
    Code:
     
    
    Private Sub Form_Current()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
       strSQL = "SELECT TOP 1 Tax.* " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                 "Tax.[Start date] >= " & Me.[delivery date].Value & " AND " & _
                 "Tax[End Date] <=  " & Me.[delivery date].Value    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.EOF Then Me.Text_TaxRate = rst![Tax Rate]
        rst.Close
        Set rst = Nothing
        
    End Sub
    Correct? If so, where do I add the query (what field and what event?)?
    That seems correct. In my example I put the procedure in the Form_Current event handler because I supposed that the form is bound, so the controls bound to [Region] and [delivery date] are updated automatically.

    If it's not the case, create a function with that code in the Form Module (Private Sub UpdateTaxRate() for instance), and call that function from the event handler for the AfterUpdate events of the controls bound to [Region] and [delivery date]. It should be something like:
    Code:
    Private Sub Region_AfterUpdate()
        UpdateTaxRate
    End Sub
    and
    Code:
    Private Sub delivery_date_AfterUpdate
        UpdateTaxRate
    End Sub
    Pay attention to the fact that I named the controls Tex_Region and Text_TaxRate in my example: you'll have to adapt the code to the real names in your form.

    Note: If [Tax Rate] is the only value you want to extract from the query, you can improve the performance by replacing:
    Code:
    strSQL = "SELECT TOP 1 Tax.* " & _
    with
    Code:
    strSQL = "SELECT TOP 1 Tax.[Tax Rate] " & _
    Quote Originally Posted by AnthonyICU2 View Post
    2. The format I'm using is YYYY/MM/DD, I'm guessing the code is simply:

    Code:
    ... & Format(Me.[delivery date].Value, "yyyy/mm/dd")...
    Can I place that directly at the end of the query? (right after "Set rst = Nothing")?
    No and no:

    1) Whatever can be the format you use for the dates, Access will only accept the mm/dd/yyyy format for a query used in VBA.

    2) The format() function must be used in the SQL statement:
    Code:
       strSQL = "SELECT TOP 1 Tax.* " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                 "Tax.[Start date] >= " & Format(Me.[delivery date].Value, "mm/dd/yyyy") & " AND " & _
                 "Tax[End Date] <=  " & Format(Me.[delivery date].Value,"mm/dd/yyyy)
    You're welcome by the way!
    Have a nice day!

  7. #7
    Join Date
    Oct 2011
    Posts
    19
    I think I did everything you mentioned but I can't get it to run.

    I attached a stripped down version of the database, is there any chance you could have a look and let me know where I went wrong.
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. the TextBox Text_Region is not bound to any source of data and so it is always null or empty.

    2. There is a dot missing in the expression:
    Code:
    "Tax[End Date] >=  " & Format(Me.[Delivery Date].Value, "yyyy/mm/dd")
    It should be:
    Code:
    "Tax.[End Date] >=  ...
    3. The actual name of the column in the table Tax is "TaxRate" not "Tax Rate".

    3. You keep using the "yyyy/mm/dd" format: this cannot work. The Jet Engine (the SQL interpreter used by Access) only understands date values in the "mm/dd/yyyy" format. Assigning an input mask for the columns [Start Date] and [End Date] in the table definition won't change that.

    4. Delimiters for Date/Time values (#) are missing (my fault, sorry!).

    All together, the instruction that assembles the SQL query should be:
    Code:
       strSQL = "SELECT Tax.TaxRate " & _
                 "FROM Tax " & _
                 "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                 "Tax.[Start date] <= #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "# AND " & _
                 "Tax.[End Date] >=  #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "#"
    and you still need to provide a value for the Text_Region TextBox control (probably from the ComboBox Combo56).
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    19
    TY so much!!!

    Still having some minor issues

    1. Ok My bad I had pointed it to the "tax type" field not the region... can you confirm in your first message you had written "Tex_Region" instead of "Text_Region" am I write in assuming that it should have been "text"?

    2. Corrected

    3. Corrected

    3. I'm trying to convert the date to the other format, I copied the column to excel using a text to column function (on the "/") I broke them back apart and then concatenated them in the YYYY/MM/DD format.

    I started with:
    2009/06/10

    Converted to that:
    06/10/2009

    When I paste the converted in the to that column
    it converts it back to YYYY/MM/DD, creating a new field with out enforcing an input mask and it automatically goes to the YYYY/MM/DD format. Any Idea? (I attached the file take a look at the program information table both fields are there)

    *I know that I need to do the same to the other dates I just started with that one



    4. Corrected
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There was indeed a typo: my intention was to write Text_Region. It's a habit I have to prefix the controls on a form to distinguish between a control and its bound column in the Recordset ( Region is the name of the column (Field) in the Recordset, while Text_Region is the name of the TextBox control that is bound to Region).

    2. Don't try to convert anything about dates (least with Excel!). Simply use the instruction that assembles the SQL query as it is:
    Code:
    strSQL = "SELECT Tax.TaxRate " & _ ...
    There is a difference among:
    a) The display format of a date, that can vary according to the International settings in the Windows control pannel and also to a specific format you specify.
    b) The way VBA and the Jet Engine "understand" a date value ("mm/dd/yyyy" exclusively).
    c) The internal format that is used by Access (and by VBA) to store a date value: it's a floating point number where the integer part represents the number of days elapsed from a reference date (12/31/1899 if my memory is right) and the decimal part representing the number of seconds elapsed since midnight.

    I'm rather busy at the moment but I'll have a look at your database later on.
    Have a nice day!

  11. #11
    Join Date
    Oct 2011
    Posts
    19
    Thanks for the fast reply and the great explanation for how SQL works with the dates.

    Perfect the code now seams to return the percentage.

    I added this:

    Code:
        If Not rst.EOF Then Me.TaxType = rst![Tax Type]
    and now it's also returning the "Tax Type"

    but if you click on the create new record button; it crashes.
    at the

    Code:
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    Attached is an update version of the file.
    Attached Files Attached Files
    Last edited by AnthonyICU2; 10-21-11 at 15:25.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To prevent the error from occurring when a new record is created, change the Form_Current procedure like this:
    Code:
    Private Sub Form_Current()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
       If Not IsNull(Me.Tex_Region.Value) Then
           strSQL = "SELECT TOP 1 Tax.* " & _
                     "FROM Tax " & _
                     "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                     "Tax.[Start date] <= #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "# AND " & _
                     "Tax.[End Date] >=  #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "#"
        
            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF Then Me.Text_TaxRate = rst![Tax Rate]
            rst.Close
            Set rst = Nothing
        Else
           Me.Text_TaxRate = Null
        End If
        
    End Sub
    Have a nice day!

  13. #13
    Join Date
    Oct 2011
    Posts
    19
    I hope I'm not too annoying.

    That fixed the new record error.

    When entering a new invoice it doesn't run the query when the user selects/enters values in the region and date fields.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, you're welcome!

    Change the code in the Form Module, like this:
    Code:
    Private Sub Form_Current()
    
        ComputeTaxRate
        
    End Sub
    
    Private Sub Delivery_Date_AfterUpdate()
    
        ComputeTaxRate
        
    End Sub
    
    Private Sub Tex_Region_AfterUpdate()
    
        ComputeTaxRate
        
    End Sub
    
    Sub ComputeTaxRate()
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
       If IsNull(Me.Tex_Region.Value) = False And IsNull(Me.[Delivery Date].Value) = False Then
           strSQL = "SELECT TOP 1 Tax.* " & _
                     "FROM Tax " & _
                     "WHERE Tax.Region = '" & Nz(Me.Tex_Region.Value, "") & "' AND " & _
                     "Tax.[Start date] <= #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "# AND " & _
                     "Tax.[End Date] >=  #" & Format(Me.[Delivery Date].Value, "mm/dd/yyyy") & "#"
        
            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF Then Me.Text_TaxRate = rst![Tax Rate]
            rst.Close
            Set rst = Nothing
        Else
           Me.Text_TaxRate = Null
        End If
    
    End Sub
    As usual, check the accuracy of the control names.
    Have a nice day!

  15. #15
    Join Date
    Oct 2011
    Posts
    19
    THANK YOU SOOOOOO MUCH....

    Everything looks perfect now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •