if recordset is empty, add new record

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub

Does this message come up even if you nave no code to create a new record?

"SandyR" wrote:

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub

Then I get "you entered an expression with no value" when I try to initialize
the fields in my set up procedure.

"Klatuu" wrote:

Does this message come up even if you nave no code to create a new record?

"SandyR" wrote:

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub

After I close the VB screen, the header of the form appears, but the body of
it is empty.

"Klatuu" wrote:

Does this message come up even if you nave no code to create a new record?

"SandyR" wrote:

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub

Could you not just use the Default Values property of the contorls to
accomplish what you are trying to do in set_up_form?
request_date
submitter

The command button text changes, of course have to be dealt with
programmatically.

When are you runnin set_up_form?

It may be worth a shot to call it from the form's current event.

Have patience, Sandy. We will get throught it.

"SandyR" wrote:

Then I get "you entered an expression with no value" when I try to initialize
the fields in my set up procedure.

"Klatuu" wrote:

Does this message come up even if you nave no code to create a new record?

"SandyR" wrote:

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub

I do appreciate the effort you have put into this. And I have learned
several things in the process. Now all I need is to get it to work.

I shortened the set up procedure by setting the default values for several
fields, but I have one field where the caption varies depending on the
contents of another field, so I can't get rid of it completely. I took it
out of the form_load routine, and have it only in the form_current procedure
and when the status change button is clicked. Now the form starts up, but I
get the heading section and a blank (ie. nothing at all on it) from section.
When I click on ADD RECORD I am back to the "you can't go to the specified
record"

Note that form.allowadditions is true, form.dataentry is true here. and
form.allowedits is true. Here is my add record procedu

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdNewRecord_Click:
Exit Sub

Err_cmdNewRecord_Click:
MsgBox Err.description
Resume Exit_cmdNewRecord_Click

End Sub

"Klatuu" wrote:

Could you not just use the Default Values property of the contorls to
accomplish what you are trying to do in set_up_form?
request_date
submitter

The command button text changes, of course have to be dealt with
programmatically.

When are you runnin set_up_form?

It may be worth a shot to call it from the form's current event.

Have patience, Sandy. We will get throught it.

"SandyR" wrote:

Then I get "you entered an expression with no value" when I try to initialize
the fields in my set up procedure.

"Klatuu" wrote:

Does this message come up even if you nave no code to create a new record?

"SandyR" wrote:

I would be happy to do that, but before the screen comes up i get the message
"you can't go to the specified record" on the Docmd line.


"Klatuu" wrote:

It may have to do with the filtering imposed. I did not think about that
earlier. Also, is it possible that because the filtering did not present any
records that you are already on a new record.

I would first try setting the filter so I know it will return no records,
then try entering some data in the form.

"SandyR" wrote:

copied from my original post:

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"


"Klatuu" wrote:

No, you did not. What you tried was:
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If

The AddNew method is used for processing recordsets as in

Set rst = CurrentDb.OpenRecordset("sometable")
rst.AddNew.

The GotoRecord is used to add a record to a form's underlying recordset.



"SandyR" wrote:

As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

"Klatuu" wrote:

Forgot that part, but simple enough

After you set the filter on

If Me.Recordset.RecordCount = 0 Then
Docmd.GotoRecord , ,acNewRec
End If

"SandyR" wrote:

OK, I did that, but it doesn't solve the problem of needing to create a new
record if none match the filter. Any suggestions?

"Klatuu" wrote:

exactly.
In the load event it would something like:

If user = "fred" Then
Me.Filter = "[SomeField] = 'xyz'"
Else
Me.Filter = "[SomeField] = 'abc'"
End if
Me.FilterOn = True

"SandyR" wrote:

I am not clear on how to create a filter that is different depending on the
user. Are you saying that instead of changing the recordsource property, I
should change the filter property in the on load procedure, then set the
filter on property?

"Klatuu" wrote:

I haven't tested this idea, but i think it will simplify your life. Rather
than try to establish a recordset when you load, set the form's Filter
property and Filter On property using the same logic.

"SandyR" wrote:

I have a form with the recordsource property left blank. The onOpen event
procedure runs a query depending on who the user is. If the query finds no
records, I want to create a new record. I initialize several fields in new
records in a procedure called by the onCurrent event procedure. I have
tried several ways to do this. When I do it the way shown below using the
addnew method, It blows up in the initialization procedure with the error
message 2427 "You entered an expression that has now value".

I also tried to use the Docmd.gotoRecord , , acNewRec instead of the addnew
method. In this case, the error message is on the Docmd line and says "can't
go to the specified record"

Can anyone tell me what I am doing wrong?

My onOpen procedure looks like this:

Private Sub Form_Open(Cancel As Integer)
' change record source filter depending on who the user is
If CurrentUser = "jdouglas" Or CurrentUser = "dcostello" Then
RecordSource = "select * from [purchase requisition] where status in
('S','P','A')"
Else
RecordSource = "select * from [purchase requisition] where submitter =
currentuser"
End If
'if no records meet the criterion, then create a new one
If Me.Recordset.RecordCount = 0 Then
Me.DataEntry = True
Me.Recordset.AddNew
Requery
End If
End Sub

My initialization procedu

Private Sub set_up_form()
If IsNull(request_date) Or request_date = " " Then request_date = Now
If IsNull(submitter) Then submitter = CurrentUser
If Me.status = " " Or IsNull(Me.status) Then
cmdStatusChange.Caption = "Click here to submit"
ElseIf Me.status = "S" Then
cmdStatusChange.Caption = "waiting for Purchasing Approval"
ElseIf Me.status = "P" Then
cmdStatusChange.Caption = "waiting for Finance Director
Approval"
ElseIf Me.status = "A" Then
cmdStatusChange.Caption = "Approved"
Else: cmdStatusChange.Caption = "error"
End If

End Sub