if recordset is empty, add new record (repost)

[Guest]

I have a form where the record source is a table [purchase requisition]. In
the form_open procedure, i create a filter based on the username. If no
records are selected by the filter, I want to create a new one. When I test
the form with a username that has no records in the file, instead of creating
a new record, it gives the error "you can't go to the specified record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the form) if
the user already has a record.

I would be most appreciative is someone could give me some hints as to why
this is happening or how to track it down.

Thanks!

 

[Ken Snell \(MVP\)]

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If

 

[Guest]

Thanks for your reply!

I tried substituting your code, and now I get the error message "3426 this
action was cancelled by an associated object". I thought maybe I still
needed to set data entry to true, so I added that back in, but I still get
this message.

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>

I have a form where the record source is a table [purchase requisition].
In
the form_open procedure, i create a filter based on the username. If no
records are selected by the filter, I want to create a new one. When I
test
the form with a username that has no records in the file, instead of
creating
a new record, it gives the error "you can't go to the specified record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints as to why
this is happening or how to track it down.

Thanks!

 

[Ken Snell \(MVP\)]

You must have other code in the form that is "triggered" when a new record
is added -- and that code errors or otherwise cancels the event for which it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

I tried substituting your code, and now I get the error message "3426 this
action was cancelled by an associated object". I thought maybe I still
needed to set data entry to true, so I added that back in, but I still get
this message.

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>

I have a form where the record source is a table [purchase requisition].
In
the form_open procedure, i create a filter based on the username. If
no
records are selected by the filter, I want to create a new one. When
I
test
the form with a username that has no records in the file, instead of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints as to
why
this is happening or how to track it down.

Thanks!

 

[Guest]

I have no code on any of the events that you listed. I have some AfterUpdate
code, and some OnClick code. I do have default values for some fields.
Could those be causing the problem? I also have a filter. The default value
for the new record would make it be selected by the filter. Is there a
timing problem here perhaps? Neither DoCmd with the add new parameter nor
Recordset.addnew will work for me. Is there any other way that I can
approach this problem?

You must have other code in the form that is "triggered" when a new record
is added -- and that code errors or otherwise cancels the event for which it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

I tried substituting your code, and now I get the error message "3426 this
action was cancelled by an associated object". I thought maybe I still
needed to set data entry to true, so I added that back in, but I still get
this message.

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>


I have a form where the record source is a table [purchase requisition].
In
the form_open procedure, i create a filter based on the username. If
no
records are selected by the filter, I want to create a new one. When
I
test
the form with a username that has no records in the file, instead of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints as to
why
this is happening or how to track it down.

Thanks!

 

[Ken Snell \(MVP\)]

Is the form's AllowAdditions property set to Yes or No? It needs to be Yes.

--

Ken Snell
<MS ACCESS MVP>

I have no code on any of the events that you listed. I have some
AfterUpdate
code, and some OnClick code. I do have default values for some fields.
Could those be causing the problem? I also have a filter. The default
value
for the new record would make it be selected by the filter. Is there a
timing problem here perhaps? Neither DoCmd with the add new parameter nor
Recordset.addnew will work for me. Is there any other way that I can
approach this problem?

You must have other code in the form that is "triggered" when a new
record
is added -- and that code errors or otherwise cancels the event for which
it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert
events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply!

I tried substituting your code, and now I get the error message "3426
this
action was cancelled by an associated object". I thought maybe I
still
needed to set data entry to true, so I added that back in, but I still
get
this message.

:

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>


I have a form where the record source is a table [purchase
requisition].
In
the form_open procedure, i create a filter based on the username.
If
no
records are selected by the filter, I want to create a new one.
When
I
test
the form with a username that has no records in the file, instead of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is
my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints as
to
why
this is happening or how to track it down.

Thanks!

 

[Guest]

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset.

Is the form's AllowAdditions property set to Yes or No? It needs to be Yes.

--

Ken Snell
<MS ACCESS MVP>

I have no code on any of the events that you listed. I have some
AfterUpdate
code, and some OnClick code. I do have default values for some fields.
Could those be causing the problem? I also have a filter. The default
value
for the new record would make it be selected by the filter. Is there a
timing problem here perhaps? Neither DoCmd with the add new parameter nor
Recordset.addnew will work for me. Is there any other way that I can
approach this problem?

You must have other code in the form that is "triggered" when a new
record
is added -- and that code errors or otherwise cancels the event for which
it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert
events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>



Thanks for your reply!

I tried substituting your code, and now I get the error message "3426
this
action was cancelled by an associated object". I thought maybe I
still
needed to set data entry to true, so I added that back in, but I still
get
this message.

:

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>


I have a form where the record source is a table [purchase
requisition].
In
the form_open procedure, i create a filter based on the username.
If
no
records are selected by the filter, I want to create a new one.
When
I
test
the form with a username that has no records in the file, instead of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here is
my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints as
to
why
this is happening or how to track it down.

Thanks!

 

[Ken Snell \(MVP\)]

Hmm... this is a problem I've not seen before. Would you be willing to send
me a zipped copy of the database so that I might take a look?

--

