Getting along with max

[Trinb37]

Hi,
I have a problem that does not seem to vanish. I am trying to update the last record in a table with autonumber using Max function. How should I go about doing that? this is my code that I am working with.

Private Sub Return_Click()
Dim strnumber As String
Text34.Value = Val(ClipBoard_GetData)
strnumber = Text34.Value
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Transactions SET UnitPrice = '" & strnumber & "' WHERE TransactionID =4 " ' to be able to move in any place in the table and update
DoCmd.SetWarnings True
End Sub

What should I do with the mas function to benefit from the function? Please help. I am very desperate.
James

 

[The_Doc_Man]

I doubt seriously you want Max at all. Maybe DMax?

If your goal is to find the record number of the last record, you want DMax( "[my-field-name]", "My-table-name" )

I'll try to not second-guess why you want to do this. Just look it up in the Help files before you actually use it so you will understand what it does. Max is only meaningful in a summations (aggregate) query. You can't use Max in an update query on the same table for which you expect to perform an aggregate function (I think). Well, MAYBE sometimes you can, but the semantics of such an attempt would be bizarre at best.

 

[Trinb37]

I just have one problem though, and that is whenever I run the code I would get an error messageoCmd.RunSQL "UPDATE Transactions SET UnitPrice = '" & strnumber & "', Autonumber = " & DMax("[TransactionID]", "Transactions") + 1
What do you suggest that I am doing wrong? If can get this code to work My worries would be over. Thank you in advance for your help.
James

 

[The_Doc_Man]

You NEVER set an Autonumber in the first place if it is REALLY an autonumber.

It is hard to be sure but it appears that you are attempting to change an Autonumbered field. Don't do that. If your design requires it, your field isn't really autonumbered.

Step back and explain what you are trying to do at the next level up from raw details. Odds are, you are trying to do something for which an Autonumber field is inappropriate.

 

[Trinb37]

Hi,
It is me again. I am trying to make a program work like the features of a cash register. It must (1) put the dat into the "Transaction" table (2) Print that information out on screen(in this case the Textbox), have not quite figure out how to do that. And (3) in the event that there is a more than one of the same item then it should be able to update the last line of the items list (with price). So if some one want three apple, one plum and two candy bars then the items that are more than one should be able to have there "Amont Units" field updated and suntotaled to have the totaled at the end. Tell me what you think.
James

 

[Pat Hartman]

You don't do this by updating the table. An autonumber works fine for the item table, you don't need to increment it and you don't need to show it on the form. The form just lists the items. You need a calculated field in the subform's query to calculate the extended amount. It is not stored, it simply exists in the recordset produced by the query. And finally, the total for the order is calculated on the subform. Add a footer and in a control on the footer, put:

=Sum(ExtendedAmount)

Extended Amount is the calculated field from your query. Whenever the unit price or quantity changes, it is recalculated and when the record is saved, the subtotal in the form footer is re-calculated.

 

[Trinb37]

Thanks very much for your advice. But I am not really dealing wiith too many forms. All I want is to just display the information of the table in the value of a textbox. Well maybe the only form would be the one that the buttons are on, and that is for the register keypad. So the use of a form footer would not be necessary in this case. The reason why I feel strongly about this project is that I did it before. It is just that I can not bring to mind what is it that I did.
James