Get Specific Field Value from the Query

[luzmen]

Hello World,


I have a query with four (4) columns, namely:

ID, Name, Address, & RndNumber


This query is for a raffle draw, that is why i included a column Random Number using an expression of RND function..

I want a VBA code to put the ID of the highest random number to the txtbox.

I tried this code but it does not appear on the txtbox:

Dim varHigh As Long

DoCmd.OpenQuery "qry_participants"
 
    varHigh = DMax("RndNumber", "qry_participants")
    Me.txtHighest.Value = DLookup("ID", "qry_participants", "RndNumber=" & varHigh)

 

[arnelgp]

edit qry_participants query to add Sort order (Descending) on RndNumber column.
then you only need to use Dlookup:

Me.txtHighest.Value = DLookup("ID", "qry_participants")

 

[Minty]

Also you don't need to open the query, the DLookup will use it without it being open.

 

[luzmen]

Thanks for the reply...

There is something bothering me... the TextBox (txtHighest) does not refreshing even though i put a code

Me.txtHighest.Requery

... it will only change when i re-open the form...

 

[arnelgp]

first remove the the Expression (=DLookup("ID", "qry_participants")) from the Control Source property of Textbox txtHighest.
move the expression on the Load Event of the Form.

Private Sub Form_Load()
Me.txtHighest=DLookup("ID", "qry_participants")
End If

on your code, to "requery" txtHighest, you need to call DLookup again:

Me.txtHighest=DLookup("ID", "qry_participants")

 

[luzmen]

I figured it out... i put also the requery command in the last part of the command button code..

Thank you all!