Find highest number in a set of records

[gandnt]

I have a form that shows numbers from a table lets call it Col A. The number in col A is repeated over and over again say a product ID number. In Col B I have a second number like so
Col A Col B
10001 1
10001 2
10001 3
10001 4 and so on
10002 1
10002 2 and so on
I need to reference col A but then find the highest number in Col B in this case it would be 10001, 4 or the second set would be 10002,2 or what ever the highest number in Col B is because that number could be big. My customer knows the col A number puts it in the form but would like to hit a command button that looks to Col A as reference and then shows the highest number in Col B so they know what the next number in the set should be for them to enter. Thanks for any help Please know I am just learning Access.

 

[missinglinq]

Assuming that ColumnA is defined as an actual Number, as it appears:

Private Sub SearchButton_Click()
 MsgBox DMax("[ColumnB]", "DMaxTable", "[ColumnA] = " & Me.SearchNumber)
End Sub

With a little more effort you could refine this so that it checks to see if the value in ColumnA exists, and if it does, assign the correct number to the appropriate control by adding 1 to the highest number, or if it's a new number in ColumnA assign the number 1.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.NewRecord Then Me.ColumnB = Nz(DMax("[ColumnB]", "DMaxTable", "[ColumnA] = " & Me.ColumnA), 0) + 1
End Sub

This latter approach would probably be best if this is a multi-user database.

Linq ;0)>

 

[gandnt]

Does not seem to work

Does it make a diffrence that Col A is a text field? Most are numbers as I stated like 10001 but some are WT20123

I now see that DMAX is used to find the highest number. Maybe I have made this to hard. Can I find the number...

1
2 Set 1
3
4

1
2 Set 2
3

1
2 Set 3 and all sets come from the same table same col
3
4
5
6
If i am anywhere in the first set and hit the command button it would take be to 4 or in the second set it would take me to 3 or in the third set it would jump me to 6?
Just need to jump to the last number in that set. One last question in the code metioned in the response above I created a command button then deleated on the coding and pasted the new code in above, then replace the "col A" name and the Col B" name. Is that all I should do? or where it says Dmaxtable should I specify the table name like Dmax"name of my table"

Thanks

 

[smig]

if it's a text field it will look like:
DMax("[ColumnB]", "DMaxTable", "[ColumnA] = '" & Me.SearchString & "' ")

 

[gandnt]

I found an even easier way to do this. I created a form with the two fields (columns) in question. Then On the orginal form I called this form up. I used the wizard to create the call for the form and in the wizard it asked me which fields are equal. I gave it the info and whoala it brings up the form. It automatically filters it by the info in my first column. At the bottom of the form that pops up it tell me how many duplicates their are and if I hit the last record button at the bottom it shows me the highest number which is what I need to know to create the next number in the list.

 

[smig]

and you find this way easier ?