Full Version: Pre Filled Form
UtterAccess Forums > Microsoft® Access > Access Forms
sportytheif
Hello,
need a form to have prefilled text based on a table that will store the text. This text shouldn't be editble on the form and there will be fields based off of another table that will be editable on the form.
I have the basics down, I just can't get the text to flow on the form and allow editable fields for specific data to be placed in between the non-editable text.
This is a long contract form that has specific fields that need to be changed on a regular basis. The other fields will for the most part stay the same, but may need changing in the future, thats why i need them based on a table.
Any ideas to help solve this situation will be greatly appreciated.
Thank you for your time
Darryl
fredrisg
Here's a function I wrote to disable fields on a form:
CODE

Public Function gfunControlDisable(pstrForm As String, pstrControl As String) As Boolean
On Error GoTo Err_gfunControlDisable
'----------------------------------------------------------------------
'   Purpose: Disable control
'   By: Steve
'   Created: 1/5/04
'   Modified: 1/5/04
'   Function Return Value:
'       False - if unsuccessful
'       True - if successful
'   Data Type Returned: Single
'   Notes:
'   1. add .controls reference
'
'
'
'
'----------------------------------------------------------------------
    gfunControlDisable = False
    'On Error Resume Next
    
    Forms(pstrForm).Controls(pstrControl).Enabled = False
    
    If Forms(pstrForm).Controls(pstrControl).ControlType <> acCheckBox Then
        Forms(pstrForm).Controls(pstrControl).Locked = True
        Forms(pstrForm).Controls(pstrControl).BackStyle = 0
        
    End If
    
    'moved backstyle
    On Error GoTo Err_gfunControlDisable
    gfunControlDisable = True
Exit_gfunControlDisable:
    Exit Function
Err_gfunControlDisable:
    Dim lstrErrMsg As String
    Dim lintErrResponse As Integer
    lstrErrMsg = "Error has occured in gfunControlDisable on form: " & pstrForm & ". Please " & _
                 "advise System Administrator if problem persist. Error: " & Err.Number & " - " & _
                 Err.Description
    lintErrResponse = MsgBox(lstrErrMsg, vbOKOnly + vbInformation, "Error")
    Resume Exit_gfunControlDisable
End Function

