Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB ACCESS using 'select max(id) from table (windows NT)

    Hi all

    I have a sql statement in VB to return the max number from an access table called data and then i need to write that max id in another table (with some other fields)

    Name.SetFocus
    Tkt_Query = "SELECT MAX[(ID)] FROM [data] where [name] = '" & Name.Text & "' order by ID"
    Set db = CurrentDb()
    Set RS_Ticket = db.OpenRecordset("data", dbOpenDynaset)


    However the select statment brings back the first occurance, not the max. Any ideas why this would happen?
    Tia

    Sheila

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

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Hello Sheila,

    There is no connection between the statements you posted. First, you assign an SQL string to the variable Tkt_Query. Then, you open a recordset on data (a table, I presume). The SQL statement is not used.

    Other points:
    <UL><LI>I don't think you need to set focus to the control named Name. If you don't, you can get at the value by just referring to [Name] or [Name].Value.
    <LI>Name is not a good name for a field or control, because it is also a property of most objects. In particular, Me.Name is the name of the form, so you run the risk of confusion.
    <LI>The SQL contains invalid bracketing, it should be "SELECT Max(ID) FROM ..."
    <LI>You probably intend to open a record set based on the SQL string. To do so, use

    Set RS_Ticket = db.OpenRecordset(Tkt_Query, dbOpenDynaset)[/list]Post back if you need more assistance.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    I'm not sure what you're trying to do. Your code creates a SQL string, but the recordset you're opening is on the table, not the SQL string. Is there other code that you didn't post that is actually using the SQL?
    Charlotte

  4. #4
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Thanks very much for gettting back to me Hans & Charlotte

    Tkt_Query = "SELECT Min (ID) AS Min FROM [ticket data] where [ticket] = '" & TxtTicket.Text & "' ORDER BY ID DESC"
    Set db = CurrentDb()
    Set RS_Ticket = db.OpenRecordset(Tkt_Query, dbOpenDynaset)

    maxid = RS_Ticket(0)

    when i tried using 'Set RS_Ticket = db.OpenRecordset(Tkt_Query) ' dbOpenDynaset) before, I got a error mssage saying "too few parameters Expected 1"
    however now I;m getting "You tried to execute a query that does not include the specified expression "ID" as part of an aggregate function"
    As to the brackets, I'd already tried max(ID), max([id]), max id etc but none of them worked and I;m still getting the first instance of the ID in the table "data" even if it doens't match the Name field. (its not actually called name) I put that in the code so that it hopefully would explain better waht I was trying to do. Also I got an error message saying something like "control must have focus" before i used the line txtName.setfocus...

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Are you doing this from Visual Basic or does your "VB" in the subject refer to Access VBA? And what versions are you using? Also, your SQL changed from Max([ID]) to Min([ID}) but you're still talking about getting a Max value. There is no point in order descending when using Max or Min, and don't use Min (or Max) as an alias for the value. Use something like MinID or MaxID to avoid confusing the query engine.

    Tkt_Query = "SELECT Max(ID) AS MaxID FROM [ticket data] where [ticket] = '" & TxtTicket.Text & "' "
    Charlotte

  6. #6
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Sorry about the Min() - I was trying something....... I do actually need the Max()

    I'll try to explain better what I'm doing.... I need to write to 2 tables; ticket data and ticket GB. After I've written the record to table ticket data, I need to get back the autonumber ID of that record so that I can write the ID and some other fields to GB with ID being the link between the table ticket data record and 1 or more record in the table GB record. Does that explain what I'm trying to do any better?

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

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    About the SetFocus issue:

    You can only refer to the Text property of a text box if it has the focus. The Text property contains the text in the text box as entered by the user; it need not be the same as the "stored" value of the text box. The Value property of the text box (this is the default property) contains the "stored" value.
    I know this can be confusing, maybe an example will help. Say, you have a text box txtLastName; it contains "Jones". The user clicks in the text box after the "s" and presses Backspace. At that moment, txtLastName.Text contains "Jone", but txtLastName.Value still contains "Jones". When the user tabs or clicks away from the text box, the new Value will be "Jone", and the Text property is not available any more.

    So<pre>"SELECT Max(ID) As MaxID FROM [ticket data] WHERE [ticket] = '" & txtTicket.Text & "'"</pre>

    is only valid if txtTicket has the focus, but<pre>"SELECT Max(ID) As MaxID FROM [ticket data] WHERE [ticket] = '" & txtTicket.Value & "'"</pre>

    is valid whether txtText has the focus or not. Since Value is the default property, you can omit it in most cases, so you could also use<pre>"SELECT Max(ID) As MaxID FROM [ticket data] WHERE [ticket] = '" & txtTicket & "'"</pre>


  8. #8
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Hi again
    I'm getting an error message that when i debug comes out at: Set RS_Ticket = db.OpenRecordset(Tkt_Query, dbOpenDynaset)The message is "Too few parameters. Expected 1".

  9. #9
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Im using access 2000 and Visual Basic 6

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

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    That usually means that there is a spelling error in one of the names in the SQL string.

    Are you absolutely sure that the table is named ticket data and that it contains fields named ID and ticket? Even a difference of one letter or space will cause the error message you mention. If you have added other fields to the Tkt_Query string, check them also.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    That's true in Acces, Hans, but not in VB.
    Charlotte

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

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    You're right. Sheila posted the confirmation that it was VB6 while I was posting that reply...

  13. #13
    Lounger
    Join Date
    May 2003
    Location
    cork, Ireland, Republic of
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB ACCESS using 'select max(id) from table (windows NT)

    Hi Hans
    Thanks very much for your help - you were quite right, I had misnamed the field in the query.
    regards
    Sheila

Posting Permissions

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