Help with template

[lookforsmt]

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]

mr. mahesh,

test and check.

 

[lookforsmt]

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]

check tbl_srtype, I added some records. check them and modify.

 

[lookforsmt]

HI! Arnelgp,

I have changed the below logic

        Me.Claim.Value = Switch(z > 0, [B][COLOR="Blue"]"Excess"[/COLOR][/B], z = 0, "Cleared", True, [B][COLOR="blue"]"Short"[/COLOR][/B])

To

        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

 

[arnelgp]

test and test, let me know the errors.

 

[lookforsmt]

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

 

[lookforsmt]

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]

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.

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]

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]

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]

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.

 

[lookforsmt]

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]

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]

Yes, very much Pat, i have such high regards to Mr. Arnelgp.
anyways, thanks for your time in responding to my post.

 

[Pat Hartman]

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]

Yeah I was going to say something. Maybe you forgot this thread.

Help with text file trim and create 2 tables

Hi! all, I have a unique problem, not sure if there is any solution for this. I have a account statement in txt format which runs in quite number of pages, with plenty of spaces and empty rows but has a standard format across all pages. What i am looking is to get the Output result (refer...

www.access-programmers.co.uk

Help to import text file of multiple formats

HI! i have similar difficulty to import text file to access. The look of the report is different from the last time i had imported a text file. i am wondering if there is a way to capture the text details and provide a vba code which can be used for any type of text file imports. Cause, i...

www.access-programmers.co.uk

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.

 

[Minty]

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

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.