Recordsets and pulling values from them

[Mr_Si]

Hi all, I have some code where I run a query and then want to pull values out of it and store them in two variables:

Case Is = "Banksian Medal" 'Most points in horticultuaral classes. Can only be won by same person every 3 years.
            Dim strBanksianWinner1 As String
            Dim strBanksianWinner2 As String
            Dim strConditionSelect As String
            Dim strConditionWhere As String
            Dim strConditionSQL As String
            Dim rs As DAO.Recordset

                '************************************************************************************
                'First of all, make a note of the past two winners to discount them from the runnings
                'This will open a form and include only entries BEFORE year being dealt with
                'from the Awards Form, when pressing Calculate.
                '************************************************************************************
                
                strConditionSelect = "SELECT TOP 2 tblShowAwardEntrant.lngzShowID, " _
                                    & "tblShowAwardEntrant.lngzAwardID, " _
                                    & "tblShowAwardEntrant.lngzEntrantID " _
                                    & "FROM tblShowAwardEntrant WHERE "
                
                strConditionWhere = "(((tblShowAwardEntrant.lngzAwardID)=24)) AND " _
                                    & "lngzShowID <> " & Me.lngzShowID & " AND " _
                                    & "lngzShowID < " & Me.lngzShowID _
                                    & " ORDER BY tblShowAwardEntrant.lngzShowID DESC;"
                
                strConditionSQL = strConditionSelect & strConditionWhere
                
                'print sql statement
                'MsgBox strConditionSQL
                
                'replace existing query with the above one
                CurrentDb.QueryDefs("qryBanksianCondition").SQL = strConditionSQL                
                '************************************************************************************
                'From this list of two, put the names of the two entrants (stored in column  of the
                'lngzEntrantID field in to two variables
                '************************************************************************************
                
                Set rs = CurrentDb.OpenRecordset(strConditionSQL)
                
                    rs.MoveFirst
                    strBanksianWinner1 = rs.Fields!lngzEntrantID.Column (5)
                    rs.moveNext
                    strBanksianWinner2 = rs.Fields!lngzEntrantID.Column (5)

               Set rs = ""

The thing is, I know recordsets are probably the most efficient way to do this but I've barely worked with them, so don't know how to pull values from them.

Please can someone help me?

I need to pull the lngzEntrantID value from the first record and store it in strBanksianWinner1 and then go to the next record, which is actually the last one and do the same, but store it in strBanksianWinner2


Is my code anywhere near to doing this?

Thanks all.

 

[MStef]

Try to do it via DLookup function.

 

[Mr_Si]

Hi MStef,

Thanks for the reply.
At what point in the code would I use the DLookup function?
I've also never used it before so it is difficult to know how to implement it.
What I've found so far is not hugely helpful

 

[MStef]

Try to do it in Sub Form_Current, (On current event). I don't know exactly, because I don't know your task.

 

[Mr_Si]

This code is called on the click of a button called 'btnCalculate'.

The idea is that it runs a query to return the names of entrants who have won in the passed two years. I want to pull the value lngzEntrantID from each of the two records (first and last) and put them into 2 separate variables.

I don't know how the dlookup syntax would work for this.

I was trying earlier with recordsets but kept getting the "item not found in this collection" error.

I can use dcount, but don't understand dlookup.

 

[Mr_Si]

Maybe I need to use the following code?

StrBanksianWinner1 = DFirst(lngzEntrantID.column(5), tblShowEntrantAward)

And then use DLast for the other variable...
Would that work?

 

[Mr_Si]

Mstef, I think I finally understand what you were getting at. I run the query, open the form, maybe with an open argument and then run DFirst or DLast again the form and then parse the value back into the variable.

Right, I shall try it tomorrow and report back.