Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Option group to show/hide and clear fields

    Help, I'm stuck and out of my depth again.

    I am trying to use an option group on a data entry form to switch between a text field and an OLE object field.
    The fields sit on top of each other but only one can be visible at any one time.
    If the user want to switch to the other field type I need to clear any data entered in the other field
    (double checking with a message box that the user is certain this is what they want to do.)
    The reason for doing this due to lack of space on the report.
    I am hoping that the text field and OLE field can occupying the same space on the report, but only one will displayed, based on there being data for that field in the record.
    Only one of the fields can contain data for any one record, not both.



    The example below is based on a table called tblSites which contain three fields, the record ID, SiteText (the text field) and SiteObject (the OLE field).


    Private Sub FrameTextOrObject_AfterUpdate()

    ************************************************** ******************
    Alter the behaviour of the form acording to the option chosen. i.e The
    chosen option becomes enable whilst the other is disabled

    Dim response as Byte

    Select Case Me!FrameTextOrObject

    Case 1 'choose the text field option

    Dim response As Byte
    response = MsgBox("Changing the data type to TEXT will overwrite the object you have just entered. Are you sure you want to do this?", vbYesNo + vbQuestion, "Change option")
    If response = vbNo Then
    Exit Sub
    Else
    'some code to delete the object in SiteObject (OLE field)
    End If

    Me.SiteObject.Visible = False
    Me.SiteText.Visible = True

    Case 2 'choose the OLE field option

    Dim response As Byte
    response = MsgBox("Changing the data type to OBJECT will overwrite the text you have just entered. Are you sure you want to do this?", vbYesNo + vbQuestion, "Change option")
    If response = vbNo Then
    Exit Sub
    Else
    'some code to delete any text entered in SiteText (text field)
    End If

    Me.SiteText.Visible = False
    Me.SiteObject.Visible = True

    End Select

    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    maybe its just me but i'm not sure what the question is
    JerryC

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    You didn't say what the difficulty was so this may be totally irrelevant; however, try putting in me.repaint after setting the visible properties to see if that makes it work better.

    If that doesn't help, be more specific about what your problem is.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Option group to show/hide and clear fields

    First of all, are you working with a form or a report? You mention both, and it isn't clear which one you actually mean.

    You refer to a text field and an OLE Object field. Do you mean fields or controls? Fields can't "sit on top of each other", so I assume you mean controls bound to those types of fields. I don't understand why you would toggle between a text field and an OLE Object field at all. What would be in the OLE Object field and how would the user enter it? I see not particular reason to allow values in only one of the fields, but you haven't really explained what they contain, so I may be missing something that's obvious to you.

    As for showing or hiding them, you don't need an option group, a checkbox will work just as well, or a command button in the form header or footer. The easiest way to actually toggle them is like this (by the way, I don't like using the dot operator for controls, so I changed it):

    Me!SiteObject.Visible = Me!chkShow
    Me!SiteText.Visible = Not Me!SiteObject.Visible

    This assumes you used a checkbox called chkShow to toggle the controls. The first line says to set the visible property of SiteObject to the value of chkShow (true or false). The second line says to set the visible property of SiteText to the opposite of the visible property of SiteObject. so if chkShow is checked, SiteObject will be visible and SiteText will not be. If chkShow is unchecked, SiteObject will not be visible and SiteText will be.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    Hi,

    I may be of partial assistance here.

    To: 'some code to delete any text entered in SiteText (text field)

    Seems to me you can just assign an empty string: field=""

    To: 'some code to delete the object in SiteObject (OLE field)

    This is much more of a problem that I have tried at length, unsuccessfully I might add.
    In Help it says to click on Insert , select Objects to insert an object/link into OLE type field. That worked perfectly. But I haven't figured out how to do that in code.
    If I knew, I'd probably be able to figure out how to remove the object/link.

    I tried by assigning the OLE field to an obj type variable (which worked), then set the var=Nothing (which worked) and finally tried to 'set field=(empty) var' or just field=var, neither of the two worked. So, I soldier on to find this Holy Grail. Perhaps somebody knows how to assign a value to an OLE field from code.

    Finally, I think I would put the code to make the form controls visible/invisible inside the If..then...else construct.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Option group to show/hide and clear fields

    Don't try to set the field = "" unless you want an empty string stored in the field. That's the customary way to empty the Text property in VB and some of the Office apps, but not in Access. In Access, set the field to Null to clear it.

    As for the OLE Object, I don't generally use them, so I haven't tried to remove them, but you can't just set them to null. Keep in mind that the OLE Object represents a linked or embedded document that is still connected to the originating application. It wouldn't do you much good to empty out a Word document if it still existed, but without any text.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    Just some nit-picking with your code. What happens if the user closes the MsgBox with the 'x'.

    response = MsgBox("Changing the data type to TEXT will overwrite the object you have just entered. Are you sure you want to do this?", vbYesNo + vbQuestion, "Change option")
    If response = vbNo Then
    Exit Sub
    Else
    'some code to delete the object in SiteObject (OLE field)
    End If


    I'd reorganise it slightly and test explicitly for a yes:

    response = MsgBox("Changing the data type to TEXT will overwrite the object you have just entered. Are you sure you want to do this?", vbYesNo + vbQuestion, "Change option")
    If response = vbyes Then
    'some code to delete the object in SiteObject (OLE field)
    End If

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    Oops. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Access disables the 'x' for you (I've mixed the behaviour with that of another programming environment).

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    Firstly, I'm working with a data entry form. Later I need to figure out how to display either text or an object in the same physical space on a report.
    Secondly, I meant controls (it was a bit late late in the day and my head was a bit fuzzy).
    In addition to the textual content, the report for the database I'm working with is very graphical, containing CAD drawings, photographs and extracts of excel spreadsheets (all inserted as objects).
    The problem I've got is making it versatile enough to suit all users. Basically, in one place, there is only room on the report for text or an object - some users want to type a conclusion, others want to insert part of a spreadsheet.
    I thought if I could make sure they can only enter data in one or the other on the form, it would be easier to determine which is displayed on the report.
    I've got no problem showing or hiding the controls on the report, I just can't figure out how to clear the content of one control if the users changes their mind and opts to use the other, i.e. they type some text, then decide to insert an object.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Option group to show/hide and clear fields

    Don't bother. Just make the object dominate. In other words, test for the existence of an object. If there is one, show it regardless of whether there is text as well. Same thing in the report. If you feel you must remove the object, do it with Me!SiteObject = Null.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Option group to show/hide and clear fields

    Thanks Charlotte, for setting me straight on this "" vs Null thing.

    Since reading the posts for the last few months, I had developed the idea
    that assigning Null wasn't such a good thing.

    As far as the OLE field is concerned, I had considered it to contain strictly
    a pointer to the object, just like is done in a Set statement.
    Therefore, setting the field to NULL or Nothing was my attempt to remove
    the pointer value from the field.

    The book "Mastering Access 97", 4th edition, pages 1002-1005, contains quite a bit
    of info on how to work with embedded and linked objects on forms and in tables,
    but again, nothing that I could see how to do this with code.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Option group to show/hide and clear fields

    You can't always use Null. For example, starting with Access 97, you can't set a property to Null, only to an empty string or a value because the property values went from being variants to being strings as defaults. In non-Access VBA and in VB, you are usually working with the Text property of a control, rather than the Value property, so you reset the value of the Text Property to an empty string, since Text holds string values. In Access, when you're working with the Value property, you can set it to Null, unless you actually want an empty string in the field (not something you usually want to do). I think the semantic problem arises because Access is designed to work with bound controls, while VB and the rest of Office are not.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •