Open Form with set Equipment and max ID

[JayRab]

Open a form and find max ID and set value as that id.

I currently have a form in datasheet view. They can click on ID and open a form to CLOSE the event. The code below pulls ID 1707 since that is what I clicked.

DoCmd.OpenForm "EquipmentUpFrm", , , "[ID]=" & Me.ID & ""

I want them to be able to do the same thing if they Click the equipment string instead( red code example of how I want it to work obviously it doesnt.)

The BLUE Code will pull up the sheet like I want...but it pulls up with the wrong ID. I need it to pull id 1707 and it pulls ID 404.

Private Sub Equipment_DblClick(Cancel As Integer)
strCheck = Me.Equipment.Value
strAvailability = Me.Availability
Debug.Print (strAvailability) ' Check to see Availability
If strAvailability = "UP" Then
DoCmd.OpenForm "Equipment DownTime", , , "[Equipment]='" & Me.Equipment & "'"
x = 0
Do While x <= Forms![Equipment DownTime].Equipment.ListCount
If Forms![Equipment DownTime].Equipment.ItemData(x) = strCheck Then
Forms![Equipment DownTime].Equipment.ListIndex = x
Exit Sub
End If
x = x + 1
Loop


Else
DoCmd.OpenForm "EquipmentUpFrm", , , "[Equipment]='" & Me.Equipment & "'" 

'DoCmd.OpenForm "EquipmentUpFrm", , , "[Equipment]='" & Me.Equipment & "' AND [ID]=Max(ID)"
End If

End Sub

Any help with this would be apprecaited.

[amrut]

If your code above pulls up the correct ID, why can't the same code be used in the click event of equipment string if they are on the "same row of datasheet" ? Does it make sense ?
You can use DMax to get max ID -

DoCmd.OpenForm "EquipmentUpFrm", , , "[Equipment]='" & Me.Equipment & "' AND [ID]=" & DMax("ID","yourTableName","[Equipment]='" & Me.Equipment & "'")

[JayRab]

The problem is the equipment ID string is in a different sub sheet. See pic above.

Ill give your code a try and see how it goes.

Only reason I am doing this is during training. The majority of the people wanted to click the top arrow instead of the bottom arrow. Once this code works I will get rid of the Maintenance work Request Hyperlink as well. Since again they just want to click in the Upper Colored areas to change Status and Availabilities.

[JayRab]

Got it working since you explained it, i remembered how I should do it. had a lapse. I just grabbed from the query i use for the first code with dlookup()

strid = DLookup("[ID]", "[Active/Open]", "[Equipment]='" & Me.Equipment & "'")
DoCmd.OpenForm "EquipmentUpFrm", , , "[ID]=" & strid & ""

Appreciate the help again.

[amrut]

Glad to help. Mark it as solved.