Run-time error 3020 - combo boxes

[freem500]

I've got a combo box on a form used to select a 'Partner' and jump to the correct record. When the record appears, the Partner name is displayed in a second combo box, along with all other details. The second combo box is bound to the field in the table.

The problem is that the following sequence of events causes error 3020 (Update or CancelUpdate without AddNew or Edit):

• Select a record in the top combo box (the unbound one)
• Change the bottom box to a new value
• Select a new record from the top combo box

I can understand (in a hazy way!) why this error is being generated - I suppose it's because the user has tried to update a new record, but the update has not completed. How, and where, do I trap this error?

Just for information - this is a DB I'm testing and amending, although I didn't develop it. I'm trying to reproduce all possible errors that users might find.

The error is occurring in the following code in the AfterUpdate event of the combo:

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[fldPartnerID] = " & Str(Nz(Me![cboPartnerName], 0))
    
    'Synchronise PartnerID And PartnerName
    Me![cboPartnerID] = cboPartnerName
    
    If Not rs.EOF Then
        [COLOR="Red"]Me.Bookmark = rs.Bookmark[/COLOR]
    End If

Thanks for any help on this ... feeling a bit desperate at the moment!

 

[boblarson]

I think it's because you are using a bound combo box to try to do a search on the form. You should be using an UNBOUND combo box for doing the search.

 

[freem500]

The top combo is used for the search - and that is unbound. The bottom one is really meant for changing the Partner name on the record to a different one (since that is bound), and it works fine as long as you remember to move to the next record (and therefore save the change), but if you follow that 'update' with another search in the top combo, the error occurs.

It's just struck me that I should probably force the record to save after the bottom combo has been used to edit the record. Would I do that with Me.Refresh on the AfterUpdate event of the bottom combo (the bound one)?

 

[boblarson]

In the unbound, try putting:

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

 

[freem500]

Tried that and it has fixed the error ... the problem now is that the record has it's new value, but the unbound combo box thinks it's still the old one.

I did try putting Me.Refresh in the other bound combo, and that seemed OK - what is the difference between Me.Refresh and DoCmd.RunCommand acCmdSaveRecord?

Thanks for your help on this!

 

[boblarson]

Me.Refresh refreshes the records in the form - but it will only show changes to records that pre-existed in the form. Requery will update the form's underlying recordsource to reflect added/deleted records.

The acCmdSaveRecord is saving the changes that occurred in the form, whether it be a new record or a modified record.

To get a combo box to show newly added records you would need to requery the combo box.

 

[freem500]

Thanks for the explanations .... it's all working now!