Help with Variant

[lookforsmt]

HI! all I hope everyone is safe and sound,

i have the below code on variant, how can i improve the code when i have say 10 variants and and 10 different ControlSource and other logic remains the same. Can anyone help with to provide me a better solution.

I had the below code from my earlier project which i want to amend the code based on the current requirement. Help with Template

Private Sub ClaimedAmount_AfterUpdate()
    Dim x As Double, y As Double, z As Double
    Dim a As Double, b As Double, c As Double

    Dim var As Variant
    Dim var1 As Variant

    If Nz(Me.cboSRSubType, "") = "" Then
        Me.NR1.ControlSource = ""
        
    Else
        x = Me.ChqAmount
        y = Me.ClaimedAmount
        z = x - y
        If Me.Claim & "" <> "Cleared" Then _
        Me.Claim.Value = Switch(z > 0, "Short", z = 0, "Cleared", True, "Excess")
        
        var = DLookup("RuleID & '|' & NR1", "tbl_Rules", "Rule1='" & Me.cboSRSubType & "' And Rule2='" & Me.cboSRSubSubType & "' And  Claim='" & Me.Claim & "'")
        
        Me.RulesID = CLng(Split(var, "|")(0))
        Me.NR1.ControlSource = "=" & Split(var, "|")(1)

    End If

'----------------------------var1----------------------------

    If Nz(Me.cboSRSubType, "") = "" Then
        Me.VR1.ControlSource = ""
        
    Else
        a = Me.ChqAmount
        b = Me.ClaimedAmount
        c = a - b
        If Me.Claim & "" <> "Cleared" Then _
        Me.Claim.Value = Switch(c > 0, "Short", c = 0, "Cleared", True, "Excess")
        
        var1 = DLookup("RuleID & '|' & VR1", "tbl_Rules", "Rule1='" & Me.cboSRSubType & "' And Rule2='" & Me.cboSRSubSubType & "' And  Claim='" & Me.Claim & "'")
        
        Me.RulesID = CLng(Split(var1, "|")(0))
        Me.VR1.ControlSource = "=" & Split(var1, "|")(1)

    End If
End Sub

 

[plog]

A. You could use arrays and then loop through them:

Using arrays (VBA)

Office VBA reference topic

docs.microsoft.com

For Each...Next statement (VBA)

Office VBA reference topic

docs.microsoft.com

B. You could move all the common code to a new function and just pass the function whatever different values it needs for each different set of data:

Function statement (VBA)

Office VBA reference topic

docs.microsoft.com

C. Combination of the 2 things above.

Whichever you choose you are correct in thinking that just copying and pasting code and tweaking each section is the wrong way. Do not repeat code.

 

[lookforsmt]

Thanks plog for the suggestion. My challenge is i am not able to do this due to the limited knowledge on coding. Can you suggest me the start on the code pls.

 

[arnelgp]

see this code:

Private Sub ClaimedAmount_AfterUpdate()
    Dim x As Double, y As Double, z As Double
    Dim a As Double, b As Double, c As Double

    Dim var As Variant
    Dim var_values As Variant

    If Nz(Me.cboSRSubType, "") = "" Then
        Me.NR1.ControlSource = ""
        Me.VR1.ControlSource = ""
       
    Else
        x = Me.ChqAmount
        y = Me.ClaimedAmount
        z = x - y
        If Me.Claim & "" <> "Cleared" Then _
        Me.Claim.Value = Switch(z > 0, "Short", z = 0, "Cleared", True, "Excess")
       
        var = DLookup("RuleID & '|' & NR1 & '|' & VR1", "tbl_Rules", "Rule1='" & Me.cboSRSubType & "' And Rule2='" & Me.cboSRSubSubType & "' And  Claim='" & Me.Claim & "'")
       
        If Not IsNull(var) Then
            var_values = Split(var, "|")
            Me.RulesID = var_values(0)
            Me.NR1.ControlSource = "=" & var_values(1)
            Me.VR1.ControlSource = "=" & var_values(2)
           
        Else
       
            Me.RulesID = Null
            Me.NR1.ControlSource = ""
            Me.VR1.ControlSource = ""
        End If
    End If
