.Archiving PR numbers - 3rd post

[Jan Il]

Hi all - Access 2000 - WinME

I am sorry, but, I am still in need of help in how to set up a way to
archive Purchase Request numbers that have been superceded/replaced by a
Purchase Order numbers.

My Table is: AcctCode Order Table..which has the following;

PRID - Autonumber - PK
PRNo - Text
OpenWONo - Text
PRDate - Date/Time
PONo - Text
AcctCode - Text
Equip - Text
Dept - Text
Vendor - Text
Description - Text
Amount - Currency - Std.
PRInactiveDate - Date/Time

This is the SQL for the Purchase Record Table Query

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].OpenWONo, [AcctCode Order Table].PONo, [AcctCode Order Table].PRDate,
[AcctCode Order Table].AcctCode, [AcctCode Order Table].Equip, [AcctCode
Order Table].Dept, [AcctCode Order Table].Vendor, [AcctCode Order
Table].Description, [AcctCode Order Table].Amount, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table];

Here is the current qryPRInactiveDate SQL:

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table]
ORDER BY [AcctCode Order Table].PRNo DESC;


Sequence of events - First a Purchase Requisition is opened. Then it is sent
to the designated personnel for approval. Once approved, it is then sent to
Purchasing, where a Purchase Order is generated. Once the Purchase Order is
generated and closed, the Purchase Requisition number ceases to exist. It is
invalidated as far as the system is concerned, and will never be used again.

But, I am not happy with the fact that once the PO is created, the original
PR number is no longer available for reference. Perhaps it is just me, but,
if for example, a Vendor is originally given the PR number to apply to all
invoices and shipping documents at the onset of the order, and for some
reason, fails to reference the replacement PO number and continue to use the
original PR number instead, which, no longer exists in the system. How then
can I associate the PR number to the correct replacement PO in the system?
You can't call up what essentially does not exist..right??

Therefore, I must create a way to archive the retired/replaced original PR
numbers so that they can be referenced if necessary in a specific form, but,
when the Vendor or order information is called up in a form, the PR number
will not be displayed.

Is _any_ of this making any sense at all?? I am trying to explain as best I
can, and I truly apologize if I'm just not explaining things very clearly.
But, it really is very important that I be able to track the original PR
information. Please let me know how I might explain more clearly in order
to provide sufficient information for assistance with this problem.

Very best regards,
Jan

 

[Michel Walsh]

Hi,


Do not delete the PR. It just become YOUR own number, without meaning for
the administration, but YOU can still see it, if required. A simple "if the
PO exists, displays it, otherwise display the PR" can be use for forms, as
example:

Nz( PO, PR)


and ... just do not delete the PR in the table!


I assume you have a field PO and another field, PR, in the table.


It is probably too simple, I probably miss something, but the point is that
the design of the TABLES is YOURS, even if the presentation of the data of
FORMS may not be totally yours! Access allows easily that "job
responsibility split", use it at your advantage. Design yours tables to make
your work easy.



Hoping it may help,
Vanderghast, Access MVP

Hi all - Access 2000 - WinME

I am sorry, but, I am still in need of help in how to set up a way to
archive Purchase Request numbers that have been superceded/replaced by a
Purchase Order numbers.

My Table is: AcctCode Order Table..which has the following;

PRID - Autonumber - PK
PRNo - Text
OpenWONo - Text
PRDate - Date/Time
PONo - Text
AcctCode - Text
Equip - Text
Dept - Text
Vendor - Text
Description - Text
Amount - Currency - Std.
PRInactiveDate - Date/Time

This is the SQL for the Purchase Record Table Query

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].OpenWONo, [AcctCode Order Table].PONo, [AcctCode Order Table].PRDate,
[AcctCode Order Table].AcctCode, [AcctCode Order Table].Equip, [AcctCode
Order Table].Dept, [AcctCode Order Table].Vendor, [AcctCode Order
Table].Description, [AcctCode Order Table].Amount, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table];

Here is the current qryPRInactiveDate SQL:

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table]
ORDER BY [AcctCode Order Table].PRNo DESC;


Sequence of events - First a Purchase Requisition is opened. Then it is sent
to the designated personnel for approval. Once approved, it is then sent to
Purchasing, where a Purchase Order is generated. Once the Purchase Order is
generated and closed, the Purchase Requisition number ceases to exist. It is
invalidated as far as the system is concerned, and will never be used again.

