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

accessonly11

Member
Local time
Tomorrow, 02:16
Joined
Aug 20, 2022
Messages
91
trying to make my question more simple
here is screenshot of my ORDERS FORM (which include a subform of ORDER DETAILS)
1660998950003.png

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
1660999924582.png

how it is possible in easiest way
 
Bring that data in with your combo data, and refer to the relevant columns.
 
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'
 
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
1661000456469.png

and the design view of query is here
1661000558368.png

resulted of it is
1661000819319.png
 
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.
 
What is the RowSource of the combo (ProductID) shown in your form screenshot
 
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)
 
Last edited:
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.
 
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 :(
 
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:

Rich (BB code):
   With Me.ProductId 
      If Not IsNull(.Value) Then 
         Me.RetailPrice = .Column(3)  'or whatever column it is
      End If 
   End With
 
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
 
you can add code to the ProductID textbox to get the price.
Code:
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, you can use the AfterUpdate event of the combobox to store point-in-time values in order details. For instance:

Rich (BB code):
   With Me.ProductId
      If Not IsNull(.Value) Then
         Me.RetailPrice = .Column(3)  'or whatever column it is
      End If
   End With
1661082734459.png

it will change the values of entire column, not only the row cell
 
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
 
Something weird going on here.:(
Price now is dependent on last max price sold?
Isn't that the cart leading the horse?
 
you can add code to the ProductID textbox to get the price.
Code:
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
1661084331541.png

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

i think, my database structure should be re-structure
 
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.
 
@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, 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

1661174246245.png


thanks to all
 

Users who are viewing this thread

Back
Top Bottom