Fill in multiple fields with AfterUpdate

[robert_neville]

My database serves as a complex contact manager, which no other commercial product emulates. My database has a Company Form for inputting company information. The Company form has an address subform.

My objective involves selecting a previously entered address through a combo box field (addr1) and having AfterUpdate code automatically fill in the other address fields. My code draft returns an error upon activating the event. The combo box should allow new records for the input and to easily change the company addresses as well. The error message and code follow below.

Run-time error ‘3331’:
Error Description: To make changes to this field, first save the record

Private Sub cboAddr1_AfterUpdate()
' DOES NOT WORK
Me![txtCompAddrID] = Me![cboAddr1].Column(0)
Me![cboAddrName] = Me![cboAddr1].Column(1)
Me![cboAddr1] = Me![cboAddr1].Column(2)
Me![cboCity] = Me![cboAddr1].Column(3)
Me![cboStateID] = Me![cboAddr1].Column(4)
Me![txtPostalCode] = Me![cboAddr1].Column(5)
Me![txtCountry] = Me![cboAddr1].Column(6)
End Sub

Let me know if you could lead me in the right direction with this objective.

 

[Pat Hartman]

Sounds like you are using a bound field to search for an address. If you want to have a combo that you use for searching, add a new combo to the form and make sure its control source is blank. That will let you find records without changing any data.

 

[robert_neville]

Yes, the field is bound combo box. The objective behind the combo box involves changing one address to another common location (it happens a lot). The combo box serves less as a search utility. Two combo boxes serve this purpose, Addr1 and AddrName. Either type in the new address line and continue manually inputting the address fields; or select from the list and automatically populate the other address fields.

I also developed a third unbound combo box on the sub-form footer for testing purposes. This combo box fairs better than the other two, yet generates the following error when you select an address & press enter; then select another location and press enter.

Error Message
To make changes to this field, first save the record. [3331]

This situation may imply changing the code to save the record after a selection has been made. Yet this approach is foreign to me since the AfterUpdate code works for other people. Let me know if you could coach me on the syntax. The bug is essentially annoying if the user is not familiar with saving the record first.

Let me know if you have any further insights.

 

[Pat Hartman]

Your form is conflicting with itself. Are you doing updates via DAO, ADO, or queries in addition to using a bound form?

 

[PepeGallo]

I don't know if my solution will work for you, but I had the same problem and I just added:

Me.refresh

after the lines of code that assign the combo box values to the bound fields in the AfterUpdate() event.

 

[Pat Hartman]

Me.Refresh, in addition to refreshing the recordsource with any data changes, also saves the current record. That is why it solved your problem. In some cases saving the current record, prior to the action that causes the conflict will work. However, to make it clear what you are doing, I recommend using the correct command to save the current record-
DoCmd.RunCommand acCmdSaveRecord
That way you will understand what the purpose of the code is. In the future, someone might possibly remove the Me.Refresh thinking that a Refresh isn't necessary at that point.

 

[jan@BRU]

To make changes to this field, first save the record

I also have a problem with the initial error code mentioned

"To make changes to this field, first save the record"

Consider a three-tier table hierarchy, let's say

IndustrySector (SectorID, OtherSectorFields)
Customer (CustomerID, SectorID, OtherCustomerFields)
Order (OrderID, CustomerID, OtherOrderFields)

linked appropriately with 1:n relationships.

Now consider a main form, on the first level (displaying data for the industry sector and having a subform, which is supposed to show all the Orders received from that industry sector. Since Order and IndustrySector are linked via the customer table, the subform is based on a JOIN query between Order&Customer, so that SectorID is added to the fields, in the query and the Main and Subform can be linked via the SectorID/SectorID MasterFields/Child Fields link:

qOrder ([Order].[OrderID], [Order].[CustomerID], [Order].[OtherOrderFields], [Customer].[SectorID])

What works:

1) I can add new orders to the qOrder query manually, not a problem.
2) I can add new orders to a form based on qOrder query. Brilliant.
3) I replace the Order.CustomerID box by a combo box, based on the Customer table, and which displays Customer name rather than CustomerID... Great stuff.

What does not work:

When I place the form as a subform of a masterform, which - itself - is based on the Industrysector table, linkt the two together via SectorID (see above) and then I try to enter a new order, one of the following problem occurs:

Either:
I add fields, which are not part of the query's JOIN first (e.g. OrderID) and subsequently (try to) enter an (existing) CustomerID. In this case, before I even type the first character, I get the error message mentioned:

"To make changes to this field, first save the record"

Or:
I try to save the record first, in which case I get some other funny message, arguing that I need to add a record with an existing entry of the join key on the one side of the relationship, which is true...

I read about this saving the record first with some kind of event command (see suggestion above on ME.Refresh etc.), but to which event to place that? And then, I don't understand the logic, as I'm trying to do something quite straightforward.

The only thing, which I could explain is that he doesn't allow me to add a CustomerID, as long as the subform doesn't know, whether SectorID it will add automatically corresponds to the Child/Master Fields constraints of the Subform/Form relationship. But this would be unsolvable, or is it a question of adding this field (the SectorID, which I actually don't need and don't want to display in the subform) first...

Any help?

Many thanks!

JAN