Jim-
If anything on the form you're opening is bound to the table, it won't work. Also, you should not use OpenQuery because that runs asynchronously - the first command might not be complete before the code attempts to run the second one. Do it like this:
Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
'this set of code will
'delete the records in the tblInProcessNewTask
'alter the NewTaskID to reset to autonumber 1 each time
'Append the records from the tasks table
' Point to this database
Set db = CurrentDb
'deletes all records in the tblInProcessNewTask table
db.Execute "qryDeleteInProcessNewTask", dbFailOnError
'tblInProcessNewTask
' Give the engine time to finish and free all locks
dbEngine.Idle dbFreeLocks
'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
' Set counter back to 1
db.Execute "qryAlterInProcessNewTask", dbFailOnError
'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);
'append the tblInProcessNewTask table with the in process records.
db.Execute "qryAppendtblInProcessTasks", dbFailOnError
'tblTasks 'main table with the tasks
'tblTaskDetails 'related table to the tblTasks. the details for the tblTasks table
Set db = Nothing
End Sub
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jun 25, 2014, at 3:06 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
If I put the code behind a button on another form it works great. I probably will do it that way anyway. I just thought I would try it on the open event of the form.
Thank You
Jim Wagner
On Wednesday, June 25, 2014 11:36 AM, "Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I am getting a run time error 3211
The database engine could not lock table tblInProcessNewTask because it is already in use by another person or process.
Below is my code and it is breaking on the query named qryAlterInProcessNewTask which is the alter statement you suggested to use. I ran the queries individually and it worked, but if I put them in the vba procedure the code breaks.
I must be missing something?/
Private Sub Form_Open(Cancel As Integer)
'this set of code will
'delete the records in the tblInProcessNewTask
'alter the NewTaskID to reset to autonumber 1 each time
'Append the records from the tasks table
DoCmd.SetWarnings False
'deletes all records in the tblInProcessNewTask table
DoCmd.OpenQuery "qryDeleteInProcessNewTask"
'tblInProcessNewTask
'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
DoCmd.OpenQuery "qryAlterInProcessNewTask"
'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);
'append the tblInProcessNewTask table with the in process records.
DoCmd.OpenQuery "qryAppendtblInProcessTasks"
'tblTasks 'main table with the tasks
'tblTaskDetails 'related table to the tblTasks. the details for the tblTasks table
DoCmd.SetWarnings True
End Sub
Jim Wagner
On Wednesday, June 25, 2014 10:05 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
OK. That should work. Be sure to substitute your actual table and column names. Run the delete to empty the table, run the ALTER query, then run the INSERT query.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jun 25, 2014, at 12:52 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I am emptying the table each time. I will give that a shot. Thank You
Jim Wagner
On Wednesday, June 25, 2014 9:41 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
If you're emptying the target table first, you can reset to 1 by executing an SQL command something like this:
ALTER TABLE MyTable ALTER COLUMN NameOfAutoNumberField COUNTER (1, 1);
That will reset your auto number field to start back at 1 and increment by 1 - as long as the table is empty.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Jun 25, 2014, at 11:08 AM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello all,
I have a table that I would like to append records from another table. The issueI am encountering is this.
How do I append the records into a table. I want to start at 1 every time.
I am able to append the records to the table but the new auto number are 4 at digits. Like 5299, 5300 etc.
Thank You
Jim Wagner
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar