Gotta agree with jammybasturd on this. If you must delete all the records in a table use code in a function called by a button.
I have tables which are populated for letters and similar things and use this method. It's handy if yu later decide you need to add a specific warning ("You are deleting all your old letters. Have a nice day!") or check that there is something to repopulate the table with.
Calling queries is a good method to use. I used to write code to do the delete, but calling the queries makes the whole thing a bit easier when you're updating Access.
Create a delete query to delete all of the records. Then create a Macro that you can attach to a button on your form. In the Macro, SetWarnings to Off so that it doesn't require you to say OK when it is ready to delete the records. On the 2nd line, do an OpenQuery for the delete query that you just wrote. Then on the next line do a TransferSpreadsheet to import the new data. Then SetWarnings to On on the next line. Do not forget to do this last step.
in the open query macro I was set to query that I was wrote, I dont understand what action to be set in view and data mode in that open query.In transfer spreadsheet macro, file name is stand for what? is it for place I stored my excell?
For the Macro, when you put the OpenQuery line in, the Query Name box should have the name of the DeleteQuery that you just wrote, the View should be Datasheet, and the DataMode should be Edit.
On the next line down, when you put the TransferSpreadsheet line in, The TransferType is Import, the TableName is the name of the table that you just ran the delete query against and you now want to populate, the FileName is the full path and name of the spreadsheet that you want to import, HasFieldNames should say yes if you have column headings in your spreadsheet, and Range should have the name of the Tabbed Worksheet in the Excel file you are importing.
What you should do is create your Query, and then run it from a Module wiht a CommandButton. See this below as the code I have attached to a CommandButton to run 2 queries. Done the way that it is, there is no need to use the SetWarnings On/Off, as so many people just forget to set them back on again. Here the [color = green]CurrentDB.Execute[/color] takes care of all that for you.
[color = BLUE]
Private Sub cmbProcessJobCharge_Click() 'The name of the CommandButton.
On Error GoTo Err_cmbProcessJobCharge_Click
'This Prints out the job charge report,saves the records and closes the form'
Dim stDocName As String
stDocName = "JobCharge"
DoCmd.GoToRecord , , acNewRec
DoCmd.OpenReport stDocName, acNormal
stDocName = "LaunchPad.PostJobCharge"
CurrentDb.Execute "PostJobCharge" ‘This is One Query to run.
CurrentDb.Execute "UpdateStockedItems" ‘This is the Second Query to run.
One easy way to start creating your own VBA code is to go ahead and create a Macro. THEN, in DesignView of that Macro, click SaveAs, and click the ComboBox arrow to select Module. What happens is that it will create the VBA code for you in a module. Next, look at the Code that was created for you. That is how I learned...but since then I never ever create Macros at all.
When you finish having that done, you simply do the same as you do when attaching a Macro to a CommandButton. Only, when you are attaching, just scroll in the ComboBox to select the VBA Module you created. IF you save it with a very descriptive name you will find it. What I would do is name it something like modDeleteQuery that way you can see right off it is a Module.
hope this helps you out my friend,
have a nice one,
Right now I wanna try to use this advice and hope it will work well..Now for running my time I need an idea from someone in create BT report. in BT report I need one field of a table to increase one.For result, the last record of field name will be printed in report.
For easy way to understand hopefully:
1) table "sts_BT" - field name "typelastNoprinted"
2) table "trans1" - field name "to warehouse"
when user enter one of warehouse code in fieldname 'to warehouse' then field name 'typelastNoPrinted' will increase to one. There are many kind of warehouse code. So each warehouse code has their typelastNoprinted record. any Idea please suggest...