Max value from one table in one field in another table

[Hillwalker]

Hi

Raw recruit, no VB knowledge but some general programming experience.....

Access 2000 on XP

In form view, I wish to fill the field (ONLY on the record being viewed) with the maximum value from another table. Using query design view I generated the SQL

UPDATE B_Set_OrderNos, [B_PO-Num] SET B_Set_OrderNos.PONum = (DMax("[PONum]","B_PO-Num"));

Running the query with a command button on the form changes this field in ALL the records in the table not just the one in view.

Is there a simple way of dealing with this? What modifications could I make to the SQL and call it with a macroand the command button?

Thanks for any advice.

[DonRayner]

Hi

Raw recruit, no VB knowledge but some general programming experience.....

Access 2000 on XP

In form view, I wish to fill the field (ONLY on the record being viewed) with the maximum value from another table. Using query design view I generated the SQL

UPDATE B_Set_OrderNos, [B_PO-Num] SET B_Set_OrderNos.PONum = (DMax("[PONum]","B_PO-Num"));

Running the query with a command button on the form changes this field in ALL the records in the table not just the one in view.

Is there a simple way of dealing with this? What modifications could I make to the SQL and call it with a macroand the command button?

Thanks for any advice.

Change your command button"On Click" event to not run the query but to perform the DMax function directly on the field in the form.

Me.myfield = Dmax("[PONum]","B_PO-Num")

change the "myfield to whatever the text box name is on your form

[Hillwalker]

Change your command button"On Click" event to not run the query but to perform the DMax function directly on the field in the form.

Me.myfield = Dmax("[PONum]","B_PO-Num")

change the "myfield to whatever the text box name is on your form

Don

Thanks for your help. In the end I used a macro with SetValue