 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Control Field Validation
I am trying to put some VB code in as a form of validation on an Access form. What I am looking for is essentially the ability to check 5 different controls for data contained within. The controls are for dates that are tied to a table that records the training completion dates. In an effort to avoid out-of-sequence training or edits to the data after the initial entry, I am wanting to put in this code. It essentially enables the field for putting in data but disables subsequent fields to maintain the training sequence. This is what I have so far (and it is *long* down below). At this point, I cannot seem to get it to work right. It immediately locks down the form when there are dates in the fields. The only other thing that I can think of is to have this same VB code for the BeforeUpdate, On_Click and AfterUpdate events. Jason Lopez Private Sub Form_Load() If Me!BFT = 0 Then Me!BFT.Enabled = True Me!UPT1.Enabled = False Me!UPT2.Enabled = False Me!IFF.Enabled = False Me!FTU.Enabled = False Me!BFT.Locked = False Me!UPT1.Locked = True Me!UPT2.Locked = True Me!IFF.Locked = True Me!FTU.Locked = True Else If Me!BFT <> 0 & Me!UPT1 = 0 Then Me!BFT.Enabled = False Me!UPT1.Enabled = True Me!UPT2.Enabled = False Me!IFF.Enabled = False Me!FTU.Enabled = False Me!BFT.Locked = True Me!UPT1.Locked = False Me!UPT2.Locked = True Me!IFF.Locked = True Me!FTU.Locked = True Else If Me!BFT & Me!UPT1 <> 0 & Me!UPT2 = 0 Then Me!BFT.Enabled = False Me!UPT1.Enabled = False Me!UPT2.Enabled = True Me!IFF.Enabled = False Me!FTU.Enabled = False Me!BFT.Locked = True Me!UPT1.Locked = True Me!UPT2.Locked = False Me!IFF.Locked = True Me!FTU.Locked = True Else If Me!BFT & Me!UPT1 & Me!UPT2 <> 0 & Me!IFF = 0 Then Me!BFT.Enabled = False Me!UPT1.Enabled = False Me!UPT2.Enabled = False Me!IFF.Enabled = True Me!FTU.Enabled = False Me!BFT.Locked = True Me!UPT1.Locked = True Me!UPT2.Locked = True Me!IFF.Locked = False Me!FTU.Locked = True Else If Me!BFT & Me!UPT1 & Me!UPT2 & Me!IFF <> 0 & Me!FTU = 0 Then Me!BFT.Enabled = False Me!UPT1.Enabled = False Me!UPT2.Enabled = False Me!IFF.Enabled = False Me!FTU.Enabled = True Me!BFT.Locked = True Me!UPT1.Locked = True Me!UPT2.Locked = True Me!IFF.Locked = True Me!FTU.Locked = False Else Me!BFT.Enabled = False Me!UPT1.Enabled = False Me!UPT2.Enabled = False Me!IFF.Enabled = False Me!FTU.Enabled = False Me!BFT.Locked = True Me!UPT1.Locked = True Me!UPT2.Locked = True Me!IFF.Locked = True Me!FTU.Locked = True End If End If End If End If End If End Sub That's quite an If..Then statement you have there. First of all, IF you have a control disabled THEN I don't see why you need to lock it also. It seems like alot of extra coding for nothing. Second, why don't you just set the default value of all the controls on your form to locked, then just use the code to unlock them as needed. Third, it seems like this code would be better placed in the on current event for your form. As it is now, it is only going to run once when your form first loads. If a user does an update and then moves to a new record without ever closing the form, your code will do nothing You also might consider a Select Case statement. > I am trying to put some VB code in as a form of validation on an Access > form. What I am looking for is essentially the ability to check 5 different [quoted text clipped - 91 lines] > End If > End Sub I never thought of setting the default to Disabled and then letting the code as OnCurrent work then. But what do you mean by a "Select Case" statement? I have never heard of that before. Also, I am not a programmer in any way. Barely touching the surface of VB at this point. Though I am probably in way over my head. Jason Lopez > That's quite an If..Then statement you have there. > [quoted text clipped - 113 lines] >> End If >> End Sub >I am trying to put some VB code in as a form of validation on an Access >form. What I am looking for is essentially the ability to check 5 different [quoted text clipped - 10 lines] >of is to have this same VB code for the BeforeUpdate, On_Click and >AfterUpdate events. Stepping back a bit... if you have one *field* for each kind of date, you're perhaps limiting your flexibility in terms of table design. Suppose you want to add a *sixth* type of training? You'll need to restructure your table, rewrite all your queries, redesign your forms and reports, rewrite all your code... ouch! Could you instead consider a four-table solution: Employees EmployeeID <Primery Key> LastName FirstName <other bio data> Courses CourseID <Primary Key> Description <other info about the training> Schedule SeqNo <what order the courses must be taken> CourseID <which course> <other info, e.g. comments, valid reasons to take a course out of order or skip it, etc.> Trainings TrainingID EmployeeID CourseID CompletionDate <comments, pass/fail, retakes, etc.> You could then simply *add a record* to the Trainings table; the form's beforeupdate event could be used to ensure that you're not entering a course out of sequence; it would make reporting MUCH easier. John W. Vinson [MVP] You are already thinking along the lines that I am wanting to take these tables and forms. The training form is actually just a summary of the additional forms that will contain the greater detail of each phase/step in training. Essentially, the tables look like this: Trg-BFT; Trg-UPT1,... There are 5 phases/tables total with the summary table (TrgProg) as an extra and sub to the main form/table. To complete each phase, there are varying requirements. An example is that Trg-BFT has 5 requirements that must be met. When requirement 5 is completed, then the AfterUpdate event will send the current date to TrgProg in its respective field (and hopefully for the correct person). With all the code existing in the forms, it should work out to going to the correct person the moment that the subform is updated. But, you are essentially saying to make a table that already has the sequence there? Is that right? Jason Lopez >>I am trying to put some VB code in as a form of validation on an Access >>form. What I am looking for is essentially the ability to check 5 [quoted text clipped - 57 lines] > > John W. Vinson [MVP] >You are already thinking along the lines that I am wanting to take these >tables and forms. [quoted text clipped - 15 lines] >But, you are essentially saying to make a table that already has the >sequence there? Is that right? That's the idea, yes. You can actually create a subform based on a Query "left outer joining" the requirements table to the results table; if you have this subform with the person's ID as the master/child link field you don't need even a single line of code. John W. Vinson [MVP] On 13 Jul, 21:20, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > >a table that records the training completion dates. In an effort to > >avoid out-of-sequence training [quoted text clipped - 28 lines] > CompletionDate > <comments, pass/fail, retakes, etc.> But consider that the OP has stated "avoid out-of-sequence training" as a requirement. I don't see anything in your proposed design to enforce this. To take a different angle on your "sixth date" argument, consider these simple validation rules: Date1 < Date2 Date2 < Date3 Date3 < Date4 Date4 < Date5 Date5 < Date6 How would you achieve the same data integrity with your 'flexible' design? Jamie. -- I actually used a hybrid of code (no query worked to do what I was ultimately looking for). It essentially resets the properties and then runs the IF statements to disable the appropriate box. But, thank you Jamie for bringing back my main point for each field: proper sequencing validation. Thank you to all that have helped so far. I am not quite done with that part of my database form. So I am sure I will still have more questions yet to come. Jason Lopez > On 13 Jul, 21:20, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> > wrote: [quoted text clipped - 50 lines] > > -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.