By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Max value from one table in one field in another table

P: 2
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.
Oct 2 '08 #1
Share this Question
Share on Google+
2 Replies


DonRayner
Expert 100+
P: 489
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
Oct 2 '08 #2

P: 2
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
Oct 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.