Hello,
Could someone please help with this problem i am having with SELECT query in VBA.
I am sure it is obvious to the trained eye but i am going mad with it. :-)
1. I am creating a copy of a current table (TableA) for backup purposes (TableA-"date")
on FORM A.
<code>:
newTable = strTable & "-" & Format(Date, "yyyy") '-- new table name = 'TableA-2012'
DoCmd.CopyObject , newTable, acTable, strTable
DoCmd.Close acTable, newTable, acSaveYes
</code>
Result = The backup table is created and populated perfectly.
2. I then run another event procedure from same form (different button) to reset all money values to NULL in the original table ('TableA')
<code>
CurrentDb.Execute (UpdateQuery) '-- updateQuery is stored in DB and just sets values to NULL
</code>
Result = This works fine. Values are set to NULL.
So at this point I have the backup table with correct info in it and the original table with values reset to NULL. All ok.
3. Then in another form I make a call to select fields from the backup table (newTable = 'TableA-2012') as follows.
<code>
Set qdef = CurrentDb.CreateQueryDef("mySQL")
qdef.SQL = "SELECT ID, Name, PG, BO " & _
" FROM " & newTable & _
" WHERE ((([PG]) = Yes) And (([BO]) = Yes)) " & _
" ORDER BY [Name]"
DoCmd.OpenQuery "mySQL", acViewNormal, acEdit
</code>
Result: It seems to be pulling data from the original table i.e returning the NULL values.
If i create an SQL statement in the DB and store and then hard code a call to it in step 3 it works fine. If i go into the backup table the values are all there.
??????
Has this got something to do with table dependencies or the way the copy object was done?
I am no expert in this so any help would be greatly appreciated!!!
Is there a mapping issue in system tables ???
I have been driven crazy with this one.
Many thanks in advance !!!