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.

 

[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

 

[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.

 

[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

 

[lookforsmt]

Thanks dear Pat Hartman for your response, yes i did mention another query of copying the data on another form.
Coming to the earlier question on my db. How can i do it differently, if you can give me a clue i can work towards it. It will better to delay the project than to take the wrong path.
thanks again for the reply.

 

[lookforsmt]

Thanks Pat Hartman, i have no clue on coding. Well i got the required support from this forum so came this far.
If you can give me the start code, probably that could help me to move forward.

 

[lookforsmt]

Thanks Pat Hartman, i have made it clear it don't have any idea in coding. From last 2-3 post i have been trying to say the same thing and you are trying to say the same thing again.
Anyway thanks for your time. I will check here if anyone else can give me the clue to give me a start of what you are talking.

 

[plog]

Let's take a step back and tell us about the big picture. Give me 2 paragraphs:

1. Explain what it is your organization does. No database jargon allowed.
2. Explain what this database will assist you in achieving. A little database jargon allowed, but discouraged.

The big issue we have is tbl_Rules. You've essentially used it to store code. Most likely a lot of that should be in either in stand alone Module or in the VBA code that uses it.

For example, suppose you wanted to compile a birthday message to a customer that included how long they were a customer and use their gender (or sex, or birth designation or whatever its called in 2020). You could use your method (a table that tells a function how to compose that message) or you could just build a function that does it for you:

function get_BirthdayMessage(in_DOB, in_Gender, in_YearsCustomer)
' generates birthday message based on customer attributes

msg="Congratulations Birthday "
' start of message

Iif(in_Gender=="M") Then msg = msg & "Boy" Else msg = msg & "Girl"
' adds gender to it

msg=". Thanks for being a customer for "
...
...

get_BirthdayMessage = msg

All the code and logic is in that function and you pass it the data it needs. You're method does that clunkily in tbl_Rules and makes it hard for a human to follow it. Looking at the field [N2] that should be done in the above manner, instead of relying on a field to house the logic, you but it in a module and passes it the data it needs.