handling errors + last_inserted_id + list_box search

[damjanu]

Dear All;

I have 3 issues

1) On some of my tables I have unique indexes, so that
for example no two phone numbers can be the same.
When user enters phone number that already exists, a
scary error comes up with error number telling him
that it is a duplicate. I would like to catch this error
number xxxx and handle it so that I stop execution,
and report a MsbBox..... I hope this is possible.
Does anyone know the mechanisam for this, or
maybe has some sample code, or a usefull link....

2) Is there a last_inserted_id in access, to find out
the value of last inserted row for a autoincrement
field?

3) I have a list box with large number of entries. I would
like to have a way to search it similar to (or exactly
the same) as the one automatically implemented by access
on combo boxes - as I type the search word
it displays the entries in the list that match the letters
typed. I think this is not already done? Does anyone have
somewhere that this can be downloaded, or has anyone tried to
do this already and can maybe share their experiances.

Hopefully the fact that I have 3 questions together doesnt
scare anybody off - it shows that I thought of them.

I am near the end of my project, and it would not be possible
to complete it without this group. Thanks to all that helped
or even read my messages.

Happy New Year

Damjan

[Scott McDaniel]

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
<da*****@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...

Dear All;

I have 3 issues

1) On some of my tables I have unique indexes, so that
for example no two phone numbers can be the same.
When user enters phone number that already exists, a
scary error comes up with error number telling him
that it is a duplicate. I would like to catch this error
number xxxx and handle it so that I stop execution,
and report a MsbBox..... I hope this is possible.
Does anyone know the mechanisam for this, or
maybe has some sample code, or a usefull link....
Either catch this in the Form's error handler, or run code in the
BeforeUpdate event of the form to check this value before allowing it into
the database.

To catch this error in the FORM error handler (turn Key Preview ON) ... I
forget what the error number is, but you should be able to jot it down from
the current errors you're receiveing now.

On Error GoTo Proc_Err:

<your code module>

Proc_Err:
Select Case Err.Number
Case YourErrorNumberHere
MsgBox "Sorry this number is already in the db."
Case Else
End Select

In the BeforeUpdate event, you can use a DLookup to check for an existing
phone number. If a duplicate is found, Msgbox the user and Cancel the event.

2) Is there a last_inserted_id in access, to find out
the value of last inserted row for a autoincrement
field?
Not if your datasource is a Jet database. You can use a DMax to get the
largest value entered (assuming you're NOT using random autonumbers), but
that's about as close as you'll get. Even then you are not guaranteed to get
the record that YOU just inserted, since someone else could have inserted
one immediately after you. You can always open a recordset, perform your
inserts, and then grab the Autonumber that was assigned to the record before
leaving the procedure ... that would give you the correct number.
3) I have a list box with large number of entries. I would
like to have a way to search it similar to (or exactly
the same) as the one automatically implemented by access
on combo boxes - as I type the search word
it displays the entries in the list that match the letters
typed. I think this is not already done? Does anyone have
somewhere that this can be downloaded, or has anyone tried to
do this already and can maybe share their experiances.
Put a textbox above your listbox, then in the Change event of your textbox:

Dim nCount As Integer
Dim strSearch As String

strSearch = Me.ctlSearch.Text

For nCount = 0 To Me.lstDocks.ListCount - 1
If Left(Me.lstDocks.Column(1, nCount), Len(strSearch)) = strSearch Then
Me.lstDocks.Selected(nCount) = True
Exit For
End If
Next

My textbox was named ctlSearch and my listbox was named lstDocks. You will
have to change the name of both to match those on your form. This code also
assumes a 2 column listbox, with the first column being hidden (i.e.
columnwidth = 0), with the search happening on the SECOND column ... since
the listcount of a listbox is 0 based, the first column is acutally
Column(0), the second is Column(1) etc etc


Hopefully the fact that I have 3 questions together doesnt
scare anybody off - it shows that I thought of them.

I am near the end of my project, and it would not be possible
to complete it without this group. Thanks to all that helped
or even read my messages.

Happy New Year

Damjan