can't edit record anymore in a form

[guilin]

the Allow Edit and Allow Additions properties of the form are all set
to be true but it won't allow me to change records. I used some code to
promp before edit:

**********code starts*********************

Private Sub Form_BeforeInsert(Cancel As Integer)
'ask user if they want to add a record
Dim strMsg As String

strMsg = "Do you want to add a new invoice?"
strMsg = strMsg & "Click Yes to add or No to cancel."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add Record?") = vbYes Then
AllowAdditions = True
Else
Cancel = True
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'ask user if they want to save the updated record
Dim strMsg As String
Dim intResponse As Integer

strMsg = "You are about to change this invoice detail. "
strMsg = strMsg & "Do you wish to save the changes?"
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

intResponse = MsgBox(strMsg, vbYesNoCancel, "Update Data?")

Select Case intResponse
Case vbYes
Case vbNo
DoCmd.RunCommand acCmdUndo
Cancel = True
Case vbCancel
Cancel = True
End Select

End Sub

**********************code ends**********************************
this form also has a parent form so it can be opened from its parent or
on its own, don't know whether it changes things?
thanks for all advice....................

 

[Steve Schapel]

Guilin,

Have you changed the query that the form is based on? If you open the
query datasheet, can you edit records in there?

 

[John Vinson]

the Allow Edit and Allow Additions properties of the form are all set
to be true but it won't allow me to change records.

The most common reason for this is that you may have made some change
to the Query upon which the form is based, making the query
non-updateable. Try opening the Query in datasheet view - does it let
you edit?

John W. Vinson[MVP]

 

[guilin]

Hi Steve & John,

thanks for reply, the query is based on 2 tables:

SELECT tblInvoice.*, tblPatientInformation.LastName FROM
tblPatientInformation INNER JOIN tblInvoice ON
tblPatientInformation.PatientID=tblInvoice.PatientID;

and yes you are right it doesn't allow me to edit it in datasheet
either. But I don't quite understand how I got there, the query seems
simple enough? (sorry I havn't a clue how the nonupdateable happened?)
Also there is a subform embeded in this form linked on InvoiceID filed,
could that be the problem?

thanks again for replying!

 

[guilin]

930pm Changed the query to based on one table -- didn't work either.
10pm realized that the subform contained a calculated field: the
invoice total.

guess there's no way around it so I should build a new form for
editing/adding record? or beter seperate the subform?

 

[John Vinson]

Hi Steve & John,

thanks for reply, the query is based on 2 tables:

SELECT tblInvoice.*, tblPatientInformation.LastName FROM
tblPatientInformation INNER JOIN tblInvoice ON
tblPatientInformation.PatientID=tblInvoice.PatientID;

and yes you are right it doesn't allow me to edit it in datasheet
either. But I don't quite understand how I got there, the query seems
simple enough? (sorry I havn't a clue how the nonupdateable happened?)
Also there is a subform embeded in this form linked on InvoiceID filed,
could that be the problem?

thanks again for replying!

Neither the existance of the subform, nor the presence of a calculated
field should prevent updating. Is PatientID the Primary Key of
tblPatientInformation? Is it really necessary to join the two tables -
you could instead just have a combo box which displays the patient
last name (or full name, e.g. [LastName] & ", " & [FirstName]) but
stores the ID?

John W. Vinson[MVP]

 

[guilin]

thanks John. I did just as you suggested but the form returns blank
sheet show no record in it.......

 

[John Vinson]

thanks John. I did just as you suggested but the form returns blank
sheet show no record in it.......

You've lost me. What did you actually use as the Recordsource query
for the form? Please post the SQL view of the current query.

John W. Vinson[MVP]

 

[Steve Schapel]

Guilin,

Another thing to check here would be the Allow Edits property of the form.

 

[S Evans]

what's the sql view?

the recordset source is now
SELECT tblInvoice.* FROM tblInvoice;
and the Allow *** properties are all set to true