Don't display blank new record in continuous forms (1 Viewer)

Hi

I have a navigation button to go to new record on a continuous form. I do not want to display a blank new record on the bottom of the form. How do I do this?

Thanks
Greg

EDIT--On the form's properties....
set the Allow Additions property to No

Sorry, that won't allow you to add records either way....My bad!

Stupid Me

Thanks Casey - That stops the blank record displaying but I still need to add new records via my new record button

Greg

Greg,

You can set the Allow Additions property to No; however, you'll need to create a command button with your own code to insert a record into the table that feeds your form. Not difficult.

Is it a subform -- your continuous form? If so, this code, assuming you're running Access 2000 or later, this code placed in the On Click event of a command button will insert a record:

** Code Below **

'NOTES:
'Supposing the table behind the Main form is called TblCustomers
'and that the field holding the primary key is called CustomerID

'Also supposing the table behind the subform is called TblDetails
'and that the field holding the foreign key that links the forms
'is called CustomerID

'some drunken fools are outside in the street fighting and
'yelling their heads off, one man, one woman...

Dim lngRecId As Long

lngrecid = Forms!MainFormName.CustomerID.value

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblDetails", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rst
.addNew
![CustomerID] = lngRecId
.Update
End With

rst.Close
Set rst = Nothing

Me.Recalc

** Code Above **

If your continuous form isn't a subform, but, instead, one that stands on its own, code like the following should work:

** Code Below **

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "YourTblBehindYourForm", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rst
.addNew
.update
end With

rst.close
Set rst = Nothing

'maybe they're not drunk

DoCmd.Requery

** Code Above **

Regards,
Tim

Thanks Tim

The continuous form is a sub form.

The table behind the main form is "tbeProject"
Primary key field is "ProjectID"

Main form is "frmProjectDetails"
Sub form is 'frmJobDetailsSub"

Table behind sub form is "tbeJob"
The linking field is "ProjectID"

The code in the On Click event of the command button is as follows:

Private Sub cmdAddJob_Click()
On Error GoTo Err_cmdAddJob_Click

Dim IngRecId As Long

IngRecId = Forms!frmProjectDetails.ProjectID.Value

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tbeJob", CurrentProject.Connection, -adOpenDynamic, adLockOptimistic

With rst
.AddNew
![ProjectID] = IngRecId
.Update
End With

rst.Close
Set rst = Nothing

Me.Recalc


Exit_cmdAddJob_Click:
Exit Sub

Err_cmdAddJob_Click:
MsgBox Err.Description
Resume Exit_cmdAddJob_Click

End Sub


When run I get the following message "Arguments are of wrong type, are out of acceptable range, or are in conflict with one another"

Any ideas?

Hope the drunken brawl resolved itself - our problem is hoons in cars burning out tyres on the corner.

Greg

Hi, Greg,

There's a hyphen (-) where there should be an underscore (_) or, if everything's on one line, where there should be nothing.

Change this
to this
and hopefully order will be restored.

Regards,
Tim

Hi Tim

Brilliant - Thanks very much for your help

Greg