Hiding fields on a form based on lack of data in another field iamrdbrown 11/7/2008 7:31:00 PM I am attempting my first database that will need VB/VBA coding. I am very new to VB/VBA and need a little jump start. The database is to track unit rework that has to be performed due to product mishandling. It is a very small database at this time (only 3 tables): tblUsers, tblSecuritySettings, tblUnitData The security table is set up to identify Operators, Supervisors and QA Auditors for approval security levels. The Users table has UserID, FirstName, LastName and a lookup to the Security table for clearance. The bulk of the information is in the UnitData table where I will track the unit model number, labeling, work performed, etc. I have a couple of things I need help with on the main form for inputting/reviewing unit data. The form needs 3 'approvals' to be complete. These approvals are each done separately & need to be based on 'security level' for each. They may or may not be done on the same date, so each approval is set up as follows: UserID1, DateChecked1 and ShiftChecked1 UserID2, DateChecked2 and ShiftChecked2 UserID3, DateChecked3 and ShiftChecked3 Basically, UserID1 approval is the operator doing the work and needs to have operator security clearance (or better). DateChecked1 needs to be the date the operator puts in his/her ID. The operator will put in ShiftChecked1. UserID2 approval is for a Supervisor to complete once they have confirmed the rework has been performed correctly. It shouldn't be visible unless there is data in the UserID1 field. Once it is visible, a person will need to have Supervisor clearance to fill in the associated fields. UserID3 is for a quality auditor to fill in once they have reviewed the unit. This field and the other 2 associated with it should only be visible if the UserID1 AND UserID2 fields have been completed. It should also look for the person to have QA Auditor security clearance to be able to enter data. I know, this is an awful lot to start with for a beginner... but I got handed this as a 'see if you can figure this out and make it work' project. I am just too stubborn to admit it's over my head - especially if I can figure it out & learn from it. Thanks in advance for any help R Brown

Re: Hiding fields on a form based on lack of data in another field Marshall Barton <marshbarton[ at ]wowway.com> 11/7/2008 9:07:52 PM iamrdbrown wrote: [Quoted Text] >I am attempting my first database that will need VB/VBA coding. I am very >new to VB/VBA and need a little jump start. > >The database is to track unit rework that has to be performed due to product >mishandling. It is a very small database at this time (only 3 tables): >tblUsers, tblSecuritySettings, tblUnitData > >The security table is set up to identify Operators, Supervisors and QA >Auditors for approval security levels. > >The Users table has UserID, FirstName, LastName and a lookup to the Security >table for clearance. > >The bulk of the information is in the UnitData table where I will track the >unit model number, labeling, work performed, etc. > >I have a couple of things I need help with on the main form for >inputting/reviewing unit data. > >The form needs 3 'approvals' to be complete. These approvals are each done >separately & need to be based on 'security level' for each. They may or may >not be done on the same date, so each approval is set up as follows: >UserID1, DateChecked1 and ShiftChecked1 >UserID2, DateChecked2 and ShiftChecked2 >UserID3, DateChecked3 and ShiftChecked3 > >Basically, UserID1 approval is the operator doing the work and needs to have >operator security clearance (or better). DateChecked1 needs to be the date >the operator puts in his/her ID. The operator will put in ShiftChecked1. > >UserID2 approval is for a Supervisor to complete once they have confirmed >the rework has been performed correctly. It shouldn't be visible unless >there is data in the UserID1 field. Once it is visible, a person will need >to have Supervisor clearance to fill in the associated fields. > >UserID3 is for a quality auditor to fill in once they have reviewed the >unit. This field and the other 2 associated with it should only be visible >if the UserID1 AND UserID2 fields have been completed. It should also look >for the person to have QA Auditor security clearance to be able to enter data. > >I know, this is an awful lot to start with for a beginner... but I got >handed this as a 'see if you can figure this out and make it work' project. >I am just too stubborn to admit it's over my head - especially if I can >figure it out & learn from it. Assuming I understand all that, this is easy to do on a form in Single view. Use code like this in both the ShiftChecked1 text box's AfterUpdate and the form's Current event procedures: Me.txtUserID2.Visible = Not IsNull(Me.ShiftChecked1) Me.DateChecked2.Visible = Not IsNull(Me.ShiftChecked1) Me.ShiftChecked2.Visible = Not IsNull(Me.ShiftChecked1) Use similar code for ShiftChecked2 (or whatever field indicates appoval completed). Note that this would be very difficult to do for a form in Continuous view and impossible for a form in DataSheet view. -- Marsh MVP [MS Access]

