Control set to visible depending on the text value of another control (1 Viewer)

[PhilTFC]

Hi i am sure this is a easy question for you guys

I need a [Location] control on a form when = "First*"or"Arriva*" set [Invoiced] on the sameform to visible

this is the code i am trying

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Me.Location.Value = "First*" or "Arriva*" Then
Me.Invoiced.Visible = True
Else: Me.Invoiced.Visible = False
End If
End Sub

any help will be greatly appreciated

 

[jdraw]

Are you getting an error?
Syntax wise you may need this

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Me.Location.Value Like "First*" or  _ 
   Me.Location.Value Like "Arriva*" Then
   Me.Invoiced.Visible = True
Else 
    Me.Invoiced.Visible = False
End If
End Sub

 

[PhilTFC]

Hi Jd
thank you for your reply.
I was not getting any error messages at all, the code i am using now looks like this:

Private Sub Location_BeforeUpdate(Cancel As Integer)
If Me.Location.Value Like "First*" Or Me.Location.Value Like "Arriva*" Then
Me.Invoiced.Visible = True
Else:
Me.JobNumber.SetFocus
Me.Invoiced.Visible = False
End If
End Sub

it works for the exact text ie "First" or "Arriva" but not "First some more text" or "Arriva some more text" could it have something to do with the wildcard "*" i am using?

 

[bob fitz]

Perhaps you need:

Like "Arriva'*'"

 

[PhilTFC]

Hi Bob

Thank you for your quick reply.

I have tried the "Arriva'*" and the result is the same as before :banghead:

 

[bob fitz]

Hi Bob

Thank you for your quick reply.

I have tried the "Arriva'*" and the result is the same as before :banghead:

What I actually suggested was "Arriva'*'"

 

[PhilTFC]

It seems the email reply and the reply on the web site are different.
The email suggest's "Arriva*'***'*"
whilst here it says Like "Arriva'*'" and "Arriva'*'" either way i have tried all combinations and get the same result

 

[bob fitz]

This should work:

Private Sub Location_BeforeUpdate(Cancel As Integer)
  If Left(Me.Location.Value, 5) = "First" Or _
     Left(Me.Location.Value, 6) = "Arriva" Then
     Me.Invoiced.Visible = True
  Else
      Me.Invoiced.Visible = False
  End If
End Sub

 

[PhilTFC]

I get exactly the same results with that code too
it is obvious i am doing something wrong somewhere

 

[bob fitz]

I have attached a db in A2003 mdb version that I used to test the code.
If you are unable to see whats wrong with yours, perhaps you could post a copy of your db in A2003 mdb format thay we can look at.

 

[PhilTFC]

Thanks Bob, that is exactly what i want to happen
The only difference is that my db has a Text box control ([Invoiced]) which its control source is [InvoicedHO] (Date/Time data type) from the same table as [Location] and is a date as opposed to your example being a check box. would this make a difference?

 

[bob fitz]

Thanks Bob, that is exactly what i want to happen
The only difference is that my db has a Text box control ([Invoiced]) which its control source is [InvoicedHO] (Date/Time data type) from the same table as [Location] and is a date as opposed to your example being a check box. would this make a difference?

I don't see why it should make any difference.
Try changing the name property of the text box to txtInvoiced and then change the name in the code.

 

[PhilTFC]

Still the same result only this time the [Invoiced] text box is always visible

 

[bob fitz]

Can you post a copy of your DB in A2003 mdb format please.

 

[PhilTFC]

good luck and thank you so much for your help

 

[bob fitz]

I have looked at your db. The code runs and hides/shows the textbox so I'm not sure what the problem is.
The code will only run if the value in the control called "Location" is altered.
Perhaps you need to put the code in the Forms On Current event to achieve the desired result?

 

[jdraw]

I see 2 textboxes in your controls

txtInvoiced and txInvoiced ? seems just to be an oversight --- not an issue

If I type Arriva in the Location textbox and move off that control, I do enter the Location_BeforeUpdate code (done with breakpoint ) and it does go through the code and displays the txtInvoiced.

If I type in another value (QQQQQ), it goes through the Location_BeforeUpdate code and "hides" the txtInvoiced.

OOOooops: I see Bob has posted while I was "testing".

 

[PhilTFC]

the txtInvoiced was originally for the lable which i changed to txInvoiced temporarily while i was testing the txtInvoiced in the code.

I have also tried the code in the OnCurrent event and still get the same results.
Maybe i should change it to a check box control.

Thank you for all your time and help and have a great Christmas and new year

Done it what a plonker i am lol.. I put the code in the OnCurrent event and forgot to delete the Private sub line

Thanks Bob for your perseverance you are a genius