stop new record from being created if previous record not closed

[Guest]

Using Access 2003.

I would like to stop a new record in a subform from being created if the
[Status] field on the current subform record is not "Closed". The [Status]
field is a Yes/No field. I tried the MS support article on referring to a
field in a previous record, but cannot get the code to work. What I thought
would work was this:

User creates new record in subform.

The "On Current" event checks to see if previous record status is "Closed"

If status is not "Closed", a message box pops up, prompting user to close
previous record, and the focus returns to previous record, with no data being
entered in new record.

I copied the code from the MS article, to create the PrevRecValue Function,
and created a new field in the subform:
TextBox1: ControlSource = PrevRecValue
([Form],"PriorityID",[PriorityID],"Status")

On every record, TextBox1 only returns "0", regardless of whether the
"Status" value is 0 or 1.

I am not sure if the fact that this is a subform that's the problem, or if I
was supposed to have amended the code from the MS article (I just copied and
pasted it into VBA). I am weak in VBA so any thoughts on how to accomplish my
goal would be much appreciated.

 

[George Nicholson]

I'm guessing you simply have a timing issue.

Looks like you used:
http://support.microsoft.com/kb/210504/en-us
Referring to a field in the Previous or Next record

That will work on existing records (like a report), but I don't think it
will work well when adding new records, since it relies on passing the
current primary key value & if your primary key is auto-increment, a value
hasn't been generated yet when the Current event fires.

Take a look at:
http://support.microsoft.com/default.aspx/kb/210236/EN-US/
How to fill a record with data from a previous record...

That can "auto-fill" the previous Status value on your form and then you can
have the Current event react to that value (using Me.Undo to cancel the new
record if necessary).

HTH,

 

[Guest]

Thanks George, that did it.

I'm guessing you simply have a timing issue.

Looks like you used:
http://support.microsoft.com/kb/210504/en-us
Referring to a field in the Previous or Next record

That will work on existing records (like a report), but I don't think it
will work well when adding new records, since it relies on passing the
current primary key value & if your primary key is auto-increment, a value
hasn't been generated yet when the Current event fires.

Take a look at:
http://support.microsoft.com/default.aspx/kb/210236/EN-US/
How to fill a record with data from a previous record...

That can "auto-fill" the previous Status value on your form and then you can
have the Current event react to that value (using Me.Undo to cancel the new
record if necessary).

HTH,

Using Access 2003.

I would like to stop a new record in a subform from being created if the
[Status] field on the current subform record is not "Closed". The [Status]
field is a Yes/No field. I tried the MS support article on referring to a
field in a previous record, but cannot get the code to work. What I
thought
would work was this:

User creates new record in subform.

The "On Current" event checks to see if previous record status is "Closed"

If status is not "Closed", a message box pops up, prompting user to close
previous record, and the focus returns to previous record, with no data
being
entered in new record.

I copied the code from the MS article, to create the PrevRecValue
Function,
and created a new field in the subform:
TextBox1: ControlSource = PrevRecValue
([Form],"PriorityID",[PriorityID],"Status")

On every record, TextBox1 only returns "0", regardless of whether the
"Status" value is 0 or 1.

I am not sure if the fact that this is a subform that's the problem, or if
I
was supposed to have amended the code from the MS article (I just copied
and
pasted it into VBA). I am weak in VBA so any thoughts on how to accomplish
my
goal would be much appreciated.