Option group to show/hide and clear fields

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Oops.
Access disables the ‘x’ for you (I’ve mixed the behaviour with that of another programming environment).

Reply | Quote