Insert to Query (1 Viewer)

[cktcPeterson]

I just came across insert query while trying to find out how to copy a filed in table Applications to a specific record/field in Requested Course.

Here is what I am trying to do.

I have a form with table/query Requested Course
If the Student ID# is blank I have a pop up form people can search for their id number.

The form with a list of Students and ID numbers search page pops up.

They can select name and the ID number filters.

I want to click a command button and copy the field and past in the Requested Course form/query/table (not sure the language on that)

ideally just a copy and paste vba or macro will help, but I came across insert query and was intriqued.

Thanks!

 

[ebs17]

was intriqued

Keep that.

However, an append query always creates new data sets. A single field would be inserted into an existing record using an update query. But maybe you're just working in a bound form. This is not quite as spectacular, but highly functional and simple.

 

[Pat Hartman]

As ebs17 mentioned, you would not use an Append query for this, you would use an Update query since the record already exists. But, more likely, you would use a bound form with a combo for the StudentID and just let the record save naturally. No code is required. Since this is a one-time event, you would create a special form that is bound to a query that selects only the StudentClass records where the StudentID is null.

HOWEVER, your problem goes beyond this. You need to fix whatever form allowed this record to be saved without a StudentID in the first place. Do not even try to fix the data without first fixing the gaping hole in your logic that allowed this error to happen. To fix this form requires three separate fixes.
1. Add a compound unique index on the StudentID, ClassID, and possibly SemesterID if it is possible for a student to retake a class. Use the Indexes dialog to do this since you can't create a compound index in the table view. Post back if you don't know how to make a multi-column index.
2. Mark the StudentID and the ClassID and possibly the SemesterID if that comes into play in the situation as required. Make sure the default is null rather than 0 for these long integer FK's
3. Add code in the form's BeforeUpdate event. Validate all fields that are required and also do sanity checks on other types of fields like dates. 1/31/223 is a valid date as far as Access is concerned but doesn't make sense logically for the vast majority of applications so you want to do range checks at a minimum. You can't stop a user from entering 1/31/23 as a start date when the actual start date is 1/30/23 but you can at least make sure the date is rational.

Once you have fixed the original problem, then you can create a query that selects all the StudentClass records with a null StudentID. You probably should just delete them since there really isn't any way to figure out what StudentID is missing. Then maybe send a notice to all students that they need to check their list of assigned classes to determine if their registration was incomplete.