Archiving Purchase Req. Numbers

J

Jan Il

Hi all - Access 2002 XP and 2000, Windows ME

I apologize if this is not the correct ng to post this, as I am not sure if
this is a form or query issue.

I have a new db that I have created to track Purchase Requisition numbers,
and their subsequent Purchase Order numbers.
I have a table that has fields to record all the information from the
original PR and one to enter the PO number once accounting has processed the
PR and assigned it a PO. I have a data entry form to enter all the
information for each original PR, based upon the AcctCode Order Table.
The data entry form is based on this table that has the following fields;

OrderID - PK
PRNo
PRDate
PONo
AccrCode
Equip
Dept
Vendor
Description
Amount
PRInactiveDate

In the review form, I only want to show the PR numbers for the PR's that
have not yet been assigned PO numbers, but, also want to archive the
original PR numbers for reference, but, once they have been assigned a PO
number, I don't want then PR number to show on the review form, only the
assigned PO number. The PRInactiveDate field on the entry form is to record
the date that the new PO number and the PR number was negated. The review
form is based on the AcctCode Order Table, minus the PRInactiveDate field.
The
SQL for that query is as follows;

SELECT DISTINCT [AcctCode Order Table].PRNo, [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
FROM [AcctCode Order Table];

I also have a query for the PRInactiveDate, which has the following SQL;

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

The reason that I need to track the PR numbers, even when they have become
non-existent in the data entry program after being assigned a PO number
(???), is because the new 300,000,000.00 'State of the Art' data entry
system is not exactly...ahmm... uhmm...shall I say...accurate?? It sorta
tends to 'play' with data here and there, mainly, information on PO's. I
print out a hard copy of every PR for our dept. when they are created for
file. By archiving the original PR numbers I can then refer back to the
subsequent PO number, and original PR. I have been doing this manually, but,
as the problem compounds, really need to be able to provide a record of this
information for immediate record.

Is there a way that I can archive this PR information once the PO has been
assigned for such reference? The program just auto-deletes the PR
information once a PO number has been assigned to the PR, which makes little
sense to me, but, well...I didn't write it. I just do Access, not Mincom.
But, trying to cover all bases as best I can. I have used the Inactive
thingie before that was suggested for another application for a specific
purpose,
and it worked perfectly, but, I'm not sure that it would work as efficiently
for
this purpose. I am really sorry that this is somewhat long in the tooth,
but,
I have tried to provide as much information and explanation regarding the
issue as I can think of up front. Right now, the Access database I over the
past year, and still have in place and keep up to date, is the only accurate
recording system our dept. now has to rely on at this point. Hmm.... my..
imagine that.. a mere 400.00 something off-the-shelf program, and, me
just a novice....???

I would truly appreciate any suggestions as to what method might be best to
use to provide the PR backup information I need. It really is very
important.

Very best regards,
Jan :)
 
G

Glen Appleton

Hi Jan,

OK, if I'm reading your post correctly, this is the simplified logic you're
after:

If no PO# assigned, show PR#, else show PO#

If this is the case, the best method I can think of would be to have both
fields on the form and hide/show the appropriate field by adding some code
to the On Current (Form_Current) event of the form:

--- Begin Code ---
Private Sub Form_Current()

Me.PONo.Visible = Not IsNull(Me.PONo)
Me.PRNo.Visible = Not Me.PONo.Visible

End Sub
--- End Code ---

This will toggle the visibility of the PONo and PRNo field based on the
existence of the PONo value.

Hope this helps,
- Glen

Jan Il said:
Hi all - Access 2002 XP and 2000, Windows ME

I apologize if this is not the correct ng to post this, as I am not sure if
this is a form or query issue.

I have a new db that I have created to track Purchase Requisition numbers,
and their subsequent Purchase Order numbers.
I have a table that has fields to record all the information from the
original PR and one to enter the PO number once accounting has processed the
PR and assigned it a PO. I have a data entry form to enter all the
information for each original PR, based upon the AcctCode Order Table.
The data entry form is based on this table that has the following fields;

OrderID - PK
PRNo
PRDate
PONo
AccrCode
Equip
Dept
Vendor
Description
Amount
PRInactiveDate

In the review form, I only want to show the PR numbers for the PR's that
have not yet been assigned PO numbers, but, also want to archive the
original PR numbers for reference, but, once they have been assigned a PO
number, I don't want then PR number to show on the review form, only the
assigned PO number. The PRInactiveDate field on the entry form is to record
the date that the new PO number and the PR number was negated. The review
form is based on the AcctCode Order Table, minus the PRInactiveDate field.
The
SQL for that query is as follows;