Ken Snell
<MS ACCESS MVP>

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset.

Is the form's AllowAdditions property set to Yes or No? It needs to be
Yes.

--

Ken Snell
<MS ACCESS MVP>

I have no code on any of the events that you listed. I have some
AfterUpdate
code, and some OnClick code. I do have default values for some fields.
Could those be causing the problem? I also have a filter. The default
value
for the new record would make it be selected by the filter. Is there a
timing problem here perhaps? Neither DoCmd with the add new parameter
nor
Recordset.addnew will work for me. Is there any other way that I can
approach this problem?

:

You must have other code in the form that is "triggered" when a new
record
is added -- and that code errors or otherwise cancels the event for
which
it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert
events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>



Thanks for your reply!

I tried substituting your code, and now I get the error message
"3426
this
action was cancelled by an associated object". I thought maybe I
still
needed to set data entry to true, so I added that back in, but I
still
get
this message.

:

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>


I have a form where the record source is a table [purchase
requisition].
In
the form_open procedure, i create a filter based on the username.
If
no
records are selected by the filter, I want to create a new one.
When
I
test
the form with a username that has no records in the file, instead
of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here
is
my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on
the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints
as
to
why
this is happening or how to track it down.

Thanks!

 

[Guest]

Thanks Ken, I would be happy to send you a copy of the data base. How do I
do that?

Hmm... this is a problem I've not seen before. Would you be willing to send
me a zipped copy of the database so that I might take a look?

--

Ken Snell
<MS ACCESS MVP>

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset.

Is the form's AllowAdditions property set to Yes or No? It needs to be
Yes.

--

Ken Snell
<MS ACCESS MVP>

I have no code on any of the events that you listed. I have some
AfterUpdate
code, and some OnClick code. I do have default values for some fields.
Could those be causing the problem? I also have a filter. The default
value
for the new record would make it be selected by the filter. Is there a
timing problem here perhaps? Neither DoCmd with the add new parameter
nor
Recordset.addnew will work for me. Is there any other way that I can
approach this problem?

:

You must have other code in the form that is "triggered" when a new
record
is added -- and that code errors or otherwise cancels the event for
which
it
runs, which then cancels the adding of a new record.

Look for code running on BeforeUpdate, Enter, Exit, and BeforeInsert
events
for form and controls.
--

Ken Snell
<MS ACCESS MVP>



Thanks for your reply!

I tried substituting your code, and now I get the error message
"3426
this
action was cancelled by an associated object". I thought maybe I
still
needed to set data entry to true, so I added that back in, but I
still
get
this message.

:

Replace this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If


with this code:

'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.Recordset.AddNew
End If


--

Ken Snell
<MS ACCESS MVP>


I have a form where the record source is a table [purchase
requisition].
In
the form_open procedure, i create a filter based on the username.
If
no
records are selected by the filter, I want to create a new one.
When
I
test
the form with a username that has no records in the file, instead
of
creating
a new record, it gives the error "you can't go to the specified
record".

form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. The recordset type is dynaset. Here
is
my
procedure:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
Me.Filter = "STATUS IN ('S','P','A')"
Else
Me.Filter = "SUBMITTER = CURRENTUSER"
End If
Me.FilterOn = True


'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
End If

End Sub

I have no trouble using docmd to add a record (via a button on
the
form)
if
the user already has a record.

I would be most appreciative is someone could give me some hints
as
to
why
this is happening or how to track it down.

Thanks!

 

[Ken Snell \(MVP\)]

Look at my return email address...it's munged. Remove the words
this is not real
from the address and then you'll have the real address.

Be sure to indicate in the email which form is the one being discussed, how
do I reproduce your situation, etc. I'll take a look as time permits. Zip up
the database file.

 

[Ken Snell \(MVP\)]

I have looked at your database. The problem is your security settings in
ACCESS security.

Open the database using the rosensan user name. Use Tools | Security | User
and Group Permissions menu to look at the table permissions you have set for
the different users. You'll note that the twilliams user does not have
permission to Read data, Update data, Insert data, or Delete data in the
tblprmain table, which is the table that serves as the form's record source.

In order to add a new record to this table, the user must be able to read,
update, and insert data. The security permissions are what are causing this
error that you see. Change the settings for the user to allow access to the
table as needed for reading/creating/editing/etc. data.

I also noted some other items I suggest you change:

1) Don't use the form's Open event to run code that manipulates the form's
recordset. The form's data may not be fully loaded by the time the Open
event runs -- better to run that code in the Load event.

2) I don't see a need to set the form's DataEntry property to True when
there is no current record. I'd remove that code step.

3) You're using this expression for the form's Filter property:
"SUBMITTER = CURRENTUSER"
While this appears to work satisfactorily, I suggest that you
concatenate the value from CURRENTUSER function into the filter string this
way:
"SUBMITTER = '" & CurrentUser() & "'"
I've delimited the value from CurrentUser with ' characters because it's
a text string.

4) Don't use # and other special characters in field or control names.
ACCESS can become quite confused because # is a date / time delimiter.
See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[Guest]

Thank you so much for your time and the helpful suggestions. I appreciate
all of it very much!