Lookup Previous Record for a value

[rovman]

I have a data base I am working on and it I have one box that I want to have look up the value in the same box in the previous record once I click on the Comand Button. I have never had to do this other than the Find Previous Comand Button in the past.

Lloyd

[orange]

see
http://allenbrowne.com/subquery-01.html
get the value from another record

http://allenbrowne.com/ser-24.html

[rovman]

Thank you for the link but did not really help me much as I need the function I am looking for to happen in the database because I have several Querries

Lloyd

see
http://allenbrowne.com/subquery-01.html
get the value from another record

http://allenbrowne.com/ser-24.html

[orange]

Can you post a copy of your mdb -- remove confidential/personal info?
Can you provide a jpg of your form set up and a description of what exactly you want to happen?

[rovman]

I have attached a word document with a JPG in the document show the User Interface page. In this database I have 3 boxes associated with”PROCEDURE NUMBER” they perform the following functions;

1. The first box is automatically updated when the procedure Identifier is entered into the “PROCEDURE IDENTIFIER” text box.
2. The second box is automatically updated when the Project Number is entered into the “PROJECT NUMBER” text box.
3. The third box needs to perform an automatic function. After the above two steps have been completed the user will then click on the “PROCEDURE NUMBER” tab and the code for this box will perform the following steps :
   
   1. Look up the last record for the “PROCEDURE IDENTIFIER”.
   2. Once last record for that “PROCEDURE IDENTIFIER” is found then the following function is performed;

If Me.PROCEDURE_NUMBER > 300 Then Me.PROCEDURE_NUMBER = 300
If Me.PROCEDURE_NUMBER < 299 Then Me.PROCEDURE_NUMBER = Me.PROCEDURE_NUMBER + 1
In this database I have 3 boxes associated with”PROCEDURE NUMBER” they perform the following functions;

[rpeare]

Fuzzy screen shots make my eyes bleed! Orange was asking for you to make a copy of your database, remove any confidential material from the copy, then compact/repair, zip it up and upload it to the site. Screen shots really are not helpful for determining your data structure, what your form is doing, etc.

[rovman]

Sorry about that here it is. As you can see for the most part it is a very simple database other than I havenever had a field look backwards as part of it process.

Lloyd

ENGINEERING PROCEDURES.zip

[orange]

I do not understand your database. There is no sample data. There is only 1 table, but you seem to be dealing with some sort of hierarchy -- Project and Procedure and Client. It appears, and this is just a guess, that you need to normalize your table into related tables based on your Business rules.

Can you tell us (describe to us) exactly WHAT your table represents?

[rpeare]

I agree with orange this is a poorly constructed database.

