Logo
Disabled
Home
Date/Time
String
VBA/SQL
SQL Only
Loops
 Miscellaneous
Hot Cakes
Junk

 

# TOPIC VBA & SQL CODE NOTES
       
TABLES
1
Make A Copy Of A Table
(CopyObject Method)
DoCmd.CopyObject , "NewTableName", acTable, "OriginalTableName"
2
Make A Copy Of A Table
(Second Method)
DoCmd.DeleteObject acTable, "tblNewTableName"
CurrentProject.Connection.Execute "SELECT * INTO tblNewTableName FROM tblOriginalTableName"
NOTE: This second method is not as good as the method above. This second method will lose all the formatting and default settings, as they don't carry over from tblOriginalTableName.
3
Reset AutoCounter of a field
CurrentDB.Execute "DELETE * FROM tblNAME"
CurrentDB.Execute "ALTER TABLE tblNAME ALTER COLUMN nameofautocounterfield Counter(1,1)"
NOTE: The tblNAME can't be a bound table with the form that your button is residing on.
4
Remove Fields of A Table
CurrentDB.Execute "ALTER TABLE tblTEST DROP COLUMN A, COLUMN B, COLUMN C", dbFailOnError
5
Delete a specific record in a table.
CurrentDb.Execute "DELETE * from tblMAIN Where RecordID = 4;", dbFailOnError
CurrentDb.Execute "DELETE * FROM tblMAIN WHERE RecordID = " & tempRecNo & ";", dbFailOnError
6
DELETE an entire table.
DoCmd.DeleteObject acTable, "tblName"
7
Delete All Records From Table
CurrentDb.Execute "DELETE * FROM tblName"
8
DELETE All Tables That Contain "tblABC"
(DeleteObject Method)

