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
O 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?
I 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
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?
Ocan'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?
That 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
Oused 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,
Oused 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.