Really what you want is an individual table for each of the following (at the very least)
tblProcedures (fields would be something like Proc_ID (autonumber PK), Proc_Code, Proc_Description)
tblProjects (fields would be something like Proj_ID (autonumber PK), Proj_StartDate, Proj_EndDate, Proj_Supervisor, if you're tracking who's responsible for a project the Proj_supervisor would be a foreign key to an employee table)
tblProj_Procedures (this would be a table that records all of the procedures performed for a specific project so you'd have something like Proj_Proc_ID (autonumber PK), Proj_ID, Proc_ID, <any other fields necessary to record individual processes on a single project)
tblClients (Client_ID (autonumber pk), ClientName, Client_ContactFN, Client_ContactLN, <other client related fields>)


The design you currently have is basically just a spreadsheet which is going to make your data incredibly hard to work with over time.

For instance let's say someone Types in a client name of 'Bob Smith', another person types it in as 'Robert Smith' and another types in it as 'bobby smith'

Whenever you query your results even though this may be the exact same client you will never be able to correlate them to one another through code over your entire data set.

What you're doing with your existing code is looking at ONE field, if that field is below a certain number you're changing it to a different number (basically invalidating whatever someone wrote in so why allow it as data entry to start with)

There's nothing in this database to use an as example (again as orange pointed out) either

    Find Previous Me.PROCEDURE_IDENTIFIER
    If Me.PROCEDURE_NUMBER > 300 Then Me.PROCEDURE_NUMBER = 299
    If Me.PROCEDURE_NUMBER < 299 Then Me.PROCEDURE_NUMBER = Me.PROCEDURE_NUMBER + 1

this is the code attached to the button I think you're talking about it looks like you're trying to find the last record that had this particular procedure_identifier, but what are you doing with that, are you saying if the previous procedure numbers was less than 300 you want the 'new' procedure number to be 299? And if the previous procedure numbers was less than 299 you want the 'new' procedure number to be incremented by 1? what happens in this scenario if your prevoius procedure identifier is 299 or 300 exactly, they would be completely ignored by your pesudo code.

Please think about normalizing your tables rather than trying to continue with this design.

[rovman]

Currently this is what we want it to do;

1st Entry= Needless to say the first box is the Date the new Procedure Number is Created

2nd Entry= Procedure Identifier. The person enters this information based on the Identifier listed at the very bottom of the page as show, i.e. AP, AR etc.

3rd Entry= Project Number is a mu,ner that has been assigned by our accounting department for a given project, i.e. 4000, 2827 etc.

4th Entry= Procedure Number, The first box is updated whenever the Procedure Identifier is entered into the Procedure Identifier Box. Second box is updated once the Project Number is entered into the Project Number box. Third box is where we want it to look back at the last Procedure Identifier entry for what has been entered and then add 1 for the next number, if there is no entry for this Identifier then it will start out at 300. (I understand that the first and second box is redundant data but they wanted all this side by side so that once the data was entered it would look like this "AL-4000-299" which would be the Procedure Number for the document)

5th entry= Client since we have Clients with multipul addemums to their name depending on which office they work out off this coule vary a lot we are not worried about that.

6th Entry= Created By is by whom ever enters the data for the document number and does not have anything to do with project managment.

This database has nothing to do with tracking supervisor, project managers etc as it is meant to help prevent duplicate Procedure Numbers from being created.

I have created Inventory Databases before but have never had to build a database like this where a numbering valve was taken from a previous entry.

I agree with orange this is a poorly constructed database.

Really what you want is an individual table for each of the following (at the very least)
tblProcedures (fields would be something like Proc_ID (autonumber PK), Proc_Code, Proc_Description)
tblProjects (fields would be something like Proj_ID (autonumber PK), Proj_StartDate, Proj_EndDate, Proj_Supervisor, if you're tracking who's responsible for a project the Proj_supervisor would be a foreign key to an employee table)
tblProj_Procedures (this would be a table that records all of the procedures performed for a specific project so you'd have something like Proj_Proc_ID (autonumber PK), Proj_ID, Proc_ID, <any other fields necessary to record individual processes on a single project)
tblClients (Client_ID (autonumber pk), ClientName, Client_ContactFN, Client_ContactLN, <other client related fields>)


The design you currently have is basically just a spreadsheet which is going to make your data incredibly hard to work with over time.

For instance let's say someone Types in a client name of 'Bob Smith', another person types it in as 'Robert Smith' and another types in it as 'bobby smith'

Whenever you query your results even though this may be the exact same client you will never be able to correlate them to one another through code over your entire data set.

What you're doing with your existing code is looking at ONE field, if that field is below a certain number you're changing it to a different number (basically invalidating whatever someone wrote in so why allow it as data entry to start with)

There's nothing in this database to use an as example (again as orange pointed out) either

    Find Previous Me.PROCEDURE_IDENTIFIER
    If Me.PROCEDURE_NUMBER > 300 Then Me.PROCEDURE_NUMBER = 299
    If Me.PROCEDURE_NUMBER < 299 Then Me.PROCEDURE_NUMBER = Me.PROCEDURE_NUMBER + 1

this is the code attached to the button I think you're talking about it looks like you're trying to find the last record that had this particular procedure_identifier, but what are you doing with that, are you saying if the previous procedure numbers was less than 300 you want the 'new' procedure number to be 299? And if the previous procedure numbers was less than 299 you want the 'new' procedure number to be incremented by 1? what happens in this scenario if your prevoius procedure identifier is 299 or 300 exactly, they would be completely ignored by your pesudo code.

Please think about normalizing your tables rather than trying to continue with this design.

[rovman]

One thing that was decided at work today was we would not perform searches on who created the new Procedure Number.

[rpeare]

I'm not sure where you are on this project but with your description I think it's even more important that you put some effort into normalizing your data. You are thinking of your data as a spreadsheet where you will be carrying a lot of data that is duplicate information in the same table. What you should be doing is looking for ways to economize the space/data you use. The easiest to explain, but not the only one, for your database is to have a client table with an autonumber primary key.

When you have a client table with all the client specific information in it and you have a primary key (autonumber is just the easiest to work with) all you have to store in your 'main' table is the customer number, which can be obtained by putting a combo box on your main form with the primary key as the bound column. Instead of repeating that customer information time after time after time you have *1* place where you maintain any contacts, phone numbers, company name, etc. If you construct things this way you when you go to query your results you can produce a homogeneous set of data rather than risk the fickle fingers of fate that take people when they type things in rather than choose from a list.

[orange]

I agree that normalization and getting a data model designed to match your needs is a key first step.
See the first few topics here Normalization, Entity Relationship Diagramming and the 12 steps.
http://www.rogersaccesslibrary.com/forum/topic238.html