IF

[shepkrm]

Hello!

Once again I look to the much better coders here for help...

I am attempting to do multiple functions with a form button.

I'd like it to first check to see
If a corresponding record exists in a query.
Yes, open the form and
If there is a corresponding line in the form, show the line, If else, open the form and create a corresponding line.
No, display a message and do not open the form.

I realize this is a bit of an obtuse request, so please let me know if you have questions.

Thank you for you assistance!

-Becky

 

[GingerR]

what do you mean by "corresponding line in the form"? An actual line? or do you mean a record?

For your button code, what have you tried so far?
Have you tried dlookup?
Is what you mean is that you want to push a button on a form, and the form you are on has a recordsource and you want to see if an existing query contains the record that you are in the current form?
thinking we need some more details....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[shepkrm]

Thanks Ginger,

It's a little confusing - I have a table that contains all records that have been entered by users. There are also a series of queries that analyze if this data is ready to be submitted for approval. There is a table that contains the "Submitted for Approval" records.

What I would like to do is build a form that allows you to see the data that has been entered. If the underlying queries say that it is ready to be submitted for approval, I would like to display the submit button. This button would call a second form that displays info from "Submitted for Approval." If this is the first time it's clicked, a record would need to be created. If it is a second time (say, the button was clicked and then someone changed their mind), the existing record would need to display.

Does that help with detail? I am trying to wrap my head around what all needs to happen, so have not tried out code.

 

[GingerR]

I have a basic question, unless I am misunderstanding: are you copying a record from the original table to a second table that is for Submitted For Approval? If so, that is bad database design. What you will want to do instead is have a STATUS table, perhaps a record can have more than one status: Initiated, In Work, Sumitted For Approval, Approved, etc. So these tables might be:

Status
Field: StatusID (Autonumber)
Field: Status (text)

JobStatus
Field: JobID
Field: StatusID (number, from above)
Field: JobStatusDate (filled in when user picks a status)
Field: JobStatusUserID (ID of person picking the status)

Then make a subform within the main form, with a combo box where a user picks the current status. This way your data is "normalized", you do not have duplicate records floating around, and you can do much more with your db.

Ok?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[lamarw]

Hi shepkrm,
I've been reading over your and GingerR's posts and have some questions. GingerR makes valid comments but I feel we haven't heard the whole story.

When the submit button is clicked take the record key in the 'submitted' table and write it back to the previous table (of unsubmitted items) in a new column (SubmitKey?) then the next time the record is clicked you can look for a reference in this column and if it finds one then open that record in the submittal table otherwise open a new record. (Just a thought)

However, I think somehow (not described by you yet, please feel free to comment) a user can open a form and begin the process to submit something for approval. Then, if it's complete it can be approved. If, however, it's not complete then the user can go back to that record either to edit or to delete it. Supposedly (because you have not stated it, please feel free to comment), there are a number of fields to be filled out and (supposedly, please feel free to comment) the queries determine if the appropriate fields have been completed and if so they may (possibly, please feel free to comment) return a true or else a false.
Then, you state the use of a second form to view the data entered and if the quieries all return true then a 'Submit' button will be enabled (supposedly, please feel free to comment) for that particular record to be submitted and to (supposedly, please feel free to comment)move to the next step in the process.
You said: "If this is the first time it's clicked, a record would need to be created. If it is a second time (say, the button was clicked and then someone changed their mind), the existing record would need to display", I believe this would be the next (second) step in the approval process? First the idea is documented second it is approved for submittal? and if the person pressing the submit button does so in error then the next time the button is pressed in conenction with this submission, show the record that was created with the first button push. Apparently (please feel free to comment), the pressing of the button does not release it to step three but just indicates that the approving authority has seen it, or does it actually release it? If it does how can pressing the button show the 'existing record' again? Even if the queries say no can it still be released? These are some areas that may need some clarification. We anxiously await your comments!

Lamar