Macros

[Legends77]

Basically, here is the issue.....
1) data is entered into a table via a form. (table = tblticketform) and (form = frmticketinput)
2) what I am trying to do is have a button that when clicked or "on enter" it will Append the data from (tblticketform) to (tblticket) then delete all the data from (tblticketform) and refresh (frmticketinput) to show no records.

I already have the append and delete queries made / tested and they work. The issue is getting the button on the form to work.
In the macro, I have set the warnings off then:
1) open the append query
2) open the delete query
3) close frmticketinput
4) open frmticketinput

then warnings are turned back on

the problem is that I get the warning:
"This action can not be carried out while processing a form or report event"...."A macro specified as the OnOpen, OnLoad, OnClose, OnFormat, OnRetreat.... contains an invalid action for this property.

When I click "OK", the Macro Singl step screen appears and when I click "Stop All macros" and repeat the same errors one more time, all goes okay...

What am I missing?
I tried just using the append then delete query and "refresh" but it shows "Deleted" in the fields.

I even tried 2 macros..
The first appends / Deletes / close form then runs the second macro which simply opens the form again. but the same warnings appear.

Any help is greatly appreciated.

 

[kjv1611]

Well, here's a thought - in case it's related to the form being open. First, I'm assuming that you didn't have the form open when running the queries. If you did, then maybe you ought to try putting it in VBA, and see if it runs better there. If you didn't have the form open when you ran the queries, then maybe you should make sure your macro is an indipendent macro (not within the form), so you can close the form, and then reopen it after the queries run.

Or, in VBA, you could do it this way:

Private Sub AppendAndClearData_Click()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryAppend"
  DoCmd.OpenQuery "qryDelete"
  DoCmd.SetWarnings True
  Form.Requery 'Or perhaps better use the next line:
  DoCmd.OpenForm Form.Name
End Sub

So that code would work, if you don't have to have the form closed to run the delete query. However, if it has to be closed for the delete query, you could use an independent macro or else an independent VBA Module... So something like this (via VBA):

First, use this behind your button:

Private Sub AppendAndClearData_Click()
  AppendAndDeleteTicketInfo 'This calls the external
End Sub

Then this code would go in the additional module - just right-click on the "Modules" folder in the VB Editor, and select New -> Module (not Class Module)

Public Sub AppendAndDeleteTicketInfo()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qryAppend"
  DoCmd.Close acForm, "YourFormName"
  DoCmd.OpenQuery "qryDelete"
  DoCmd.SetWarnings True
  DoCmd.OpenForm Forms!YourFormName
End Sub

Anyway, there's a couple basic ideas in there. I'm not promising you won't get any errors, b/c I may have typed something incorrectly, and you'd definitely need to update the object names (tables, forms, controls) as applicable.

 

[MajP]

Why your refresh did not work, and you need the requery.

Me.Requery pulls all new data from the underlying table or query, including new additions and deletions;

Me.Refresh only updates the records in the current form, but will not show new or deleted records.

Here's the long version:

Me.Requery forces the entire recordset (underlying data) for the form to reload. This means ALL of the records in your current form will reload. Your current position will be lost, so if you're sitting on record 10 of 100, you'll find yourself back on the first record. Me.Requery is essentially the same as closing and reopening the form. Any new records added by other concurrent users will be available. Likewise any records that have been deleted will disappear. Requery essentially "re-runs the query" that pulled the data into the form in the first place. You can also use requery to update the data in a list box or combo box.

Me.Refresh saves the current record that you're working on. It will also retrieve any changes (but not additions or deletions) to any records shown in the current form. Any calculations on the form (unbound fields) are recalculated. Refresh does NOT reload the recordset. You do not lose your position in the form (you stay on the current record). Any new records added by other users will not be shown.