Solved get textbox value on subform from another querry based on combobox

[accessonly11]

trying to make my question more simple
here is screenshot of my ORDERS FORM (which include a subform of ORDER DETAILS)

i need to get retail price and sale price for my ORDER DETAILS FORM from a purchase query based on latest dates
here is snapshot of purchase query is here

how it is possible in easiest way

 

[Gasman]

Bring that data in with your combo data, and refer to the relevant columns.

 

[cheekybuddha]

Your forms look great!

But to really help answer your question we need to see your tables and how they relate to each other.

Can you post a screenshot of your table relationships diagram and also the SQL of the 'query based on latest dates'

 

[accessonly11]

Your forms look great!

But to really help answer your question we need to see your tables and how they relate to each other.

Can you post a screenshot of your table relationships diagram and also the SQL of the 'query based on latest dates'

here is the screenshot of my tables relationship

and the design view of query is here

resulted of it is

 

[accessonly11]

Bring that data in with your combo data, and refer to the relevant columns.

i am unable to do so

 

[GPGeorge]

i am unable to do so

Why not? Remember, we can only "see" the information you provide, so a response needs to include details like the reason for not being able to include additional columns. With that detail, people have a better chance of offering useful suggestions. Thanks.

 

[cheekybuddha]

What is the RowSource of the combo (ProductID) shown in your form screenshot

 

[strive4peace]

i am unable to do so

hi @accessonly11

why not? (EDIT: didn't see @GPGeorge's comment whilst writing mine ... same reaction)

As @Gasman suggested, best would be to add that query to the RowSource for your combobox. And then add controls that reference other columns to display values if you want to also see them? Actually, you don't need to display them to use them... but sometimes you want to.

If you made your combobox with a wizard, perhaps you don't realize how to change it using the Property Sheet. Here are a couple video tutorials you might like:

Combo Box properties in Microsoft Access (11:25)

Sort Access Form by Combobox Column (19:22)

btw, it helps, in my opinion, to arrange the relationships diagram so data flows from left-to-right ... so "1" is on the left and "∞" on the right. Customers should be on the left. Purchase should be the to left of PurchaseDetails. Now, in case you didn't know, you can lasso a group of tables on the relationships diagram and move them in a batch instead of one-by-one. Nice that you've enforced referential integrity.

EDIT: and good that you expanded lists on the relationships diagram to show all the fields, and adjusted width to show complete names.

Another fairly new thing in Access is being able to double-click on the border of a fieldlist in the relationships diagram to resize it ... maybe you already know that though since it looks like the extra spacing is pretty consistent (I often close that up)

 

[accessonly11]

Suggestion of getting values of retail price and sale price from cbo.product columns working, but there is problem, these values are not storing in order details table.

 

[Gasman]

Suggestion of getting values of retail price and sale price from cbo.product columns working, but there is problem, these values are not storing in order details table.

Well they would be in the Product table surely? or a even ProductsPrice table with a date to show when valid until?
You have to get them from SOMEWHERE

 

[strive4peace]

Suggestion of getting values of retail price and sale price from cbo.product columns working, but there is problem, these values are not storing in order details table.

@accessonly11, you can use the AfterUpdate event of the combobox to store point-in-time values in order details. For instance:

   With Me.ProductId 
      If Not IsNull(.Value) Then 
         Me.RetailPrice = .Column(3)  'or whatever column it is
      End If 
   End With

 

[accessonly11]

Well they would be in the Product table surely? or a even ProductsPrice table with a date to show when valid until?
You have to get them from SOMEWHERE

i did not create specific table for prices only, order details or purchase details having the price values

 

[arnelgp]

you can add code to the ProductID textbox to get the price.

Private Sub ProductID_Afterupdate()
Dim sValue as string
If Len(Me!ProductID & "") <> 0 Then
    sValue = DLookup("MaxOfRetailRate & '/' & MaxOfSaleRate", "RatesFromPurchaseQ", "ProductID = " & Me!ProductID) & ""
    If Len(sValue) <> 0 Then
        Me!RetailPrice = Val(Split(sValue,"/")(0))
        Me!SalePrice = Val(Split(sValue,"/")(1))
    End If
End If
End Sub

 

[accessonly11]

@accessonly11, you can use the AfterUpdate event of the combobox to store point-in-time values in order details. For instance:

   With Me.ProductId
      If Not IsNull(.Value) Then
         Me.RetailPrice = .Column(3)  'or whatever column it is
      End If
   End With

it will change the values of entire column, not only the row cell

 

[strive4peace]

hi @accessonly11

> "it will change the values of entire column, not only the row cell"

you must have a field in the source table to store the value, and that field should be the ControlSource of a control on your form -- which is what the code wil refer to

 

[Gasman]

Something weird going on here.
Price now is dependent on last max price sold?
Isn't that the cart leading the horse?

 

[accessonly11]

you can add code to the ProductID textbox to get the price.

Private Sub ProductID_Afterupdate()
Dim sValue as string
If Len(Me!ProductID & "") <> 0 Then
    sValue = DLookup("MaxOfRetailRate & '/' & MaxOfSaleRate", "RatesFromPurchaseQ", "ProductID = " & Me!ProductID) & ""
    If Len(sValue) <> 0 Then
        Me!RetailPrice = Val(Split(sValue,"/")(0))
        Me!SalePrice = Val(Split(sValue,"/")(1))
    End If
End If
End Sub

getting runtime error "you cant assign a value to this object"

i think, my database structure should be re-structure

 

[Gasman]

So what do you have as the control source for those controls?
If you want to assign a value dynamically, they need to be empty or have a query/table field name, no expression.

 

[strive4peace]

@accessonly11, adding on to @Gasman's comment ...

if you want a control to only be calculated, you can put an expression in the ControlSource.

However, if you want to be able to change the value, as you would if you're storing a point-in-time values for an order, the ControlSource must be a field in the form's RecordSource. To make it convenient for the user, you can use an AfterUpdate event on the combo to choose a product for a good starting value(s)

 

[accessonly11]

@accessonly11, adding on to @Gasman's comment ...

if you want a control to only be calculated, you can put an expression in the ControlSource.

However, if you want to be able to change the value, as you would if you're storing a point-in-time values for an order, the ControlSource must be a field in the form's RecordSource. To make it convenient for the user, you can use an AfterUpdate event on the combo to choose a product for a good starting value(s)

work is done, this code working for me
Private Sub ProductID_AfterUpdate()
With Me.ProductID
If Not IsNull(.Value) Then
Me.RetailPrice = .Column(3) 'or whatever column it is
Me.SalePrice = .[Column](4)
End If
End With
End Sub

thanks to all