Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    California
    Posts
    11

    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:

    Code:
    if isnull(Me![NumID]) then
    me![NumID] = Format(Nz(Dmax("[NumID]", "[tblprojectmain]", "[yearid]='" & Year(Date) & "'"), 0) +1)
    end if
    
    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....

    Thanks for all your help!!!!!

    SgtDep

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums!

    If indeed you intend to keep the two tables seperate, and assuming you're using the Format() function to convert to a string and not actually to format the string, I would recommend you do like:
    Code:
    Dim intLOTSmax As Long, _
        intPTSmax As Long
    
    intLOTSmax = Nz(DMax("[frmLOTS]", "[tblprojectmain]", "[yearid]='" & Year(Date) & "'"), 0)
    intPTSmax = Nz(DMax("[frmPTS]", "[tblpts]", "[yearid]='" & Year(Date) & "'"), 0)
    
    If IsNull(Me![NumID]) Then
        Me![NumID] = CStr(IIf(intLOTSmax > intPTSmax, intLOTSmax, intPTSmax) + 1)
    End If
    
    me![projectid] = [yearid] & "-" & format([Numid], "000000")
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  4. #4
    Join Date
    Jan 2009
    Location
    California
    Posts
    11
    Nckdryr,

    Thanks for your reply and coding supplied. I will test first thing in the morning....

    pbaldy,

    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...

    Thanks again to both!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •