DMax Nested in Open Form

[Adam McReynolds]

I need to use DMax to open the form on the last record based on the serial number. This code is what I have to open the form currently, but I want only the latest record related to the serial number:

DoCmd.OpenForm "Form1", , , "incoming_module_sn = '" & Me.txt_sn & "'"

Here is what I tried and it did not work:

DoCmd.OpenForm "Form1", , , DMax("incoming_module_sn", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'")

Any help would be appreciated.

 

[pr2-eugin]

Hello Adam_McReynolds, your DoCmd.OpenForm statement is not correct. Normally it should be someFieldName = someValue.. Based on that.. the better coding would be..

Dim snNumStr As String
snNumStr = Nz(DMax("incoming_module_sn", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'"), "NA")
If snNumStr <> "NA" Then
    DoCmd.OpenForm "Form1", WhereCondition:= "fieldOnTheForm = '" & snNumStr & "'"
Else
    MsgBox "No record exists"
End If

 

[Adam McReynolds]

Hello Adam_McReynolds, your DoCmd.OpenForm statement is not correct. Normally it should be someFieldName = someValue.. Based on that.. the better coding would be..

Dim snNumStr As String
snNumStr = Nz(DMax("incoming_module_sn", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'"), "NA")
If snNumStr <> "NA" Then
    DoCmd.OpenForm "Form1", WhereCondition:= "fieldOnTheForm = '" & snNumStr & "'"
Else
    MsgBox "No record exists"
End If

Thanks for the reply. This works to open the form on the serial number but it does not pull the latest record for some reason. I entered 2 identical Serial numbers in my table and it pulled up the 1st record not the 2nd one. Any ideas? Thanks again.

 

[pr2-eugin]

You could use DLast, but even if you use that you will end up with the similar scenario, as the field will look for the first occurring value of the Serial Number.

This sounds like your table has some design issues. How exactly are your table designed?

 

[Adam McReynolds]

You could use DLast, but even if you use that you will end up with the similar scenario, as the field will look for the first occurring value of the Serial Number.

This sounds like your table has some design issues. How exactly are your table designed?

Well it's my bosses DB I have been trying to update. It does have a auto number(named: prikey) created for each record so maybe a dual criteria could be used(as I am guessing DMax pulls the highest value)?

If that won't work I was wondering if I could use unbound controls on my form and insert the table data into them so that the record the form is on wont matter. Basically what this whole thing is doing is I put the serial number for a repair item into a text box and when I click the button it opens a form with the most recent repair data. Then I have a button on that form which then prints that data onto a label in a Brother label maker. Thanks again for the help.

 

[pr2-eugin]

Yes you can use the AutoNumber field, although a word of caution to this tale. autoNumbers are not for human consumption. They are not always Sequential, not always Incremental, not always Positive. The one thing AutoNumber promise you is Uniqueness.

For now your AutoNumber would produce a (occasionally) sequential increments but this might change any point of time.

Does the table not hold the Date of when the item was sent to be repaired? Normally a specific field like Date would be helpful.

 

[Adam McReynolds]

Yes you can use the AutoNumber field, although a word of caution to this tale. autoNumbers are not for human consumption. They are not always Sequential, not always Incremental, not always Positive. The one thing AutoNumber promise you is Uniqueness.

For now your AutoNumber would produce a (occasionally) sequential increments but this might change any point of time.

Does the table not hold the Date of when the item was sent to be repaired? Normally a specific field like Date would be helpful.

I get what you are saying about auto number. It has been sequential up to 53,000 records but I trust your judgement. These repairs do have completion dates(named: complete_date) that could be used to pull the last/highest date. However the 1st form I need to only enter the serial number. Could you show me how that would work into the code? Thanks so much.

 

[pr2-eugin]

Something along these lines should do the trick..

Dim autoID As Long, maxDate As Date
maxDate = Nz(DMax("complete_date", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'"), Date)
autoID = Nz(DLookUp("[COLOR=Red][B]<<autoNumberFieldName>>[/B][/COLOR]", "tbl_module_repairs", _
                    "incoming_module_sn = '" & Me.txt_sn & "' AND complete_date = #" Format(maxDate, "mm\/dd\/yyyy") "#"), 0)
If autoID <> 0 Then
    DoCmd.OpenForm "Form1", WhereCondition:= "autoIDOnTheForm = " & autoID
Else
    MsgBox "No record exists"
End If

 

[Adam McReynolds]

Something along these lines should do the trick..

Dim autoID As Long, maxDate As Date
maxDate = Nz(DMax("complete_date", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'"), Date)
autoID = Nz(DLookUp("[COLOR=Red][B]<<autoNumberFieldName>>[/B][/COLOR]", "tbl_module_repairs", _
                    "incoming_module_sn = '" & Me.txt_sn & "' AND complete_date = #" Format(maxDate, "mm\/dd\/yyyy") "#"), 0)
If autoID <> 0 Then
    DoCmd.OpenForm "Form1", WhereCondition:= "autoIDOnTheForm = " & autoID
Else
    MsgBox "No record exists"
End If

Thank again for the help. I am getting a syntax error on this part:

autoID = Nz(DLookUp("prikey", "tbl_module_repairs", _
                    "incoming_module_sn = '" & Me.txt_sn & "' AND complete_date = #" Format(maxDate, "mm\/dd\/yyyy") "#"), 0)

I think it might have to do with the date part. The complete_date field is set to medium date. Would this effect the syntax? Thanks.

 

[pr2-eugin]

Sorry my bad.. Try this,

autoID = Nz(DLookUp("prikey", "tbl_module_repairs", _
                    "incoming_module_sn = '" & Me.txt_sn & "' AND complete_date =[COLOR=Red][B] #" &[/B][/COLOR] Format(maxDate, "mm\/dd\/yyyy") [COLOR=Red][B]& "#"[/B][/COLOR]), 0)

 

[Adam McReynolds]

IT'S ALIVE!!!!! Thank you so much for your help. Here is my final code:

Private Sub btn_submit_Click()
Dim autoID As Long, maxDate As Date
maxDate = Nz(DMax("complete_date", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'"), Date)
autoID = Nz(DLookup("prikey", "tbl_module_repairs", _
                    "incoming_module_sn = '" & Me.txt_sn & "' AND complete_date = #" & Format(maxDate, "mm\/dd\/yyyy") & "#"), 0)
If autoID <> 0 Then
    DoCmd.OpenForm "Form1", WhereCondition:="prikey = " & autoID
Else
    MsgBox "No record exists"
End If
End Sub

Thanks again.Cheers!

 

[pr2-eugin]

Glad to help. Good Luck..