Full Version: convert text to combo
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Zhang
I have seen a lot of topics here when I was searching for what I want to do

I have text control over combocontrol, the same width, height, top, left

I need to dlookup yes/no field in table and if yes make combo visible, if no make text visible

so I tried :

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Chye As Boolean
Chye = DLookup("ER", "tbl_subA", "AD= " & Me.AD)
If Chye = True Then
Me.EC.Visible = True
Else
Me.WE.Value = True
End If
End Sub


I get runtime error 2001 that states that I cancelled the previous operation
any idea
HiTechCoach
In the Before update event of the form the code will not run until you have entered data and attempt to save the record. Is that what you want?


Did you mean to have Me.WE.value not Me.WE.Visible


CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Chye As Boolean
Chye = DLookup("ER", "tbl_subA", "AD= " & Me.AD)

If Chye = True Then
   Me.EC.Visible = True
Else
   Me.WE.Visible = True
End If
End Sub



I would think you would want this code in the control AD's after update event.

Like this:

CODE
Private Sub AD_AfterUpdate()

Dim Chye As Boolean

Chye = DLookup("ER", "tbl_subA", "AD= " & Me.AD)

If Chye = True Then
   Me.EC.Visible = True

   Me.WE.Visible = False

Else
   Me.EC.Visible = False

   Me.WE.Visible = True
End If
End Sub



If you can view existing records with the form then you will need to also use the form's On Current event to set the same thing.

Example
CODE
Private Sub Form_Current()

Dim Chye As Boolean

If Me.NewRecord then

   Me.WE.Visible = False
   Me.EC.Visible = False


Else
  Chye = DLookup("ER", "tbl_subA", "AD= " & Me.AD)

If Chye = True Then
   Me.EC.Visible = True

   Me.WE.Visible = False

Else
   Me.EC.Visible = False

   Me.WE.Visible = True
End If

End If

End Sub
Zhang
thank you

I get compile error method or data member not found because of this .Visible , the on current event you set the WE & EC controls as invisible if new record so they supposed to be visible in the form, right?

I can't understand why I get this error and it is supposed not to get this error

thank you again
HiTechCoach
I am having to guess at how you what the form to work. Will you explain in more detail exactly what you are trying to do?

What line is generating the error?
datAdrenaline
Just some code suggestions, but in agreement with Boyds path ...

---

Since you have multiple events that could effect the visibility of your controls, then I would create a Private procedure in the code behind the form:

CODE
Private Function ControlVisibility() As Byte

    Dim Chye As Boolean

    Chye = Nz(DLookup("ER", "tbl_subA", "AD= " & Me.AD), False)
    
    Me.EC.Visible = Chye
    Me.WE.Visible = Not Chye

End Sub


Then, I would call that code in the events that need to use it. If the event that controls the visibility only has to call the ControlVisibility code (like for the AD after update event), then you can set the event property to call the code directly with a setting like this:

=ControlVisibility()

However, if the event has to do other stuff, or behave slightly differently (like the Forms Current event) then you would set the event property to "[event procedure]" and enter your code, but in that block of code be sure to call the ControlVisibility() procedure.

CODE
Private Sub Form_Current()

    If Me.NewRecord Then

        Me.WE.Visible = False
        Me.EC.Visible = False

    Else
        ControlVisibilty
    End If

End Sub
Zhang
hi, HiTechCoach & datAdrenaline
I used the code that you posted and both work fine

I used the code in continuious form so the control type changes for all the records in the subform, can I change it for one record?
Zhang
hi,


I used the code in continuious form so the control type changes for all the records in the subform, can I change it for one record not all the records?


thank you
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.