Results 1 to 9 of 9
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    VBA Using FindFirst and Max Function


    Good afternoon everyone. This is my problem: I have the following form:


    Click image for larger version. 

Name:	form.JPG 
Views:	19 
Size:	36.9 KB 
ID:	43253

    On Current Event I would like to Edit the field "Duration" for the record with the Max "Z6 Status Date" (in this case the first record).
    I was thinking about a combination of Find First and Max function but looks like I'm not able to do it.
    Is somebody so kind to suggest how to do ?
    Thank you,
    L.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You could try something like this (adjusted to match your table name and assumes you group the records by project as illustrated in your image):
    Code:
    rstEmployees.FindFirst "[Z6 STATUS DATE] = #"  & dMax("[Z6 STATUS DATE]","[tblZ6]","[PROJECT] ='" & Me.Project & "'") & "#"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Thanks a lot Gicu, it works perfect.
    Appreciate your support.
    Cheers,

  4. #4
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Sorry, there is something strange. This is what I wrote :

    Dim z6stat As Recordset
    Set z6stat = CurrentDb.OpenRecordset("tbl_Z6_Status", dbOpenDynaset)


    z6stat.FindFirst "[Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#"

    If z6stat!Z6_STATUS <> "CLOSE" Then...

    The Find First works good because select the correct max date but when i test Z6_STATUS, the value is not the one refer to the "find first" but the one of completely another Project and Z6 and this was exactly the problem why i could not use Find First before your suggestion. What is that let him crazy ? :-)

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try:
    Code:
    Dim z6stat As Recordset 
    Set z6stat = CurrentDb.OpenRecordset("SELECT tbl_Z6_Status.* FROM  tbl_Z6_Status WHERE [Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#;", dbOpenDynaset)
    
    
    'z6stat.FindFirst "[Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#"
    
    If z6stat!Z6_STATUS <> "CLOSE" Then...
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by Gicu View Post
    Can you try:
    Code:
    Dim z6stat As Recordset 
    Set z6stat = CurrentDb.OpenRecordset("SELECT tbl_Z6_Status.* FROM  tbl_Z6_Status WHERE [Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#;", dbOpenDynaset)
    
    
    'z6stat.FindFirst "[Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#"
    
    If z6stat!Z6_STATUS <> "CLOSE" Then...
    Cheers,
    Thank you for the answer, but this is what i get (looks like don't get the record):

    Click image for larger version. 

Name:	form.JPG 
Views:	14 
Size:	37.7 KB 
ID:	43254

    Cheers,

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you check the syntax of the dMax? Maybe replace it with a variable and check its value before using it in the SQL statement to ensure it returns the right date.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Instead using functions to calculate recordset, you can use syntax like
    Code:
    SELECT TOP 1 * FROM YourTable WHERE YourConditions ORDER BY Field1 Order1 AND Field2 Order2 AND ...
    Order1, Order2, ... are ASC/DESC depending on your data.

  9. #9
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Looks like the problem is that the form use the same source (tbl_Z6_Status) I use in the On Current Event to select the record I need to be updated. So the solution I find is to use RecordsetClone (instead of Recorset) in the Set of the variable and then your suggestion of FindFirst. What I did is this:
    '----------------------------------
    Dim z6stat As Recordset
    Set z6stat = Me.RecordsetClone
    z6stat.MoveFirst


    z6stat.FindFirst "[Z6_STATUS_DATE] = #" & DMax("[Z6_STATUS_DATE]", "[tbl_Z6_Status]", "[PROJECT] = '" & Forms!frmSectionManager!myPrj & "' AND [Z6] = '" & Forms!frmSectionManager!sel_Z6 & "'") & "#"

    If z6stat!Z6_STATUS <> "CLOSE" Then
    z6stat.Edit
    z6stat!Z6_DURATION = WorkingDays(z6stat!Z6_STATUS_DATE, Now())
    z6stat.Update
    End If
    End Sub
    '---------------------------
    For the other forms without any source, where I use something similar, I simple use what you suggest with FindFirst and it works.
    Thank you for support.
    Cheers.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. FindFirst with two conditions
    By Mister-B in forum Programming
    Replies: 2
    Last Post: 12-26-2019, 04:26 AM
  2. DAO Recordset FindFirst
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 05-25-2019, 11:12 AM
  3. FindFirst with AND statement
    By robjones01 in forum Modules
    Replies: 10
    Last Post: 01-18-2018, 04:29 AM
  4. FindFirst error
    By Bazsl in forum Programming
    Replies: 6
    Last Post: 03-01-2017, 09:21 AM
  5. Using FindFirst code
    By saltydawg617 in forum Access
    Replies: 2
    Last Post: 07-29-2011, 05:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums