I have been struggling with this query forever and would appreciate any insight.
I am building a database for datasheets that have header info that applies to every record on the datasheet, then a series of records (sometimes in more than one category). For simplicity in entering data and to avoid redundancy in the database, I have separate tables for the header info and the records themselves, with a "datasheet ID" field in every record that links it to the header information (date, who collected the data, etc.).
I've set up a series of forms for entering the data, with a command button that users click when finished with a datasheet. The idea is that this button runs an update query - adding the "ID" value for the datasheet they entered into the "datasheet ID" field for all records entered since the last time the button was clicked. Simple, right?
I set the default value for "datasheet ID" to 0 then use that as the criteria for the update query. If i simply use [tablename]![ID] in the update field it works sometimes and not others - it will usually work for a while then start "hanging up" on a particular ID such as 40 or 80 rather than moving on to the next value. In other words, the records from datasheets 41 and up would all be linked to datasheet 40's info. Sometimes this query doesn't work at all, and the datasheet ID value remains zero.
I've tried using max or last functions to isolate the correct ID value, but if I use that in the "update to" query field I get an aggregate function error, and if use a separate query to obtain perform the function then I get "you must use an updateable query" when i run the update query.
Sorry this is so long - any ideas?
PS - If I delete the joins between all of these fields the queries work fine, but then the "datasheet ID' field doesn't actually link the record to the datasheet info.