How to update header values

[pbuethe]

I have an Access form which is supposed to look like an existing paper form. The paper form has a header. I have one table which is already populated and contains most of the fields for the header. The Access form is based on a query which enables me to display these values. (Actually the values in the query also involve a couple of other tables.) The header however also contains several other fields which must be entered. They are interspersed with the pre-populated fields on the paper form. I have another table with the entered fields. How can I enter these fields? If these fields are in the query it says "this recordset is non-updateable" when you try to enter in the field. Can you help?

I am going to try to explain this in more detail. I started with tblVisitsMaster, which contains data that was imported from another (non-Access) database. The fields are:
ClaimsID (PK)
ServiceDate
ProviderName
PatientNbr
ServiceYear
and some other fields.

Each record represents a visit, or claim, of a patient. One patient has service on multiple dates. ServiceYear is the year of the ServiceDate and can be 2001 or 2002.

I used this table to generate reports which were case lists of patients to be reviewed. One report listed each patient and the number of visits for that patient, and the other listed each visit separately.

After this, a paper form was developed which is the review form. A review will apply to one patient's visits for one year. On the header of this form, some of the info can come from the other database. Some has to be entered. I imported the known data into tblInterPatients. Its structure is:
MedicaidNbr (PK)
PatientName
DOB
Sex

The MedicaidNbr is the same as the PatientNbr in tblVisitsMaster. tblInterPatients has a one-to-many relationship to tblVisitsMaster.

For the fields that need to be entered, I created tblPatientsEntries with fields:
MedicaidNbr
ServiceYear
MedRecNbr
SSN
VisitsReviewed
Reviewer
ReviewDate
Comments

This would make MedicaidNbr and ServiceYear a compound PK. It would have a one-to-many relationship with tblVisitsMaster and many-to-one with tblInterPatients (actually two-to-one, one for 2001 and one for 2002). I thought of having separate fields for the last 4 fields for each year (MedRecNbr and SSN would be the same), but how would I put each in one field on a form?

In trying to create the review form, at first I based it on a query including: tblInterPatients, tblPatientsEntries, and queries based on tblInterPatients and tblVisitsMaster. I got the existing data to display, but when I tried to enter data got the message
"this recordset is non-updateable". I took tblPatientsEntries out of the query and based the form on tblPatientsEntries. Then I could enter the values in tblPatientsEntries but not display those from tblPatients. (BTW I had filled the MedicaidNbr and ServiceYear fields in tblPatientsEntries.) I tried doing a DLookup but it didn't work. Then I changed back to putting tblPatientsEntries in the query and basing the form on the query. This time on attempted entry I got the message: "Form is read-only". However AllowEdits etc. are Yes.

I am totally confused now as to how I can display the existing data and enter the new data. Do I need to change the table structure somehow? And what is the best way to select a record to display/enter? Now I have another form with combo boxes for the MedicaidNbr and year and these are criteria in the query.

After this I am also planning to have a subform where data will be entered for each visit in a year. (It will be in another table with a one-to-one relationship with tblVisitsMaster).

Can you help me sort this out? This forum has been extremely helpful in the past, and I hope you can be with this complex question.

 

[pbuethe]

I have managed to mostly solve this. I have a form to select the Medicaid Nbr and Service Year. Then press a button on this form to get the data entry form. The data entry form is based on a query containing fields from tblInterPatients and tblInterPatientsEntries. (I removed a subquery that was based on tblVisitsMaster). Some header fields are based on DLookups to a query based on tblVisitsMaster. The subform is based on a query containing tblVisitsMaster and tblInterVisits (this is the table containing data entered in the subform). I also needed to set UniqueValues to No in the first query. Now all the existing data is displayed and I can enter where needed in the main form and subform. However, the DLookups take a while to display. Also, having the case selection on a separate form seems inefficient but I couldn't seem to get it to work on the same form as the data entry.