Recordset.AddNew or acCmdRecordsGoToNew (1 Viewer)

T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Hi all Please can you advise on following. When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc. This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure. Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCount = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub Please can you advise on : 1. Does it matter which 'new record' code I use 2. Does it matter that the primary key misses a number 3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Trying again with code Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCount = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub
 
Minty

Minty

AWF VIP
Local time
Today, 10:35
Joined
Jul 26, 2013
Messages
10,374
On the advanced editor press the # sign and put formatted text in between the code tags, it will maintain white space and tabulation.

Code: 
Like 
     this
         :)
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Code:
Hi all Please can you advise on following.
Code:
When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew
Code:
If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc.
Code:
This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure.
Code:
Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCoun t = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub
Code:
Please can you advise on : 1. Does it matter which 'new record' code I use
Code:
2. Does it matter that the primary key misses a number
Code:
3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Code:
Hi all Please can you advise on following. When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew
Code:

Code:
If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc.
Code:

This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure. Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCoun t = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub Please can you advise on : 1. Does it matter which 'new record' code I use 2. Does it matter that the primary key misses a number 3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Code: 
Hi all Please can you advise on following.

Hi all Please can you advise on following. When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc. This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure. Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCoun t = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub Please can you advise on : 1. Does it matter which 'new record' code I use 2. Does it matter that the primary key misses a number 3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Code: 
Hi all Please can you advise on following. When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew

Code: 
 If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc.

Code: 
This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure.

Private Sub Form_Current() If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCoun t = 0 Then Me.subfrmInvoiceNew.Form.Recordset.AddNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] Else Me.subfrmInvoiceNew.SetFocus DoCmd.RunCommand acCmdRecordsGoToNew Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName] End If End Sub
Code: 
Please can you advise on : 1. Does it matter which 'new record' code I use 2. Does it matter that the primary key misses a number 3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Thanks Minty but what an effort. Don't recall it being this tricky last time I posted. Maybe wrong end of day ! Don't suppose you have an answer to my code query do you ? ta.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:35
Joined
Sep 21, 2011
Messages
14,446
Let me help

Hi all Please can you advise on following. When I add a new linked invoice to a customer record for the first time it only works if I use - Recordset.AddNew If there are already linked records then this works fine - DoCmd.RunCommand acCmdRecordsGoToNew BUT If I use Recordset.AddNew the primary key in the underlying table misses a number, e.g. 1,3,5,7 etc. This doesn't happen when using acCmdRecordsGoToNew So to reduce missing primary key numbers I came up with the following On Current event procedure.
Code: 
Private Sub Form_Current()
If Me.subfrmInvoiceNew.Form.RecordsetClone.RecordCount = 0 Then
    Me.subfrmInvoiceNew.Form.Recordset.AddNew
    Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName]
Else
    Me.subfrmInvoiceNew.SetFocus
    DoCmd.RunCommand acCmdRecordsGoToNew
    Forms![frmInvoiceNew]![subfrmInvoiceNew].Form![RaisedBy] = Forms![frmpassword].Form![EmployeeName]
End If
End Sub
Please can you advise on : 1. Does it matter which 'new record' code I use 2. Does it matter that the primary key misses a number 3. Has anyone got any better suggestions for code please From 'Googling' so far I think I've drawn the conclusion I can use either and it doesn't matter if the primary key jumps a number (although doesn't seem quite right to me). Thanks
 
T

Tim Bedborough

Registered User.
Local time
Today, 09:35
Joined
Nov 16, 2015
Messages
42
Thanks Gasman, looks better. All I need now is some feedback on the original code query. Roll on home time. Got my hat and coat on already.
 
You must log in or register to reply here.

Similar threads

M
Solved Cannot delete record from linked SQL Server table using VBA
Replies
1
Views
309
mikenyby
M
B
Solved Filtering Subform based off carried over data
Replies
5
Views
432
Pat Hartman
P
C
Solved Conditional formatting weirdness
Replies
14
Views
303
Pat Hartman
P
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
549
gemma-the-husky
gemma-the-husky
C
Solved Trying to go to a record on one tab based on record on another tab
Replies
19
Views
468
Cal Varchar
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom