Visual Basic Tips #28


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic

----------------------------------------------


*1. FINDING SPECIFIC RECORDS                 
        
When searching for a record by position in an Access file, don't 
depend on the record number. That value will change as you 
manipulate your recordset. Instead, use the AbsolutePosition 
property to move to a specific record. The following is a 
generic example: 

Dim db as Database 
Dim rst as Recordset 

set db = OpenDatabase("Orders.mdb") 
set rst = OpenRecordset("tblCustomers") 

rst.AbsolutePostion = 15 

This method assures success--you'll always return to the 
correct record.


*2. USING BOOKMARK                 
        
In our previous tip, we talked about using the AbsolutePosition 
property to locate a specific record in an Access database. If 
you're returning to a record, you should use the Bookmark 
property. Once you've located the record to which you want to 
return later, set a reference to it by using the statement 

varBookmark = rs.Bookmark 

where varBookmark is a declared Variant. When you're ready to 
return to the referenced record, use the statement 

rst.Bookmark = varBookmark 

where rst represents the recordset you're working with.


*3. TWO TIPS IN ONE                 
        
Service Pack 3 is available for download at Microsoft's site: 

http://www.microsoft.com/msdownload/vstudio/vbdownload.asp?lang=en 

Service Pack 3 corrects several issues regarding controls, 
international versions, and the Package and Deployment Wizard. 
In addition, Service Pack also corrects several general 
protection faults and automation errors. Ample documentation 
is included. 

An added bonus in the Service Pack is a program (and 
documentation) for creating HTML help files. To find this 
program, download and install the Service Pack. Then, run the 
file Htmlhelp.exe to set up the HTML program.


*4. RETRIEVING THE LAST ERROR                 
        
Error-handling is as big a part of your application as the task 
you're coding. Fortunately, VB contains a number of 
error-handling and debugging features. But there's more 
available. You can also use an API call to return the last 
error. First, open a module and add the following declaration: 

Declare Function GetLastError Lib "kernel32" () As Long 

Then, simply call GetLastError as it's needed. The following 
is a simple example, which we added to the same module as 
our declaration: 

Function GLE() 
MsgBox "The last error was " & GetLastError 
End Function 

To execute GLE(), simply enter the statement 

?GLE 

in the Immediate window. Doing so will display a simple message 
box with the text "The last error was" and the appropriate 
error code.


*5. CONVERTING TIME COMPONENTS                 
        
We all know that there are 60 seconds per minute, 60 minutes per 
hour, and 24 hours per day. However, a block of time given in 
those components isn't so easy to work with. For instance, do 
you know what portion of a day 70,000 seconds is? 

The following procedure is fairly generic, but it gives you a 
good idea of how to convert blocks of times (by individual 
components) into HH:MM:SS format. Specifically, this procedure 
converts blocks of seconds. 

Function TimeConvert(value As Long) 
Dim dteDate As Date 
Dim lng As Long 
Dim dbl As Double 

lng = value 
dbl = lng / 86400 
dteDate = dbl 

MsgBox Format(dteDate, "HH:MM:SS") 

End Function 

If you pass this procedure the value 70000, it will return the 
string 19:26:40, which means 70,000 seconds equals 19 hours, 26 
minutes, and 40 seconds. The expression 

dbl = lng / 86400 

divides value (the block of seconds) by 86,400--the number of 
seconds in a 24-hour period. 

To work with minutes, replace the value 86400 in that statement 
with the value 1440--the number of minutes in a 24-hour period. 
If you want, you can even use this with hours by replacing the 
value 86400 with 24. 

The HH:MM:SS format doesn't handle multiple days, so all the 
values you pass this function must be less than or equal to the 
total number of time units (hours, minutes, and seconds) in one 
24-hour period. For instance, there are only 1,440 minutes per 
day. If you're working with days and you pass the function the 
value 1441, it will return the string 00:01:00--the number of 
minutes that extend past a full day. Be careful of the values 
you pass this procedure or include some additional statements 
to catch these values.


