Command buttons: Add/Find

[Andrew]

I have a table with index keys made up 2 fields: Phone, Year.
In my form, I created 2 text fields for phone, and year input.
Also I created 2 command buttons: Add, and Find.
I can find the record with 1 field but I cannot figure out how to Find a
record with a composite key: Phone, Year.

I created an Add button to add the new record following Command Button
Wizzard.
However when I click on the button, I got the message:
"You cann't go to the specified record"

Please help.

Thank you.

 

[Allen Browne]

1. Find button
==========
This example shows how to find a record based on multiple criteria. It is
constructed so that it is easy to add more text boxes to the search if
desired. Each of the non-blank text boxes adds itself to the criteria, and
tags an AND at the end. The trailing " AND " is then removed at the end. It
then check the form's RecordsetClone to see if the record is found: if not
it notifies the user; if so, it moves to that record by setting the form's
Bookmark to the found record.

The example assumes that Phone is a text field (so shows the extra quotes as
delimiters), but Year is a number field. Change the quotes if needed.

Note that Year is not a good name for a field. There is a function with that
name, and the code can get confused, though this particular code should be
okay.

You need to add error handling. For example, if the record cannot be saved,
the attempt to set Dirty to False will fail. If error handling is new, see:
http://allenbrowne.com/ser-23a.html

Private Sub cmdFind_Click()
Dim strWhere As String 'Criteria string.
Dim lngLen As Long 'Length of string.

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

'Build up the criteria string from for each of the non-blank text boxes.
If Not IsNull(Me.txtFindPhone) Then
strWhere = strWhere & "([Phone] = """ & Me.txtFindPhone & """) AND "
End If

If Not IsNull(Me.txtFindYear) Then
strWhere = strWhere & "[Year] = " & Me.txtFindYear & ") AND "
End If

lngLen = Len(strWhere) - 5 'Without the trailing " AND ".
If lngLen <= 0 Then
MsgBox "Enter some criteria to find."
Else
strWhere = Left$(strWhere, lngLen)
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "No Match."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

2. Add button
==========
There could be several reasons why the code might not be able to move, but
the most obvious one is that the current record cannot be saved, e.g. there
is a Required field that has no entry, or a Validation Rule that is not met.

Private cmdAdd_Click()
If Me.Dirty Then
Me.Dirty = False
End If
RunCommand acCmdRecordsGotoNew
End Sub