Dim tbl As Object
For Each tbl In CurrentDb.TableDefs
If tbl.Name Like "*tblABC*" Then
DoCmd.DeleteObject acTable, tbl.Name
End If
Next
9
DCount With a textbox criteria.
tempCount = Dcount("FieldName","TableName","[FieldName] = ' " & me.txtField & " ' ")
10
DCount With A Constant Criteria
tempCount = DCount("*", "TableName", "[FieldName] Like ""*" & "dog" & "*"""
Count number of records that contain the word "dog" in the field.
11
DCount Records That Are Not Null
tempNonNull = DCount("*", "tblABC", "Len(" & chr(34) & chr(34) & chr(38) & " [TextField]) >0")
In this example, I want to count the number of records in tblABC, where the [TextField] is not null. The code simulates: Len("" & [TextField]) > 0
12
DCount Records That Are Not Null
tempNonNull = DCount("*", "tblABC", "Len('' & [TextField]) > 0")
Allows for Zero Length String
13
DLookUp With No Specific Record
ValueNeeded = DLookup("FieldName", "TableName")
14
DLookUp With Specific Record (Numeric)
ValueNeeded = DLookup("FieldName", "TableName","[RecordID] =" & 7)
Or this could also possibly work too:
ValueNeeded = DLookup("FieldName", "TableName","[RecordID] = 7")
15
DLookUp with a specific record tied to a textbox value.
ValueNeeded = DLookup("FieldName", "TableName", "[RecordID] =" & txtboxname)
16
DLookUp With Multiple Criteria
tempFullSeverity = DLookup("Severity", "tblSeverity", "[SevYear] = '" & varYear & "' And [SevCSA] = '" & Me.txtCSA & "' And [SevCoverage] = '" & Me.txtCoverage & "'")
17
DLookUp in the Control Source of a textbox of a form.
This is in the Control Source: " =DLookUp("[DisplayOnfrmMAIN]","tblGeo","[Target] = 'X' ") "
18
DMin with a Null criteria
ValueNeeded = DMin("RowNumber", "Tablename", "[FieldName] is null")
19
Importing an external table, and appending (INSERT) it to an existing table, within my immediated database.
impAppendSQL = "INSERT INTO tblMain SELECT * FROM tblExternal IN ' " & tempPath & " ' "
CurrentDb.Execute impAppendSQL, dbFailOnError
20
IMPORTING a table from an external database.
impTEST="SELECT* INTO tblCurrentDatabase FROM tblExternalTable IN ' " & tempPath & " ' "
CurrentDb.Execute impTEST, dbFailOnError
Extra spaces added between quotes and apostrophes.
21
Exporting an existing table to an external database.
22
INSERT an entire table to another table, with both tables being in the same database.
testSQL = "INSERT INTO tblMAIN SELECT * FROM tblSmall"
CurrentDb.Execute testSQL, dbFailOnError
NOTE: No criteria is allowed when you append an entire table through the use of an asterisk.
23
INSERT a new record to a table, but only a specific field, which is numeric.
sSQL="INSERT INTO tblMAIN (FieldName) VALUES (1);"
CurrentDb.Execute sSQL, dbFailOnError
Here, I am adding a new record to tblMAIN, but regardless of how many fields are in tblMAIN, I am only populating a numeric field (FieldName) with a value of "1".
24
INSERT a new record from one table to another, with multiple fields.
CurrentDb.Execute "INSERT INTO tblReceptor ( [recField1], [recField2], [recField3] ) SELECT [feedField1], [feedField2], [feedField3] FROM tblFeeder;", dbFailOnError
Note that after "SELECT", I don't have the parenthesis to surround the second set of tblFeeder field names.
25
INSERT a new record to a table based on a variable (which is a text).
CurrentDb.Execute "INSERT INTO tblMain (FieldName) VALUES (' " & variable & " ' );", dbFailOnError
Or this is another way by splitting code into two lines:
sSQL = "INSERT INTO tblMain (FieldName) VALUES (' " & variable & " ' );"
CurrentDb.Execute sSQL, dbFailOnError
26
INSERT a new record to a table based on a variable (which is a number).

CurrentDb.Execute "INSERT INTO tblMain (FieldName) VALUES (" & variable & " );", dbFailOnError
Or this is another way by splitting code into two lines:
sSQL = "INSERT INTO tblMain (FieldName) VALUES (" & variable & " );"
CurrentDb.Execute sSQL, dbFailOnError
NOTE: The difference here, when compared to the one above, is the absense of the single quote.  For numbers, you don't need a single quote.
27
Taking a table within my immediate database, and appending (INSERT) it to an external database.
expAppendSQL = "INSERT INTO tblExternal IN ' " & tempPath & " ' SELECT * FROM tblMain"
CurrentDb.Execute expAppendSQL, dbFailOnError
28
UPDATE a field in a table with a variable textbox value.
CurrentDb.Execute "UPDATE tblName SET FieldName = ' " & variable & " ' ", dbFailOnError
29
UPDATE a field in a table with a Null value.
CurrentDb.Execute "UPDATE tblName SET FieldName = Null ", dbFailOnError
30
UPDATE a field in a table with a zero value.
CurrentDb.Execute "UPDATE tblName SET FieldName = 0 ", dbFailOnError
31
UPDATE a field in a table with a zero value, limited to a specific record number.
CurrentDb.Execute "UPDATE tblName SET FieldName = Null WHERE RecordNo = 4;", dbFailOnError
32
UPDATE a field in a table with a constant.
CurrentDb.Execute "UPDATE tblName SET FieldName = 'JUNK';", dbFailOnError
33
UPDATE a field in a table to a variable, but only the last record.
CurrentDb.Execute "UPDATE tblName SET FieldName = ' " & VariableName & " ' " & " WHERE RecordNo = " & DMax("RecordNo", "tbName"), dbFailOnError
The UPDATE SQL will update all fields. I only want to update the last record, so I need to use the RecordNo ID field.
34
UPDATE a TEXT field in a table, and limited to a specific record.
CurrentDb.Execute "UPDATE tblName SET FieldName = ' " & variableName & " ' " & " WHERE RecordNo = " & Me.txtName & ";", dbFailOnError
This is similar to the above. The only difference is the WHERE condition.
35
UPDATE a TEXT field in a table, and limited to a specific record, which is a text (versus number).
updateCurrentSQL = "UPDATE tblName SET FieldName = ' " & VariableName & " ' " & " WHERE CaseName = ' " & Me.txtCaseName & " ';"
CurrentDb.Execute updateCurrentSQL, dbFailOnError
36
UPDATE a TEXT field in a table, and limited to the value in a textbox.
CurrentDb.Execute "UPDATE tblMain SET Del = 'abc' " & "WHERE EventID = " & Me.txtEventID & ";", dbFailOnError
37
UPDATE a TEXT field in a table, and limited to a specific record. Same as above, but with constants.
CurrentDb.Execute "UPDATE tblMAIN SET FieldName = 'abc' " & " WHERE RecordID = " & 1, dbFailOnError
38
UPDATE a TEXT field in a table, and limited to a specific record, which is a variable.
CurrentDb.Execute "UPDATE tblMAIN SET FieldName = 'abc' " & " WHERE RecordID = " & LastRecord, dbFailOnError
39
UPDATE a TEXT field in a table, and limited to a specific CHARACTER record. Similar to above, but with constants and a record ID that is not numeric.
CurrentDb.Execute "UPDATE tblMAIN SET FieldName = 'abc' " & " WHERE RecordID = 'A' ", dbFailOnError
40
UPDATE a DATE field in a table, and limited to a specific record.
CurrentDb.Execute "UPDATE tblMAIN SET DateField = #" & Me.txtLastDate & "# WHERE RecordID = 10", dbFailOnError
41
UPDATE a NUMERIC field in a table, and limited to a specific record.
CurrentDb.Execute "UPDATE tblPayments SET PaymentAmt = " & Me.txtPmtAmt & " WHERE RecordID = " & LastRecord, dbFailOnError
Needs to be validated.
       
FORMS
1
Make A Copy Of A Form
(CopyObject Method)
DoCmd.CopyObject , "NewFormName", acForm, "OriginalFormName"
2
Close A Form
DoCmd.Close acForm, "Form Name Here"
3
Open A Form
DoCmd.OpenForm "Form Name Here"
4
DELETE a Form
(DeleteObject Method)
DoCmd.DeleteObject acForm, "frmTEST"
This is dangerous.
5
DELETE All Forms That Contain "frmABC"
(DeleteObject Method)
Dim qry As Object
For Each qry In CurrentDb.QueryDefs
If qry.Name Like "*frmABC*" Then
DoCmd.DeleteObject acQuery, qry.Name
End If
Next
6
Form Opening Size (On-Load Event)
DoCmd.MoveSize 0, 0, 12500, 9000
(x,y,width,height)
7
Form Opening Size (On-Load Event) - Place in Upper-Left
DoCmd.MoveSize 0, 0
8
Find Height Of A Form

htForm = Forms(frmName).Section(acDetail).Height
9
Find Top Position Of Command Button

posButtonTop = Forms(frmName).btnSubmit.Top
In this example, the command button is named "btnSubmit."
10
Referencing a subform of a subform.
With Forms("NameOfMainForm")!subsub.Form!subsub
.Form!txtName1 = "Richard"
End With
Note that both submform controls are named the same, and also the names of both subforms are NOT mentioned.
11
Opening a Form, with a specific subform, and also within the subform, another specific subform.
DoCmd.OpenForm "Name of MainForm"
With Forms("Name of MainForm")!subformcontrolnameinMainForm
.SourceObject = "Subform1Name"
.Form!subformcontrolnameinSubform1Name.SourceObject = "Subform2Name"
End With
12
Opening a Form, with a specific subform, and also within the subform, another specific subform. BUT ALSO, referencing a text control in both the subform, and the sub-subform.
With Forms("NameOfMainForm")!subformcontrolnameinMainForm
.SourceObject = "Subform1Name"
.Form!txtNameOnSubform1 = "Junk"
End With

With Forms("NameOfMainForm")!subformcontrolnameinMainForm.Form!subformcontrolnameinSubform1
.Form!txtNameOnSubform2 = "Stuff"
End With
As you can see, this requires a two-step process.
13
Open a Form, but Filtered to a specific record.
With Me.Form
.Filter = "[PartID]=" & 4
.FilterOn = True
End With
…and if you refer to a subform, you would use this...
With Me.Form
.Form.Filter = "[PartID]=" & 4
.Form.FilterOn = True
End With
This would be placed in the On-Open event of the subform. In this example, the subform is tied to the table, with a unique ID called "PartID."
14
Refreshing a Subform that you just changed data to.
CurrentDb.Execute "UPDATE tblStuff SET checkboxA = -1;", dbFailOnError ' Trigger for Jump Panel
With Forms("MainForm")!subsub
.SourceObject = "GoToSourceObject"
End With
With Forms("MainForm")
.Requery
End With
Of importance is the fact that it's the second With-Statement that is requerying the form, and not the new sourceobject. In fact, placing a 'requery' command below the line for sourceobject will NOT work. (RECHECK: My statement may not be accurate, as my triggers were on the main form.)
15
Requerying a Subform
Forms![MainForm].subsub.Requery
16
Requerying a Main Form
Forms![MainForm].Requery
17
Moving to another subform and doing something to the new subform.
With Forms("MainForm")!subformControlName
.SourceObject = "GoToNewSubformName"
.Form!ControlNameInNewSubform = (what you want it to do)
.Form!btnAutoDefer.Visible = True
End With
In this example, you are doing two things: 1-something to the control, and 2-making a button called 'btnAutoDefer' now visible.
18
Print Form
DoCmd.RunCommand acCmdPrint
This will display the Print dialog box.
19
Transferring Data to a New Form
Forms!frmStart!frmStartSubform.Form!.TextboxNameOnNewForm = CurrentTextboxName
20
Switching Subforms
Method 1
With Forms("MainForm")!subformControlName
.SourceObject = "GoToNewSubformName"
End With
21
Switching Subforms
Method 1 (if MainForm and subform are variables, instead of constants)
With Forms(varFormName)(varSubformName)
.SourceObject = "GoToNewSubformName"
End With
Or this solutions also works:
Forms(varFormName).Controls(varSubformName).SourceObject = "subformABC"
22
Switching Subforms
Method 2
Forms![MainForm]![subsub].SetFocus
Forms![MainForm]![subsub].SourceObject = "NameofSubformYouWant"
23
Referencing Unlinked Forms
Forms!frmViewer.subsub.Form.NameOfControl
Best to see the Subform Reference Demo database for a complete understanding.
24
Referencing the Parent Control that houses the subform that you are on….
ViewerSubSub = Me.Parent.ActiveControl.Name
25
DLookUp Syntax for Variable Used To Programmatically Populate Control Source Of A Form Textbox (Version 1)
strDLookUp = "= '" & strObjNumber & "' & ' - ' & DLookUp(""QText"",""tblQuestionsGO"",""Q=" & objNumber & """)"
26
DLookUp Syntax for Variable Used To Programmatically Populate Control Source Of A Form Textbox (Version 2)

strDLookUp = "=DLookUp(""QText"",""tblQuestionsBASE"",""ID=" & objNumber & """)"
This will display: =DLookUp("QText","tblQuestionsBASE","ID=01")
This took forever to create!
27
Another example for Variables Used To Programmatically Populate A Control Source.