SELECT DISTINCT [AcctCode Order Table].PRNo, [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
FROM [AcctCode Order Table];

I also have a query for the PRInactiveDate, which has the following SQL;

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

The reason that I need to track the PR numbers, even when they have become
non-existent in the data entry program after being assigned a PO number
(???), is because the new 300,000,000.00 'State of the Art' data entry
system is not exactly...ahmm... uhmm...shall I say...accurate?? It sorta
tends to 'play' with data here and there, mainly, information on PO's. I
print out a hard copy of every PR for our dept. when they are created for
file. By archiving the original PR numbers I can then refer back to the
subsequent PO number, and original PR. I have been doing this manually, but,
as the problem compounds, really need to be able to provide a record of this
information for immediate record.

Is there a way that I can archive this PR information once the PO has been
assigned for such reference? The program just auto-deletes the PR
information once a PO number has been assigned to the PR, which makes little
sense to me, but, well...I didn't write it. I just do Access, not Mincom.
But, trying to cover all bases as best I can. I have used the Inactive
thingie before that was suggested for another application for a specific
purpose,
and it worked perfectly, but, I'm not sure that it would work as efficiently
for
this purpose. I am really sorry that this is somewhat long in the tooth,
but,
I have tried to provide as much information and explanation regarding the
issue as I can think of up front. Right now, the Access database I over the
past year, and still have in place and keep up to date, is the only accurate
recording system our dept. now has to rely on at this point. Hmm.... my..
imagine that.. a mere 400.00 something off-the-shelf program, and, me
just a novice....???

I would truly appreciate any suggestions as to what method might be best to
use to provide the PR backup information I need. It really is very
important.

Very best regards,
Jan :)
Click to expand...
 
J

Jan Il

Hi Glen :)

Glen Appleton said:
Hi Jan,

OK, if I'm reading your post correctly, this is the simplified logic you're
after:
Click to expand...

Yes, this is exactly what I wish to do. :)
If no PO# assigned, show PR#, else show PO#

If this is the case, the best method I can think of would be to have both
fields on the form and hide/show the appropriate field by adding some code
to the On Current (Form_Current) event of the form:
Click to expand...
--- Begin Code ---
Private Sub Form_Current()

Me.PONo.Visible = Not IsNull(Me.PONo)
Me.PRNo.Visible = Not Me.PONo.Visible

End Sub
--- End Code ---

This will toggle the visibility of the PONo and PRNo field based on the
existence of the PONo value.
Click to expand...

I have applied your code to the Form Current event, and when I did a
compile, got a yellow highlight of the word 'Visible' in the Me.PONo.Visible
= Not IsNull(Me.PONo) line of the code. I didn't get any error message,
just the yellow highlight of the word 'Visible' in that line.

Thank you very much for your time to assist with this problem, I really do
appreciate it.

Very best regards,
Jan :)





Hope this helps,
- Glen

Jan Il said:
Hi all - Access 2002 XP and 2000, Windows ME

I apologize if this is not the correct ng to post this, as I am not sure if
this is a form or query issue.

I have a new db that I have created to track Purchase Requisition numbers,
and their subsequent Purchase Order numbers.
I have a table that has fields to record all the information from the
original PR and one to enter the PO number once accounting has processed the
PR and assigned it a PO. I have a data entry form to enter all the
information for each original PR, based upon the AcctCode Order Table.
The data entry form is based on this table that has the following fields;

OrderID - PK
PRNo
PRDate
PONo
AccrCode
Equip
Dept
Vendor
Description
Amount
PRInactiveDate

In the review form, I only want to show the PR numbers for the PR's that
have not yet been assigned PO numbers, but, also want to archive the
original PR numbers for reference, but, once they have been assigned a PO
number, I don't want then PR number to show on the review form, only the
assigned PO number. The PRInactiveDate field on the entry form is to record
the date that the new PO number and the PR number was negated. The review
form is based on the AcctCode Order Table, minus the PRInactiveDate field.
The
SQL for that query is as follows;

SELECT DISTINCT [AcctCode Order Table].PRNo, [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
FROM [AcctCode Order Table];

I also have a query for the PRInactiveDate, which has the following SQL;

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

The reason that I need to track the PR numbers, even when they have become
non-existent in the data entry program after being assigned a PO number
(???), is because the new 300,000,000.00 'State of the Art' data entry
system is not exactly...ahmm... uhmm...shall I say...accurate?? It sorta
tends to 'play' with data here and there, mainly, information on PO's. I
print out a hard copy of every PR for our dept. when they are created for
file. By archiving the original PR numbers I can then refer back to the
subsequent PO number, and original PR. I have been doing this manually, but,
as the problem compounds, really need to be able to provide a record of this
information for immediate record.

Is there a way that I can archive this PR information once the PO has been
assigned for such reference? The program just auto-deletes the PR
information once a PO number has been assigned to the PR, which makes little
sense to me, but, well...I didn't write it. I just do Access, not Mincom.
But, trying to cover all bases as best I can. I have used the Inactive
thingie before that was suggested for another application for a specific
purpose,
and it worked perfectly, but, I'm not sure that it would work as efficiently
for
this purpose. I am really sorry that this is somewhat long in the tooth,
but,
I have tried to provide as much information and explanation regarding the
issue as I can think of up front. Right now, the Access database I over the
past year, and still have in place and keep up to date, is the only accurate
recording system our dept. now has to rely on at this point. Hmm.... my..
imagine that.. a mere 400.00 something off-the-shelf program, and, me
just a novice....???

I would truly appreciate any suggestions as to what method might be best to
use to provide the PR backup information I need. It really is very
important.

Very best regards,
Jan :)
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

How can I hide the PR number in a form? 1
.Archiving PR numbers - 3rd post 7
Before Update not checking combobox! 6
Retiring one field when another is filled 1
OpenForm Problem - populating Opened form 4
Combo box choice checks a box in another database table via dlooku 3
Storing Calculated Controls 1
Purchase Order form questions 6

Top