Hi
I would not make the search on an autonumber ID (it is really but will not
look like it) . You may be better searching on something a user will
understand like a name or street. But up to you.
This is not tested as I don't have access on this machine - so you may need
to "tweek" it a little.
I assume you have a control call Surname on your form.
first use the wizard to create a combo that will search for something on
your form - point it to the Surname control. Call it CboSurnameSearch
BUT - very important, even though you are on searching on the ID field you
really are - but not as far as a user knows.
In your combo
Have surname, 1stname, ID (in that order). The bound column is the ID
column. The reason for doing this is there may be more than one person with
the same name - so you could add 1st line of the address to the combo as well
if you wanted.
Next create a button called ButCnfirmNewRecord - this will requery the form
and take you to any new rcord you create
The code on the "afterupdate" event of the combo will look a bit like this
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![CboSurnameSearch])
Me.Bookmark = rs.Bookmark
In the NotInList event put something like this
Private Sub CboSurnameSearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'" & NewData & "' is not on the list - or something else." & vbCr &
vbCr
msg = msg & "Do you want to add New Record?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TableName", dbOpenDynaset)
rs.AddNew
rs![Surname] = NewData
rs.Update
Response = acDataErrAdded
Me.ButtonConfirmNew.Visible = True
End If
End Sub
Of course change TableName to what it really is.
Did you notice the ButtonConfirmNew at the end of the code.
So you need to create a new button called ButtonConfirmNew
Set it to visible = No (in it's properties box)
So this bit in the above code
Me.ButtonConfirmNew.Visible = True
will make it visible only when its needed
The OnClick event of this new button will be something like
DoCmd.Requery ""
DoCmd.GoToRecord , "", acLast
Me.ButtonConfirmNew.Visible = False
Me.SomeControl.SetFocus
As you can see with the "" you are searching (inputting) text.
Wha this does is requery the form - to do all the "stuff" needed.
Next it goes to the last record - thats the one you just created in the
NotInList code
Hides itself
Sets the focus somewhere on the form you want it to - so don't forget to
alter SomeControl in the OnClick code
Have fun
--
Wayne
Manchester, England.
Big Pepper said:
My table has an ID datatype is autonumber
I make a form base on table. I would like to type a value in textbox then
it will look up if the value match ID then display the record, if not then
goto the new record.
