The following script has two parts.

1. Combines all selected items into one field.
2. Parses the combined selections back into separate fields.

Assume the control name on the form is Combobox. If the user selected red, green and blue from the list, the routine would print the following in the debug window.

Values = red | green | blue
Number of items selected = 3
Value ='red'
Start position=1 Last position=3
Value ='green'
Start position=7 Last position=11
Value ='blue'
Start position=15 Last position=18

Private Sub Combobox_AfterUpdate()

Dim results as String, parse as String, start as Integer
Dim last as Integer, count as Integer
Dim ctl as Control, Itemx as Variant
Set ctl = Me!Combobox 'reference control name of combo box'

Part one: combine selected fields into one with a delimiter of a vertical bar |
count =0
For Each Itemx in ctl.ItemsSelected
    count = count + 1
    results = results & ctl.ItemData(Itemx) & " | "
Next Itemx
Debug.Print "Values = " & results
Debug.Print "Number of items selected = " & count

Part two: Parse the results back into seperate values
start =1
last = 9000
Do Until last = 0
    last = InStr(start, results, "|")
    If last =0 Then
      Exit Sub ' parse done
    End If
    parse = "" 
    parse = Mid(results, start, last - start -1)
    Debug.Print "Value ='" & parse & " ' "
    Debug.Print "Start position=" & start & " Last position=" & last-1
    start = last + 2
Loop

End Sub

top of page

The following command is handy to use when you want to force saving of the current record, such as printing a report from the report.

RunCommand acCmdSaveRecod

top of page

The following is the basics that are needed to access a set of data using SQL commands.

Dim CurDB as Database
Dim Inv as Recordset
Dim SQLStmt as String
Set CurDB=CurrentDb()SQLStmt = "SELECT * FROM Inventory WHERE [Item Number] =' " & Me![Item Number & "'"
Set Inv = DB.OpenRecordset (SQLStmt)

top of page

Assume you have a combo box that lists all of the records for the current form. After selecting a record, you want to form to automatically retrieve the selcted record.

Private Sub combobox_AfterUpdate()
  Me.RecordsetClone.findfirst "[keyfield] = & Me![combobox]
  Me.Bookmark = Me.Recordsetclone.Bookmark
  DoCmd.GoToControl "[keyfield]"
End Sub

top of page

When comparing starting/ending item values there can be a problem when the the starting time is in the PM and the ending time is in the AM, i.e. the next day. To fix this problem, the following forumla will calculate the lapsed time correctly.

Assume start=10 AM and end = 2 PM. To compute the correct lapsed time, simply add 1 to the end time. This works correctly even when the time doesn't wrap around to the next day.

1 + end - start = 4 hours

top of page

To compute the number of business days between two dates, use the following script.

Assume you have a form that has 3 fields

• Start Date (named startdate)
• Estimated Days (named days)
• Completion Days (named completion)

You enter a starting date of 2/15/02 and estimated days of 45. You want the program to compute the completion days and not include weekends.

To ensure the start date does not fall on a weekend, enter the following in the rule properties.

DatePart("w",[startdate]) Mod 7 >=2

If a Saturday or Sunday is entered, the value will be a 0 or 1

The following will compute the completion date.

completion = startdate
For i = 1 to (days -1
  completion = DateAdd("d",1,completion)
  If DatePart("w",completion)=7 then
    completion = DateAdd('d",2, completion)
  End If
Next i

top of page

You may want to display a message to a user and control the format. Assume you have a message like this...

You entry is invalid, either 1) enter a new value or 2) correct the value.

and you want it appear like this...

You entry is invalid, either
1) enter a new value or
2) correct the value.

To force new lines use the Chr(10) and Chr(13) within your text. The script would look like this...

"You entry is in valid, either" & Chr(10) & Chr(13) & "1) enter a new value or" & Chr(10) & Chr(13) & "2) correct the value."

top of page