New Record

E

Emma

Hi for some reason my code brings up the first record instead of creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
 
E

Emma

The problem is ID is an auto number and I would like to use a NewID instead
but the data I have so far doesn't have NewID's.
 
K

Ken Sheridan

You should be able to insert a new row with the following SQL statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e. be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England
 
D

Damon Heron

Its not clear what you are doing with this statement.
strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
Click to expand...
how about
strSQL = "INSERT INTO [Case Note Client] (ID) VALUES (" & [intNewID] & ") ;"

this will insert your new ID number into the table as the autonumber.

Damon


Emma said:
The problem is ID is an auto number and I would like to use a NewID
instead
but the data I have so far doesn't have NewID's.

Emma said:
Hi for some reason my code brings up the first record instead of creating
a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user
environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
 
E

Emma

Hi Ken,
can you show me how to go to a new blank record with only the date showing,
using the auto number id?

Ken Sheridan said:
You should be able to insert a new row with the following SQL statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e. be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England

Emma said:
Hi for some reason my code brings up the first record instead of creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
 
E

Emma

It does add a record to the table unfortunately the form doesn't go to a new
record.
I added the following to try to get the form to update:
If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If


Ken Sheridan said:
You should be able to insert a new row with the following SQL statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e. be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England

Emma said:
Hi for some reason my code brings up the first record instead of creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
 
B

BruceM

CurrentUser is Admin unless you have put user-level security in place.

As Ken asked, why not just add a new record? Your command button code could
be:

Me.Recordset.AddNew

The form's Before Insert event could have:

Me.ID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

The Before Insert event runs when a new record is inserted. The Before
Insert event has the practical effect of running as soon as the user starts
typing into a new record. If the user decides against adding the record
(clicked the button by mistake, or something) the ID number is not entered
if the user navigates away from the new record.

If you want to save the record as soon as the number is assigned, add:

Me.Dirty = False

You can assign the number in other events instead, depending on how you are
using the database. For instance, you can use the form's Before Update
event if the user does not need to see the number right away.

FWIW, when I use INSERT SQL I use:
DBEngine(0)(0).Execute strSQL, dbFailOnError

But I have to say I cannot confidently explain the difference between that
and what you did. I have used INSERT SQL to insert a group of related
records after first creating the parent record by other means, and am not
familiar with using it as you are trying to do. The only thig I can
suggest, if for some reason you need to use your approach, is to try:

Me.Bookmark = Me.RecordsetClone.LastModified


Emma said:
It does add a record to the table unfortunately the form doesn't go to a
new
record.
I added the following to try to get the form to update:
If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If


Ken Sheridan said:
You should be able to insert a new row with the following SQL statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table
either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e. be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England

Emma said:
Hi for some reason my code brings up the first record instead of
creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user
environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
E

Emma

Here's how I fixed the problem: Now I have another problem the case notes are
order descending by Now but the new records I add start over so there are 4
records with today's date in the proper order then there are the new test
records I entered and their in the right order but should be at the beginning
not the end. Should I make a new post for this?

Private Sub newrcd_Click()

DoCmd.GoToRecord , , acNewRec

If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

BruceM said:
CurrentUser is Admin unless you have put user-level security in place.

As Ken asked, why not just add a new record? Your command button code could
be:

Me.Recordset.AddNew

The form's Before Insert event could have:

Me.ID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

The Before Insert event runs when a new record is inserted. The Before
Insert event has the practical effect of running as soon as the user starts
typing into a new record. If the user decides against adding the record
(clicked the button by mistake, or something) the ID number is not entered
if the user navigates away from the new record.

If you want to save the record as soon as the number is assigned, add:

Me.Dirty = False

You can assign the number in other events instead, depending on how you are
using the database. For instance, you can use the form's Before Update
event if the user does not need to see the number right away.

FWIW, when I use INSERT SQL I use:
DBEngine(0)(0).Execute strSQL, dbFailOnError

