Results 1 to 10 of 10

Thread: NEED HELP URGENT - VB6/ACCESS :( :(

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2000
    Location
    South-Africa
    Posts
    46

    Post

    I please need help with the following:

    How can I use code to (automatically) retrieve the heighest number from a database.
    (Display the retrieved number in a textbox)
    (access)

    I want to add one to the retrieved number and then store it back into the database.
    (Creating my own autonumber field)

    Can anybody help me with this!!!

    Thanks.


  2. #2
    Addicted Member
    Join Date
    Oct 1999
    Location
    Oporto, Portugal
    Posts
    134

    Post

    You can make a query that returns a recordset ordered by the field with the number, goto the las record and retrieve the number. Then do your stuff.

    If you need code reply here.

    ------------------
    Jorge Ledo
    j_ledo@hotmail.com
    Portugal

  3. #3
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308

    Post

    Hi, Corne.
    I am not sure, that I understood you. But to find the highest number you use MAX function:

    sql = "select max(ProductId) from Products"

    Data1.RecordSource = sql
    Data1.Refresh
    Text1 = Data2.Recordset(0)

    If you don't have a data control, than you have to modify this code, but the idea is the same. To add 1 :
    sql = "select max(ProductId)+ 1 from Products"

    But the easyest way is just assign an AutoNumber field data type to the field that you need, and all this will be done automatically for you every time you add new record.

    Larisa

  4. #4
    Guest

    Post

    dim rs as recordset
    set rs = db.openrecordset("select count(*) as NumberOfRecords from table1")
    'New value would be rs("NumberOfRecords")+1

    ------------------

    Vincent van den Braken
    EMail: azzmodan@azzmodan.demon.nl
    ICQ: 15440110
    Homepage: http://www.azzmodan.demon.nl




  5. #5
    Guest

    Post

    If you dont want to use a query, try this..

    dim intnumber as integer ' default = 0

    with db.recordset

    .movefirst

    While not .eof
    if .fields(place field number) > intnumber then
    intnumber = .field(place field number)
    end if

    .movenext

    doevents
    wend
    end with

    intnumber = intnumber + 1

    now you have created a new id number
    but watch out. if the database contains like
    lots of records. the loop performens will be low.


  6. #6
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

    LG's answer is the best answer thus far. Use that one

  7. #7

    Thread Starter
    Member
    Join Date
    Jan 2000
    Location
    South-Africa
    Posts
    46

    Post

    Thanks for help so far!!

    My Code look like this so far.

    Private Sub optSprings_Click()

    'declare our variables
    Dim dbProgram As New ADODB.Connection
    Dim rsMain As New ADODB.Recordset

    'open the database
    dbProgram.Open "dsn=Program"

    Set rsMain = dbProgram.Execute("Select
    Max(Job_No) + 1 from Main")

    txtLastJob.Text = rsMain("Job_No")

    dbProgram.Close

    End Sub

    But if I run this I get the following run time error:

    ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

    PLEASE HELP!!!! ;(

  8. #8
    Guru Clunietp's Avatar
    Join Date
    Oct 1999
    Location
    USA
    Posts
    1,844

    Post

    when you use the MAX SQL function without specifying a AS NAME, it gets some funky name assigned by the database.

    REPLACE

    txtLastJob.Text = rsMain("Job_No")

    WITH

    txtLastJob.Text = rsMain(0)

    Tom


  9. #9

    Thread Starter
    Member
    Join Date
    Jan 2000
    Location
    South-Africa
    Posts
    46

    Post

    Thanks for your help but i use this:

    REPLACE

    txtLastJob.Text = rsMain("Job_No")

    WITH

    txtLastJob.Text = rsMain(0)

    Tom

    i get an invalid use of null?

    ????

  10. #10
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308

    Post

    Hi,Corne.
    Try this:

    txtLastJob.Text = rsMain!Job_No

    Larisa

Posting Permissions

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



Click Here to Expand Forum to Full Width