Help with template (1 Viewer)

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
Thanks Pat Hartman for the response.

For example, condition A1 mentions three possibilities but doesn't specify what to do for any of them.
My goal is: When the user captures information on the Form1, it would lookup in this table and display the result on the form in field (NR1)

To get a record from the table, the logic needs to look at some fields and come up with one of three values.
This is exactly what i am trying to explain you which you have already explained me back.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:21
Joined
May 7, 2009
Messages
19,169
mr. mahesh,

test and check.
 

Attachments

  • VoucherTemplate_3.zip
    35.1 KB · Views: 116

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
Thanks Dear arnelgp for this.
I checked for the 4 instances it works fine and picks the correct logic. However when the instance is zero and SRSubSubType is "Post dated cheque cleared", It picked RuleId 1.
I will have a scenario where the AdjustedAmount even though is zero the claim is Short.
Can this be hard coded in a way that I dont go the code and only refer to table tbl_Rules which is my main logic.

Sorry couldn't send in detail sending through my mobile. Will try to clarify by evening.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:21
Joined
May 7, 2009
Messages
19,169
check tbl_srtype, I added some records. check them and modify.
 

Attachments

  • VoucherTemplate_3.zip
    36.6 KB · Views: 129

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
HI! Arnelgp,

I have changed the below logic
Code:
        Me.Claim.Value = Switch(z > 0, [B][COLOR="Blue"]"Excess"[/COLOR][/B], z = 0, "Cleared", True, [B][COLOR="blue"]"Short"[/COLOR][/B])
To
Code:
        Me.Claim.Value = Switch(z > 0, "Short", z = 0, "Cleared", True, "Excess")
i tried to do add new record on Form1 with the below combination:

SRSubType : IN Clearing
SRSubSubType : Postdated cheque cleared
ChequeAmount : 4000
ClaimedAmount : 4000
AdjustedAmount : 0

Below is the result :
RuleId : 0
Claim : Cleared

Below is the error :
Error#94 " Invalid use of Null"
Debug highlights on " Me.RulesID = CLng(Split(var, "|")(0))

i have attached error snapshot
 

Attachments

  • Image2.png
    Image2.png
    70 KB · Views: 137

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:21
Joined
May 7, 2009
Messages
19,169
test and test, let me know the errors.
 

Attachments

  • VoucherTemplate_3.zip
    37.1 KB · Views: 126

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
HI! still getting the same error. I have attached your last version. Once you open the db it starts with error.

When SRSubSubType is "Postdated cheque cleared", AdjustedAmount is always Zero.

The last row in tbl_Incident, RuleId is Zero. It should pick RuleId is 11
 

Attachments

  • VoucherTemplate_3c.mdb
    480 KB · Views: 124

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
HI! Arnelgp, applogies, i figured out the issue it was not in the coding, but the data in SRSubSubType and Rule2 in both tbl_SRType & tbl_Rules respectively were not matching. Fixed the matching names and it works perfectly.
How can i put error handler with a message to know what the error is.

I will check rest of the logics to see if there is any more issues.
Will check and revert.
Thanks
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
HI! arnelgp, i have checked the logic and it gives me perfect result in field NR1 on the Form1.

Thank you for the code and helping me to get my project on track.

Was getting error,#94 "Invalid use of Null", when selecting SRSubSubType so disabled the below highlighted.
Code:
Private Sub cboSRSubSubType_AfterUpdate()
    If InStr(1, Me.cboSRSubSubType & "", "Cleared") > 0 Then Me.Claim = "Cleared"
'    [B]Call ClaimedAmount_AfterUpdate[/B]
End Sub
Will this create a issue.

NR1 in query1 does not show the same result. How can i get the result same as on Form1 to export this in excel.

thanks
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
HI All
Hope you are all fine and in the pink of health
The reason to initiate this thread after a gap of 18 months is that very soon my organisation will be discounting MS Access in few weeks time.
Well, i dont have any control of them and the day they stop Access it will be disaster for me and my project.

Is this possible to make the same template work in excel instead of Access.

@arnelgp, you have given me the solution on Access and it was working beautifully.
Will it be possible to do the same in Excel instead of Access.

Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:21
Joined
Feb 19, 2002
Messages
42,978
What a stupid decision your company is making. What do they expect you do do?

