Tough VBA problem, that needs tracking

[DanKay1]

Table
ID Name IssueDate Amount Kept GivenAway
01 John 2002 2000
02 Bryan 2002 3000
01 John 2001 2000
01 John 2003 3000


Guys, I need help with VBA. I need to fill in Kept and GivenAway fields which are initially empty. The criteria is to sort it by IssueDate and keep first 3000 per person give away next 3000. The most can be given away is 3000 everything after that should be kept. I have an irratative solution that puts in array each person, but its really slow. Do you think you can come up with a better solution from your experience. Thanks in advance.

ID Name IssueDate Amount Kept GivenAway
01 John 2002 2000 1000 1000
02 Bryan 2002 3000 3000 0
01 John 2001 2000 2000 0
01 John 2003 3000 1000 2000

 

[BNPMike]

I don't understand this at all. Your first table doesn't seem to add anything to the discussion compared to the second table. In the second table both John and Bryan have reached their 3000 limit so they can't give anything away. So the issue is what happens next.

What is the relevance of sorting by date - if you get to 3000, who cares how?

Presumably you have another table with John annd Bryan and their non-transactional details? Why not just run an SQL query that loads their totals into a total field. Then whenever they try and give away anything you can flag this as an forbidden transaction if they are over their limit.

 

[kjv1611]

Well, for starters, a good way to use this is with a DAO.Recordset . I used this, and fixed up someone at my company for a report that pulled over 60,000 records at a time. It would go something like this:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim intRecordCount as Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblYourTableNameHere")

intRecordCount = 0
With rs
  Do While NOT .EOF
    Conditinoal Code...
      .Fields("Kept") = ~whatever you want to set it to
      .Fields("GivenAway") = ~whatever you want to set it to
    End Conditional Code
    intRecordCount = intRecordCount + 1
    .MoveNext
    
  Loop
[GREEN]'Then you could do a messagebox to show how many records updated for testing:[/GREEN]
  MsgBox intRecordCount & "Total records updated"
End With

Something like that.... hopefully that could get you started. [SMILE]


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Also, if need be, you can dim as many recordsets off the one db as needed:

Dim db as DAO.Database
Dim rs1 as DAO.RecordSet
Dim rs2 as DAO.RecordSet
Set db = CurrentDb
Set rs1 = db.OpenRecordSet("Table1")
Set rs2 = db.OpenRecordSet("Table2")

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[DanKay1]

BNPMike,

The problem is that we need to give away based on the issue date since the amount kept will be multiplied by rate for that specific year. By the agreement we have to keep the first 3000, give away next 3000 and keep the rest.

 

[DanKay1]

kjv1611,
thanks for the basics, but i am having problems with logic itself not syntax.

 

[kjv1611]

Logically speaking, if the 2nd list/table there is supposed to be the actual values, it does not appear to follow your logic? For example, you have John's 2002 amount at 2000, kept 1000 and gave away 1000. Should that not be kept 0, gave away 2000? If the first 3000 is give away, and all else after that is kept? Why does John show diff types of logic for all 3? It doesn't seem to match any one logic? Is there more to it than what you mentioned in first post?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[willir]

Dan

You have moment?

Could this be a design issue that is causing your problem? What specifically are you keeping and giving away? Amount = money?? (If so, how can I get on the recieving line ;-) )

Next, when do you decide if and how much is given away - during data entry, or periodically / monthly?

Next, can a person give away so much the first time, and more the next time?

Last, for now, [tt]
ID Name IssueDate
01 John 2001
01 John 2002
01 John 2003
[/tt]

Is John doing something every year?

Richard

 

[BNPMike]

kjv1611

We note you give a detailed solution then later start to consider the flaws in the original statement of requirement.

Good job you are being ironic. Good job developers never do that for real, at work.

 

[kjv1611]

Well, yeah, I first posted, just to give a solution, based on the logic in the paragraph, then later considered the logic did not seem to line up - kind of off the cuff. [SMILE]. I personally have come a long way in this stuff just in the past 6-8 months, mostly from things I've learned hear from the "experts" in the forums. Still have a long way to go, though.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[BNPMike]

Often all you need is a cardboard programmer. This is the idea that you walkthough your code with another person but often find that you can see your own mistakes just by telling the other person. So the other person can equally well be just a cardboard cut-out. That's me. I have no substance I just ask people exactly what they mean...

Well not entirely true. I'm a made of a special kind of cardboard that dries out if you don't wet it regularly... with beer.