DataBase - Append Query problem

Asked By Rocky

29-Jan-10 02:05 AM

I have an Append Quey that works fine when you right click on the
query ("ReaderIndCancelProforma_Append") and choose open

INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on
mailing list], TypeId, ReasonId, Reason, [Date off mailing list] )
SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id],
Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas,
[Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off
FROM Reader_DistrCurrent
WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader
Id]));

The problem I have is when I run the query from a command button on a
form.  It only appends the [Reader Id]

Private Sub Command8_Click()
On Error GoTo Command8_Click_Err

DoCmd.SetWarnings False

DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
acViewNormal, acEdit 'this doesnt work

DoCmd.OpenQuery
works

DoCmd.OpenQuery "ReaderIndProFormaCancel_Delete",
acViewNormal, acEdit ' this works

DoCmd.OpenQuery "ReaderIndProformaCancel_Update",
acViewNormal, acEdit 'this works


DoCmd.SetWarnings True

Command8_Click_Exit:
Exit Sub

Command8_Click_Err:
MsgBox Error$
Resume Command8_Click_Exit

End Sub

Why should this suddenly happen.  All the other queries work fine

I have other forms that run similar code and they all work fine.

Would appreciate some help on what the problem could be

Rocky Swartz.

29-Jan-10 02:01 AM

When you say it does not work - what actually happens?
You can put in some debugging code to help you find the error.
-----------------
Private Sub Command8_Click()
'On Error GoTo Command8_Click_Err

'DoCmd.SetWarnings False

Debug.Print Forms!Reader_DB!ReaderId
Debug.Print Forms!Reader_CancelProforma!Remarks

DoCmd.OpenQuery "ReaderIndCancelProforma_Append",
acViewNormal, acEdit 'this doesnt work

End Sub
----------------

To debug, put those lines of code in your button click routine, comment out
the error handler and the set warnings statements.
Click the button to run the query.
Open the immediate window Ctl+G to see what access got for ReaderID and
Remarks


If the form Reader_DB is not open when you click the button, the query will not
run because it will not be able to get the value for ReaaderId.
Same thing for the form Reader_CancelProforma
You can also open both forms Reader_DB and Reader_CancelProforma and open a
copy of that query saved as a select query to see what data it picks up from
the 2 forms.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

29-Jan-10 12:35 PM

Try   DoCmd.RunSQL  instead of OpenQuery


DoCmd.OpenQuery