how can I move records down to be able to insert a new one?

  • Thread starter Thread starter rich
  • Start date Start date
R

rich

Hi,
I have a table that holds information on a job queue. The next job to
be run is the record at the top of the table. The first column is being
used to hold the Job_ID, when a job is inserted into a table it is
automatically assigned a Job_ID. When the job is ran it is deleted
from the table. At the moment when a new job is added it is put at the
bottom of the table. I would like to give the ability for the operator
to insert a job in any postition of the table.

The only way I can see of doing this is if they chose to 'insert' a job
rather than 'add' a job it copies the job at the bottom of the queue
into a new record and then for every job between the insert point and
the bottom it updates the record with the job above it? The new job
would then update the job that was in the position it wanted? I hope
this make sense!
Are there any easier ways of doing this?
Any help or ideas would be appreciated

Thanks in advance
 
Records in a Table are like things in a bucket: they don't have an inherent
sort order. Therefore, you cannot "insert" Record at a particular position.

However, you can create your own "SortNumber" and use the sorting (ODER BY
clause) in Query / SQL (used as the Form's RecordSource) to sort the records
the way you want to display on the Form.
 
My first impression is that you are confusing a database table with a
spreadsheet.

Records in a table have no pre-defined order. You can impose one on records
retrieved ftom the table in a query based on the table, by specifying the
sort order for one or more fields.

It seems, from your description, that Job_ID is an autonumber. If so, there
is no way that you can alter it. However, it is also possible that Job_ID
is being assigned via code (perhaps via an expression such as "=
dmax("Job_ID","tblJobs") +1" ). If that's the case, then it would be
possible (but not simple - you'd need to do some recordset manipulation via
VBA code) to change the Job_ID. But rather than shifting records in a query
(via changing the field being used for the sort order), it would likely
easier, and likely more useful, to add a Priority field which can be
assigned when the job is entered, and the list of up-coming jobs could then
be sorted initially by priority, and then by date/time of entry.

How exactly are records being added to the table , and being deleted from
the table "when the job is ran"? This should be done via a form - direct
entry of records into a table is not the way Access is designed to be used.
If that's what you are doing, with Job_ID as an autonumber, then the answer
is simply that it is not possible. An Excel spreadsheet would be a better
tool for your purpose if that's what you are doing,

We need more information about your situation to answer in any more detail.

HTH,

Rob
 
Hi,
Thanks for the replies.

The job details (records) are entered in a user form in a SCADA package
(iFix), VBA scripting and the ADO object then enters the details into
the database. When the machine requires a new job, at the moment it
runs a script to connect to the database it then orders the Queue table
in order of Job_ID and using the movefirst method gets the first job in
the recordset. Once the Job has been loaded into the machine the Job is
then deleted from the Queue, again using vba script.
I realise this may not be the correct way of using a database but it is
a handy way of storing the jobs and giving them an ID.

Thanks for your ideas.

Rich
 
Back
Top