Can't Edit Records Using a Form

  • Thread starter Thread starter David Kevorsky
  • Start date Start date
D

David Kevorsky

Hello,

On one of the forms of an in-house Access application (developer
unavailable), user input is notaccepted on the textbox controls of the
form.

Note that none of the suggestions in the Help Topic "Troubleshoot
editing data in a field in Datasheet or Form view" apply; that is:

Only one field is an AutoNumber field.
None of the fields are calculating fields.
None of the fields are disabled or locked.
Nor are the records locked by another user or process.
The form's AllowEdits property is set to Yes
and the Enabled property of the controls is set to Yes.

I would appreciate Any idea on what could cause the form to not
respond to user input? Thanks in advance for any help or suggestions.

David
 
I would appreciate Any idea on what could cause the form to not
respond to user input?
Click to expand...

It's probably the Query upon which the form is based. Open the Form in
design view, and view its Properties; select the Record Source
property (the first line on the Data tab). Click the ... icon by that
property to open a query design grid. Open the query as a datasheet -
does it have a *> "new row" at the bottom?

If it's not updateable, go to SQL view of the query and post the SQL
text here; someone should be able to figure out why it's not letting
you update.
 
Thank you both for your reply. Yes, upon following the steps you
listed,
I found that the query is not updatable.
The question is, how can the query be updatable?
Here's the query for reference:

SELECT tblVisit.RecordNo, tblVisit.VisitID, tblDonors.DonorNo,
tblDonors.FirstName, tblDonors.SecondName, tblDonors.LastName,
tblVisit.VisitDate AS Expr1, DateDiff("m",[VisitDate],Date()) AS
Expr2, tblVisit.VisitLocation, tblLocation.Location
FROM tblDonors INNER JOIN (tblLocation INNER JOIN tblVisit ON
tblLocation.VisitLocation = tblVisit.VisitLocation) ON
tblDonors.RecordNo = tblVisit.RecordNo;
 
Thank you both for your reply. Yes, upon following the steps you
listed,
I found that the query is not updatable.
The question is, how can the query be updatable?
Here's the query for reference:

SELECT tblVisit.RecordNo, tblVisit.VisitID, tblDonors.DonorNo,
tblDonors.FirstName, tblDonors.SecondName, tblDonors.LastName,
tblVisit.VisitDate AS Expr1, DateDiff("m",[VisitDate],Date()) AS
Expr2, tblVisit.VisitLocation, tblLocation.Location
FROM tblDonors INNER JOIN (tblLocation INNER JOIN tblVisit ON
tblLocation.VisitLocation = tblVisit.VisitLocation) ON
tblDonors.RecordNo = tblVisit.RecordNo;
Click to expand...

You're apparently trying to create a single form to edit three tables
- donors, visits and locations. This MIGHT be possible, if the Join
fields are properly indexed; the "one" side field
(tblLocation.VisitLocation and tblDonors.RecordNo) must have a unique
index, such as being the primary key of the table.

But as a rule, you may be better off trying to limit the number of
tables in the Form's query. If each Donor may have multiple Visits,
you might want to try using a Form based on the Donor table, with a
Subform based on the Visits table; you could use a combo box on the
subform to display the Location from the locations table while storing
the VisitLocation value.
 
Thank you very much Mr. Vinson.

Your suggestion does help indeed.
One more glitch. By having the frmEditVisits as a subform to the
frmEditDonors,
when the parent form is first loaded no data is shown in the subform,
unless,
I move to a next record on the main form. If I navigate back to the
first record on the main form, its data on the subform is shown.
How do I get data on the subform to be shown as soon as the forms are
loaded?

Thanks again.

David.

John Vinson said:
Thank you both for your reply. Yes, upon following the steps you
listed,
I found that the query is not updatable.
The question is, how can the query be updatable?
Here's the query for reference:

SELECT tblVisit.RecordNo, tblVisit.VisitID, tblDonors.DonorNo,
tblDonors.FirstName, tblDonors.SecondName, tblDonors.LastName,
tblVisit.VisitDate AS Expr1, DateDiff("m",[VisitDate],Date()) AS
Expr2, tblVisit.VisitLocation, tblLocation.Location
FROM tblDonors INNER JOIN (tblLocation INNER JOIN tblVisit ON
tblLocation.VisitLocation = tblVisit.VisitLocation) ON
tblDonors.RecordNo = tblVisit.RecordNo;
Click to expand...

You're apparently trying to create a single form to edit three tables
- donors, visits and locations. This MIGHT be possible, if the Join
fields are properly indexed; the "one" side field
(tblLocation.VisitLocation and tblDonors.RecordNo) must have a unique
index, such as being the primary key of the table.

But as a rule, you may be better off trying to limit the number of
tables in the Form's query. If each Donor may have multiple Visits,
you might want to try using a Form based on the Donor table, with a
Subform based on the Visits table; you could use a combo box on the
subform to display the Location from the locations table while storing
the VisitLocation value.
Click to expand...
 
Your suggestion does help indeed.
One more glitch. By having the frmEditVisits as a subform to the
frmEditDonors,
when the parent form is first loaded no data is shown in the subform,
unless,
I move to a next record on the main form. If I navigate back to the
first record on the main form, its data on the subform is shown.
How do I get data on the subform to be shown as soon as the forms are
loaded?
Click to expand...

Something's wrong with the Form or the query, then. Is the Subform
based on a query which references the mainform (this is not
necessary)? Do you have any VBA code in the form? What are the
Recordsource properties for the form and the subform? What are the
Master and Child Link Fields of the subform control?
 
Something's wrong with the Form or the query, then. Is the Subform
based on a query which references the mainform (this is not
necessary)?
Click to expand...

The subform is based on the query qryAllVisits that I listed earlier
which in summary is :

SELECT (some fields) FROM
tblDonors INNER JOIN (tblVisit INNER JOIN tblLocation ON
tblVisit.Location = tblLocation.Location) ON tblDonors.RecordNo =
tblVisit.RecordNo;
Do you have any VBA code in the form?
Click to expand...

Indeed. The subform's OnLoad event subprocedure contains the following
line:
DoCmd.GoToRecord , , acFirst

Note however, that both before adding this line, and after, the
behavior is the same.

The Main form's OnLoad event procedure has this line:

Me!frmEditVisits2.Form.Requery

(note, frmEditVisits2 is the name of the subform)
Again, the subform behavior remains the same before and after adding
this line of code - which is very strange.
What are the Recordsource properties for the form and the subform?
Click to expand...

The Main form's record source is the table tblDonors.
Its Recordset Type, however is Dynaset; Fetch Defaults = Yes.

The subform's Source Object is frmEditVisits2 The Master and Child
Link Fields of the subform are RecordNo; DonorNo

frmEditVisits2' Record Source is qryAllVisits, and the Recordset Type
is Dynaset.

Note, If I change the form's Source Object to be simply a query, I get
a datasheet display of all visits for each donor - obviously - and no
form.

Thank you again. I hope this information is clear.

David
 
The subform is based on the query qryAllVisits that I listed earlier
which in summary is :

SELECT (some fields) FROM
tblDonors INNER JOIN (tblVisit INNER JOIN tblLocation ON
tblVisit.Location = tblLocation.Location) ON tblDonors.RecordNo =
tblVisit.RecordNo;
Click to expand...

Ok... this is odd. It sounds like you have fields from tblDonors on
the mainform and also on the subform. WHY? Ordinarily you would set
the Subform's master link field and child link field to Donors. What
are the Master/Child Link Fields?
Indeed. The subform's OnLoad event subprocedure contains the following
line:
DoCmd.GoToRecord , , acFirst
Click to expand...

This will fire before the mainform is loaded (subforms get loaded
first) but shouldn't cause this problem...
Note however, that both before adding this line, and after, the
behavior is the same.

The Main form's OnLoad event procedure has this line:

Me!frmEditVisits2.Form.Requery
Click to expand...

and this should not be necessary, since the subform gets loaded
(queried) when the main form loads in any case.

Note, If I change the form's Source Object to be simply a query, I get
a datasheet display of all visits for each donor - obviously - and no
form.
Click to expand...

Sounds like you have no Master/Child link.
 
Hello

As per my last reply,

The subform's Source Object is frmEditVisits2 The Master and Child
Link Fields of the subform are RecordNo; DonorNo .
 
The subform's Source Object is frmEditVisits2 The Master and Child
Link Fields of the subform are RecordNo; DonorNo .
Click to expand...

Probably should just be RecordNo since there is no DonorNo in the main
form!
 
Back
Top