code to refernce fields in a query? 1

[Lavey]

hi, I asked about this a few days ago and am still struggling to come up with a solution!! I have a query that has calculated fields (run by a function), i also have a form that has a control source of an SQL statement (multiple queries and an orders table), becuase of this when i add all of the calculated fields (192 of them!!) i end up with too many fields and the SQL gets truncated!
Is there a way to call the values of the query in an after update event on my form (after update - because the user has too choose the number of units to be purchased).
i have tried some different things but end up with undimensioned variables and external names not recognised!
To point to the query i tried the folowing.....
P1 = [qryBBPrices.UKprice1]
P1 = [qryBBPrices]![ukprice1]
P1 = [qryBBPrices].UKprice1

HELP!!
Am desperate !! X-)

 

[balor]

I still think you should open a new recordset with the values you need. The query you base the recordset on can refere to the form to get the right data (Ex. "WHERE ukPrice1 > forms!fTestform!txtprice&quot.

Here is a simple example of how you open a recordset:


Private Sub cmdCalculate_Click()

Dim rst As DAO.Recordset

On Error GoTo Err_cmdCalculate_Click

Set rst = CurrentDb.OpenRecordset("qTestQuery", dbOpenSnapshot)
With rst
If Not .BOF And Not .EOF Then
txtText1 = !Field1
txtText2 = !Field2
Else
MsgBox "No records"
End If
End With
Set rst = Nothing

Exit_cmdCalculate_Click:
Exit Sub

Err_cmdCalculate_Click:
MsgBox Err.Description
Resume Exit_cmdCalculate_Click

End Sub

 

[Lavey]

Balor,

THis is exactly what i needed to do ! Thank you - one more thing.... the recordset that is displayed is not referenced to my product? it takes the first record of the query..
Where/how do i put my SELECT statement ?
I need to open the recordset to view the 8 prices for the particular product...(recordset = product ID.ukprice1, etc)

Your help is MUCH appreciated !

 

[balor]

Maby you should read a little more about Access development. Here is a very good book:
"Access 2000 Developer's Handbook"

You can put the SQL-statement directly in the code if you want (instead of "qTestQuery&quot. To get the following records you use MoveNext. Loop the recordset until end of file.

Example:

Do While Not rst.EOF
nSum = nSum + rst!ukprice1
rst.MoveNext
Loop

 

[Lavey]

Balor,

thanks for the tips, all works good !!
code ended up as follows (in case your interested)

Private Sub BBID_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("lookup", dbOpenSnapshot)

With rst
Do Until !id = BBID
rst.MoveNext
Loop

If Not .BOF And Not .EOF Then
text1 = !ukprice1
Text2 = !UKprice5
Text3 = !UKprice10
Text4 = !UKprice20
Text5 = !UKprice50
Text6 = !UKprice100
Text7 = !UKprice200
text8 = !UKprice500
Else
MsgBox "No records"
End If
End With
Set rst = Nothing

End Sub

I will definately look into getting that book (very new to this and have a lot to learn !!)

Thanks again for your help!

 

[balor]

Select the right record directly instead of looping thru the recordset!

Set rst = CurrentDb.OpenRecordset("SELECT * FROM lookup WHERE id = " & BBID, dbOpenSnapshot)

 

[Lavey]

Balor - thank you once again!!

Indeed knowledge is power!!

Seriously, thanks alot!