Info from Subform to main form

[kabir_hussein]

Hello

I have a query regarding data from a sub-form being transfered to a main form. At present i have a part list and a subform which holds prices from different suppliers. The cheapest price shown will automatcally be selected as the best price and will be shown on the main form. This part is easy.

The part i am having problems with is, the row with the best price has also a lead week (see attachment) field and this must be copied onto the main form. However i do not know how to do this.

I will be very greatful for any help

 

[adam_fleck]

How do you get the best price?

If you're using DMin then that could make things a bit tricky.

An approach that might work would be to write a function to get the information you want.

Something like this:

Public Function get_Best_Price(orderID as Long, Lead_Week)

    Dim pSQL as String
    Dim pRex as Recordset


    pSQL = "SELECT TOP 1 tbl_Quotes as.* FROM  tbl_Quotes" & _
              " WHERE tbl_Quotes.OrderID = " & orderID & _
              " ORDER BY tbl_Quotes.fld_Price;"
    
    Set pRex = currentDB.openRecordset(pSQL)

    If pRex.EOF then
        Exit Function
    End If

    pRex.MoveFirst
    get_Best_Price = pRex("fld_Price")
    lead_week = pRex("fld_Lead_week")
    pRex.Close

End Function

In the code editor window, you'll need to set a reference to Microsoft DAO 3.6 before this code will work.

To use the function, on the main form's current event you do something like this:

Private Sub Form_Current()

    Dim  L_Week

    Me.Best_Price = get_Best_Price(me.order_ID, L_Week)
    Me.Lead_Week = L_Week

End Sub

Why this works (if it does - you'll need to change the code to match the names you've given objects in your database).

You send order_ID and L_Week to the function get_Best_Price. The function uses order_ID to find out which is the best price for that order_ID. In doing so, it changes the value of the second variable, lead_week. This change is passed back to the variable that was used when you called the function, so, after you've called the function, L_WEEK will be changed to the lead_week value for your best quote. Not a good explanation, sorry. But try it. If you can get it to work, you'll maybe find this is a good way of setting a whole load of variables using only one function call.

 

[kabir_hussein]

Hi

many thanks for the reply, I have tried to do what you said using the following code

Dim lead_week


Me.text12 = text12(Me.SupplierChoiceNu, lead_week)
Me.lead_week = lead_week


End Sub

but it come up with an error messgae.

I have attached another screen shot this time showing all the form names etc

Thank you for all the help