Unanswered: Compare 2 Tables to find max value thru VBA
Hello to all!!!
I have 2 identical forms (frmLOTS and frmPTS) based on 2 identical tables (tblprojectmain and tblpts). I have the following code that provides an automatic id based on the current year into the ProjectID field:
if isnull(Me![NumID]) then
me![NumID] = Format(Nz(Dmax("[NumID]", "[tblprojectmain]", "[yearid]='" & Year(Date) & "'"), 0) +1)
me![projectid] = [yearid] & "-" & format([Numid], "000000")
I would like to find the max ProjectID from the 2 tables and then use it in the above code for the current projectid of the record being created.
I know it might be easier to just use 1 table and 2 different forms but thinking long term and 2 different departments using them, it might be more efficient to do it the way I am trying....
I think the better solution is probably to have one table and include a field for which department (or whatever) it is. I think the fact that you want the ID field unique across the two tables supports the fact that they belong together.
Thanks for your reply and coding supplied. I will test first thing in the morning....
Also, thanks for your comment. I kinda feelin' the same but was also thinking long term with the multitude of records that will be entered from each department... wasn't exactly sure how many records could be created in a single table.
At least now I have coding for both scenarios, I can see how things work out prior to deployment...