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