But, I am not happy with the fact that once the PO is created, the original
PR number is no longer available for reference. Perhaps it is just me, but,
if for example, a Vendor is originally given the PR number to apply to all
invoices and shipping documents at the onset of the order, and for some
reason, fails to reference the replacement PO number and continue to use the
original PR number instead, which, no longer exists in the system. How then
can I associate the PR number to the correct replacement PO in the system?
You can't call up what essentially does not exist..right??

Therefore, I must create a way to archive the retired/replaced original PR
numbers so that they can be referenced if necessary in a specific form, but,
when the Vendor or order information is called up in a form, the PR number
will not be displayed.

Is _any_ of this making any sense at all?? I am trying to explain as best I
can, and I truly apologize if I'm just not explaining things very clearly.
But, it really is very important that I be able to track the original PR
information. Please let me know how I might explain more clearly in order
to provide sufficient information for assistance with this problem.

Very best regards,
Jan

 

[Jan Il]

Hi Michel,

Hi,


Do not delete the PR. It just become YOUR own number, without meaning for
the administration, but YOU can still see it, if required. A simple "if the
PO exists, displays it, otherwise display the PR" can be use for forms, as
example:

Nz( PO, PR)


and ... just do not delete the PR in the table!

No, I do not delete the Purchase Requisition number from my db. The db I
have created for our department is separate from the mess they now have
installed on the server for all three companies in our transit operation.
When I say, I mean total disaster. Half the stuff does not work, the other
half is useless. Thus, for us to be able to continue tracking our expenses
needs for our department, I had to create a backup Access db for us to work
from.
The PR numbers are being deleted in the admin program, I keep it in our db,
and I have a field PRInactiveDate, in which I enter the date that the PR
number is replaced by the PO number. That way, we will know when the PR
number went out of existence in the admin program.

I assume you have a field PO and another field, PR, in the table.

Yes, there is a field in the table for each one, PRNo and PONo. First the PR
is generated in the admin program, and the PR number is recorded in the
PRNo field in the table of our dept. db. When the Purchasing Agent creates
the PO and sends a copy to us, the PO number is then recorded in the PONo
field in our dept. db. Also, the date the PO number is created is recorded
in the PRInactiveDate field in the table, to indicate when the PR number no
longer existed.

The purpose of this form is so that Supervisor's and dept. management can
have access to the information regarding which vendors have been issued open
field PO's, open field Work Orders, and the Purchase Req.'s. Not all vendors
are assigned open field PO's, as we may only order from them a few times a
year. The open field PO's and Work Orders are not closed until the end of
the fiscal year, and purchases are charged against the open PO and WO
amounts. Thus, we will now know the status of the purchase process is at any
point, there's no provision for collectively reviewing this type of
information in the new Admin. State-Of-The-Art program.

It is probably too simple, I probably miss something, but the point is that
the design of the TABLES is YOURS, even if the presentation of the data of
FORMS may not be totally yours! Access allows easily that "job
responsibility split", use it at your advantage. Design yours tables to make
your work easy.

I created a similar set up for another type of project once before, however,
the process just does not want to work in this case. I will try your
suggestion and see how it will work.

Thank you very much for you time to reply and assist with this issue, I
truly do appreciate it.Best regards,
Jan

Hi all - Access 2000 - WinME

I am sorry, but, I am still in need of help in how to set up a way to
archive Purchase Request numbers that have been superceded/replaced by a
Purchase Order numbers.

My Table is: AcctCode Order Table..which has the following;

PRID - Autonumber - PK
PRNo - Text
OpenWONo - Text
PRDate - Date/Time
PONo - Text
AcctCode - Text
Equip - Text
Dept - Text
Vendor - Text
Description - Text
Amount - Currency - Std.
PRInactiveDate - Date/Time

This is the SQL for the Purchase Record Table Query

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].OpenWONo, [AcctCode Order Table].PONo, [AcctCode Order Table].PRDate,
[AcctCode Order Table].AcctCode, [AcctCode Order Table].Equip, [AcctCode
Order Table].Dept, [AcctCode Order Table].Vendor, [AcctCode Order
Table].Description, [AcctCode Order Table].Amount, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table];

Here is the current qryPRInactiveDate SQL:

SELECT DISTINCT [AcctCode Order Table].PRNo, [AcctCode Order
Table].PRInactiveDate
FROM [AcctCode Order Table]
ORDER BY [AcctCode Order Table].PRNo DESC;


