If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Problem with jet database
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 |
#3
|
|||
|
|||
Problem with jet database
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 . |
#4
|
|||
|
|||
Problem with jet database
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? |
#5
|
|||
|
|||
Problem with jet database
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 |
#6
|
|||
|
|||
Problem with jet database
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 |
#7
|
|||
|
|||
Problem with jet database
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? |
Thread Tools | |
Display Modes | |
|
|