Help about updating subform record.

[binbin]

Hello everyone,

Let me describe my form setup first.

I have a main form named "frmMain" with a combo box named "cboCustomer."

In this main form I have a sub form named "subfrmMain" with a combo box named "cboProduct" and a text box named "txtDiscount."

The txtDiscount's value relies on the cboProduct "after update" and "on got focus" event through which I call the value from it's row source query like below.

Private Sub cboProduct_AfterUpdate()    
   If Me.Parent!cboCustomerType = "VIP" Then
       Me.txtDiscount = Me.cboProduct.Column(3)
   Else    
       Me.txtDiscount = 0
End If
End Sub

Private Sub cboProduct_GotFocus()
   If Me.Parent!cboCustomerType = "VIP" Then
       Me.txtDiscount = Me.cboProduct.Column(3)
   Else    
       Me.txtDiscount = 0
End If
End Sub

I want to keep the value of the txtDiscount depending on the value of the cboCustomer on the frmMain, sort of like a discount history, but I do want to change it when I change the cboCustomer. This is where I begin to have a problem. After I choose "VIP" from the cboCustomer, the txtDiscount displays the correct amount only for one record if the focus is on the cboProduct of that record.

So If I have more than one record in the subfrmMain, only the row where the cboProduct has focus will the value for the txtDiscount change BUT the succeeding records will not update their txtDiscount value until it's cboProduct has focus. So the long solution for me is to cycle through all the records to trigger the event on the cboProduct control "on got focus."

I have tried to make an event on cboCustomer on the frmMain after update to requery the cboProduct or refresh the subfrmMain, but didn't not work. The txtDiscount value only change when cboProduct gets focus.

I hope I explained my problem well since I'm having trouble explaining it, I have tried to search but did know exactly what to search for.

I would very much like to update every row/record in the subfrmMain to reflect the correct txtDiscount value depending on the cboCustomer value.

Thank you much for your patience.

[June7]

Is txtDiscount an UNBOUND control. Using code to set value of UNBOUND control will cause the control to display the same value for all records because there is only one control. Options:

1. Use expression in textbox to display the discount for each record by referencing the combobox column:

=[cboProduct].[Column](3)

2. bind the textbox to Discount field

In either case, use code in combobox AfterUpdate event to populate the discount field.

Me!Discount = Me.cboProduct.Column(3)

[binbin]

Hi, sorry for the incomplete info.

All the controls I mentioned are bound to a field. The cboCustomer control is on a Parent table while the controls cboProduct and txtDiscount are on the Child table. I think I have already used the code you have shown on the after update and got focus event of cboProduct. This works fine, the user will choose from combobox cboCustomer what kind of customer (VIP or Not) the items are sold to, then on the subform "subfrmMain" the cboProduct control has the "If" statement code below. To see if the customer is VIP or not.

Private Sub cboProduct_AfterUpdate()       
   
   If Me.Parent!cboCustomer = "VIP" Then
       Me.txtDiscount = Me.cboProduct.Column(3)
   
   Else    
       Me.txtDiscount = 0
End If

End Sub

Private Sub cboProduct_GotFocus()
   
   If Me.Parent!cboCustomer = "VIP" Then
       Me.txtDiscount = Me.cboProduct.Column(3)
   
   Else    
       Me.txtDiscount = 0
End If

End Sub

Then after choosing the product from cboProduct, this will auto populate the txtDiscount control with the correct discount value. This works fine at this point. But after encoding all the customer's orders the user suddenly realizes that the customer is not a VIP, the user would have to change the value of the control cboCustomer on the main form to blank which in turn should affect the discount value on the subform. However, this does not happen even after I refresh the subform or requery the control cboProduct.

The update of the txtDiscount value only occurs if I go to the subform control cboProduct (giving the control focus.) This is an Ok solution if I only had one record in the subform datasheet, unfortunately, the orders usually go from a minimum of 10 items (records) to about 80 items. Which the user then would have to go to each record's cboProduct control to trigger the autopopulate of each txtDiscount value thereby updating it... What if the user forgets to do that?

That is why, if it is possible, that as soon as the user changes the value of cboCustomer on the main form, the appropriate discount value on the subform instantaneously updates. So that the user does not have to manually go to each cboProduct control to trigger autopopulate code.

Thanks so much!

[June7]

How many options can I come up with?

Code in cboCustomer AfterUpdate event to:

1. requery cboProduct and populate txtDiscount (that would impact only one record)

2. run an SQL UPDATE action to edit records in table and refresh the subform

3. cycle through the subform RecordsetClone and change the discount field value

[binbin]

Hi thank you for the reply.

I think I got option number 1 and implemented it.

I'm still trying to figure out option 2, I'm no good with SQL, but I think an update query (which is a noob's SQL) will be able to do this and I will have to experiment a bit.

I'm lost with option 3, sorry, recordsetclone somehow intimidates me and the best I could find is a loop through records which I don't even know if that is what you meant? Sorry about that, I have not done this kind of database before that offers the user (My brother) some added features.

Thank you for your patience

[June7]

Can use an Access query object and have code run the query - VBA example:
DoCmd.OpenQuery "query name"

Or code can execute the SQL statement (switch query to SQL view to see what an SQL statement looks like) - simple VBA example:
CurrentDb.Execute "UPDATE tablename SET fieldname=something WHERE ID=" & Me.ID

Looping through records is close to what option 3 suggests.

[binbin]

I made an update query which replaces any value in the subform if the cboCustomer value was change and then I tried out what you said and so far it's working, I did have to refresh the subform after the query to reflect the updated values which was fairly easy to do.

Thank you and I'm very grateful for the help.