How do I insure that my Update Query will only update the current record?

[khughes46]

I have a multi-user DB that users may be using at various times. The concept is for recording grades at the end of a period (quarter in this case). Once the grade and date have been entered a report is printed for that student. In order to prevent that same report from printing again I have a Yes/No filed to prevent that. However, if more than 1 user is doing this, when user 1 runs the report and update, user 2's record will also get updated and prevent the report from being produced. How do I insure that only the record for user 1 is updated? I am not up on VBA coding yet, but am willing to try if I get good instructions. Thanks.

[burrina]

Add a reference to the form in your query. Here is a screenshot example.


HTH

[khughes46]

I can't read the criteria row on my monitor. Could you please type it out?

[ranman256]

use the record key.

[khughes46]

I don't know what you mean. Where and how would I use the record key?

[June7]

The unique ID for the record.

UPDATE tablename SET fieldname=True WHERE ID = Forms!formname!ID;

Or use the userID to restrict the record(s) that will be updated.

UPDATE tablename SET fieldname=True WHERE userID = Forms!formname!userID;

[khughes46]

I am having difficulty figuring out "ID" following the WHERE. Here is how I changed my SQL statement:
UPDATE [Student Course History] SET [Student Course History].Print1 = Yes
Where [sde campus query]![localid] = forms![course completion main form]!localid;

What am I missing?

[June7]

You are updating [Student Course History] but WHERE clause references a field in [sde campus query]. Unless [Student Course History] is a query that includes [sde campus query], this won't work.

I presume [Student Course History] is a table. Is localid a field in that table?

[khughes46]

Yes it is. I am using a form/subform where sde campus query is the data source for the form and Student Course History is the source for the subform; linked by LocalId. There are 4 quarters in a grading sequence and therefore there may be up to 4 records per student per course in the history.

[June7]

Did you mean [Student Course History] is a table?

Doesn't really make sense to reference a table for the field to update but reference a field in another table/query as filter criteria. Referencing two datasets in same query without a JOIN clause causes Cartesian relationship.

[khughes46]

Main form source is not updated, only subform source. There may be more than 1 user opening the form at the same time. It is even possible that they could be working on the same student, but not the same subject. Once the data is entered into the subform a report is printed and then I want to update the subform record just printed and only that record.

[June7]

That doesn't really address my question or comment.

[khughes46]

Sorry about that. Yes it is a table. and the two tables are joined by LocalId.

[June7]

Options for editing record programmatically:

1. if the field is included in the form RecordSource use SetValue action - with macro in design, click Show All Actions

2. if the table you want to edit is not in the form RecordSource, need to run an SQL UPDATE action.

Whichever approach suits the situation, add action to the end of the Print button macro. Not seeing either there now.

There is no filter criteria for the report. It shows all students.

[khughes46]

I will use the same criteria to only print the current record and will be checking the PRINTED (Yes/No) switch for No.