Text Box Showing Max value from a table

[Glow]

Hello all;

I have just started using Access this week, and have found your forums very helpful in setting up my database.

I am making a form that inputs new records, but on the previous form, I want to make sure that the first two fields in combination are unique, however the two fields by themselves will not necessarily be. The first field is a 1-3 letter code (CodeFirst) and the 2nd field is an integer (CodeLast)

I have a Combo box for CodeFirst that pulls all the distinct values from the table (CodeSheet), I then want to set CodeLast to the max value +1 of all instances of CodeFirst.

Ex:

CodeFirst CodeLast
ABC 111
ABC 222
ABC 444
DEF 222
DEF 333

So in my combo box, if i choose ABC, I would need the new CodeLast to be 445, and if I chose DEF, I would need 334.

Here is what I was thinking, but I do not even know where this would go. I'm sorry I am terribly lost at the moment!

SELECT max(CodeSheet.CodeLast) FROM CodeSheet WHERE CodeSheet.CodeFirst=Forms!CodeSheetInputForm!Combo162;

Thank you all for your assistance!

 

[John Big Booty]

Have a look at the DMax() function.

 

[John Big Booty]

... and welcome to the forum.

 

[Glow]

Ah yes, thank you this is working for me!

I put the code with the Combobox, and made it load the data into the text box After Update.

My only issue remaining is that upon loading the form, the combo box lists the categories, but will not allow me to select any of them. (loading as read only somehow?)

I go into Design view, and then immediately back to Form view, and the issue is no longer there.

Any idea on how to avoid this?

Thanks again!