Lookup and/or create new record from same box?

[Mike]

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

 

[Douglas J. Steele]

Use a combo box with its LimitToList property set to True.

Put code in the combo box's NotInList event to accept their ID. The example
for this in the Help file is pretty good.

 

[Klatuu]

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 = MeMyCombldValue
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

 

[Mike]

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

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 = MeMyCombldValue
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

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

 

[Mike]

The part in the After Update event works. It looks up the value if it
exists.

However, when the value doesn't exist, it throws an error:

Compile Error:
Method or Data Member not found.

It is specifically point to the quoted section of the following line in the
NotInList event: (quotes aren't in code, just in post)

"Me.MyCombo" = MeMyCombldValue

I get the feeling it needs to be defined somewhere, no?

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 = MeMyCombldValue
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

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

 

[Douglas J. Steele]

Dave made a slight typo. There should be a period between Me and MyCombo.

Me.MyCombo = Me.MyCombldValue

(And, of course, you should replace MyCombo with the actual name of your
combo box)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The part in the After Update event works. It looks up the value if it
exists.

However, when the value doesn't exist, it throws an error:

Compile Error:
Method or Data Member not found.

It is specifically point to the quoted section of the following line in
the NotInList event: (quotes aren't in code, just in post)

"Me.MyCombo" = MeMyCombldValue

I get the feeling it needs to be defined somewhere, no?

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 = MeMyCombldValue
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

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

 

[Mike]

I figured out the replacing MyCombo bit only after posting the previous
message. Common mistake, i'm sure, but it still made me feel dumb

It works now. Thanks everyone.

Also, [Activity] needs updated to the correct value, too. In the case of the
example text given, it should be [ID]

Thanks again!

Now to get the proper input masks to work!

Dave made a slight typo. There should be a period between Me and MyCombo.

Me.MyCombo = Me.MyCombldValue

(And, of course, you should replace MyCombo with the actual name of your
combo box)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The part in the After Update event works. It looks up the value if it
exists.

However, when the value doesn't exist, it throws an error:

Compile Error:
Method or Data Member not found.

It is specifically point to the quoted section of the following line in
the NotInList event: (quotes aren't in code, just in post)

"Me.MyCombo" = MeMyCombldValue

I get the feeling it needs to be defined somewhere, no?

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 = MeMyCombldValue
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


:

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