Microsoft Access Code Samples

Sample Code for Making A Main Form Search By "Part Number" (in This Example)

Note: This Code is sample code which doesn't actually work properly. It's here simply for archiving persons for the future.

This is useful for making a data-entry form look for, say, the part number you are entering when entering a new entry. This helps make sure that (in this example) the part number you are assigning to a new entry does not already belong to an older entry. It pulls up the record if it already exists.

This code is flawed, however, because it still adds a new "blank" entry to the table in the event that a pre-existing record with that part number comes up. This is because it adds a new entry (and assigned an "autoid" value) BEFORE performing this check. The last 3 lines at the bottom are an attempt to start addressing this, but this code at this point needs work. It is NOT final.

Private Sub Part_Number_AfterUpdate()
Dim strTempSSN As String
strTempSSN = Me.Part_Number
Me.RecordsetClone.FindFirst "[part_number]= " & Chr(34) & strTempSSN & Chr(34)
If Me.RecordsetClone.NoMatch Then
 MsgBox "You are Entering a New Part Number for a New Record."
Me.Part_Number = strTempSSN
With CodeContextObject
.AllowAdditions = True
.AllowDeletions = True
End With
DoCmd.GoToRecord , , acNewRec
Me.Part_Number = strTempSSN
Else
 Me.Part_Number = Me.swp_value
 Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub


Private Sub Part_Number_Enter()
Me.swp_value = Me.Part_Number
End Sub

Perhaps a better alternative is to set up the form to search for duplicates and flash a warning message if the value you enter is a duplicate. Go here to read how to do this.

How to Hide/Close/Minimize (etc) a Window in Visual Basic

DoCmd.RunCommand acCmdWindowHide

You can also minimize, maximize, or restore the window with the following code samples:

Or, the CLOSE the window, the following code will do it, and do so without causing error messages:

Allow Edits Property

Checking for Null Values on a Form

The methods for specifying for (or checking for) null values on a form is a bit unorthodox. Here are some samples of how to do this (the 1st line DOES work, not sure about the other ones; they were suggested to me from newsgroup readers):

"Giving Focus" To A Report

The code for making a report receive the focus is not the same as it is for a form. The following code will do it:

Conditional Formatting/Sensing the Active Control

Code for Deleting Records

These code samples are UNTESTED, I have simply put them here as reference since I am currently trying to figure them out:

 Me.recordsetclone.bookmark=me.bookmark
 Me.recordsetclone.delete
DoCmd.RunSQL "Delete * from log_table WHERE log_table.autoid = " & Forms!main_form![log_form]![autoid]

Moving, Rezing Controls Programatically

The Original, Less-Preferred Way:

I received this suggestion at a newsgroup regarding this issue: (microsoft.public.access.formscoding 8-16-01 11:20 am)

You should really use relative terms not absolute values:

The "Better" Way Which I Later Learned

Based on the above statements, I generated the following code, which I like better:

I noticed that the height numbers in Visual Basic don't correspond to the units in inches expressed in the properties sheet. I extracted the unit of the "normal" size with code similar to the following:

Message Box Format

message = "There is a record with packlist number " & Me.packlist & " in the tooling database. Do you want " _
& "to pluck in those values?"
response = MsgBox(message, vbYesNo, "From Larry...")
If response = vbNo Then
Resume Exit_Command43_Click 'this will cancel the Exit

Dim response As Integer, message As String
message = "The REQUESTED BY field cannot contain a null value. Do you wish to enter a value " _
& "right now? (You don't have to do so to save this record successfully, but the field is " _
& "still a required field)?"
If IsNull(Me.requested_by) Then
response = MsgBox(message, vbYesNo, "OOPS!")
If response = vbYes Then
Cancel = True 'this will cancel the Exit

Update Queries Running Within Visual Basic

sSQL = "UPDATE tbl_ticksheet SET tbl_ticksheet.linked_to_keycode_log_table = True WHERE case_number = " & Me.autoid
CurrentDb.Execute sSQL
sSQL = "UPDATE tbl_ticksheet SET tbl_ticksheet.source = " & stQuote & Me.ticksheet_source_cbox & stQuote & "WHERE case_number = " & Me.autoid
CurrentDb.Execute sSQL

You can also Go Here For More Information

DLookup Syntax

Me.keycode = DLookup("[keycode]", "tbl_key_codes", "[product] = " & strQuote & Me.product & strQuote & "AND [used] = NO")

Removing Drop-Down Menu When Displaying a Form

1. Open the form in DESIGN view
2. Under the "other" tab, in the "menu bar" row, enter =1.

Input Mask Within Visual Basic

Me.regstration_number.InputMask = "000\-00\-0000;;_"