BTW. arnel was very kind to write all that code for you for FREE. Now you are asking him to recreate it in Excel and you are getting paid for it. Isn't that a bit much? And all because your company made a stupid decision. Why not recommend that they hire arnel to do the job for your?
 

June7

AWF VIP
Local time
Today, 15:21
Joined
Mar 9, 2014
Messages
5,423
Replicating code in Excel would be a PITA. I once eliminated Excel used as frontend interface to Access db. Took months to get forms and reports built and Access VBA written. Going the other way is likely worse. And then Excel as database is just insane.

What do they want instead - SQLServerExpress, MySQL, Oracle, SQLServer? First 2 are freebies but if you can't code with Access VBA will have to manage with Excel VBA or VB.net or whatever.

Why are they eliminating Access? Access RunTime is free then users don't need full Access. Only you, the developer, would need.
 
Last edited:

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
Thank you Pat and June7 for your response, Yes it sounds stupid that they will be going to remove Access from the org. Well thats their decision.
BTW. arnel was very kind to write all that code for you for FREE. Now you are asking him to recreate it in Excel and you are getting paid for it. Isn't that a bit much? And all because your company made a stupid decision. Why not recommend that they hire arnel to do the job for your?
Yes Arnel was very kind and he is the only person on this forum who has helped me and others on this forum to get their projects completed.
I just mentioned it was Arnel contribution that helped me get my project completed and it was very kind of him.

Why are they eliminating Access? Access RunTime is free then users don't need full Access. Only you, the developer, would need.
HI June, i am not sure why they are giving up Access, probably org internal decision. i did say i have huge dependency on Access but it was to deaf ears.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:21
Joined
Feb 19, 2002
Messages
42,978
and he is the only person on this forum who has helped me and others on this forum to get their projects completed.
Really?
i did say i have huge dependency on Access but it was to deaf ears.
You can still use the Access RunTime (free) even if they won't include Access in your Office Suite. Of course, you won't be able to build anything at work but you can still run your existing apps.
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
Yes, very much Pat, i have such high regards to Mr. Arnelgp.
anyways, thanks for your time in responding to my post.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:21
Joined
Feb 19, 2002
Messages
42,978
We all have high regards for Mr. Arnelgp but he is far from the only expert here helping people. He is probably the only real expert who will do your job for you though. Since the help you want is free labor, perhaps he will help you again.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:21
Joined
May 21, 2018
Messages
8,463
Yeah I was going to say something. Maybe you forgot this thread.
I learned a lot doing this so I did not mind, but would say I went way beyond what you would find on almost any forum. That was a full application.
You have 677 threads and a ton of different people who have given you a lot of help.

Anyways, I do think you could convert that application to Excel since it is pretty basic. But it would require much more work than doing in Access.
 
Last edited:

Minty

AWF VIP
Local time
Today, 23:21
Joined
Jul 26, 2013
Messages
10,355
@lookforsmt I suspect someone in the higher management doesn't understand how Access works but thinks they know how a spreadsheet works.

What they want to do is the exact opposite of 99.9% of organisations we work with professionally.
Their outdated spreadsheet solutions have become unwieldy, unreliable and no longer fit for purpose. They are losing data, and can't use the spreadsheets as they used to when that only had 3 users and 100 records.

Your company now want to head in the exact opposite direction. Send your manager a copy of this thread.
I'm not anti-Excel, we produce and maintain some very complex Excel solutions, but there is a time and a place and a workflow where they work, I doubt yours is one.

Good luck.
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:21
Joined
Dec 26, 2011
Messages
672
Dear Pat, again i am saying and i am not just praising for getting anything in return as you say free labor from Mr. Arnelgp and neither do i want to react to your statement.
Also, i don't mean to hurt the others who have equally helped me a lot too and take away the credit from them.

Dear MajP,
I learned a lot doing this so I did not mind, but would say I went way beyond what you would find on almost any forum. That was a full application.
You have 677 threads and a ton of different people who have given you a lot of help.
i did not mean to offend you by any means, the thread that you put up was Not possible without your help. I still remember i was awake late hours waiting for your help and you went out of the way to provide the HELP. It was simply great of you

I don't want to differentiate between all you' lovely people here who have helped me learn few tricks of coding, although i must admit i am still have very poor knowledge in coding.

Dear Minty, i would love to forward this thread.. but...i have my own reasons.
Thanks anyway.
 

Users who are viewing this thread

Top Bottom