Access Q&A: Left, Running Total, Register

Access Q&A: Left, Running Total, Register
By Richard Rost

17 years ago

More Access questions I've answered recently:

I can't remember what to type in to get the last three numbers from one field containing 9 numbers. I thought you typed X:(Right,6,[file name]), but when I do this it acts as the right is a field name as well. What do I need to do??

Try this:

NewField: Right(FieldName,3)

I cover Left(), Right(), and Mid() in my Access 309 class:

www.599cd.com?GOAC309

copying data a field in one record to a different field in the next record: In my database I have a form Named account info. I have a subform for transactions. (Each transaction is a record) In the subform I would like a running balance. How do I copy the data in a field called new balance to a field (balance) in the next record.

Well, running balances are so much easier in reports than in forms. Do you really need to see a running balance on each line of your subform, or is a single SUM at the bottom of the subform OK? If you can deal with just a sum at the bottom, just put an unbound text box in the footer of your subform with the control source of =SUM([FieldName]) in it. I cover how to do this in my Access 301 class (www.599cd.com?GOAC301).

Now, forms don't behave the same way that reports do. It's easy to create a running total in a report. But you'd need some pretty complex coding to get a form to behave that way. Now, to do what you want to do - to simply copy a value from one record to the next, wouldn't be that hard.

What I would do would be to use DMAX and DLOOKUP and get the value from the previous record. Say you're dealing with an order table, and you want to get the LineItemTotal from the last record (which would be the record with the greatest OrderDetailID less than the current record's OrderDetailID)... you would say:

LastOrderID = DMAX("OrderDetailID","OrderDetailT","OrderDetailID < " & OrderDetailID)
LineItemTotal = DLOOKUP("LineItemTotal","OrderDetailT","OrderDetailID=" & LastOrderID)

In English, this basically says, "look up the largest OrderDetailID from the OrderDetail table where the OrderDetailID is less than the ID from the current record, and put that in a variable called LastOrderID." That will get you the ID for the previous record.

Then, "look up the LineItemTotal from the OrderDetail table where the ID equals the ID we just got from DMAX. Put that value in the LineItemTotal for THIS field."

Now, put that code in an event for your new record - probably in the BeforeInsert field (but you might need to play with it to figure out which event best runs it) and that should do it.

I cover DLOOKUP in detail in my Access 302 class (www.599cd.com?GOAC302). DMAX is covered in Access 306 (www.599cd.com?GOAC306).

It's not easy - which is why I suggest just use a TOTAL on the bottom of the form and save the running total for the report.

Hi Richard. I would like to produce a trial version of an Access application that I'm currently developing and after a period of time, say two weeks, I want the program to require the user to input an "access key" once only in order for him/her to continue use. Whilst I'm fairly competent with Access and can produce professional looking applicaitons, my eperience of using code is very limited, so any help that you can provide would be greatly appreciated. Many thanks for your time

There are lots of different ways you could do this - depending on how secure it needs to be, and how tech-savvy your end users are. The simplest solution would be to make a table that has two fields in it: InstallDate and IsRegistered. When the program is run, read the value of InstallDate from the table. If it's blank, set it. If it's not blank, check to see if the program has been registered. If not, and it's after two weeks, pop up a registration form.

You can read a value from a table in code using the DLOOKUP function. I cover it in detail in my Access 302 class (www.599cd.com?GOAC302). Since you only have the one record in your Settings table, your line would be like this:

MyInstallDate = DLOOKUP("InstallDate","SettingsT")

And that's it. You can look up IsRegistered the same way. Once you know those two things, setting up the logic for your VBA code is really quite easy...

If IsNull(MyInstallDate) Then
SetInstallDate
Else
If Not IsRegistered AND Date() - MyInstallDate > 14 then 'more than 2 weeks
GetRegistration
end if
end if

Now, to SET the registration, you could use a RecordSet or a simple UPDATE query. I would recommend the update query - it's easier. Just build an update query that sets the value of InstallDate to TRUE, save it, and then run that query from your code:

Docmd.OpenQuery "NameOfMyQuery"

If you don't know how to build an update query, see my Access 222 lesson on action queries (www.599cd.com?GOAC222). You can also just grab the SQL of the query and run that from code to...

Docmd.RunSQL "---sql statment here---"

That will handle setting your dates. GetRegistration is up to you... make a form with a password field on it, and check that somehow when the user submits it. Then set the values accordingly.

Geez... I could probably make a whole tutorial JUST on how to do this - and I could probably spend a whole hour on this topic. And of course, the method I'm suggesting is ONLY if your end users don't really know how to use Access... because an experienced Access developer could simply attach to your tables and then set the values themselves. In this case, you'd need to use some kind of encrypted external file, registry keys, or something like that (or I guess you could do it with encrypted values IN your tables... but that's another story).

I hope this points you in the right direction. If you'd like more detailed help on this, see my TechHelp page (www.599cd.com/TechHelp).

Subscribe to Access Q&A: Left, Running Total, Register
Get notifications when this page is updated