Invisible Texbox when values are null in a formula

[BlueChicken]

I made a form to record information on employee travel. Each record has a request number, six (6) flight information sections (ariline, flight number, confirmation number, depart location, depart time, arrive location, arrive time), one hotel (hotel name, hotel confirmation, check in time, check in date, check out time, check out date), and rental car (rental agency, rental confirmation, pick up date, pick up time, drop off date, drop off time).

I am making a report on all of this data so I can send it to the employee who is traveling so that they have all the information they need for their trip in one place. I'll show you what I am doing first then I'll explain my problem.

Here is what the report looks like in design view minus the title:

=Now()

="RE: Trip Request " & [TravelDeskRequestNumber] & "."

="Dear " & [Suffix] & " " & [FirstName] & " " & [LastName] & ","

="Below is your information on your trip that begins " & [AirTravelDate1] & "."

Your airline information is as follows:

="You are on " & [Airline1] & " flight " & [FlightNumber1] & ", departing at " & [AirTravelDepartTime1] & " on " & [AirTravelDate1] & " from " & [AirTravelDepartLocation1] & " to arrive at " & [AirTravelArriveLocation1] & " at " & [AirTravelArriveTime1] & ". The flight locator for this flight is " & [AirTravelLocator1] & "."
^^This is repeated 5 more times with the numbers changing (1-6)^^

="You will be staying at " & [AutoNumber] & " to check in on " & [HotelCheckInDate] & " at " & [HotelCheckInTime] & ", and to check out on " & [HotelCheckOutDate] & " at " & [HotelCheckOutTime] & ". Your confirmation number is " & [HotelConfirmationNumber] & "."

="You have a rental with " & [RentalAgency] & ", to be picked up at " & [RentalPickUpTime] & " on " & [RentalPickUpDate] & ". Please return your rental by " & [RentalDropOffTime] & " on " & [RentalDropOffDate] & ". Your confirmation number is " & [RentalConfirmationNumber] & "."

If you have any questions regarding your itenerary, please contact us immediately.

Thank-you.

Travel Desk

The only problem is that not all of the employees need all 6 flight sections, hotels, or rental cars so in the form the text boxes are blank, but in this report due to the formulas, the words are there but the actual details are just spaces... looks really odd and I want them to disappear if there is no actual data.

I have tried a few things that I can think of and none of them have worked because they were meant for text boxes and bound labels not formulas. Can shrink does not work. *<--EDIT*

If you have any idea how to fix this PLEASE let me know. I have to get this report working as soon as possible.

Thanks!

 

[BlueChicken]

I forgot to add:

The names of the text boxes and labels are (in order)

DateLabel
TripRequestLine
DearLine
InformationLine
FlightLabel
Flight1
Flight2
Flight3
Flight4
Flight5
Flight6
HotelLabel<<<<<<<<<<<
HotelTextBox ----------Both of these were just added
RentalCarLabel <<<<<<<
RentalCarTextBox
QuestionLine
ThankYou
TravelDesk

I don't know if the names make any difference... but there they are anyway.

 

[Rick Stanich]

You could have a vba module check each textbox for "", if it is "" (null) then hide the textbox.

 

[BlueChicken]

How would I do that... I have some basic knowledge of Access but I don't know what you are talking about. Can you give a few more details?

Thanks!

 

[SOS]

You could have a vba module check each textbox for "", if it is "" (null) then hide the textbox.

Rick:

Correction - "" is NOT Null - it is an empty string (there is a BIG difference).

If you want to check for Nulls AND empty strings then you can use something like:

If Len([YourFieldNameHere] & "") = 0 Then...

 

[BlueChicken]

If Len([YourFieldNameHere] & "") = 0 Then...

This would be done using Visual Basic right?

Bassically and If THEN statement... so, after the IF (oh my there will be lots of them...) what should I do for the the then. I don't know what the coding is for making items invisible.

 

[BlueChicken]

Ok - this is what I have tried so far just for the AirTravel section and it is not working at all... there is no code error, no pop up message, but the boxes do no disappear.

