Unwanted new record

[kbrooks]

I have a very simple database set up for a department to notify me of charge corrections I need to make in our main system. The form is set up where each record is a line, and a new record just goes on the next row instead of a new page. (Tabular view, I believe)

I have some simple code set up in the AfterUpdate and the OnEnter to duplicate some fields from the previous record, simply to save data entry for the user. The code appears to be the same in both events, not sure why it's needed in both, or if it is? I had help with the code so not exactly sure.

Anyway, my problem is that when they tab to a new record, and then decide they have nothing else to add and close the form, a new pretty much blank record is added. It's not a huge deal as there are only a few users, but it's rather annoying and I'd like to stop it if possible. How do I go about doing this?

TIA

 

[jfgambit]

Can you post the code so that we could review it?

 

[kbrooks]

Why certainly. Forgive me if I cut paste more/less than needed, as I don't really know vb well at all.

After Update:
Private Sub Account_AfterUpdate()
Me![Account].Tag = Me![Account].Value
End Sub

Private Sub Account_Enter()
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![Account].Tag) Or Me![Account].Tag = "") Then
Me![Account].Value = Me![Account].Tag
End If

End Sub

On Enter:
Private Sub Account_Enter()
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![Account].Tag) Or Me![Account].Tag = "") Then
Me![Account].Value = Me![Account].Tag
End If

End Sub

 

[Fizzio]

Use the BeforeInsert event as Pat describes here

 

[kbrooks]

I saw that same post when I searched, sorry! Since I didn't insert the code I wasn't exactly sure when or how it was doing what I wanted and didn't want to mess it up.

Do I remove the code from the AfterUpdate and event and add it to the BeforeInsert? Or remove from both AfterUpdate and OnEnter? Wasn't really sure why it was in both places, or if it was needed.

 

[Fizzio]

s'ok k,

Use the BeforeInsert instead of the On_Enter code of the subform as this is the event that dirties the record as soon as you enter the form instead of when you want to add a new record.

 

[kbrooks]

Ok it took me this long to get the code changed and give it a try, but I can't get it to work. I have this in the Before Update event...

Private Sub PatientName_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![PatientName].Tag) Or Me![PatientName].Tag = "") Then
Me![PatientName].Value = Me![PatientName].Tag
End If
End Sub

I have the same event on Account#, PatientName, and ServiceDate. I entered a new record and then tabbed to the next record. The first field is the account# that I wanted copied from the previous record, but it's not copying in. I tabbed to the name field and that didn't copy in either.

Any ideas?

 

[kbrooks]

On reading over my notes, I realized I had the code in the BeforeUpdate event, and not the BeforeInsert as suggested above. However I do not have a BeforeInsert event field available to me. Is that due to the version of Access? I have a hard time believing that but I'm not sure what else it could be.

Or is BeforeInsert and BeforeUpdate the same thing?

 

[Rich]

Have you tried the Form_Current Event?

 

[kbrooks]

No I haven't. And now I do see the BeforeInsert event. I was looking on individual field properties and not the form properties.

But I'm confused how the form properties will allow me to copy in 3 of the fields but not the others. I assumed I needed to put it in the field's events?

 

[Fizzio]

The controls 'belong' to the form and so you can reference the controls from the forms vba code.

The vba code belongs to the form and therefore using the referencing Me.xxxx means the control xxxx on form Me.

When you enter a new record, navigate through records, etc, it displays all the field values in the bound controls. Therefore the vba code for any form can easily refer to any the controls on that form by using me. or me!

If you want to reference something elsewhere, that is when you need to use the Forms!NameOfOtherForm!NameOfControl.

Does this clarify things?