*6. API SEARCH TRAP                 
        
Three APIs can search for files: FindFirstFile, FindNextFile, 
and FindClose. If you use one of these functions, be sure to 
close the task--if you don't, you may inadvertently lock some 
files and directories. APIs open a handle to the objects being 
searched, and, in some cases, the operating system won't allow 
you to perform certain tasks with an object while there's an 
active handle.


*7. MOVING FILES                 
        
You can use several methods to move files. If you know the 
exact name and path, there's nothing easier than the Name 
command. The Name function's main purpose is to rename files, 
but you can also use it to move files. For instance, the 
familiar syntax 

Name "C:\August.doc" As C:\September.doc" 

renames August.doc to September.doc. 

However, you can also use this function to move August to 
another folder by using the syntax 

Name "C:\August.doc" As "C:\Complete\August.doc" 

This statement moves August.doc to the \Complete folder. 
Furthermore, you can move and rename a file at the same time 
by using the syntax 

Name "C:\August.doc" As "C:\Complete\September.doc" 

This time, we added \Complete and changed the file's name to 
September.doc--with one statement.


*8. RETURNING THE FIRST AND LAST DAY                 
        
VB has any number of date functions, but DateSerial probably 
gets the most action. In this tip, we'll show you two easy 
expressions that return the first and the last day of a month, 
when you supply a date. This first procedure will return the 
first day. 

Function FirstDay(dteDate As Variant) As Variant 
FirstDay = DateSerial(Year(dteDate), Month(dteDate), 1) 
End Function 

The DateSerial() function relies on three arguments--year, 
month, and day--to create a serial value. We'll pass the 
function a delimited date string and the DateSerial() 
function. The Year() and Month() functions will return the 
first two arguments, the year and month. The date argument will 
always be the value 1, so no matter what date we pass the 
function, it will always return the first day of that month. 
For instance, if you enter in the Immediate window the statement 

?FirstDay(#8/15/99#) 

the function will return the date 8/1/99. 

To return the last day of the month, use the procedure 

Function LastDay(dteDate As Variant) As Variant 
LastDay = DateSerial(Year(dteDate), Month(dteDate) + 1, 0) 
End Function 

This procedure is similar to the previous one. However, we add 1 
to the month. The 0 value as the day argument is what makes this 
procedure work. Obviously, there's no 0 day in any month. By 
specifying this value, we force DateSerial() to return the day 
before the first day, which is always going to be the last day 
of the previous month.


*9. COMMENTING CODE                 
        
We all agree that commenting our code is a good idea. However, if 
you find yourself writing elaborate explanations for your code, 
you might need to rethink your code. Of course, sometimes 
complicated code is your only recourse, but it certainly 
shouldn't be your habit. First, consider the time you'll waste 
later trying to figure out what you did and why. Even with the 
comments, you may still have a difficult time. Someone following 
behind you may not be able to decipher your work at all. Second, 
complicated or tricky code has a way of causing unforeseen 
errors that are difficult to debug. In the long run, keep your 
code simple and direct. That way, you'll need fewer comments 
and fewer aspirin when you return to the code months later 
for updates.


*10. PRINTING ACCESS REPORTS                 
        
Many developers use Crystal Reports, but if your database is 
Access, use it. Doing so is convenient and easy. The following 
code will print the specified report. Of course, you can pass 
the database and the report name as arguments instead of 
hardcoding them into the procedure. 

Dim app As Access.Application 
Set app = New Access.Application 

app.OpenCurrentDatabase("c:\folder\database.mdb") 
app.DoCmd.OpenReport "reportyouwanttoprint" 
app.CloseCurrentDatabase 

You must reference the Access Object Library for this to work, 
but if you're working with an Access MDB file, chances are you 
already have. To check, pull down the Project menu and choose 
References, MS Access 8.0 Object Library (or 9.0 if you're 
running Office or Access 2000). If not, be sure to add 
that library.
