The Golden Rule
Create a backup of your database each time you are about to change something in the table structure. Never experiment on your actual database. Always use a test database.
Switchboard Manager
You have just created all of your tables, queries, forms and reports and now what... You need to create a switchboard to give your user easy access to your forms and reports, open your database and go to: Tools > Database Utilities > Switchboard Manager > New (or Edit), that will take you to the "Edit Switchboard Item"... Keep in mind that it only allows 8 commands... You can also create your own Switchboard form from scratch, that will give you more flexibility as of what control you can use, such as Hyperlinks.You need full access to an Access database that has some features disable…
Hold down the Shift key and open the database, that will give you full access. If you are the administrator and you want to enable or disable available features on startup you can go to Tools > Startup...
You have three fields for example FirstName, MiddleName and LastName (or Address, State, Zip) you want to combine them as one on your report, form or query… Don’t panic… it’s easy:
Move to "Add New Record" after form is loaded (place code in the On Load event of the form):
RunCommand acCmdRecordsGoToNew
End If
Move to "Last Record" after form is loaded (place code in the On Load event of the form):
If .EOF = False And .BOF = False Then .MoveLast
End With
Move from First to Last Record on Close, but there must not be anything in the Before Or After Update Events, or you will receive an error message (place code in the On Close event of the form):
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
Me.Bookmark = rs.Bookmark
'Your Optional Code goes here
Set rs = Nothing
End Sub
Function to call a Subprocedure. Access will not allow you to use a macro to call a subprocedure, so you use a function a middle man, so the macro will call the function and the function will call the subprocedure:
If the query return a value do this… :
DoCmd.OpenReport "rptSample", acViewNormal
To change letters from lower case to upper case, place this piece of code in the After Update event of the text box (Replace txtSample with your actual control):
txtSample = UCase(txtSample)
To change letters from lower case to upper case, place this piece of code in the On Key Press event of the text box (This is more efficient than the previous method):
KeyAscii = Asc(UCase(Chr(KeyAscii)))
How can I use DoCmd.OpenQuery and not have it prompt that it is performing an
append or delete? I would like it to perform the operation without giving
me the warnings and just do it.
you need to set the Warning off before running the query:
DoCmd.SetWarnings False
' Your code goes here
DoCmd.SetWarnings True
Outlook Email
If you have a procedure in Access to automatically send email using Outlook or Outlook Express, you will be prompting to click on Yes every time, this is due to security features in Outlook. You need to download a free copy of "Click Yes" to automatically click yes for you:
http://www.snapfiles.com/get/clickyes.html
Common Query error message and solution:
"You tried to execute a query that does not include the specified expression as part of an aggregate function. (Error 3122)"
In the design view of the query you have to select "Group By " from the Totals drop down menu for whatever field Access is referring to in Update Query to trim:
To trim a field from unneeded data, for example you stored user ID as S000 and you want to get rid of the "S", here's the standard update query:
UPDATE tblSample SET tblSample.ID = Trim(Mid(Trim(ID),2)) WHERE
Left(Trim(ID),1)="S";
Let's say you have a Memo field in your database that you would like to update but keep the old data. You quickly realize that and update statement will overwrite whatever was in your field. To keep your old data and add new data to the field, you have to add the existing field in your Update statement.... SET tblSample.Memo = tblSample.Memo & ';...... that will allow you to keep your existing memo and add the new Memo preceding by a semicolon:
UPDATE tblSample SET tblSample.Memo = tblSample.Memo & '; " & Me!txtMemo & "' WHERE tblSample.ID = 123
Update multiple fields at once:
To update multiple fields at once you have to use OR in your WHERE clause:
UPDATE tblSample SET tblSample.Memo = tblSample.Memo & '; " & Me!txtMemo & "' WHERE tblSample.ID = 123 Or tblSample.ID = 555
Having Issues with Autonumber, for example you mistakenly change the field type from Autonumber to number. Big mistake!!!! or the designer before you use text type instead of Autonumber, here's how to take care of it according to Allen Browne, that just saved me a lot of time:
1. In the database window, copy the table to clipboard, and paste it back
with another name. Select the option to copy the structure only (not "data
and structure").
2. Open the new copy in Design view, and delete the number field. Save.
3. Still in design view, create a new autonumber field. Save and close.
4. Create a new query, based on the table that has the data.
5. Change the query to an Append query (Append on Query menu).
6. Drag the fields into the output grid, and map them. If they have the
same name and data type, Access will map them for you.
Provided your Number field is unique, step 6 will permit you to map the
existing numbers to the AutoNumber field, effectively maintaining the
numbers for the existing data, yet ending up with an AutoNumber field.
How to use ErrorHandler to trap error messages:
To make your application more user friendly, it's good practice to use custom ErrorHandler to display descriptive error messages to the end user instead of non-descriptive numbers, one way to do this is to use a Select Case. During testing of your application make a note of all the error messages and their respective code and translate that into plain English. ErrorHandler can be used in both Functions and Procedures. In the following example, I trapped two access error messages, 3075 and 3129, and I tell the user what needs to be done:
Private Sub Sample()
On Error GoTo ErrorHandler
'Your code goes here
ExitHere:
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3075
MsgBox Err.Number & vbCrLf & " Please select at least one record...", vbOKOnly + vbCritical, "Microsoft Access Error Message"
Resume ExitHere
Case 3129
MsgBox Err.Number & vbCrLf & " Please enter a comment in the comment box...", vbOKOnly + vbCritical, "Microsoft Access Error Message"
Resume ExitHere
Case Else
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly + vbCritical, "Microsoft Access Error Message"
Resume ExitHere
End Select
End Sub
How to control how forms are displayed:
forms are open on whichever view that is currently active, if the current form is in Minimize view, when you open another form it will also be in Minimize view. To force Access to open a form in a particular view you have to put a DoCmd command in on Activate event. In the following example I place the following code in the on Activate event of my form to force it to open in Maximize view, you could also force the form to open in Minimize view.
DoCmd.Maximize
