Cannot find a matching key. Error 3101.

[Guest]

I have an autolook up query that automatically fills data in on a form. The
primary key is the phone number. It works fine when I it finds a matching
key. If it doesn't find a matching key and I try to save the record I get the
following message. The Microsoft Jet database engine cannot find a record in
the table <name> with key matching field(s) <name>. (Error 3101). I
understand what this is telling me, but is there a way to save a record when
the fields do not match. Is there a way of bypassing this.

 

[John Vinson]

I have an autolook up query that automatically fills data in on a form. The
primary key is the phone number. It works fine when I it finds a matching
key. If it doesn't find a matching key and I try to save the record I get the
following message. The Microsoft Jet database engine cannot find a record in
the table <name> with key matching field(s) <name>. (Error 3101). I
understand what this is telling me, but is there a way to save a record when
the fields do not match. Is there a way of bypassing this.

Please post your code. What do you WANT to see in the form if there is
no data to fill in?

John W. Vinson[MVP]

 

[Guest]

Please post your code. What do you WANT to see in the form if there is
no data to fill in?

John W. Vinson[MVP]
I am new to working with acess,. I tried looking at the sample data base, but I couldn't find anything that looked like what I was trying to do. I do not have any code that I know, at least I didn't create any. I wouldn't know were to put the code if I did have any. What I was trying to do was make a service call record database. When you put in the phone number it fills the data from the customer table, which when it does find the matching number, it does fill in the data. What I thought it would if it didn't find a mathing number it would let me save it any way.

Thanks

 

[John Vinson]

I am new to working with acess,. I tried looking at the sample data base, but I couldn't find anything that looked like what I was trying to do. I do not have any code that I know, at least I didn't create any. I wouldn't know were to put the code if I did have any. What I was trying to do was make a service call record database. When you put in the phone number it fills the data from the customer table, which when it does find the matching number, it does fill in the data. What I thought it would if it didn't find a mathing number it would let me save it any way.

I'm sorry, James - I cannot see your database, and I really don't know
what kind of "autolookup" you are using. Is this on a Form? in a
Table? If it's a Form, and you are looking up the phone number in a
combo box, it's likely that you've used one of the Wizards to create
VBA code in the combo box's properties. Try opening the Form in design
view, view the Properties of the combo box, and see if it says [Event
Procedure] on the combo's AfterUpdate event (or any other event). If
it does, please click the ... icon by the event to open the VBA
editor, and copy and paste the VBA code to a message here.

It may also help to post the following properties of the combo box:
Row Source; Control Source; Column Count; Bound Column; LImit to List.


John W. Vinson[MVP]

 

[Guest]

I am new to working with acess,. I tried looking at the sample data base, but I couldn't find anything that looked like what I was trying to do. I do not have any code that I know, at least I didn't create any. I wouldn't know were to put the code if I did have any. What I was trying to do was make a service call record database. When you put in the phone number it fills the data from the customer table, which when it does find the matching number, it does fill in the data. What I thought it would if it didn't find a mathing number it would let me save it any way.

I'm sorry, James - I cannot see your database, and I really don't know
what kind of "autolookup" you are using. Is this on a Form? in a
Table? If it's a Form, and you are looking up the phone number in a
combo box, it's likely that you've used one of the Wizards to create
VBA code in the combo box's properties. Try opening the Form in design
view, view the Properties of the combo box, and see if it says [Event
Procedure] on the combo's AfterUpdate event (or any other event). If
it does, please click the ... icon by the event to open the VBA
editor, and copy and paste the VBA code to a message here.

It may also help to post the following properties of the combo box:
Row Source; Control Source; Column Count; Bound Column; LImit to List.


John W. Vinson[MVP]

I have 2 tables one named 'customers' the other 'service calls'. I made a select query. (autolooup). I made a form. The field on the form for the phone# is a text box. When I add a new record and the phone number matches the phone number in the 'customers' table, it automatcally fills in the name, address, city, etc. by itself. It works fine. It also updates the 'service calls' table. It is when I enter a phone number and doesn't find a matching phone number, that I get the following. I have an autolook up query that automatically fills data in on a form. The

primary key is the phone number. It works fine when I it finds a matching
key. If it doesn't find a matching key and I try to save the record I get the
following message. The Microsoft Jet database engine cannot find a record in
the table <name> with key matching field(s) <name>. (Error 3101). I
understand what this is telling me, but is there a way to save a record when
the fields do not match. Is there a way of bypassing this.

 

