Hi Zef
It's still 10 mins to lunch so try this stuff. LoL ;-)
I like that question - made me think (just a bit). This code has not been
tested but (hopefully) it should be OK and am sitting here waiting for lunch
Note it's not been tested but it "should" be OK but may need jigging about
with.
Note that if the name that you want "is" on the list the focus will be set
to the [1stname] text box of that record - if that's where you want the user
to be when the form open - if not you can change it.
Note you need to have column 1 of the combo set to show the [ClientID], then
[Surname], then [firstname[, [etc] and whatever other stuff you need.
Set this on on the Not On List option of your combo ( [ComboSurnameSearch]
)
Private Sub ComboSurnameSearch_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 in the data base." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Have another go."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblClients", dbOpenDynaset)
rs.AddNew
rs![Surname] = NewData
rs.Update
Response = acDataErrAdded
Me.ButtonNewRecordConfirm.Visible = True
End If
End Sub
On the after-update option add this
Private Sub ComboSurnameSearch_AfterUpdate()
If Me.ComboSurnameSearch = "Add" Then
DoCmd.GoToRecord , , acNewRec
CD1stName.SetFocus
Else
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientIDField] = " & Str(Me![ComboSurnameSearch])
Me.Bookmark = rs.Bookmark
Me.SubBookings.SetFocus
End If
Me.ComboSurnameSearch = ""
End Sub
Add a new button to your page set the visible (on-load to "False") to
confirm that the user wants to add the new record. The not on list option
will display it until the new record is created - you can change this to
"true" but why bother as in this case simply don't code the "visible" bit -
just set the button on the form and leave it. BUT it will always be there
and forms should be as empty as possible until the control is needed (well
that's my ideas anyway).
As you can see I have set this to close the main form and then reopen it as
I am still having problems with my server (it's crap and dead old) but if you
work for a company that isn't so mean you could simply requery the form.
As you can see from the
DoCmd.GoToRecord , , acLast
This will also reopen the main form onto the new record that you have just
created.
Set this to the on-click of the new button
Private Sub ButtonNewRecordConfirm_Click()
On Error GoTo Err_ButtonNewRecordConfirm_Click
DoCmd.Close
DoCmd.OpenForm "MainFormName", acNormal
DoCmd.GoToRecord , , acLast
Exit_ButtonNewRecordConfirm_Click:
Exit Sub
Err_ButtonNewRecordConfirm_Click:
MsgBox Err.Description
Resume Exit_ButtonNewRecordConfirm_Click
End Sub
Hope this helps - I'm off to lunch
And "Yes" I know I have done what you're not meant to by simply adding the
word "add" but couldn't think of anything else and a bit lazy (and hungry)
today.
--
Wayne
Manchester, England.
Hi,
I would like to create a mailing list where there are no duplicates. It
should check while the user is entering data if any previous records
exist with the same data.
For example, if there is a John Smith residing at 123 Oldsville Road,
Somewhere, SM with phone number 12345, as soon as the user enters John
in the 'First Name' Access should pop up all records matching first
name John. When the user enters Smith, all records with John Smith
should show up. Same record searching for phone number, address fields
etc. The user does not need to necessarily enter data in a certain
order. If he enters only the phone number, it should show up all
records with that phone number.
I hope I haven't left any confusion.
Thanks,
Zef.
