Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
 
 
Access Q&A: Left, Running Total, Register
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   10/25/2007 4:53:24 PM

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

Post a New Comment or Question
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
 

You may want to read these articles from the 599CD News:

8/5/2020Access Custom Sequential Number
8/4/2020Access Restore Deleted AutoNumber
8/1/2020Access Custom Sort Order
7/30/2020Access SharePoint Seminar
7/29/2020Microsoft Access Developer 16
7/29/2020Is There a Free Version of Access?
7/28/2020Access Disallow Editing Data
7/25/2020Access NZ Function NULL to Zero
7/24/2020Access Link Parent Child Same Table
7/23/2020Access OnCurrent Event TechHelp
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search The Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed   
Keywords: access tips password left right mid dlookup dmax registration  PermaLink