Option Compare Database
Private Sub Check1_Click()
If Len([Airline1] & "") = 0 Then
Airline1.Visible = True
Else
Airline1.Visible = False
End If
If Len([AirTravelDate1] & "") = 0 Then
AirTravelDate1.Visible = True
Else
AirTravelDate1.Visible = False
End If
If Len([AirTravelDepartLocation1] & "") = 0 Then
AirTravelDepartLocation1.Visible = True
Else
AirTravelDepartLocation1.Visible = False
End If
If Len([AirTravelDepartTime1] & "") = 0 Then
AirTravelDepartTime1.Visible = True
Else
AirTravelDepartTime1.Visible = False
End If
If Len([AirTravelArriveLocation1] & "") = 0 Then
AirTravelArriveLocation1.Visible = True
Else
AirTravelArriveLocation1.Visible = False
End If
If Len([AirTravelArriveTime1] & "") = 0 Then
AirTravelArriveTime1.Visible = True
Else
AirTravelArriveTime1.Visible = False
End If
If Len([AirTravelLocator1] & "") = 0 Then
AirTravelLocator1.Visible = True
Else
AirTravelLocator1.Visible = False
End If
If Len([FlightNumber1] & "") = 0 Then
FlightNumber1.Visible = True
Else
FlightNumber1.Visible = False
End If
'SPACE
If Len([Airline2] & "") = 0 Then
Airline2.Visible = True
Else
Airline2.Visible = False
End If
If Len([AirTravelDate2] & "") = 0 Then
AirTravelDate2.Visible = True
Else
AirTravelDate2.Visible = False
End If
If Len([AirTravelDepartLocation2] & "") = 0 Then
AirTravelDepartLocation2.Visible = True
Else
AirTravelDepartLocation2.Visible = False
End If
If Len([AirTravelDepartTime2] & "") = 0 Then
AirTravelDepartTime2.Visible = True
Else
AirTravelDepartTime2.Visible = False
End If
If Len([AirTravelArriveLocation2] & "") = 0 Then
AirTravelArriveLocation2.Visible = True
Else
AirTravelArriveLocation2.Visible = False
End If
If Len([AirTravelArriveTime2] & "") = 0 Then
AirTravelArriveTime2.Visible = True
Else
AirTravelArriveTime2.Visible = False
End If
If Len([AirTravelLocator2] & "") = 0 Then
AirTravelLocator2.Visible = True
Else
AirTravelLocator2.Visible = False
End If
If Len([FlightNumber2] & "") = 0 Then
FlightNumber2.Visible = True
Else
FlightNumber2.Visible = False
End If
'SPACE
If Len([Airline3] & "") = 0 Then
Airline3.Visible = True
Else
Airline3.Visible = False
End If
If Len([AirTravelDate3] & "") = 0 Then
AirTravelDate3.Visible = True
Else
AirTravelDate3.Visible = False
End If
If Len([AirTravelDepartLocation3] & "") = 0 Then
AirTravelDepartLocation3.Visible = True
Else
AirTravelDepartLocation3.Visible = False
End If
If Len([AirTravelDepartTime3] & "") = 0 Then
AirTravelDepartTime3.Visible = True
Else
AirTravelDepartTime3.Visible = False
End If
If Len([AirTravelArriveLocation3] & "") = 0 Then
AirTravelArriveLocation3.Visible = True
Else
AirTravelArriveLocation3.Visible = False
End If
If Len([AirTravelArriveTime3] & "") = 0 Then
AirTravelArriveTime3.Visible = True
Else
AirTravelArriveTime3.Visible = False
End If
If Len([AirTravelLocator3] & "") = 0 Then
AirTravelLocator3.Visible = True
Else
AirTravelLocator3.Visible = False
End If
If Len([FlightNumber3] & "") = 0 Then
FlightNumber3.Visible = True
Else
FlightNumber3.Visible = False
End If
'SPACE
If Len([Airline4] & "") = 0 Then
Airline4.Visible = True
Else
Airline4.Visible = False
End If
If Len([AirTravelDate4] & "") = 0 Then
AirTravelDate4.Visible = True
Else
AirTravelDate4.Visible = False
End If
If Len([AirTravelDepartLocation4] & "") = 0 Then
AirTravelDepartLocation4.Visible = True
Else
AirTravelDepartLocation4.Visible = False
End If
If Len([AirTravelDepartTime4] & "") = 0 Then
AirTravelDepartTime4.Visible = True
Else
AirTravelDepartTime4.Visible = False
End If
If Len([AirTravelArriveLocation4] & "") = 0 Then
AirTravelArriveLocation4.Visible = True
Else
AirTravelArriveLocation4.Visible = False
End If
If Len([AirTravelArriveTime4] & "") = 0 Then
AirTravelArriveTime4.Visible = True
Else
AirTravelArriveTime4.Visible = False
End If
If Len([AirTravelLocator4] & "") = 0 Then
AirTravelLocator4.Visible = True
Else
AirTravelLocator4.Visible = False
End If
If Len([FlightNumber4] & "") = 0 Then
FlightNumber4.Visible = True
Else
FlightNumber4.Visible = False
End If
'SPACE
If Len([Airline5] & "") = 0 Then
Airline5.Visible = True
Else
Airline5.Visible = False
End If
If Len([AirTravelDate5] & "") = 0 Then
AirTravelDate5.Visible = True
Else
AirTravelDate5.Visible = False
End If
If Len([AirTravelDepartLocation5] & "") = 0 Then
AirTravelDepartLocation5.Visible = True
Else
AirTravelDepartLocation5.Visible = False
End If
If Len([AirTravelDepartTime5] & "") = 0 Then
AirTravelDepartTime5.Visible = True
Else
AirTravelDepartTime5.Visible = False
End If
If Len([AirTravelArriveLocation5] & "") = 0 Then
AirTravelArriveLocation5.Visible = True
Else
AirTravelArriveLocation5.Visible = False
End If
If Len([AirTravelArriveTime5] & "") = 0 Then
AirTravelArriveTime5.Visible = True
Else
AirTravelArriveTime5.Visible = False
End If
If Len([AirTravelLocator5] & "") = 0 Then
AirTravelLocator5.Visible = True
Else
AirTravelLocator5.Visible = False
End If
If Len([FlightNumber5] & "") = 0 Then
FlightNumber5.Visible = True
Else
FlightNumber5.Visible = False
End If
'SPACE
If Len([Airline6] & "") = 0 Then
Airline6.Visible = True
Else
Airline6.Visible = False
End If
If Len([AirTravelDate6] & "") = 0 Then
AirTravelDate6.Visible = True
Else
AirTravelDate6.Visible = False
End If
If Len([AirTravelDepartLocation6] & "") = 0 Then
AirTravelDepartLocation6.Visible = True
Else
AirTravelDepartLocation6.Visible = False
End If
If Len([AirTravelDepartTime6] & "") = 0 Then
AirTravelDepartTime6.Visible = True
Else
AirTravelDepartTime6.Visible = False
End If
If Len([AirTravelArriveLocation6] & "") = 0 Then
AirTravelArriveLocation6.Visible = True
Else
AirTravelArriveLocation6.Visible = False
End If
If Len([AirTravelArriveTime6] & "") = 0 Then
AirTravelArriveTime6.Visible = True
Else
AirTravelArriveTime6.Visible = False
End If
If Len([AirTravelLocator6] & "") = 0 Then
AirTravelLocator6.Visible = True
Else
AirTravelLocator6.Visible = False
End If
If Len([FlightNumber6] & "") = 0 Then
FlightNumber6.Visible = True
Else
FlightNumber6.Visible = False
End If
'SPACE
End Sub

 

