Help with Variant (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
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

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

Banishment Pending
Local time
Today, 11:29
Joined
May 11, 2011
Messages
11,611
A. You could use arrays and then loop through them:



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:


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

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
see this code:
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

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
Thanks Arnelgp for the revised code,

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

thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
yes you can.
 

lookforsmt

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
Thanks Arnlegp one more issue

how do i put the below code in two lines
Code:
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

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

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
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

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
There is no query2 in the example. What column are you talking about?
 

lookforsmt

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
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
 

Attachments

  • VoucherTemplate_3c_1.zip
    41.2 KB · Views: 216

lookforsmt

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
wanted to mention the columns names: DR1 to DR4 and CR1 to CR4 and RD1 to R4
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

Banishment Pending
Local time
Today, 11:29
Joined
May 11, 2011
Messages
11,611
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

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
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.
 

Minty

AWF VIP
Local time
Today, 16:29
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 20:29
Joined
Dec 26, 2011
Messages
672
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
 

Users who are viewing this thread

Top Bottom