Trouble Updating Just One Row in a Detail Form - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Trouble Updating Just One Row in a Detail Form

Jim Thompson

6 hours ago

Ive built a form and subform modeled on the inventory examples

I wanted to add another field the the form, so i added an unbound text box, MemberPrice, and added the data needed to the query associated with the add button.

I added the field update to the vba code for the add button

It works as before, that is all other fields update as expected when adding the new item but MemberPrice is updated in all details, while the old fields are preserved ...   I'm missing something.

Private Sub AddBtn_Click()
    If IsNull(ProductCombo) Then
        DoCmd.GoToControl "ProductCombo"
        ProductCombo.Dropdown
        Exit Sub
    End If

    ProductID = ProductCombo.Column(1)
    ProductName = ProductCombo.Column(2)
    MemberPrice = ProductCombo.Column(5)
    UnitPrice = 0
    If ProductCombo.Column(4) Then
        Tax = UnitPrice * 0.06
    Else
        Tax = 0
    End If

Donald Blackwell @Reply

4 hours ago

What do you mean by "updated in all details"? Are you working in a continuous form with multiple detail rows? If this is the case, Access can't know which record the MemberPrice should get it's data from since it is an unbound field so when you update from VBA, it updates all MemberPrice fields to that value. The other, bound, fields stay locked to what they are since Access can recognize that it is related to a specific record.

If this is not the case, then will need more detail of how you form/subform are layed out, which form the button is in and where the MemberPrice field is at to troubleshoot.

	Alex Lewis @Reply 3 hours ago
Looking at your code, your best bet is to make MemberPrice a bound value to whatever table/query your combo box is getting its values from. You mentioned your MemberPrice is unbound, which could explain why it’s updating all rows on your form. Could you post a screenshot of the table/query your combo box is based off of?

Richard Rost @Reply

2 hours ago

Donald has it. In a continuous form, an unbound control is really just one control displayed repeatedly. When you assign a value to it in VBA, Access displays that same value in every visible detail row because there is no field in the form's RecordSource to store a separate value for each record.

Add MemberPrice to the table/query that is the RecordSource for the subform, then set the MemberPrice textbox Control Source to that field. It should be a bound control, just like ProductID, ProductName, UnitPrice, etc.

Also, if MemberPrice is supposed to be the price copied onto the order detail at the time the item is added, make sure it is a field in the order detail table, not merely a field coming from the ProductT lookup. That preserves the historical price if the product's member price changes later.

Next Unseen

New Feature: Comment Live View