[John Vinson]

The
primary key is the phone number. It works fine when I it finds a matching
key. If it doesn't find a matching key and I try to save the record I get the
following message. The Microsoft Jet database engine cannot find a record in
the table <name> with key matching field(s) <name>. (Error 3101). I
understand what this is telling me, but is there a way to save a record when
the fields do not match. Is there a way of bypassing this.

I don't understand what you expect.

You look up a nonexistant record.
It's NOT THERE.
What is it that you want to save???

John W. Vinson[MVP]

 

[Guest]

I don't understand what you expect.

You look up a nonexistant record.
It's NOT THERE.
What is it that you want to save???

John W. Vinson[MVP]

What I thought I could do is when I input the phone number and it doesn't
find the (matching key) lookup phone # in customer table and automatically
fill the information in on the form. I wanted to leave the unfound phone #
there and manually put the location, address, city, etc. and then be able to
save this record. I hope this makes more sense.

 

[John Vinson]

What I thought I could do is when I input the phone number and it doesn't
find the (matching key) lookup phone # in customer table and automatically
fill the information in on the form. I wanted to leave the unfound phone #
there and manually put the location, address, city, etc. and then be able to
save this record. I hope this makes more sense.

I'd recommend using two controls: an unbound textbox to look up the
record, and a bound control to enter the phone number. You should have
code in the control's AfterUpdate event; you can use code like

Private Sub txtFindPhone_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's data
rs.FindFirst "[Phone] = '" & Me.txtFindPhone & "'"
If rs.NoMatch Then
' go to the new record
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
' copy the phone number into the bound control
Me.txtPhone = Me.txtFindPhone
MsgBox "Not found, adding new record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

John W. Vinson[MVP]

 

[Guest]

What I thought I could do is when I input the phone number and it doesn't
find the (matching key) lookup phone # in customer table and automatically
fill the information in on the form. I wanted to leave the unfound phone #
there and manually put the location, address, city, etc. and then be able to
save this record. I hope this makes more sense.

I'd recommend using two controls: an unbound textbox to look up the
record, and a bound control to enter the phone number. You should have
code in the control's AfterUpdate event; you can use code like

Private Sub txtFindPhone_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's data
rs.FindFirst "[Phone] = '" & Me.txtFindPhone & "'"
If rs.NoMatch Then
' go to the new record
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
' copy the phone number into the bound control
Me.txtPhone = Me.txtFindPhone
MsgBox "Not found, adding new record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

John W. Vinson[MVP]
Now I really am lost you said to have an unbound text box to look up the record, I don't get if it is unbound how can it lookup anything. Tanks for your patience.

 

[John Vinson]

Now I really am lost you said to have an unbound text box to look up the record, I don't get if it is unbound how can it lookup anything. Tanks for your patience.

The code I posted will navigate to the record containing that phone
number if there is one (the "lookup"), or enter the new phone number
if there isn't. Just what you asked for.

John W. Vinson[MVP]

 

[Guest]

The code I posted will navigate to the record containing that phone
number if there is one (the "lookup"), or enter the new phone number
if there isn't. Just what you asked for.

John W. Vinson[MVP]
The code that you gave me do I have to put any field, file, table names in it. Like it says me.name, do I have put a file name or table name. Do you know if there any good books on learning how to use Access. Thanks.

 

[John Vinson]

The code that you gave me do I have to put any field, file, table names in it. Like it says me.name, do I have put a file name or table name. Do you know if there any good books on learning how to use Access. Thanks.

The Me shortcut refers to the current form.

Me.Name means "the Name property of this form".

I did assume that you have two textboxes on the form - one named
txtFindPhone, unbound, into which the user types a phone number to
locate a record; and a second textbox named txtPhone, bound to the
Phone field. If you have different names for those controls, change
the code to match your control names.

There are numerous good books on Access. See the resources at:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Reposting the code just in case...

Private Sub txtFindPhone_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's data
rs.FindFirst "[Phone] = '" & Me.txtFindPhone & "'"
If rs.NoMatch Then
' go to the new record
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
' copy the phone number into the bound control
Me.txtPhone = Me.txtFindPhone
MsgBox "Not found, adding new record"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub


John W. Vinson[MVP]