You could add logic into your form that goes down through a logical if structure and disable controls you don't want to be edited based on the conditions you set form in say the form load event.
Steve
dannyseager
Why are you storing the text again if it exists in another table?
Just store the ID
sportytheif
I am not storing the text again. I just want it to display on the form so that the user can make sense of the contract.
I have a table setup with the text already in it...I just cant figure out a way to get this text to appear on the form. Thats all i want to do. Make the text from one table appear on a form as if your reading a contract and filling in the dotted lines..only the dotted lines would be from an entirely differnt form. I hope this makes sense.
Darryl
ScottGem
Not completely. If I follow you, you have boiler plate text stored in a table that you want to display. This boiler plate is combined with another table that contains the dotted line info to produce a contract.
If I have it right, this should be fairly simple. I would imagine, the form where you enter the variable info (the fill in the blanks) has a value that indicate what record in the boiler plate table to match to. So simply put the boiler plate in a subform.
sportytheif
You have the first part correct. There is only 1 contract as of right now and the contract is a monster. If i did it your way, the subform would change based on the variable field. I want the broiler plate text to be displayed with the variable fields in place. This way you are reading and making changes to the contract all at the same time.
Darryl
sportytheif
If I do it the way you described, How would I go about setting it up. it's just not clicking in my head right now.
Thanks for your help
Darryl
ScottGem
The question becomes will you ever have more than one boiler plate document. If not, then what I would do is create my form with labels that show the boiler plate. For example; you might have something like this:
i>For a fee of
Enter Fee amount" ___________
The company will perform the following task
Enter project description: __________________
This way you intersperse the the boiler plate with the prompts for variable data.
However, if you may have different sets of boiler plate, then each record has a PK and a descriptor. In your variable data table, you have the ID as a FK. On your main form you have a combobobox to select the ID using the descriptor. You then have a subform that is bound to the boiler plate table and linked to the main form using the ID. So when the user selects the boiler plate, the subform will display the text.
sportytheif
Whoa...That just lost me..Ok...Most likely there will be more broiler plate contracts in the system..So I have to design as if though there will be more...That second paragraph has me a bit confused...
For starters,
1. I already created the broiler plate information in labels on a form, but the text was manually inserted and in
Ordert just lost me..Ok...Most likely there will be more broiler plate contracts in the system..So I have to design as if though there will be more...That second paragraph has me a bit confused...
For starters,
1. I already created the broiler plate information in labels on a form, but the text was manually inserted and in
ORDER to change the text of the label you will have to edit the form itself, I can't figure out a way to escape this..
2. I have a lot of information in the contracts and already ran into the problem that all of the data can not fit on one form, how would this be solved using your method, if I am forced to use 3 or 4 forms for the contract?
**I'm still confused**
ScottGem
Again you would only use 1. if you only had one boiler plate. So since you are building for multiple boiler plates, we can forget that option.
For 2. Lets assume you have a structure like this:
tblBoilerPlate
BoilerPlateID (PK Autonumber)
Description
tblBPParagraphs
BPparagraphID (PK Autonumber)
BoilerPlateID (FK)
ParagraphNum
ParagraphText
Now you have a main form with a combobox for BoilerPlateID. You then have a subform bound to tblBPParagraphs and linked on BoilerPlateID. The subform as 2 visible controls for the paragraph number and its text. The subform would then display all the clauses for that boiler plate in a scrollable box on your main form. That's the way I would do it.
sportytheif
Ok thats starting to clear up the fog...What about the variable fields that are based on another table. Where and how would the get incorporated into this form??
ScottGem
That's your main form. it would look like this:
blVariables
VariableID (PK Autonumber)
BoilerPlatedID (FK)
Fee
Company
Description
For whatever variables you need.
sportytheif
OK, I am attaching a preview of the relationships. Can you take a peek and tell me if it looks right to you please?
Thanks for your help
Darryl
ScottGem
Well you have some normalization issue with your tblAgreement. You shouldn't have fields like FlightClass and FlightClass2. Those are repeating groups. But that is relatively minor if you will always have only 2 of each.
therwise the rest of it looks OK and you should be able to display the contracts and then assemble them.
sportytheif
Actually the most you can have is one but sometimes there is special circumstaces. Thats why I added an extra. Is there a such thing as Over Normalization?
ScottGem
Some won't agree with that, but I think there are times you can take normalization a bit too far.
sportytheif
I am adding the text to the BPParagraphs and it is very weird to me. [censored] I add text, sometimes or actually most of the time the text is too large for the field so I am forced to create a new line and continue where I left off. The problem is this...
. When it is time to update the text of the broiler plate, that won't be easy because you will have to go through hundreds of records to see what you are doin?
and
2. How would I make this all work on one form if i need so many records for just 1 paragraph?
Thanks for your help
Darryl
NoahP
Rule of thumb I use is, if I'm ever going to have search for the same data in multiple fields of one table, it needs to go into it's own table.
think for this database, you need to break table agreements down some more. How much more I can't say without knowing all the fields and how they relate. If some of the 1 and 2 fields are relating to the fighters, then you don't need both fighters in this table. You need only the information about the agreement itself in tblAgreement and then a different table that would include:
tblAgreementFighters
AgreeFightID Autonumber PK
FighterID FK to tblFighters
all fields that relate only to the fighter for this particular agreement.
HTH
Noah
sportytheif
Noah,
The fighters in essence do relate to eachother be cause they are engaging in a bout. The agreement is based on who the fighter is fighting.
NoahP
Any details that apply to BOTH fighters AND the agreement should be in tblAgreements. If it's something like what class of airline ticket each gets, that should be in a different table (tblAgreementFighters that I described above). Leaving tblAgreements as it is now, whenever you want to look for agreements for a fighter, you will have to search TWO fields in this table or you will miss someone. Having the related table, with only one record per fighter makes it much easier to search and work with down the road.
will say that you can over-normalize, but, getting tables to 3NF is about where you want to be. Less than 3NF is not enough, more than 3NF is debatable and depends on the specific circumstances and business model.
Noah
sportytheif
Then I would run into the problem of trying to get all of these fields on one form and being able to make it read as if you were reading a real contract. Thats the real problem that I am trying to overcome.
arryl
ScottGem
What Noah is saying is what I was referring to. Since I would assume you would always have 2 fighters, then you should have a BoutFighters table that is linked to tblAgreements. The data specific to the bout would go in tblAgreements and the data specific to the fighters inthat bout would be in tblBoutFighters.
Is for the boilerplate text, use memo fields. Each record should be either a full praragraph or the text around a variable.
sportytheif
So basically I have to break down the tblagreements down 1 more time and base the new table on information related to the fighter and leave what is related to the agreement where it is. Will I still be able to have single fields off of both tables in one form to allow the user to complete an agreement without having to move through seperate forms?
ScottGem
Use subforms. Or use an unbound form and write the data to the tables manually.
sportytheif
What do you mean by writting the data manually??
tried creating a form, but I can include the tblBPParagraphs and tbl Agreements on the same form. I can however get a main form and 2 subforms if i base the information off of Tblboiler plate but in this fashion it will look like a bunch of fields to be filled in, but no paragraph info.
This is the problem i have been trying to overcome.
Darryl
ScottGem
By manually, I meant using unbound forms and then using code to add the data to the tables.
You shouldn't need tblBoilerplate on this form at all. Since the BoilerPlateID is a FK in tblBPParagraphs you only need to select the BoilerPlateID in tblAgreements and the subform will show all the pragraphs for that boiler plate.
sportytheif
Ok that makes sense but here is the problem..The subforms wont allow the data to flow like a contract that is being read. So basically i have all the fields from the agreement on the top and in a tabular form i have all the paragraphs of the agreement but it wont make sense to an end user..Does this all make sense?
arryl
ScottGem
I'm not sure you can do what you want. Maybe it would be better to create a sample document that you can open in a different window.
sportytheif
Create a sample document? How would this work...
arryl
sportytheif
Ok. What if the form was based on one table would it be possible to then have text boxes or memo boxes based on another table but not linked..Ie. Base a form on the tblagreements then add the fields for the paragraph table to this form. The way I see it, my only problem is trying to use subforms for the paragraphs which is causing the problem.
Since the paragraphs aren't changed by the end user, that wouldn't cause a problem in that table and I could create an invisble combo box for the tblbroilerplate selection which would then autopopulate the fields for the paragraphs and the end user wouldnt know because the main menu (based on the user click) will assign the value to the broiler plate combo box... I hope this is making in sense..lol..and I would get what I want...
So i guess the million dollar question is, how do i incorporate the paragraphs table into the form which is based on tbl agreements without a subform?
Darryl
ScottGem
You would have to do DLookups and that would slow the form down greatly. The only other suggestion would be what I said about making a sample document and displaying that on your form.
sportytheif
Okay..How would I make a sample form?
lso, I came up with an Idea but dont know ifit would work.. Is there a way for a text box to refrence a particular record on a subform. For example, I am able to refrence the top most record on the subform, but what about record 2 or 3. I think that would solve the issue. Like setting the criterea. I would even settle for multiple queries. I guess.
Thanks Scott for your time and help!
Darryl
ScottGem
Just put all the text into a Word file or on a form.
You should be able to tie the paragraphs in your boilerplate paragraphs table to variables.
I suggested a ParagraphNUm field. that could be tied to the Variable number or name.
sportytheif
Hey Scott,
Sorry for bringing this back up. I just got back to this project as I was pulled off for another. Anyways..Happy New Year. I started back on this project and I am a bit lost on the last post you provided.
How would I tie these two together if one is on one form and the other is going to be another form? and The contract I am trying to use has about 10 pages which is roughly 5000 words, How would I fit all of this on a form and still do what you suggested?
Thanks,
Darryl
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.