Re: Lookup and/or create new record from same box?

Thanks for the quick responses. I'll give these a shot and let you know if was able to get it to work or not :)

Mike

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5D428E97-0DAF-48AB-AA7D-9255B8CE58EC@xxxxxxxxxxxxxxxx
This is a very common situation. What you are calling a lookup box is, I
think, a Combo Box.
The typical technique is to use the After Update event of the combo to find
an existing record and navigate to it if it exists.

With Me.RecordsetClone
.FindFirst "[ID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Now when you want to add a new record if the ID doesn't exist in the table,
set the combo's Limit To List property to Yes. If you enter an ID that is
not in the list, the Not In List Event will fire. Here is an example of how
you add the new record or not depending on user input:

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.MyCombo = MeMyCombo.OldValue
CurrentDb.Execute ("INSERT INTO Customer Table Table ([ID]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
With Me.RecordsetClone
.FindFirst "[Activity] = '" & NewData & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Response = acDataErrAdded
Else
Me.MyCombo.Undo
Response = acDataErrContinue
End If


--
Dave Hargis, Microsoft Access MVP


"Mike" wrote:

Here's what i'm trying to achieve.

I want people to be able to look up a client by their unique ID number. I
know this is easily achievable through a lookup box. Not a big deal, i know.
What i'd really like to do, is to be able to type in the said number and
have it search the table for that record and go to it, if it exists. If it
doesn't exist, i'd like it to tell the user that the record doesn't exist,
but prompt them with a yes/no box (or something) to create a record with
that ID if they like. Then, pass that ID on to the new record.

Is this even possible? Or is there a better/simpler way that i'm not even
thinking of?

Thanks
Mike


.

Relevant Pages

  • Re: Lookup and/or create new record from same box?
    ... I figured out the replacing MyCombo bit only after posting the previous message. ... you add the new record or not depending on user input: ... Response = acDataErrContinue ...
    (microsoft.public.access.forms)
  • Re: Jump to named anchor after form processing
    ... > Thank you for your response. ... but there is some user input that I need to obtain in order to ... <input your user input for posting ... OT on this ASP NG!!! ...
    (microsoft.public.inetserver.asp.general)
  • Re: User inputs at one shot
    ... > The script will look something like this: ... > do something2 based on the user input ... > prompt for user input again ... response to question 2 ...
    (comp.unix.shell)
  • Re: Virtual PC - S3 Trio32/64
    ... Thank you Mike for the quick response. ... It's unfortunate that some of ... these system attributes can't be based on user input or info reported by ... the host to offer choices. ...
    (microsoft.public.windows.vista.general)
  • Re: Jump to named anchor after form processing
    ... Thank you for your response. ... Hmm... ... but there is some user input that I need to obtain in order to do ... Rob ...
    (microsoft.public.inetserver.asp.general)