Problem with jet database

I keep getting the following message on a form when I try to edit it:

The Microsoft Jet database engine cannot find a record in the table
‘Personnel’ with key matching fields(s)’ ‘qryReceive.LastName’.

It will not let me go from form view to design view. I can get into design
from the database window but not directly from form view. The form contains
the field LastName, as does the underlying query. The query (qryRecieve)
joins two tables, tblRecieve and Personnel by "LastName".

Can anyone help me solve this issue?

Not enough detail. Do you receive an error directly from the query, when you
first go into the form or when you do some action?

Bonnie
http://www.dataplus-svc.com

Orlan wrote:
I keep getting the following message on a form when I try to edit it:

The Microsoft Jet database engine cannot find a record in the table
‘Personnel’ with key matching fields(s)’ ‘qryReceive.LastName’.

It will not let me go from form view to design view. I can get into design
from the database window but not directly from form view. The form contains
the field LastName, as does the underlying query. The query (qryRecieve)
joins two tables, tblRecieve and Personnel by "LastName".

Can anyone help me solve this issue?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1

I get the message when I go from the form in form view to design view.
Opening the query itself does not give the message.

From the form view, if I do not enter a valid last name in the LastName
field, it gives me the message.

"bhicks11 via AccessMonster.com" wrote:

Not enough detail. Do you receive an error directly from the query, when you
first go into the form or when you do some action?

Bonnie
http://www.dataplus-svc.com

Orlan wrote:
I keep getting the following message on a form when I try to edit it:

The Microsoft Jet database engine cannot find a record in the table
‘Personnel’ with key matching fields(s)’ ‘qryReceive.LastName’.

It will not let me go from form view to design view. I can get into design
from the database window but not directly from form view. The form contains
the field LastName, as does the underlying query. The query (qryRecieve)
joins two tables, tblRecieve and Personnel by "LastName".

Can anyone help me solve this issue?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1

.

joins two tables, tblRecieve and Personnel by "LastName".

Do you have more than one "Smith" or other duplicate last names? Right there
would stop the form from being editable. The LastName field needs to be the
primary key in at least one of those tables for this to work. You should
almost always join on a primary key field.

Even then it's usually best to have a form probably based on the Personnel
table and on that form a subform based on tblRecieved. Or maybe the other way
around. Form/subform works better than a query when putting two tables to be
editied on a form rather than a query that joins those two tables.


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Orlan" wrote:

I keep getting the following message on a form when I try to edit it:

The Microsoft Jet database engine cannot find a record in the table
‘Personnel’ with key matching fields(s)’ ‘qryReceive.LastName’.

It will not let me go from form view to design view. I can get into design
from the database window but not directly from form view. The form contains
the field LastName, as does the underlying query. The query (qryRecieve)
joins two tables, tblRecieve and Personnel by "LastName".

Can anyone help me solve this issue?

Why are you worrying about an error going from form to design? It's just
because you haven't filled in the last name or are interrupting a process. I
would ignore that error and just go to design or go to design from the
database window.

Bonnie
http://www.dataplus-svc.com

OVJ wrote:
I get the message when I go from the form in form view to design view.
Opening the query itself does not give the message.

From the form view, if I do not enter a valid last name in the LastName
field, it gives me the message.

Not enough detail. Do you receive an error directly from the query, when you
first go into the form or when you do some action?
[quoted text clipped - 13 lines]

Can anyone help me solve this issue?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1

That suggests that the form is Dirty, i.e. an unsaved record has been
initiated, when you attempt to, switch to design view. Doing this acts in
the same way as closing a form and automatically updates any current unsaved
record. If required data is missing, in this case a LastName value, this will
trigger a data error. You should be able undo the current record by pressing
the Esc key, though you might need to do it twice. Provided the form is
showing the record selector you can see if its Dirty or not, as the record
selector changes to a pencil symbol when the form is Dirty.

However, your post does imply another flaw in the design of the database as
you are using the LastName columns as keys. Personal names are unsuitable as
keys as they can legitimately be duplicated; I once worked with two Maggie
Taylors, and on the wildlife foundation in South Africa where one of my sons
currently works there is a second Sheridan. Even combining names with other
columns does not guarantee a unique key; I've mentioned here before how I was
present at a clinic one morning when two patients arrived, both female, with
exactly the same first and last names, and the same date of birth!

For people, or any other entity type for which duplicate values are
legitimate, e.g. Cities, a surrogate numeric key is best employed, e.g. the
primary key of a Personnel table can be EmployeeID, which can for convenience
be an autonumber, with an EmployeeID foreign key in any table which
references Personnel. For data entry in a form based on a referencing table
a combo box can be used, set up as follows:

Name: cboEmployee

ControlSource: EmployeeID

RowSource: SELECT EmployeeID, FirstName & " " & LastName FROM Personnel
ORDER BY LastName, FirstName;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

This will provide you with a list of employees in the format 'Ken Sheridan'
sorted by last name then first name, so the user merely has to select from
the drop down list. The value of the control, and thus the value in the
underlying EmployeeID foreign key column, will be the hidden numeric value.

This also means that your form can probably be based on the tblRecieve table
only rather than joining it to the Personnel table. If there are other
values from the Personnel table which you wanted to show in the form you can
do so by including the relevant column(s) in the combo box's RowSource. Say,
for example you wanted to show the selected employee's AppontmentDate on the
form then you'd amend the combo box's RowSource to:

RowSource: SELECT EmployeeID, AppointmentDate, FirstName & " " & LastName
FROM Personnel ORDER BY LastName, FirstName;

and it ColumnCount to: 3, and its ColumnWidths property to: 0cm;0cm;8cm

and add an unbound text box to the form with a ControlSource property of:

=cboEmployee.Column(1)

The Column property is zero-based, so Column(1) is the second column,
AppoinmentDate.

You can add as many columns to the RowSource property, and corresponding text
boxes as you wish to show other columns from Personnel

Ken Sheridan
Stafford, England

OVJ wrote:
I get the message when I go from the form in form view to design view.
Opening the query itself does not give the message.

From the form view, if I do not enter a valid last name in the LastName
field, it gives me the message.

Not enough detail. Do you receive an error directly from the query, when you
first go into the form or when you do some action?
[quoted text clipped - 13 lines]

Can anyone help me solve this issue?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1

I think Ken Sheridan's comment about unsaved data solves the immediate
problem. I will have to work on the last name issue. I use it to bring up an
email address so an email can be sent from the form.

Oh well, more fun in coding.

Orlan


"Jerry Whittle" wrote:

joins two tables, tblRecieve and Personnel by "LastName".

Do you have more than one "Smith" or other duplicate last names? Right there
would stop the form from being editable. The LastName field needs to be the
primary key in at least one of those tables for this to work. You should
almost always join on a primary key field.

Even then it's usually best to have a form probably based on the Personnel
table and on that form a subform based on tblRecieved. Or maybe the other way
around. Form/subform works better than a query when putting two tables to be
editied on a form rather than a query that joins those two tables.


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Orlan" wrote:

I keep getting the following message on a form when I try to edit it:

The Microsoft Jet database engine cannot find a record in the table
‘Personnel’ with key matching fields(s)’ ‘qryReceive.LastName’.

It will not let me go from form view to design view. I can get into design
from the database window but not directly from form view. The form contains
the field LastName, as does the underlying query. The query (qryRecieve)
joins two tables, tblRecieve and Personnel by "LastName".

Can anyone help me solve this issue?