Easy Dmax Question..I think! =)

[MrMitch]

Form: FrmCO
Table: COs

Table:

| PM | SITE | COTotal | CONum |
B. Smith 7-0121 $14,000 01


I have a field on the form (CON) I would like to look at the table and see if there is a CONum for that site when that site is selected on a combo box (Site), and if not start at 01, and if there is a record or records there to see what the last number is and add 1 to the CONum.

I'm not sure how to write this Dmax command.. =(

Hope that makes sense and someone could throw me a bone on this simple question. Thanks!

 

[RuralGuy]

Will this link to Domain function syntax help?

 

[MrMitch]

Thanks for the info RuralGuy, unfortunatly, I am still pretty new to access and vb. I tried working with what you sent me, but I just dont think I understand how it should be written out correctly to get what I want. =/

 

[RuralGuy]

I'm pretty sure all you need is:
YourVariable = DMax("CONum","COs")
...to give you the largest CONum currently in the COs table. Does that help?

 

[MrMitch]

That works, and I added a + 1 to the end of it so it would add each time the form is used to add a record, but I need that +1 based on the records in the table so it looks at the "site" field and it will see if there is a record for that site, and if there is, to simply add a +1 in the CONum field to the new record being saved. If there is no record in that table for that site they are entering data for, then I would like it to start at 1. Let me see if I can give you an example of how the data would look after it was saved:

| Site | CONum |
| 7-2 | 1 |
| 7-1 | 1 |
| 7-2 | 2 |

 

[RuralGuy]

How about:
YourVariable = Nz(DMax("CONum","COs","[Site] = '" & Me.txtSite & "'"),0)+1
...using your control names of course.

 

[MrMitch]

RG,

Thanks you so much! You rock dude. That works perfect.

 

[RuralGuy]

Glad I could help.