But I have to say I cannot confidently explain the difference between that
and what you did. I have used INSERT SQL to insert a group of related
records after first creating the parent record by other means, and am not
familiar with using it as you are trying to do. The only thig I can
suggest, if for some reason you need to use your approach, is to try:

Me.Bookmark = Me.RecordsetClone.LastModified


Emma said:
It does add a record to the table unfortunately the form doesn't go to a
new
record.
I added the following to try to get the form to update:
If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If


Ken Sheridan said:
You should be able to insert a new row with the following SQL statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table
either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e. be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England

:

Hi for some reason my code brings up the first record instead of
creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user
environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

It seems you have user-level security, and users are logging in to use the
database, otherwise CurrentUser is Admin. I must have completely
misunderstood the problem if that is how you solved it.

As for the order, that can be set using a query rather than the table. What
value is in the date field for the old records? You may be able to add a
date to all blank date fields (old records) by way of an update query, then
sort in descending order by DateField, then ID (assuming ID is in
approximate chronological order).

Emma said:
Here's how I fixed the problem: Now I have another problem the case notes
are
order descending by Now but the new records I add start over so there are
4
records with today's date in the proper order then there are the new test
records I entered and their in the right order but should be at the
beginning
not the end. Should I make a new post for this?

Private Sub newrcd_Click()

DoCmd.GoToRecord , , acNewRec

If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

BruceM said:
CurrentUser is Admin unless you have put user-level security in place.

As Ken asked, why not just add a new record? Your command button code
could
be:

Me.Recordset.AddNew

The form's Before Insert event could have:

Me.ID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

The Before Insert event runs when a new record is inserted. The Before
Insert event has the practical effect of running as soon as the user
starts
typing into a new record. If the user decides against adding the record
(clicked the button by mistake, or something) the ID number is not
entered
if the user navigates away from the new record.

If you want to save the record as soon as the number is assigned, add:

Me.Dirty = False

You can assign the number in other events instead, depending on how you
are
using the database. For instance, you can use the form's Before Update
event if the user does not need to see the number right away.

FWIW, when I use INSERT SQL I use:
DBEngine(0)(0).Execute strSQL, dbFailOnError

But I have to say I cannot confidently explain the difference between
that
and what you did. I have used INSERT SQL to insert a group of related
records after first creating the parent record by other means, and am not
familiar with using it as you are trying to do. The only thig I can
suggest, if for some reason you need to use your approach, is to try:

Me.Bookmark = Me.RecordsetClone.LastModified


Emma said:
It does add a record to the table unfortunately the form doesn't go to
a
new
record.
I added the following to try to get the form to update:
If IsNull(Me![Case Worker]) Then
Me![Case Worker] = CurrentUser
End If


:

You should be able to insert a new row with the following SQL
statement:

strSQL = "INSERT INTO [Case Note Client] (ID) VALUES(" & _
intNewID & ")"

This does of course mean that all of the other columns in the table
either
have to allow Nulls, i.e. not be 'required', or prohibit Nulls, i.e.
be
'required' and have a value set as their DefaultValue property.

Is there any particular reason why you cannot simply navigate to a new
record and allow the autonumber ID value to be inserted automatically?

Ken Sheridan
Stafford, England

:

Hi for some reason my code brings up the first record instead of
creating a
new record. Here's my code:

Private Sub newrcd_Click()

Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ID]", "[Case Note Client]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user
environment

strSQL = "INSERT INTO [Case Note Client] (ID) SELECT " & _
intNewID & " AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'Me![Case Worker].Value = CurrentUser

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with code 3
Making form go blank when new Record is called 1
If a record doesn't exist 12
creating a new record with a unique ID # 20
VBA works in orignal but fails in copy of database 1
delete sub and stay on same parent record 4
insert into table 2 fields instead of one 5
Form's new SQL recordsource returns nothing 5

Top