Recordsets and pulling values from them (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 16:34
Joined
Dec 8, 2007
Messages
163
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:

Code:
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

Registered User.
Local time
Today, 16:34
Joined
Oct 28, 2004
Messages
2,251
Try to do it via DLookup function.
 

Mr_Si

Registered User.
Local time
Today, 16:34
Joined
Dec 8, 2007
Messages
163
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

Registered User.
Local time
Today, 16:34
Joined
Oct 28, 2004
Messages
2,251
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

Registered User.
Local time
Today, 16:34
Joined
Dec 8, 2007
Messages
163
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

Registered User.
Local time
Today, 16:34
Joined
Dec 8, 2007
Messages
163
Maybe I need to use the following code?

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

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

Mr_Si

Registered User.
Local time
Today, 16:34
Joined
Dec 8, 2007
Messages
163
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.
 

Users who are viewing this thread

Top Bottom