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

Topic

New Reply

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

Reply | Quote

Replies

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:

• 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.
• 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.
• The SQL contains invalid bracketing, it should be “SELECT Max(ID) FROM …”
• 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.

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

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 & “‘ “

Reply | Quote

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?

Reply | Quote

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

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

is only valid if txtTicket has the focus, but

"SELECT Max(ID) As MaxID FROM [ticket data] WHERE [ticket] = '" & txtTicket.Value & "'"

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

"SELECT Max(ID) As MaxID FROM [ticket data] WHERE [ticket] = '" & txtTicket & "'"

Reply | Quote

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”.

Reply | Quote

Im using access 2000 and Visual Basic 6

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

That’s true in Acces, Hans, but not in VB.

Reply | Quote

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

Reply | Quote