[BlueChicken]

Actually there is an error. I went back into the print view and noticed that both of my date fields, =Now(), were showing as Name errors. In design view they took on the coding of some of the other formulas, and when I tried to change it back to =Now() Access gives me the error: "There was an error compiling this function. The visual basic module contains a syntax error. Check the code then recompile it."

So... now I am MAJORLY confused...

 

[SOS]

With repeating fields your database has more problems than this not working. You have a non-normalized database here and that is bad, bad, bad.

Perhaps a good read would help as you need to do some redesign before going further.

 

[BlueChicken]

With repeating fields your database has more problems than this not working. You have a non-normalized database here and that is bad, bad, bad.

Thanks for the link! The section on Input mask helped me reformat part of my form! However... the way I organized my data actually really works for me. I am always careful I put the right number but, really with the data I am working with its not like I cannot repeat, since having 6 airline information slots to begin with is repeating. Changing the names between them with more than just different numbers would really throw me for a loop, and anyone else who takes up the project to enter data.

I do sort of know what I am doing - after all I did get all the way to the report (which is working just not disappearing) on my own, and I even managed to put together the code (though it isn't working... but all the numbers are correct! ).

 

[WayneRyan]

Blue,

SOS is right. The repeating fields are really presenting problems for you.
And they'll continue to get worse as things progress.

Two points:

1) The flight data should be in a child table.

Advantages:  Use as many or as few flights as you desire (why limit to 6?).

             You can use a subreport for the flights, the subreport can shrink
             to remove the "extra white space".

             Research is much easier: (Who's flown on Southwest airlines?)

                 Select A.EmployeeName
                 From   YourMainTable As A Inner Join YourFlightTable As B On
                           A.TripID = B.TripID
                 Where  B.AirLine = 'Southwest'


2) Your repeating fields will tend to leave empty white space on your letter.
The textboxes will always be there and may not be what you visually desire.

Also, research is very difficult:

                Select EmployeeName
                From   YourMainTable
                Where  Airline1 = 'Southwest' Or
                       Airline2 = 'Southwest' Or
                       Airline3 = 'Southwest' Or
                       Airline4 = 'Southwest' Or
                       Airline5 = 'Southwest' Or
                       Airline6 = 'Southwest'

If you added another criteria like --> "Who's flown Southwest in the last three
months?" your lines of code get out of control quite fast.

In the long run, you really should break the flight data into its own child table.
You'll spend more time writing code with your current setup as opposed to
very little with the right design.

hth,
Wayne

 

[BlueChicken]

Ok, part of that makes some sense to me - however I still need to be able to produce a report showing someone's airlining, hotel, and rental car all on one sheet in an organized letter type format so that I am able to send that to them with all of their travel information for only that particular trip.

Each travel request is issued a number and that is the key I am using in the form itself, making multiple forms (one for each section like hotel, airline, and rental car) only works for the hotel and rental car because all the flights would have the same request ID number.

It also means having to go to multiple places just to enter one employee's travel information...

And merging all of those separate sections onto a report - by which I do not know how I would organize that...

Also, I limited the airline at six because our employees only fly in two directions (there and back). There are six because on average, our flights have anywhere from 0 layover stops to two layover stops - causing the number of "flights" to range between 2-6 depending on the connections for each flight.