Hiding fields on a form based on lack of data in another field

I

iamrdbrown

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
 
M

Marshall Barton

iamrdbrown said:
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.
Click to expand...


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

iamrdbrown

Marshall Barton said:
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.
Click to expand...

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
 
D

Douglas J. Steele

iamrdbrown said:
Marshall Barton said:
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.
Click to expand...

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
Click to expand...

Sounds as though you're using a form in Continuous or DataSheet view.
 
I

iamrdbrown

Douglas J. Steele said:
iamrdbrown said:
Marshall Barton said:
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.
Click to expand...

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
Click to expand...

Sounds as though you're using a form in Continuous or DataSheet view.
Click to expand...
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
 
D

Douglas J. Steele

iamrdbrown said:
Douglas J. Steele said:
iamrdbrown said:
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
Click to expand...
Click to expand...
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.
Click to expand...

You don't happen to have a default value set for whatever field is bound to
txtFKUserID1, do you?
 
I

iamrdbrown

Douglas J. Steele said:
iamrdbrown said:
Douglas J. Steele said:
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
Click to expand...
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.
Click to expand...

You don't happen to have a default value set for whatever field is bound to
txtFKUserID1, do you?
Click to expand...

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
 
D

Douglas J. Steele

iamrdbrown said:
Douglas J. Steele said:
iamrdbrown said:
:


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.
Click to expand...

You don't happen to have a default value set for whatever field is bound
to
txtFKUserID1, do you?
Click to expand...

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...
Click to expand...


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?
 
I

iamrdbrown

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?
Click to expand...
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?
 
D

Douglas J. Steele

iamrdbrown said:
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?
Click to expand...
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?
Click to expand...

Check the text box itself. Also, see whether there's any code behind the
form that may be setting the value.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fill in a field based on input of data in another field 5
Copy data from field on a form/paste it to a field in another form 4
Access Help with Access 2013 - changing a form field back colour based on another fields entry (Check box) 0
Hiding controls based on non-presence of control.name in a table 7
Updating Fields in One Table Based on a Field in Another Table 1
Populate fields based on data entry in another field 3
DAta Validation in a field based on the contents of another field 7
Making a field in/visible based on a value of another field 6

Top