End Sub

 

[lookforsmt]

Thanks Arnelgp for the revised code,

can i apply the same logic, if the ContolSource is approx. 10 (NR1; VR1; ....etc.)

thanks

 

[arnelgp]

yes you can.

 

[lookforsmt]

Thanks very much, take care and God bless

 

[lookforsmt]

Thanks Arnlegp one more issue

how do i put the below code in two lines

var = DLookup("RuleID & '|' & NR1 & '|' & VR1", "tbl_Rules", "Rule1='" & Me.cboSRSubType & "' And Rule2='" & Me.cboSRSubSubType & "' And  Claim='" & Me.Claim & "'")

tried doing this, it gives me error

var = "(DLookup("RuleID & '|' & NR1 & '|' & VR1", & _
    And "tbl_Rules", "Rule1='" & Me.cboSRSubType & "' And Rule2='" & Me.cboSRSubSubType & "' And  Claim='" & Me.Claim & "'"))

 

[Gasman]

PMFJI
Do not use the first And on the second line at the beginning. ? The underscore does that.
Also remove the last & on the first line.

 

[Pat Hartman]

1. Creating unnecessary variables simply obfuscates the code.
2. Having to swap the ControlSource on the fly looks like a design issue.
3. Why use true rather than <0? What happens if the difference is other than -1?

 

[Pat Hartman]

There is no query2 in the example. What column are you talking about?

 

[lookforsmt]

sorry, i removed the post, updated older version of my db. updated the new one.
query2 when you run gives the result in control. It displays perfectly on the form. I wanted get the same on the form

 

[lookforsmt]

wanted to mention the columns names: DR1 to DR4 and CR1 to CR4 and RD1 to R4

 

[Pat Hartman]

I admire your ingenuity in figuring out how to reuse one form but I question the logic of the table design that forced the issue.

The only way to make the query work is to write a bunch of functions to retrieve the data from the columns specified by "rules" for each of the columns.

 

[plog]

Agree with Pat. Your database is scary and seriously question the method you've chosen.

First, tbl_Rules isn't normalized. When you start using codes for field names and numerating them as well, its time for a different structure. Second, remember when I told you its good you are thinking about not just copying code? Well, you've seriously just copied code all over the place in tbl_Rules. The N1 field uses the same code in 10 different records, N2 uses the same code in 6 different records, etc. etc.

You may have gotten things to work, but you certainly haven't done it in an efficient manner from either a coding nor a database structuring perspective.

 

[Pat Hartman]

Let's start with - why do you have all these variations on field names? Why isn't a debit a debit? If the answer is that the data is coming from some other system, then you don't need to use the same names as the old application. You can standardize the names when you import the data.

 

[lookforsmt]

Thank you Pat Hartman for looking into this.
Honestly, i am at the last stage of completion and doing any changes at this stage will delay my completion task.
Well i agree that my data is not in standard way and there are lots of inconsistency.
To answer you post#16, i would love to get the table, "tbl_Incident updated in the first instance, since it is not getting updated, thought of copying the data to tbl_Voucher. Perhaps this way i could copy the data.

The rules in tbl_Rules are different scenario and debit or credit is actioned. It will be one time update in the tbl_rules.
User updates the forms with the basic details and voucher based on the rule_Id will do the remaining. I hope i have explained it clearly.

 

[lookforsmt]

HI, any suggestion pls.

 

[Minty]

I suspect that even though you have "made things work" your design is scaring off someone trying to wack the nail in with the cabbage.

 

[lookforsmt]

Thanks Minty for your response.
I understand the db seems to be scary and everyone focus is on the db designs. Well that is the reality.
To change it may take sometime which i can work on later, but right now i have the below on hand which will help me to complete the project.

i want to move the unbound text fields on the form to table tbl_Voucher. is this doable.

thanks