Microsoft Access Continuous Form

Question

I have set up a Microsoft Access database; I want to capture information from my continuous form into my table.

I have a header section which automatically enters my Transaction Date and Transaction type, and you manually enter a vehicle ID from a combo box, all works fine. The detail section as 7 columns, the first two columns the information is automatically entered depending on which vehicle you select in the header section. The fields 3 to 7 you manually enter the required information, all works fine. The problem I have is the continuous form them opens ghost rows and these rows also are updated in my table. I used VBA in my vehicle id field after update property. Could you please advise how I can prevent these ghost fields.

Answer 1

Hi @Alan Fealey

In a bound form, Access allows new records when the form’s AllowAdditions property is enabled. Microsoft documents that when AllowAdditions is set to True, users can add records, and when it is set to False, users cannot add new records.

Because your VehicleID_AfterUpdate code is automatically populating values into the detail area, Access may treat that row as a new record and attempt to save it. That is why you can end up with unintended blank or partial rows being committed.

You can try:

Option 1: Modify your VBA to use DefaultValue

The DefaultValue property is automatically entered when a new record is created. It can be set for a form control or for a table field, and it is commonly used to prefill values in new records. Microsoft also notes that you typically set a default value for a form control when it is not bound to a field, or when it is linked to data in another table.

So, instead of directly writing values into bound controls in AfterUpdate, one possible approach is to assign defaults for the next record rather than forcing values into the current row immediately.

Private Sub cboVehicleID_AfterUpdate()
    Me.DetailColumn1Name.DefaultValue = """" & Me.cboVehicleID.Column(1) & """"
    Me.DetailColumn2Name.DefaultValue = """" & Me.cboVehicleID.Column(2) & """"
End Sub

Option 2: Add it at the very top of your AfterUpdate event

If Me.NewRecord Then Exit Sub

Option 3: At table level, set VehicleID = Required

However, these are just some suggestions since I do not know your settings underneath the form. In order to provide the tailored answer to your case, it'd be great if you can provide your Access file via private message.

I look forward for your reply.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.