If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Query problem
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 "ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this 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. |
#2
|
|||
|
|||
Append Query problem
When you say it doesn't 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 won't run because it won't 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 "Rocky" wrote in message ... 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 "ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this 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. |
#3
|
|||
|
|||
Append Query problem
Try DoCmd.RunSQL instead of OpenQuery
DoCmd.OpenQuery "Rocky" wrote in message ... 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 "ReaderIndCancelProformaHistChange_Append", acViewNormal, acEdit 'this 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. |
Thread Tools | |
Display Modes | |
|
|