Order of entry 
Author Message
 Order of entry

How can I keep an order without an auto-increment field when adding records
to a table? Here is what I have now that doesn't keep it in order. Addnew
seems to place it where it wants to.

'*************************************************
Set pvrsttblOutOfSequence = db.OpenRecordset("Select * from
tblOutOfSequence")
pvrsttblOutOfSequence.MoveLast
Set pvrsttblImport = db.OpenRecordset("Select
tblImport.[FileItCameFrom],tblImport.[Claim Number] from tblImport order by
[FileItCameFrom] AND [Claim Number]")
pvrsttblImport.MoveLast
pvrsttblImport.MoveFirst
Claim = pvrsttblImport![Claim Number]
pvrsttblImport.MoveNext
Do While Not pvrsttblImport.EOF
   If pvrsttblImport![Claim Number] <> (Claim + 1) Then
      outofsequence = True
      Claim = pvrsttblImport![Claim Number]
      ImportFile = pvrsttblImport![FileItCameFrom]
      With pvrsttblOutOfSequence
           .MoveLast
           .AddNew
           ![Claim Number] = pvrsttblImport![Claim Number]
           ![FileItCameFrom] = pvrsttblImport![FileItCameFrom]
           .Update
           .Bookmark = .LastModified
      End With
   End If
   Claim = pvrsttblImport![Claim Number]
   pvrsttblImport.MoveNext
   pvrsttblOutOfSequence.MoveLast
Loop
'*************************************************

This code use to be alot cleaner but now I am making it functional. ;-)

Scott



Mon, 27 Jun 2005 06:35:55 GMT  
 Order of entry
I'm trying to understand why you need to keep things 'in
order'?  Tables are buckets.
You my try adding an [Order By] clause to your recordset
SQL strings.
--
HTH
Tim Hansen

========================

Quote:
>-----Original Message-----
>How can I keep an order without an auto-increment field
when adding records
>to a table? Here is what I have now that doesn't keep it
in order. Addnew
>seems to place it where it wants to.

>'*************************************************
>Set pvrsttblOutOfSequence = db.OpenRecordset("Select *
from
>tblOutOfSequence")
>pvrsttblOutOfSequence.MoveLast
>Set pvrsttblImport = db.OpenRecordset("Select
>tblImport.[FileItCameFrom],tblImport.[Claim Number] from
tblImport order by
>[FileItCameFrom] AND [Claim Number]")
>pvrsttblImport.MoveLast
>pvrsttblImport.MoveFirst
>Claim = pvrsttblImport![Claim Number]
>pvrsttblImport.MoveNext
>Do While Not pvrsttblImport.EOF
>   If pvrsttblImport![Claim Number] <> (Claim + 1) Then
>      outofsequence = True
>      Claim = pvrsttblImport![Claim Number]
>      ImportFile = pvrsttblImport![FileItCameFrom]
>      With pvrsttblOutOfSequence
>           .MoveLast
>           .AddNew
>           ![Claim Number] = pvrsttblImport![Claim Number]
>           ![FileItCameFrom] = pvrsttblImport!
[FileItCameFrom]
>           .Update
>           .Bookmark = .LastModified
>      End With
>   End If
>   Claim = pvrsttblImport![Claim Number]
>   pvrsttblImport.MoveNext
>   pvrsttblOutOfSequence.MoveLast
>Loop
>'*************************************************

>This code use to be alot cleaner but now I am making it
functional. ;-)

>Scott

>.



Mon, 27 Jun 2005 07:15:27 GMT  
 Order of entry
Tim, we print labels from the data that must be in the order entered.
Otherwise, trying to find where the labels go in a warehouse could be a bit
difficult. Why doesn't my "order by" work in my code?

Scott


Quote:
> I'm trying to understand why you need to keep things 'in
> order'?  Tables are buckets.
> You my try adding an [Order By] clause to your recordset
> SQL strings.
> --
> HTH
> Tim Hansen

> ========================

> >-----Original Message-----
> >How can I keep an order without an auto-increment field
> when adding records
> >to a table? Here is what I have now that doesn't keep it
> in order. Addnew
> >seems to place it where it wants to.

> >'*************************************************
> >Set pvrsttblOutOfSequence = db.OpenRecordset("Select *
> from
> >tblOutOfSequence")
> >pvrsttblOutOfSequence.MoveLast
> >Set pvrsttblImport = db.OpenRecordset("Select
> >tblImport.[FileItCameFrom],tblImport.[Claim Number] from
> tblImport order by
> >[FileItCameFrom] AND [Claim Number]")
> >pvrsttblImport.MoveLast
> >pvrsttblImport.MoveFirst
> >Claim = pvrsttblImport![Claim Number]
> >pvrsttblImport.MoveNext
> >Do While Not pvrsttblImport.EOF
> >   If pvrsttblImport![Claim Number] <> (Claim + 1) Then
> >      outofsequence = True
> >      Claim = pvrsttblImport![Claim Number]
> >      ImportFile = pvrsttblImport![FileItCameFrom]
> >      With pvrsttblOutOfSequence
> >           .MoveLast
> >           .AddNew
> >           ![Claim Number] = pvrsttblImport![Claim Number]
> >           ![FileItCameFrom] = pvrsttblImport!
> [FileItCameFrom]
> >           .Update
> >           .Bookmark = .LastModified
> >      End With
> >   End If
> >   Claim = pvrsttblImport![Claim Number]
> >   pvrsttblImport.MoveNext
> >   pvrsttblOutOfSequence.MoveLast
> >Loop
> >'*************************************************

> >This code use to be alot cleaner but now I am making it
> functional. ;-)

> >Scott

> >.



Mon, 27 Jun 2005 07:28:05 GMT  
 Order of entry


Quote:
> How can I keep an order without an auto-increment field when adding
> records to a table? Here is what I have now that doesn't keep it in
> order. Addnew seems to place it where it wants to.

There is _no_ intrinsic order in a table in a Rdbms. Any order there is,
has to be defined by you.

One easy way is to set a column called TimeStamp, type DateTime,
defaultvalue = Now() and don't make it appear on any of your forms.

Then set all your forms and queries etc. to look like

  SELECT MyName, YourName, Etc
  FROM MyTable
  ORDER BY TimeStamp DESC;

or whatever.

HTH

Tim F



Mon, 27 Jun 2005 07:45:06 GMT  
 Order of entry

Quote:

>How can I keep an order without an auto-increment field when adding records
>to a table? Here is what I have now that doesn't keep it in order. Addnew
>seems to place it where it wants to.

>'*************************************************
>Set pvrsttblOutOfSequence = db.OpenRecordset("Select * from
>tblOutOfSequence")
>pvrsttblOutOfSequence.MoveLast
>Set pvrsttblImport = db.OpenRecordset("Select
>tblImport.[FileItCameFrom],tblImport.[Claim Number] from tblImport order by
>[FileItCameFrom] AND [Claim Number]")
>pvrsttblImport.MoveLast
>pvrsttblImport.MoveFirst
>Claim = pvrsttblImport![Claim Number]
>pvrsttblImport.MoveNext
>Do While Not pvrsttblImport.EOF
>   If pvrsttblImport![Claim Number] <> (Claim + 1) Then
>      outofsequence = True
>      Claim = pvrsttblImport![Claim Number]
>      ImportFile = pvrsttblImport![FileItCameFrom]
>      With pvrsttblOutOfSequence
>           .MoveLast
>           .AddNew
>           ![Claim Number] = pvrsttblImport![Claim Number]
>           ![FileItCameFrom] = pvrsttblImport![FileItCameFrom]
>           .Update
>           .Bookmark = .LastModified
>      End With
>   End If
>   Claim = pvrsttblImport![Claim Number]
>   pvrsttblImport.MoveNext
>   pvrsttblOutOfSequence.MoveLast
>Loop
>'*************************************************

>This code use to be alot cleaner but now I am making it functional. ;-)

Not sure about all that code, but your Order By clause uses
the AND operator, making the clause a boolean expression.
The order by clause is nromally a list of fields.   I think
you want to use a comma instead:
        . . .
        ORDER BY [FileItCameFrom], [Claim Number]"

--
Marsh
MVP [MS Access]



Mon, 27 Jun 2005 23:15:53 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Collection Object that maintains order of entry?

2. Order Number Entry??

3. Tab order in online data-entry form?

4. Need Object Model advise for Order Entry App

5. sample order entry application

6. sample order entry application

7. Example order entry VB application

8. Order Entry -VB4 & Access code needed

9. Order Entry System Needed

10. Order Entry modem communications fix

11. VB order entry inventory control

12. Order Entry System Needed

 

 
Powered by phpBB® Forum Software