Dlookup without a record ID (1 Viewer)

[JimH]

I placed a long button on a form that on the mouse over event basically changes the caption of the button to read a record number based on the percentage the mouse is moved from left to right. Then on the OnClick event I move to that record. I would like to add one more line of code that looks up that records date and assigns it to a tool tip or some other indicator for the user.

Since the reference on what record to look at is simply a record number I have to use that record number in my Dlookup, I am having problems figuring out the 3rd part of the Dlookup. Thanks!

Private Sub Command41_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim Place As Long
Dim intX As Long
intX = DCount("*", "qryTblHistory")
Place = Round(intX)
Dim strChr As String
strChr = Round((X / Me.Command41.Width * Place))
If Me.Command41.Caption <> strChr Then
Me.Command41.Caption = strChr
Me.Command41.ControlTipText = DLookup("[date]", "qryTblHistory", Me.??????RecordNumber???? = strChr)
End If

End Sub

 

[raskew]

Hi -

That's a neat little effect. Tried it based on Northwind's Orders table. Used a recordset to return the OrderDate. Initially expected that it wouldn't be able to keep up, but was amazed at how fast it calculated each move. Used lblDisplay to show both record number and OrderDate, since ControlTips seem to have minds of their own as to when they're going to display.

Private Sub Command0_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim rs     As Recordset
Dim strChr As String
Dim strSQL As String
Dim Place  As Long
Dim intX   As Long
Dim intY   As Long

    intX = DCount("*", "Orders")
    Place = intX
    intY = Int(X / Me.Command0.Width * Place)
    strChr = str(intY)
    If Me.Command0.Caption <> strChr Then
       Me.Command0.Caption = strChr
       If intY > 0 Then
         strSQL = "SELECT Top " & intY & " OrderDate from Orders"
         Set rs = CurrentDb.OpenRecordset(strSQL)
         rs.MoveLast
         Me.lblDisplay.Caption = "Record " & strChr & " - " & rs!OrderDate
         Me.Command0.ControlTipText = rs!OrderDate
         rs.Close
       End If
    End If
End Sub

HTH - Bob

 

[JimH]

Thanks

Bob,

That was exactly what I was looking for! thanks!!!!!

Jim

 

[raskew]

You're most welcome. And thank you for providing the initial code.

Bob