Update Query SQL Subforms Multiple Queries Command Buttons (long and complex)

Tables:  tbLImport(PK AutoNumber,FK GID), tblDataEntry(PK GID)

Relationships: All Records from tbLImport on GID and only records from tblDataEntry where GID is equal

Queries: select_tbLImport_where_GID_is_on_frmDataEntry, update_tbLImport_where_AutoNumber_is_on_sbfrm_Import (see below for SQL)

Forms: frmDataEntry(sbfrm_Import), sbform_Import, frmManuaLImport (see below for command buttons)

Here is what I am doing.

I have a spreadsheet that I get everyday with employee event summaries.  I import the event in to tbLImport with an append query.  I also append tblDataEntry with any new GID's from the spreadsheet.  When something new happens with the employee this shows up on the spreadsheet.   The spreadsheet has basic information about the employee and the event.  For each event that occurs for an employee, I need to make several different types of actions.  These actions get logged for each event.

All of this works fine and dandy, until recently.  Now it is possible that I will have an employee event that will not get recorded on the spreadsheet.  Also, we have noticed that events can be updated from the source of the spreadsheet (typos, etc).  This means that I might be working on an event, and the basic event could change.  For instance, if the source told me the event was that the employee got a raise for $2 then I would begin taking action and making comments on that event.  Two days later, the source tells me the raise was for $4 dollars.  In that case, I would still be taking the same action with the exception that now my database stores this as a new event.  This means that all of my records of action for the raise stayed the same, except the update of the dollar amount.

So what I need the database to do is give me the ability to update any new record with data from past records.  This is what I call a ManuaLImport since it does the same thing as my AutomatedImport except the source data comes from existing records rather than my daily spreadsheet.  

Finally the problem.

Starting on frmDataEntry which is prepopulated with data from sbfrm_Import. sbfrm_Import is set to sort on AutoNumber DESC and cannot be scrolled or record selected.  I do this so that when I filter GID on  frmDataEntry, I only get the most recent tbLImport data. This is an important user interface feature and ensures that past records are not altered, security etc..  Going back to the scenario above; what happens is that if something changes in the source spreadsheet after I start my work, then I am forced to go back and copy/paste my work to the new event record.

So what I am doing is putting a button on frmDataEntry that opens up all the records related to the GID I filter on for the purpose of choosing a past record to update the new record with.  That button launches the frmManuaLImport which is based on select_tbLImport_where_GID_is_on_frmDataEntry which works flawlessly.  

Next I will now have the option to navigate to a history item or create a new event for that GID.  Once I have chosen a past event or created a new event, a button on frmManuaLImport launches update_tbLImport_where_AutoNumber_is_on_sbfrm_Import.  Everything appears to work great except...

You are about to update 0 rows(s)!

Now I know that was a lot of beef for a stupid error, but I have been bashing my head against the screen trying to figure out why this is happening.  What makes it even worse is that If I open up sbform_Import and run update_tbLImport_where_AutoNumber_is_on_sbfrm_Import, I am prompted for the AutoNumber and when I put it in, I get my update.

So with out further ado(pun intended), please tell me what is wrong with this SQL!

UPDATE tbLImport SET tbLImport.ImportDate = Forms!frmManuaLImport!ImportDate
WHERE (((tbLImport.AutoNumber)=Forms!frmDataEntry!sbfrm_Import!AutoNumber));
Start Free Trial