Selecting an Individual record by Input

[Guest]

I am using Access 2000

I have a large database of telephone numbers (1500).
I now have to update individual records as and when invoices arrive.
I have a form, based on a select query, with all the information and fields
that I need but moving between records is controlled by the navigation bar.
This is time consuming and it would be much quicker, and accurate, if I
could input the telephone number and pull up the record.

QUESTION: Is there any way I can do this ?

Thank you in advance for your help.

Sean Bishop

 

[Guest]

Sean:

Put code along these lines in the AfterUpdate event procedure of the unbound
text box in which the phone number is entered:

Dim rst As Object
Dim strCriteria As String

If Not IsNull([txtFindPhoneNumber]) Then
strCriteria = "[PhoneNumber] = """ & _
[txtFindPhoneNumber] & """"

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Phone number not found"
End If
End If

where txtFindPhoneNumber is the name of the unbound text box, and
PhoneNumber is the name of the field in the underlying table. I've assumed
that the phone number is a text field with values in the format entered in
the text box. Its important that they match. If for instance you store the
numbers without spaces, but spaces are entered in the text box then you'd
amend the code like so:

strCriteria = "[PhoneNumber] = """ & _
Replace([txtFindPhoneNumber]," ","") & """"


Ken Sheridan
Stafford, England

 

[Guest]

Dear Ken,

Thank you very much for your response.
It works, but (I'm sorry) if I input an incorrect number in it defaults to
the last record whereas your code indicates a message box would appear saying
the reference was invalid.

Sean

Sean:

Put code along these lines in the AfterUpdate event procedure of the unbound
text box in which the phone number is entered:

Dim rst As Object
Dim strCriteria As String

If Not IsNull([txtFindPhoneNumber]) Then
strCriteria = "[PhoneNumber] = """ & _
[txtFindPhoneNumber] & """"

Set rst = Me.Recordset.Clone
rst.FindFirst strCriteria

If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Phone number not found"
End If
End If

where txtFindPhoneNumber is the name of the unbound text box, and
PhoneNumber is the name of the field in the underlying table. I've assumed
that the phone number is a text field with values in the format entered in
the text box. Its important that they match. If for instance you store the
numbers without spaces, but spaces are entered in the text box then you'd
amend the code like so:

strCriteria = "[PhoneNumber] = """ & _
Replace([txtFindPhoneNumber]," ","") & """"


Ken Sheridan
Stafford, England

I am using Access 2000

I have a large database of telephone numbers (1500).
I now have to update individual records as and when invoices arrive.
I have a form, based on a select query, with all the information and fields
that I need but moving between records is controlled by the navigation bar.
This is time consuming and it would be much quicker, and accurate, if I
could input the telephone number and pull up the record.

QUESTION: Is there any way I can do this ?

Thank you in advance for your help.

Sean Bishop

 

[Guest]

Sean:

Change 'If Not rst.EOF' to 'If Not rst.NoMatch'. That should do the trick.
One thing you do need to be careful of, however, is that the PhoneNumber
column does not contain any zero-length strings, so be sure to disallow them
in table design. Any existing ones can first be changed to Nulls, making
sure first that the column's Required property is False, with:

UPDATE [YourTable]
SET [PhoneNumber] = NULL
WHERE LEN([PhoneNumber]) = 0;

Usually this type of record navigation uses a combo box rather than a text
box (which is why it wasn't working with the EOF property incidentally), so
you might like to consider that. Set the combo box's RowSource property to
an SQL statement which lists all the phone numbers in order, e.g.

SELECT [PhoneNumber]
FROM [YourTable]
ORDER BY [PhoneNumber];

If the same phone number can be in more than one row in the table use SELECT
DISTINCT so the combo box lists each number once. Set the control's
LimitToList property to True (Yes) and its AutoExpand Property to True. The
code for its AfterUpdate event procedure would be exactly the same apart
from changing the name of the text box to that of the combo box. The user
can either scroll down the list and select a number, or type in the number,
in which case it would go to the first match in the list as each digit is
entered. To cater for a user typing in a phone number not in the list put
the following in the control's NotInList event procedure:

Const ConMESSAGE = "Phone number not in list."

MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Response = acDataErrContinue

You can keep the combo box in sync with the current record if you navigate
other than via the combo box by putting code like this in the form's Current
event procedure:

Me.[PhoneNumber] = = Me.cboFindPhoneNumber

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

Thank you for your reply.
I have taken up your suggestion of a ComboBox.
This works.
It has the added benefit that I can show three or four columns of
information which is a great benfit to the user.
Again many thanks.

Sean

Sean:

Change 'If Not rst.EOF' to 'If Not rst.NoMatch'. That should do the trick.
One thing you do need to be careful of, however, is that the PhoneNumber
column does not contain any zero-length strings, so be sure to disallow them
in table design. Any existing ones can first be changed to Nulls, making
sure first that the column's Required property is False, with:

UPDATE [YourTable]
SET [PhoneNumber] = NULL
WHERE LEN([PhoneNumber]) = 0;

Usually this type of record navigation uses a combo box rather than a text
box (which is why it wasn't working with the EOF property incidentally), so
you might like to consider that. Set the combo box's RowSource property to
an SQL statement which lists all the phone numbers in order, e.g.

SELECT [PhoneNumber]
FROM [YourTable]
ORDER BY [PhoneNumber];

If the same phone number can be in more than one row in the table use SELECT
DISTINCT so the combo box lists each number once. Set the control's
LimitToList property to True (Yes) and its AutoExpand Property to True. The
code for its AfterUpdate event procedure would be exactly the same apart
from changing the name of the text box to that of the combo box. The user
can either scroll down the list and select a number, or type in the number,
in which case it would go to the first match in the list as each digit is
entered. To cater for a user typing in a phone number not in the list put
the following in the control's NotInList event procedure:

Const ConMESSAGE = "Phone number not in list."

MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Response = acDataErrContinue

You can keep the combo box in sync with the current record if you navigate
other than via the combo box by putting code like this in the form's Current
event procedure:

Me.[PhoneNumber] = = Me.cboFindPhoneNumber

Ken Sheridan
Stafford, England

Dear Ken,

Thank you very much for your response.
It works, but (I'm sorry) if I input an incorrect number in it defaults to
the last record whereas your code indicates a message box would appear saying
the reference was invalid.

Sean