strSyntax = "=Sum(IIf([Q" & strObjNumber & "]= 1,1,0))"
This will display: =Sum(IIf([Q01]=1,1,0))

strObjNumber = 01
28
Another example for Variables Used To Programmatically Populate A Control Source. This uses DCount

strDCount = "=DCount(""[Q" & strObjNumber & "]"", ""qryABC"", ""[Q" & strObjNumber & "]=1"")"
This will display: = DCount("[Q01]","qryABC","[Q01]=1")
strObjNumber = 01
29
Use CreateControl method to create a textbox, and paste onto form.

Dim ctl As Control
Set ctl = CreateControl("frmMain",acTextBox,720,1440,2880,240)
ctl.Name = "txtTest01"
ctl.TabStop = True
ctl.FontSize = 8
ctl.BackColor = 16777215
ctl.FontName = "Arial (Detail)"
ctl.SpecialEffect = 0 '0 = Flat
ctl.ControlSource = "=DLookUp(""LastName"",""tblPeople"",""ID=1"")"
This example will create a textbox named "txtTest01" positioned 720 twips from the left, 1440 twips from the top, 2880 twips wide and a height of 240 twips. More importantly, check out the syntax used for the Control Source. The double quotes are needed!
30
Use CreateControl method to create an Option Group with a checkbox. (See Notes)

