Accessing, storing, and using data in query fields

[faythe1215]

I'm sure this is simpler than I'm making it out to be...

I have a query a couple of calculated values. I am trying to iterate through the entire query one record at a time. I need to access various bits of the information stored in the query, but I can't seem to get it. I've looked at tutorials on Recordsets and DLookup, but I can't figure it out.

If I use DLookup, I don't know how to move through the records, but if I use Recordsets, I can't figure out how to access the information in the various fields.

Query Example

CountOfItem_ID*	ItemName	MeasurementType_ID	AvgOfRetailPrice*
3	Box	1	1.99
1	Coupler	2	.99

*calculated fields - (idk if that matters, though)

[June7]

Access the bits of information to do what with them?

Open recordset in VBA then in loop structure, cycle through the records.

[faythe1215]

I need to store the info in CountOfItem_ID and MeasurementType_ID as integer variables.

So, as I understand it, I'm doing this:

Dim CountItem as Integer
Dim MeasureType as Integer

Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset "[queryName]"

rs.MoveFirst

While rs.EOF = false
'    CountItem = (this is where I get stuck, because I can't figure out how to get that info from the field)
'    MeasureType = 
rs.MoveNext
Wend

[ssanfu]

I need to store the info

Store it where?

Here is an example of ONE way to add the data to a table:

'  Dim CountItem as Integer
'  Dim MeasureType as Integer
Dim sSQL As String
Dim rs as DAO.Recordset
Dim rt as DAO.Recordset

Set rs = CurrentDb.OpenRecordset ("[queryName]")  'source data
Set rt = CurrentDb.OpenRecordset ("Metrics")  'target table name 

If Not rs.EOF Then
rs.MoveFirst

While rs.EOF = false
   rt.Add
   rt!CountItem = rs.CountOfItem_ID
   rt!MeasureType = rs.MeasurementType_ID
   rt.Update

'    CountItem = (this is where I get stuck, because I can't figure out how to get that info from the field)
'    MeasureType = 

rs.MoveNext
Wend

End If

'Clean up
rs.Close
rt.Close
Set rs = Nothing
Set rt = Nothing

Another method is to create (in code) a SQL append query.

[faythe1215]

Sorry, I guess that's where our wires have been getting crossed. I do not need to store those values permanently. I need access them in my VB code, use them, and then I move on to the next record in the query and start again.

With the code ssanfu provided, I should be able to proceed...

[faythe1215]

Ran the below code just to see if I could get the Recordset part working...

Private Sub btnUnitCheck_Click()
    Dim RecSet As DAO.Recordset
    Set RecSet = CurrentDb.OpenRecordset("qryPurchase_AvgPrice")
    
    Dim NumPurchased As Integer
    
    RecSet.MoveFirst
    NumPurchased = RecSet.CountOfItems_ID                  '    Error 
    MsgBox "NumPurchased = " & NumPurchased
    
End Sub

Got Compile Error - Method or Data member not found.

[faythe1215]

Never mind. I fixed that:

RecSet!CountOfItems_ID       'instead of RecSet.CountOfItems_ID

I actually have a new problem.

I created the fields [CountOfItems_ID] and [AvgOfRetailPrice] with the totals option on the query design, one with a count and the other an average. Any time I try to access it with the method above, it tells me that I have a 3265 error. Is there another way to generate this so that the information can be accessed?

[faythe1215]

Ugh, never mind. Typo.

No more late night working for me. Sorry everyone... I'll just leave this here for people to learn from my *face palm* mistakes.