Sequence of events - First a Purchase Requisition is opened. Then it is sent
to the designated personnel for approval. Once approved, it is then sent to
Purchasing, where a Purchase Order is generated. Once the Purchase Order is
generated and closed, the Purchase Requisition number ceases to exist.

It

is
invalidated as far as the system is concerned, and will never be used again.

But, I am not happy with the fact that once the PO is created, the original
PR number is no longer available for reference. Perhaps it is just me, but,
if for example, a Vendor is originally given the PR number to apply to all
invoices and shipping documents at the onset of the order, and for some
reason, fails to reference the replacement PO number and continue to use the
original PR number instead, which, no longer exists in the system. How then
can I associate the PR number to the correct replacement PO in the system?
You can't call up what essentially does not exist..right??

Therefore, I must create a way to archive the retired/replaced original PR
numbers so that they can be referenced if necessary in a specific form, but,
when the Vendor or order information is called up in a form, the PR number
will not be displayed.

Is _any_ of this making any sense at all?? I am trying to explain as

best

I
can, and I truly apologize if I'm just not explaining things very clearly.
But, it really is very important that I be able to track the original PR
information. Please let me know how I might explain more clearly in order
to provide sufficient information for assistance with this problem.

Very best regards,
Jan

 

[Jan Il]

Hi Michel,

Hi,


Do not delete the PR. It just become YOUR own number, without meaning for
the administration, but YOU can still see it, if required. A simple "if the
PO exists, displays it, otherwise display the PR" can be use for forms, as
example:

Nz( PO, PR)

I did try utilizing the code you provided, but, thus far it is not working.
It is possible that I am not entering it properly, or in the right place. I
am still getting both the PR and PO numbers displayed in the reference form
when accordingly I should only be seeing the PO if one has been assigned to
replace the PR number. At least, as I understood how the code was meant to
work.

Thank you so much again for your time to help, and I truly apologize that I
am unable to obtain the results as you intended.

Best regards,
Jan

 

[Michel Walsh]

Hi,

If you do not want the user to modify it, you specify the ControlSource
property:

= Nz( Po, Pr)


which uses Po, unless it is NULL, then it uses Pr.


If you want the option to be able to change the value, you can do the
following, in the onCurrentEvent:


Me.ControlNameHere = Nz( Po, Pr )



Hoping it may help,
Vanderghast, Access MVP

 

[Jan Il]

Hi Michel,

Hi,

If you do not want the user to modify it, you specify the ControlSource
property:

= Nz( Po, Pr)


which uses Po, unless it is NULL, then it uses Pr.


If you want the option to be able to change the value, you can do the
following, in the onCurrentEvent:


Me.ControlNameHere = Nz( Po, Pr )

I have entered the code here as you suggested;

****************Start Code******************************

Private Sub Form_Current()

Me.PONo = Nz(PONo, PRNo)

End Sub

*******************End Code*****************************

The control and other information is as the controls in the forms. However,
I am getting an error message that says:

Microsoft Visual Basic

Run-Time error '-2147352567 (800200009)'
This recordset is not updateable

Thus, it is still displaying both numbers.

Thank you very much for all you help,

Jan

 

[Michel Walsh]

Hi,

I strongly suggest you avoid having a control name like a field name, but
not necessary bind (associated) to the field name. In other words, try to
have Me.PoNo, Me.PrNo the controls associated to the fields PoNo and PrNo
respectively (even if they are invisible) and have a third control,
Me.Reference that you associate to no fields (you leave it unbound, in the
design) but in the onCurrent event, you do:



Me.Reference = Nz( Me.PoNo, Me.PrNo )



Hoping it may help,
Vanderghast, Access MVP

 

[Jan Il]

Hi Michel,

Hi,

I strongly suggest you avoid having a control name like a field name, but
not necessary bind (associated) to the field name. In other words, try to
have Me.PoNo, Me.PrNo the controls associated to the fields PoNo and PrNo
respectively (even if they are invisible) and have a third control,
Me.Reference that you associate to no fields (you leave it unbound, in the
design) but in the onCurrent event, you do:



Me.Reference = Nz( Me.PoNo, Me.PrNo )

You're right, and I forgot to change their names. Got 'em
fixed. So much to do, so many 'lil drafts between the
ears...<sigh>

Now everyone is a happy camper, it's all working as is
should.

Thank you so much for your time and patience. I truly
appreciate it.

Best regards,
Jan