STEP 1: Create the Option Group using a CreateControl Method
Dim ctl As Control
Set ctl = CreateControl(FormName:="frmMain", ControlType:=acOptionGroup, Left:=720, Top:=1440, Width:=2280, Height:=240)
ctl.Name = "opt01"
ctl.DefaultValue = 3
ctl.TabStop = True
STEP 2: Create a checkbox using a variation of the CreateControl Method
(Notice that you will need to use the 'Section' and 'Parent' arguments for this.)
Set ctl = Application.CreateControl("frmMain", acCheckBox, acDetail, "opt01", , Left:=725, Top:=1450, Width:=260, Height:=195)
ctl.OptionValue = 1
ctl.SpecialEffect = 2 '2=Sunken
ctl.BorderStyle = 2 ' 1=Thin 2=Sizable
ctl.BorderWidth = 0 ' 0=Hairline
(I will skip the creation of the label-object here, but you would use the regular Create Control Method for this.

This a multiple step process, depending on how many buttons you want in your option group. For each checkbox, there are two objects, the checkbox and the label object.
Also, you can't simply just create the Option Group, and then the checkbox with the same method. If you do, the checkbox will not allow you to set an "Option Value."
31

DELETE a Textbox (DeleteControl Method)
STEP 1: Open the form in design view
DoCmd.OpenForm "frmMain", acDesign
STEP 2: Use DeleteControl Method
DeleteControl "frmMain", "txt01"
STEP 3: Close form, and make sure you save changes.
DoCmd.Close acForm, "frmMain", acSaveYes
Note: For this to work, the form must be in design view. These steps will first open a form, delete a textbox and then close the form.
       
QUERY
1
Run A Query (Method 1)
DoCmd.OpenQuery "Name Of Query"
2
Run A Query (Method 2)
CurrentDb.Execute "Name Of Query"
This method will elimination the confirmation pop-up box.
       
REPORTS
1
Cancel A Print (2501 Error)
Private Sub btnPrintInstructions_Click()
On Error GoTo btnPrintInstructions_Click_Err
DoCmd.RunCommand acCmdPrint
Exit Sub
btnPrintInstructions_Click_Err:

If Err.Number = 2501 Then
'Do Nothing
Else
'Also Do Nothing
End If
End Sub
There are times when you create a command button to print an object. When given the option of "Okay" and "Cancel", clicking "Cancel" will generate a Runtime Error 2501. This code will bypass that.
2

Open A Report
DoCmd.Open Report "Name Of Report", acPreview (or acViewPreview)
3
Print A Report
(Bypass Viewing)
DoCmd.OpenReport "Name Of Report", acViewNormal
This prints immediately, without viewing first.
MISCELLANEOUS
1
Display the current path/location of a database in a message box.
MsgBox CurrentProject.Path
2
Hide menus on top when you open up a form.

Dim I As Integer
For I=1 To CommandBars.Count
CommandBars(I).Enabled=False
Next I

Place this in the On-Open event
3
Dimension multiple variables..
If you want to Dim a,b,c,d as variables, you don't have to use a separate line for each. You can also use the following:
Dim a As Integer, b As Integer, c As Integer, d As Integer
If you try the following, it won't work:
Dim a,b,c,d As Integer
4
Save Copy Clipboard Message
DoCmd.RunCommand acCmdSaveRecord
This simple line of code is necessary in cases where a message come on after clicking a control, warning you that the data you just input needs to be saved.
5
Message Box Text
MsgBox "This button is not functional" & Chr(13) & _
"for this FREE demo you're using.", _
vbOKOnly + vbExclamation, "R2.0 DEMO MESSAGE"
6
Message Box With OK/Cancel buttons.
Private Sub btnCLOSE_Click()
Dim CloseMessage As Integer
CloseMessage = MsgBox("You are about to lose all changes!", vbOKCancel + vbExclamation, "Next Gen Message")
If CloseMessage = 1 Then
DoCmd.Close acForm, "NameOfForm"
ElseIf CloseMessage = 2 Then
End
End If
End Sub
This is used in a 'Close' button, where I need to warn the user that he will lose all changes if we click close, since he hasn't saved anythign yet.
You need to know that OK=1 and Cancel=2. Also, notice that I am using "ElseIF" instead of just "Else". Using "ElseIf" will eliminate the need for a second "End If" statement.
7
Hide the Menu-Bar
Simply place this in the On-Load Event of the form:
Application.CommandBars("Menu Bar").Enabled = False
8
Handling Nulls

'" & Nz(Replace(IIf(IsNull(rs1![FieldName]), "", rs1![FieldName]), "'", "''")) & "'
We are throwing the kitchen sink with this solution. We are including the NZ function, Replacing nulls with a blank space, and also using the Isnull function.
9
Timer - Add a delay to a procedure. (Method 1)

Dim dtEndTime As Date
Const lngPauseTime = 5&
dtEndTime = DateAdd("s", lngPauseTime, Now())
Do While Now() < dtEndTime
DoEvents
Loop
In this example, my delay is for 5 seconds.
10
Timer - Add a delay to a procedure. (Method 2)
tWait = DateAdd("s", 5, Time())
Do Until Time() >= tWait
Loop
In this example, my delay is for 5 seconds.
11
Add hourglasses to a procedure.

DoCmd.Hourglass True
'Procedure goes here.
DoCmd.Hourglass False

12
Add pop-up that displays the procedure runtime.

Dim tempNowStart As Date
Dim tempNowEnd As Date
tempNowStart = Now
'Procedure goes here.
tempNowEnd = Now
Lapse = DateDiff("s", tempNowStart, tempNowEnd)
MsgBox "Looping Complete" & Chr(13) & Chr(13) & "Start: " & tempNowStart & Chr(13) & "Finish: " & tempNowEnd & Chr(13) & "Runtime: " & Lapse & " seconds."
13
Colors for 2010 Version

vbBlack
vbRed
vbYellow
VbGreen
vbCyan
vbBlue
vbMagenta
vbWhite

These are accepted and recognized color constants that you can write into the code, instead of the numeric codes.
14
Object Values
MSysObjects.Type
Tables = 1
Forms = -32768
Queries = 5
Reports = -32764
Modules = -32761