Re: Hiding fields on a form based on lack of data in another field iamrdbrown 11/10/2008 1:34:01 PM "Marshall Barton" wrote: [Quoted Text] > Assuming I understand all that, this is easy to do on a form > in Single view. Use code like this in both the > ShiftChecked1 text box's AfterUpdate and the form's Current > event procedures: > > Me.txtUserID2.Visible = Not IsNull(Me.ShiftChecked1) > Me.DateChecked2.Visible = Not IsNull(Me.ShiftChecked1) > Me.ShiftChecked2.Visible = Not IsNull(Me.ShiftChecked1) > > Use similar code for ShiftChecked2 (or whatever field > indicates appoval completed). > > Note that this would be very difficult to do for a form in > Continuous view and impossible for a form in DataSheet view. > > -- > Marsh > MVP [MS Access] > I tried this and all of the boxes that should be invisible on the form remained visible/active and usable. I tried the following code and got the same results... no hidden boxes... I plan to hide all boxes except those associated with UserID1 until UserID1 data is complete. I only put in the code for UserID2 boxes just to see if it would work - save some keystrokes until I figure out what is going on... Private Sub Form_Current() Dim txtFKUserID1 As Integer Dim txtDateChecked1 As Date Dim txtShiftChecked1 As String Dim txtFKUserID2 As Integer Dim txtDateChecked2 As Date Dim txtShiftChecked2 As String Dim txtFKUserID3 As Integer Dim txtDateChecked3 As Date Dim txtShiftChecked3 As String If IsNull(txtFKUserID1) Then [frmUnitData_NewRecord]![txtFKUserID2].Visible = False [frmUnitData_NewRecord]![txtDateChecked2].Visible = False [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False End If End Sub Thanks, R Brown

Re: Hiding fields on a form based on lack of data in another field "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 1:54:04 PM "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message news:B031ECF6-E398-42ED-90AD-E6B49C7D005E[ at ]microsoft.com... [Quoted Text] > > > "Marshall Barton" wrote: > >> Assuming I understand all that, this is easy to do on a form >> in Single view. Use code like this in both the >> ShiftChecked1 text box's AfterUpdate and the form's Current >> event procedures: >> >> Me.txtUserID2.Visible = Not IsNull(Me.ShiftChecked1) >> Me.DateChecked2.Visible = Not IsNull(Me.ShiftChecked1) >> Me.ShiftChecked2.Visible = Not IsNull(Me.ShiftChecked1) >> >> Use similar code for ShiftChecked2 (or whatever field >> indicates appoval completed). >> >> Note that this would be very difficult to do for a form in >> Continuous view and impossible for a form in DataSheet view. > > I tried this and all of the boxes that should be invisible on the form > remained visible/active and usable. I tried the following code and got > the > same results... no hidden boxes... I plan to hide all boxes except those > associated with UserID1 until UserID1 data is complete. I only put in the > code for UserID2 boxes just to see if it would work - save some keystrokes > until I figure out what is going on... > > Private Sub Form_Current() > Dim txtFKUserID1 As Integer > Dim txtDateChecked1 As Date > Dim txtShiftChecked1 As String > Dim txtFKUserID2 As Integer > Dim txtDateChecked2 As Date > Dim txtShiftChecked2 As String > Dim txtFKUserID3 As Integer > Dim txtDateChecked3 As Date > Dim txtShiftChecked3 As String > > > If IsNull(txtFKUserID1) Then > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False > End If > End Sub Sounds as though you're using a form in Continuous or DataSheet view. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)

Re: Hiding fields on a form based on lack of data in another field iamrdbrown 11/10/2008 2:45:01 PM "Douglas J. Steele" wrote: [Quoted Text] > "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message > news:B031ECF6-E398-42ED-90AD-E6B49C7D005E[ at ]microsoft.com... > > > > > > "Marshall Barton" wrote: > > > >> Assuming I understand all that, this is easy to do on a form > >> in Single view. Use code like this in both the > >> ShiftChecked1 text box's AfterUpdate and the form's Current > >> event procedures: > >> > >> Me.txtUserID2.Visible = Not IsNull(Me.ShiftChecked1) > >> Me.DateChecked2.Visible = Not IsNull(Me.ShiftChecked1) > >> Me.ShiftChecked2.Visible = Not IsNull(Me.ShiftChecked1) > >> > >> Use similar code for ShiftChecked2 (or whatever field > >> indicates appoval completed). > >> > >> Note that this would be very difficult to do for a form in > >> Continuous view and impossible for a form in DataSheet view. > > > > I tried this and all of the boxes that should be invisible on the form > > remained visible/active and usable. I tried the following code and got > > the > > same results... no hidden boxes... I plan to hide all boxes except those > > associated with UserID1 until UserID1 data is complete. I only put in the > > code for UserID2 boxes just to see if it would work - save some keystrokes > > until I figure out what is going on... > > > > Private Sub Form_Current() > > Dim txtFKUserID1 As Integer > > Dim txtDateChecked1 As Date > > Dim txtShiftChecked1 As String > > Dim txtFKUserID2 As Integer > > Dim txtDateChecked2 As Date > > Dim txtShiftChecked2 As String > > Dim txtFKUserID3 As Integer > > Dim txtDateChecked3 As Date > > Dim txtShiftChecked3 As String > > > > > > If IsNull(txtFKUserID1) Then > > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False > > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False > > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False > > End If > > End Sub > > Sounds as though you're using a form in Continuous or DataSheet view. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > No sir, I have it set for Single form, Form view only. I also have it set for data entry format. I am trying to have the form only show the current unit/product being worked on. I don't want to have the potential for the operator to put the wrong information in on a unit already completed. Thanks, R Brown

Re: Hiding fields on a form based on lack of data in another field "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 4:17:45 PM "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message news:A89EEBCB-5380-4D2F-859F-7602B7DB533B[ at ]microsoft.com... [Quoted Text] > > "Douglas J. Steele" wrote: > >> "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message >> news:B031ECF6-E398-42ED-90AD-E6B49C7D005E[ at ]microsoft.com... >> > >> > I tried this and all of the boxes that should be invisible on the form >> > remained visible/active and usable. I tried the following code and got >> > the >> > same results... no hidden boxes... I plan to hide all boxes except >> > those >> > associated with UserID1 until UserID1 data is complete. I only put in >> > the >> > code for UserID2 boxes just to see if it would work - save some >> > keystrokes >> > until I figure out what is going on... >> > >> > Private Sub Form_Current() >> > Dim txtFKUserID1 As Integer >> > Dim txtDateChecked1 As Date >> > Dim txtShiftChecked1 As String >> > Dim txtFKUserID2 As Integer >> > Dim txtDateChecked2 As Date >> > Dim txtShiftChecked2 As String >> > Dim txtFKUserID3 As Integer >> > Dim txtDateChecked3 As Date >> > Dim txtShiftChecked3 As String >> > >> > >> > If IsNull(txtFKUserID1) Then >> > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False >> > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False >> > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False >> > End If >> > End Sub >> > No sir, I have it set for Single form, Form view only. I also have it set > for data entry format. I am trying to have the form only show the current > unit/product being worked on. I don't want to have the potential for the > operator to put the wrong information in on a unit already completed. You don't happen to have a default value set for whatever field is bound to txtFKUserID1, do you? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)

Re: Hiding fields on a form based on lack of data in another field iamrdbrown 11/10/2008 4:29:01 PM "Douglas J. Steele" wrote: [Quoted Text] > "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message > news:A89EEBCB-5380-4D2F-859F-7602B7DB533B[ at ]microsoft.com... > > > > "Douglas J. Steele" wrote: > > > >> "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message > >> news:B031ECF6-E398-42ED-90AD-E6B49C7D005E[ at ]microsoft.com... > >> > > >> > I tried this and all of the boxes that should be invisible on the form > >> > remained visible/active and usable. I tried the following code and got > >> > the > >> > same results... no hidden boxes... I plan to hide all boxes except > >> > those > >> > associated with UserID1 until UserID1 data is complete. I only put in > >> > the > >> > code for UserID2 boxes just to see if it would work - save some > >> > keystrokes > >> > until I figure out what is going on... > >> > > >> > Private Sub Form_Current() > >> > Dim txtFKUserID1 As Integer > >> > Dim txtDateChecked1 As Date > >> > Dim txtShiftChecked1 As String > >> > Dim txtFKUserID2 As Integer > >> > Dim txtDateChecked2 As Date > >> > Dim txtShiftChecked2 As String > >> > Dim txtFKUserID3 As Integer > >> > Dim txtDateChecked3 As Date > >> > Dim txtShiftChecked3 As String > >> > > >> > > >> > If IsNull(txtFKUserID1) Then > >> > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False > >> > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False > >> > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False > >> > End If > >> > End Sub > >> > > No sir, I have it set for Single form, Form view only. I also have it set > > for data entry format. I am trying to have the form only show the current > > unit/product being worked on. I don't want to have the potential for the > > operator to put the wrong information in on a unit already completed. > > You don't happen to have a default value set for whatever field is bound to > txtFKUserID1, do you? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > No, sir. I have tried it both ways and still get the same thing. The boxes stay visible/usable regardless. It doesn't seem to make a difference. I suspect it is something simple that I just don't know how to find. I guess it is pretty obvious that this beginner is over her head... Thanks, R. Brown

Re: Hiding fields on a form based on lack of data in another field "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 4:49:07 PM "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message news:44351EF1-4BCD-4759-B8D7-40FC2AC8CACB[ at ]microsoft.com... [Quoted Text] > "Douglas J. Steele" wrote: > >> "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message >> news:A89EEBCB-5380-4D2F-859F-7602B7DB533B[ at ]microsoft.com... >> > >> > "Douglas J. Steele" wrote: >> > >> >> "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message >> >> news:B031ECF6-E398-42ED-90AD-E6B49C7D005E[ at ]microsoft.com... >> >> > >> >> > I tried this and all of the boxes that should be invisible on the >> >> > form >> >> > remained visible/active and usable. I tried the following code and >> >> > got >> >> > the >> >> > same results... no hidden boxes... I plan to hide all boxes except >> >> > those >> >> > associated with UserID1 until UserID1 data is complete. I only put >> >> > in >> >> > the >> >> > code for UserID2 boxes just to see if it would work - save some >> >> > keystrokes >> >> > until I figure out what is going on... >> >> > >> >> > Private Sub Form_Current() >> >> > Dim txtFKUserID1 As Integer >> >> > Dim txtDateChecked1 As Date >> >> > Dim txtShiftChecked1 As String >> >> > Dim txtFKUserID2 As Integer >> >> > Dim txtDateChecked2 As Date >> >> > Dim txtShiftChecked2 As String >> >> > Dim txtFKUserID3 As Integer >> >> > Dim txtDateChecked3 As Date >> >> > Dim txtShiftChecked3 As String >> >> > >> >> > >> >> > If IsNull(txtFKUserID1) Then >> >> > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False >> >> > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False >> >> > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False >> >> > End If >> >> > End Sub >> >> >> > No sir, I have it set for Single form, Form view only. I also have it >> > set >> > for data entry format. I am trying to have the form only show the >> > current >> > unit/product being worked on. I don't want to have the potential for >> > the >> > operator to put the wrong information in on a unit already completed. >> >> You don't happen to have a default value set for whatever field is bound >> to >> txtFKUserID1, do you? >> > > No, sir. I have tried it both ways and still get the same thing. The > boxes > stay visible/usable regardless. It doesn't seem to make a difference. I > suspect it is something simple that I just don't know how to find. I > guess > it is pretty obvious that this beginner is over her head... Are you certain that the code is firing? Sometimes event procedures get disassociated from the events for which they're supposed to apply. Make sure you've got the form selected (as opposed to a control on the form), and look at the Properties window. Does it say [Event Procedure] in the form's On Current property? When you click on the ellipsis (...) to the right of the property, are you taken into the code that you've posted? If so, then try changing If IsNull(txtFKUserID1) Then [frmUnitData_NewRecord]![txtFKUserID2].Visible = False [frmUnitData_NewRecord]![txtDateChecked2].Visible = False [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False End If to If IsNull(txtFKUserID1) Then MsgBox "txtFKUserID1 is Null" [frmUnitData_NewRecord]![txtFKUserID2].Visible = False [frmUnitData_NewRecord]![txtDateChecked2].Visible = False [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False Else MsgBox "txtFKUserID1 is " & txtFKUserID1 & ", not Null" End If Which message box appears? If it's the second one, what does it say the value of txtFKUserID1 is? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)

Re: Hiding fields on a form based on lack of data in another field iamrdbrown 11/10/2008 5:02:04 PM [Quoted Text] > > Are you certain that the code is firing? Sometimes event procedures get > disassociated from the events for which they're supposed to apply. > > Make sure you've got the form selected (as opposed to a control on the > form), and look at the Properties window. Does it say [Event Procedure] in > the form's On Current property? When you click on the ellipsis (...) to the > right of the property, are you taken into the code that you've posted? > > If so, then try changing > > If IsNull(txtFKUserID1) Then > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False > End If > > to > > If IsNull(txtFKUserID1) Then > MsgBox "txtFKUserID1 is Null" > [frmUnitData_NewRecord]![txtFKUserID2].Visible = False > [frmUnitData_NewRecord]![txtDateChecked2].Visible = False > [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False > Else > MsgBox "txtFKUserID1 is " & txtFKUserID1 & ", not Null" > End If > > Which message box appears? If it's the second one, what does it say the > value of txtFKUserID1 is? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > Bless your heart - that at least gives me something... I did get the second message box. So where in the world is that 0 coming from? The default values for all of the UserID fields is blank if you look at the table settings or form properties. Where else do I look for a hidden default value?

Re: Hiding fields on a form based on lack of data in another field "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 9:49:43 PM "iamrdbrown" <iamrdbrown[ at ]discussions.microsoft.com> wrote in message news:71A30D57-52FE-4748-9B7A-7B724E985EF3[ at ]microsoft.com... [Quoted Text] > >> >> Are you certain that the code is firing? Sometimes event procedures get >> disassociated from the events for which they're supposed to apply. >> >> Make sure you've got the form selected (as opposed to a control on the >> form), and look at the Properties window. Does it say [Event Procedure] >> in >> the form's On Current property? When you click on the ellipsis (...) to >> the >> right of the property, are you taken into the code that you've posted? >> >> If so, then try changing >> >> If IsNull(txtFKUserID1) Then >> [frmUnitData_NewRecord]![txtFKUserID2].Visible = False >> [frmUnitData_NewRecord]![txtDateChecked2].Visible = False >> [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False >> End If >> >> to >> >> If IsNull(txtFKUserID1) Then >> MsgBox "txtFKUserID1 is Null" >> [frmUnitData_NewRecord]![txtFKUserID2].Visible = False >> [frmUnitData_NewRecord]![txtDateChecked2].Visible = False >> [frmUnitData_NewRecord]![txtShiftChecked2].Visible = False >> Else >> MsgBox "txtFKUserID1 is " & txtFKUserID1 & ", not Null" >> End If >> >> Which message box appears? If it's the second one, what does it say the >> value of txtFKUserID1 is? >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> > Bless your heart - that at least gives me something... I did get the > second > message box. So where in the world is that 0 coming from? The default > values for all of the UserID fields is blank if you look at the table > settings or form properties. Where else do I look for a hidden default > value? Check the text box itself. Also, see whether there's any code behind the form that may be setting the value. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please)