if recordset is empty, add new record

G

Guest

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 procedure:

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
 
G

Guest

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.
 
G

Guest

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 said:
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 said:
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 procedure:

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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Forgot that part, but simple enough

After you set the filter on

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

SandyR said:
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 said:
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 said:
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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Klatuu said:
Forgot that part, but simple enough

After you set the filter on

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

SandyR said:
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 said:
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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

Klatuu said:
Forgot that part, but simple enough

After you set the filter on

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

SandyR said:
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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

Klatuu said:
Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
As I mentioned in my original post, when I tried that, I got the error
message "can't go to specified record"

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

SandyR said:
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 said:
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 said:
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"


:

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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

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

SandyR said:
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 said:
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.

:

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"


:

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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

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

SandyR said:
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 said:
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.

:

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"


:

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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Then I get "you entered an expression with no value" when I try to initialize
the fields in my set up procedure.

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

SandyR said:
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.


:

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.

:

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"


:

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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 procedure:

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 said:
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 said:
Then I get "you entered an expression with no value" when I try to initialize
the fields in my set up procedure.

Klatuu said:
Does this message come up even if you nave no code to create a 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.


:

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.

:

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"


:

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.



:

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

:

Forgot that part, but simple enough

After you set the filter on

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

:

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?

:

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

:

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?

:

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.

:

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 procedure:

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
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

passing variable between sub procedure 1
if recordset is empty, add new record (repost) 11
Code help with IsNot( ) operator 3
If...then...else - what's wrong? 9
Recordset question 1
pl hlep me to correct this code 1
Continuous Form: Change the color of one row 1
If Statement Problem 6

Top