Adding a new record using with AllowAdditions set to false?

[sponge]

Hi,

Is it possible to add a new record thorough VBA (for a command button) with the AllowAdditions property set to false?

I only want new records created if this command button is pressed.

TIA!

 

[WayneRyan]

Sponge,

Why does letting them push a Command Button change things?

Command Button code:

Me.AllowAdditions = True

Wayne

 

[sponge]

Hi,

Thanks for the reply.

That code would indeed allow people to add a row but then from that point on, the user would be able to continue adding, without a button.

I've tried enabling allowadditions, add a row, and then disabling for the command button code, but it does not work...

 

[WayneRyan]

Sponge,

Still not understanding, but in the form's AfterInsert event, you can put:

Me.AllowAdditions = False

Wayne

 

[sponge]

Hi

Thanks! That's gotten me over this hurdle. I realized some other issues have cropped up that I was wondering if you'd know how to resolve:

1. After clicking on the button, a new record gets added properly. When text is typed, another row gets added however - this row "disappears" once I have tried to "select" the next row (e.g. through tabbing). So adding Me.AllowAdditions = False in the form's afterinsert event does the trick.. but leaves this as sort of an "annoyance"... Is there any way to prevent this from happening (i.e. no "extra" row appearing after pushing the button and typing text?)

2. Is there a way to allow more than one row to be added? The code I used only allows one record to be added and nothing more.. Also, is there a way to delete all "empty" rows upon exiting the form?

Many, many thanks in advance!

 

[vbc22]

I suspect you're using the form's Continuous Form view property; that's why a blank text field(s) appears when you select the last record. To prevent this from happening, you can:

1. Select Single Form view, but then you will have to create a button to allow traversing through other records---unless you have the form's Navigation Buttons property set to "Yes".

2. Select Single Form view, and set Data Entry to "Yes". Now this becomes a pure form for adding new records only.

 

[dazadd99]

I have similar problem...

My problems was that users were going to the Last record using the navigation buttons.
When they clicked "Next" button a new record would be created and they could create limitless new records, this was resulting in loads of empty records being created.

I manually created navigation buttons that allowed the same process but turned off "Allow Additions" in the forms properties.

On my newly created navigation buttons, the Next button moves through the existing records up to the last record, where it cannot create a new one.
My newly created "New Record" button runs the code below::

Private Sub btnNewRec_Click()
On Error GoTo Err_btnNewRec_Click

Dim Msg, Style, Title, Response
Msg = "Add a NEW record ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "New Record" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Form.AllowAdditions = True ' Temporarily Allow additions
DoCmd.GoToRecord , , acNewRec ' Create new REcord
Form.AllowAdditions = False 'Disallow additions

Else ' User chose No.
End If

Exit_btnNewRec_Click:
Exit Sub

Err_btnNewRec_Click:
MsgBox Err.Description
Resume Exit_btnNewRec_Click



End Sub


What do you think to that??
I bet there's an easier way - I just couldn't find it.

Regards

dazadd99

 

[ScottGem]

Daz,
look for examples of custom navigation buttons. Most that I have seen do a test for EOF (or BOF) before going to the Next record. This prevents pressing the Next button and getting an new record to add. This way you leave Allow Additions turned on, and your New button opens a blank record.

 

[vbc22]

Hi,

The following is in response to dazadd99's post.

Access makes building databases easy, but when the application gets complicated, that's where the digging of codes begin and the learning curve gets steeper.

I went through this phase.

Rather than deal with the connected model, I separated all my forms from the database and worked in a disconnected way; I'd create controls such as textboxes that didn't have a control source...so they were all unbound.

The form replicates all the information that would go into the database. Once it had the complete information, attached code to (for example) a Save button, and it'd write it all to the database. This method is code intensive, but in the end it's more flexible.

The code I work with is ADO. An example would be:

Private Sub btnSave_Click()
    Dim sql As String, rst As New ADODB.Recordset
    
    sql = "SELECT firstName, lastName, phone, email FROM tContacts"
    
    rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    rst.AddNew
    
    rst("firstName") = Me.txtFirstName
    rst("lastName") = Me.txtLastName
    rst("phone") = Me.txtPhone
    rst("email") = Me.txtEmail
    rst.Update
    
    rst.Close
    Set rst = Nothing
End Sub

This reply didn't really contribute to the question at hand, but it took the problem and [would] solve it in a different way.