Text box visibility problem in a form

[MATORAX]

I've got a database which is based on several tables: one of them is called "Natural person" and another is called "Legal person". The tables store information about clients. I've also got a query which is supposed to join data from those tables and it gives something like that:

Client_ID       Name    Surname    Company_name
1               Jeff    Smith
2                                  TechCorp
3               Kate    White
4               John    Ritchie
5                                  Flower Shop

Now you can see that some fields here are empty - for example Company_name in first record.

I've got a form which refers to this query and inside it there is a combo box and some text boxes. Combo box is used to choose a Client_ID, and the text boxes are used to display information about client (Name, Surname, Company_name) from records. I want to be able to hide unnecessary text boxes when the fields are empty - for example: when the Company_name is empty then I want to hide a text box which is related to this field an so on.
I try to do this with code below (which is placed in Form_Current):

If IsNull(Company_name.Value) Then
        Me.Company_name.Visible = False
        Me.Name.Visible = True
        Me.Surname.Visible = True
Else
        Me.Company_name.Visible = True
        Me.Name.Visible = False
        Me.Surname.Visible = False
End If

The code above works but there's a glitch and I can't find out what is creating it. Example: When I open a form - everything is visible like it should be (Name, Surname - visible, Company_name - not visible), when I change combo box value to 2 - everything is good (Company_name - visible, Name, Surname - not visible), but when I change combo box value from 2 to 1 or from 5 to 1 - then the Company_name text box isn't hiding and I can see an empty text box, while other text boxes are still invisible (but they should be visible, while Company_name text box shouldn't).

Please help me find a way to get rid of this problem - I am a beginner in Access, an I don't know what to do.
PS: Sorry for my English.

 

[arnelgp]

try another approach:

If Trim(Company_name.Value & "") = "" Then
Me.Company_name.Visible = False
Me.Name.Visible = True
Me.Surname.Visible = True
Else
Me.Company_name.Visible = True
Me.Name.Visible = False
Me.Surname.Visible = False
End If

also this will work for single and datasheet form on combobox after update event together with form's current event.

for continuous form it will not work since hiding a textbox (name/or surname) will hide all (name/surname) since there is only one instance of these textboxes on the form.

 

[CJ_London]

Or another one

Company_name.Visible <>nz(Company_name)=""
Name.Visible = nz(Name)=""
Surname.Visible = nz(Surname)=""

Note that Name is a reserved word so using it can cause unexpected issues - suggest change to forename or similar

 

[MATORAX]

I tried to change the code as you suggested but with no luck. Nothing changed with the glitch I mentioned earlier. When I change location of the code to combobox after update section then the text boxes don't hide at all.

 

[arnelgp]

what is your form continues (multiple records), single record form, datasheet.

 

[MATORAX]

Single record form - I've tried so many ways to do this but none of them worked. Code mentioned in the first post works if I navigate through records by using buttons (I made another form to check it - it is also linked to query from first post).

EDIT: OK, that's really, really weird but when I changed the visibility of all text boxes to false in Form_Load, and inserted the code from first post in AfterUpdate section of the combo box it started working just fine. I really don't know why but now it is working like a charm.