Moving through datasheet subform

[FurryGorilla]

Hi

Just having a little problem with moving through records in a subform in datasheet view.

I have an order form which scrolls through individual orders. When you scroll through each of the orders the items on each order are displayed in the frmOrderItems which is a subform of frmOrders

When the user is to click on a button on the main form, calculations are to be carried out on each of the items in the subform for checking stock levels, quantities, etc.

The code for the button is as follows:

Private Sub btnTest_Click()
On Error GoTo Err_btnTest_Click
         
    frmOrderItems.SetFocus
    DoCmd.GoToRecord , , acFirst  ' Go to first record in subform

    Do....
    
	Some calculations and such in here
	DoCmd.GoToRecord , , acNext

    Loop
    
    DoCmd.GoToRecord , , acLast
    Some calculations and such here

Exit_btnTest_Click:
    Exit Sub

Err_btnTest_Click:
    MsgBox Err.Description
    Resume Exit_btnTest_Click
End Sub

I've cut some of the code out to make it easier to read. I've had a look on some other posts and seen that people have suggested using

DoCmd.GoToRecord , , acFirst

and then using a loop to go through the rest of the records in the subform. The problem I'm having is that if you have more than one order in the system which have items on these orders you get an error saying you can't access a the record. Is there a way to get it to only check the records on the subform of the particular order or will it always try to check the other orders?

Just to let you know I have a tblOrders, tblOrderItems and tblStock.. The subform has an SQL query as it's data source which returns the StockID, Name, Qty/Length from the tblOrderItems and tblStock and only shows the records where the txtOrderNo from frmOrders matches up with the OrderNo from tblOrderItems.

Hopefully I've explained myself clearly and any help would be much appreciated.

Many thanks
Chris ____________________________
Have you seen my munkee?

 

[jebry]

Hi Chris!

I'm not sure what calculations you need to do but you could work with recordsets instead working with the form. Something like this:

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select YourFields From YourTables On YourJoins Where Order.OrderNo = " & Me!txtOrderNo. & " anyothercriteria"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

Now you can use rst to loop through only the records you are interested in.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[FurryGorilla]

Ta Jeff although after much annoyance I found it was down to missing out a set of speech marks. :-( ____________________________
Have you seen my munkee?