Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move next (Access 2000)

    Is there a command in VB with which to move to the next row in the table of Access database ? For example my table TblCrates consists of :

    CrateId City

    1. Berlin

    I need a command in VB with which to go to the next row,in the case the CrateId will be 2. Can somebody help me ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    "Move to the next row in a table" is meaningless in VB.

    You can open a recordset on a table, and use the MoveNext method of the recordset. For example:

    Sub Test()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblSomething", dbOpenDynaset)
    Do While Not rst.EOF
    ' do something with the record
    ....
    ' and move to the next record
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Thank you Hans.MoveNext had done what i want.I am on the new record now. While i am on the new record, i want to set the field CustomerID to 121 and the Field Suborder to No ( a Yes/No field) After the rstMoveNext and before Loop i intend to insert :

    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Set fld = tdf.Fields("Customers")
    Set tdf.Fields("Customers")= 121
    Set fld = tdf.Fields("Suborder")

    Can i do this ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    You cannot edit records in a table this way, you need a recordset for that, as in the code I posted. But I don't understand why you want to do it this way. Why not create an update query?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Yes, i will make an update query

    regards

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Dear Hans,

    When i first applied your code it worked,but now i cannot get the next number of my table.Will you please see my table in the attachement perhaps i have something overseen.
    In my example, i want to see order number 7429
    regards
    Keks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    Apparently, you want to create a new record, but what are you going to do with it?

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Thank you for the reply.I will try to explain.
    I receive suborders from the other companies.On the basis of these suborders we create a new order servng as consignment note.We create this order by writing down the items from the list.I want to avoid this manual operation.I still cannot realize my plan since my knowledge in Access is insufficient.My plan to improve our work involves the following steps:
    1. i add on everywhere a new field on the table orders, called Suborder
    2. When a company wants to send this suborder order,the field Suborder is set to yes.
    3. We receive this order in kind of table2.
    4. i append the table2 to table1 but only that order, which has a suborder = Yes. This append function is based on your wonderful suggestion explained in a special thread
    5. I want to create a new order , in fact the next order number.I want to avoid using the form to do that since i want to make everything in one code.
    6. After that i will update the new order with the already appended order with suborder = Yes
    If i succeed, i will save a lot of work on manually rewriting the suborders into orders.

    Regards
    Keks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    You can add a new record in code, and set fields in the new record. You can also retrieve the OrderID of the new record:

    Private Sub Command0_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("orders", dbOpenDynaset)

    ' Add a new record
    rst.AddNew
    ' Set some fields in the new record
    rst!CustomerID = 37 ' can also be a variable, of course
    rst!SubOrder = True
    ' Get the new AutoNumber value
    MsgBox rst!OrderId
    ' Save the record
    rst.Update

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    It goes without saying that it works smoothly.Somehow, just saying "Thank You" seems inadequate but I i am really so grateful to you

    Keks

  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Dear Hans,

    I know that my present question should be with a new thread,but since you already know my problems you may send me a quick answer.i have arrived at the stage when i have in my table 2 rows.
    One order where the Suborder = True and the last order,which is blank,made according to the present thread.Just to finish my task,i need to update the data from the two tables : orders and order details from the order with the Suborder = true into the new ,blank,last order.After that i have to delete the order with Suborder = True.I am applying the attachement.In my case the last order
    should be updated with order 4 and after that order 4 should be deleted.

    Would you help me ?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    I don't see the point of this. If the order you need is the one with OrderID=4, why not simply keep it and use it? That's much simpler than copying data to another record.

  13. #13
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    If i could convert orderid = 4 into a new order, in my case for example,7435, which is the last order, then it is o.k. But i cannot.The order id = 4 does not follow the sequence of the autonumber of the database and what i want is actually to copy the content of the order details of order 4 into the new last order.

    kind regards

    Keks

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move next (Access 2000)

    But why do you need a new OrderID at all? Why can't you just use OrderID 4? It already contains the information you need.

  15. #15
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move next (Access 2000)

    Because i might get conflicts with the numbers.That number for example 4 might belong to a another group of companies and by processing them the figures might overlap.For me it is much better to replace orderid 4 with the new order.But if i cant, than i will use it of course

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •