Alternate AutoNumber

[Asif]

I have two forms "New Job" and "Cancel Job" linked to these is a field
called "Status" when the "New Jobs" form opens the "Status" value
should be 1 but when "Cancel Job" form is opened for that record then
the "Status" value should change to 2. I'm thinking of running a macro
but don't know where to start. Also "Status" is bound to an Auto
Number should I change that to a normal number setting or can I
alternate between two Auto Numbers?

Thanks

 

[Scott McDaniel]

I have two forms "New Job" and "Cancel Job" linked to these is a field
called "Status" when the "New Jobs" form opens the "Status" value
should be 1 but when "Cancel Job" form is opened for that record then
the "Status" value should change to 2. I'm thinking of running a macro
but don't know where to start. Also "Status" is bound to an Auto
Number should I change that to a normal number setting or can I
alternate between two Auto Numbers?

Not sure what you mean by

<Also "Status" is bound to an Auto
Number should I change that to a normal number setting or can I
alternate between two Auto Numbers?>

If the Status field in your Job table is related to another table (one which would perhaps hold various Job Status, such
a Pending, Hold, Complete etc), and assuming that the Job.Status column is storing the value of the PrimaryKey from the
JobStatus table, then this would be correct. If the relationship is something different, please let us know.

Are you storing all Job info in a single table? I'm assuming that your are and, if so, then you can just run code in the
various form's Open events to update the Status value:

For the New Job form:

Sub Form_Open()
Me.Status = 1
End Sub

For the Cancel Job form:

Sub Form_Open
Me.Status = 2
End Sub

Of course, this assumes that you're opening the forms to the correct record ... if not, then this code would set the
value of the first record in the form's Recordset to 1 ...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Asif]

Not sure what you mean by

<Also "Status" is bound to an Auto
Number should I change that to a normal number setting or can I
alternate between two Auto Numbers?>

If the Status field in your Job table is related to another table (one which would perhaps hold various Job Status, such
a Pending, Hold, Complete etc), and assuming that the Job.Status column is storing the value of the PrimaryKey from the
JobStatus table, then this would be correct. If the relationship is something different, please let us know.

Are you storing all Job info in a single table? I'm assuming that your are and, if so, then you can just run code in the
various form's Open events to update the Status value:

For the New Job form:

Sub Form_Open()
Me.Status = 1
End Sub

For the Cancel Job form:

Sub Form_Open
Me.Status = 2
End Sub

Of course, this assumes that you're opening the forms to the correct record ... if not, then this code would set the
value of the first record in the form's Recordset to 1 ...

Scott McDaniel
scott@takemeout_infotrakker.comwww.infotrakker.com

Scott, not sure what you mean by "storing job info in single table"
however I've tried your coding but I'm getting an error message

"You can't assign a value to this object"

I have a table (tbl_Status) with two fields "StatusID" and
"StatusDescription". I also have a table (tbl_Process), this table has
a field "StatusID" which as you said rightly is storing the primary
key of tbl_Status. When I cancel a particular entry I want the
StatusID in tbl_Process to change from 1 to 2. Hope I'm making my self
clear if not let me know and I'll try again.

Thanks

 

[Scott McDaniel]

Scott, not sure what you mean by "storing job info in single table"
however I've tried your coding but I'm getting an error message

"You can't assign a value to this object"

I have a table (tbl_Status) with two fields "StatusID" and
"StatusDescription". I also have a table (tbl_Process), this table has
a field "StatusID" which as you said rightly is storing the primary
key of tbl_Status. When I cancel a particular entry I want the
StatusID in tbl_Process to change from 1 to 2. Hope I'm making my self
clear if not let me know and I'll try again.

So your form is based on tbl_Process? If so, then you should be able to change the value of tbl_Process.StatusID:

'/use this on your Cancel form:
Sub Form_Open()
Me.StatusID = 2
End Sub

Substitute 1 for 2 on your "New" form.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com