Access database programming

[Nandini]

I have created a bibliographic database using Access 2003. Here some tables
queries, and forms are designed. Some fields have null values. I provided the
codes for invisibility of the text boxes having null values in current view
of the form. But the said form shows the blank spaces in the place of
invisible text boxes having null values, placed between the text boxes having
values. I want to know the code by which those blank spaces of the form can
be adjustable or removed also in its current view.
With best regards.

 

[Ken Sheridan]

If the form is in continuous form view you''d have to concatenate the values
into a single control with a ControlSource such as:

=(Field1 + " ") & (Field2 + " ") & (Field3 + " ") & (Field4 + " ") &
Field5

By using the + operator within each parenthesised expression the following
space is suppressed if the field in question is Null.

If the form is in single form view you can adjust the positions of the
controls at runtime. The following is some code from a demo form which shows
how to do this with 5 equally sized controls and their labels. In this case
the controls are hidden if zero, not Null, but you could easily amend it to
rest for IsNull(Field1) etc rather than Field 1 = 0 etc. You'd need to
experiment with the value of the conADJUST constant and the default Left
values to get the best fit for your controls. Note that it only works where
the controls are of equal width:

Const conADJUST = 1227

' make all controls visible by default
Field1.Visible = True
Field1_Label.Visible = True
Field2.Visible = True
Field2_Label.Visible = True
Field3.Visible = True
Field3_Label.Visible = True
Field4.Visible = True
Field4_Label.Visible = True
Field5.Visible = True
Field5_Label.Visible = True

' set default Left values for controls
Field1.Left = 60
Field1_Label.Left = 60
Field2.Left = 1287
Field2_Label.Left = 1287
Field3.Left = 2514
Field3_Label.Left = 2514
Field4.Left = 3741
Field4_Label.Left = 3741
Field5.Left = 4968
Field5_Label.Left = 4968

' all controls are shown if form is at a new record
If Not Me.NewRecord Then
' otherwise adjust controls leftwards to omit zero values
If Field1 = 0 Then
Field1.Visible = False
Field1_Label.Visible = False
Field2.Left = Field2.Left - conADJUST
Field2_Label.Left = Field2_Label.Left - conADJUST
Field3.Left = Field3.Left - conADJUST
Field3_Label.Left = Field3_Label.Left - conADJUST
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field2 = 0 Then
Field2.Visible = False
Field2_Label.Visible = False
Field3.Left = Field3.Left - conADJUST
Field3_Label.Left = Field3_Label.Left - conADJUST
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field3 = 0 Then
Field3.Visible = False
Field3_Label.Visible = False
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field5 = 0 Then
Field5.Visible = False
Field5_Label.Visible = False
End If
End If

Ken Sheridan
Stafford, England