Change Caption Problem

[kirkm]

Hi DBGuy, ok it's a class module. Thanks.

 

[CJ_London]

The Form shows in datasheet mode.

you are viewing in datasheet? The column heading will take the controlsource of a bound control, or if the control has a bound label, the caption of that label. datasheets do not display unbound controls.

this comment

In design mode it shows 4 labels in the Form Header and 4 Text Boxes in the detail. The Form shows in datasheet mode.

means the labels are unbound

 

[kirkm]

Thanks CJ that may get me a bit further. I know have

Function ChooseComposer(sArtist, sTitle) As DAO.Recordset
    'Union Query to get Composer from CDTracks and mp3Tracks
    Dim sql As String
    Dim Cap1 As String
    Dim rs As DAO.Recordset
    Cap1 = "Composer for " & sArtist & Delim & sTitle
    sArtist = QuoteIt(CStr(sArtist))
    sTitle = QuoteIt(CStr(sTitle))
    sql = "SELECT MP3Tracks.TPerformer as Artist, MP3Tracks.TTitle as Title, MP3Tracks.mComposer as Composer, MP3Tracks.Year as Year"
    sql = sql & " FROM MP3Tracks Where MP3Tracks.TTitle =" & sTitle
    sql = sql & " UNION SELECT CDTracks.TPerformer as Artist , CDTracks.TTitle As Title, CDTracks.TComposer As Composer, CDTracks.[T(P)] As Year"
    sql = sql & " FROM CDTRacks Where CDTracks.TTitle =" & sTitle
    Set rs = CurrentDb.OpenRecordset(sql)
    If rs.RecordCount <> 0 Then
       ' Form_frmExtSingles.[xLabel Label].Caption = "Composer"
            Dim frm As Form
              Set frm = Form_frmExtSingles
             ' Set frm = CurrentProject.AllForms("frmExtSingles")
                    With frm
                        !Label.ControlSource = "Composer"
                        .RecordSource = sql
                        .TheCaption = Cap1
                        .Sizer
                        .Visible = True
                    End With
            Set frm = Nothing
        Else
            MsgBox "No Matches"
    End If
    Set rs = Nothing
End Function

It still doesn't change the column name though. The function doesn't have to return anything, it was older code I'd modified. It just has to open the Form, I was to change that but then got stuck with the name problem. Also the recordset is just to get a count, so if zero don't open the Form. There may be a better way to test for that.

 

[CJ_London]

really confused about what you are trying to do, where you are calling this code from, what type of form you have.

Label is a poor name for a control - and if it is a label, it won't have a control source

 

[kirkm]

It's a popup Form launched by d-click on another Form. "Label" is a correct description. It's not critical, I can live with its caption not being"Composer", but would have been handy to know why it couldn't be changed. If column heading will take the controlsource of a bound control wouldn't instruction "!Label.ControlSource = "Composer" do that ?

 

[Isaac]

@kirkm
Can you post a dumbed-down version of the database, demonstrating where you have used Form_FormName.ControlName.Caption="something" and it did not work? Remember I am not referring to your original method of declaring a form variable, setting it, and using a with statement. I am specifically saying: Form_Formname.Controlname.Caption="something"

Note - Arnel is right, too, double check that the form's HasModule property is set to Yes, which it generally is if you've done any coding behind it, but a newish form that little has been done to it yet, they actually start out as No.

 

[kirkm]

I think I have a dumbed version. Open Form1 and click button. Another Form opens and one field is headed "Label"
I want to change this to "Composer" in Function ChooseComposer (if recordCount > 0).
I couldn't put Form_frmExtSingles.Label.Caption = "something" as it errored with Method or data member not found so I remmed it out.
HasModule is Yes.

 

[isladogs]

I've only skim read this lengthy thread so apologies if I'm repeating anything that's already been written.
First of all I would normally change a caption from the form itself, not a calling form.

However, if you want to do it like this then you need to first open frmExtSingles in design view and preferably hidden.
Then use code to change the label caption using the correct name for the label and and syntax shown below.
Then save the form and open normally

The following works for me (tested!)

    If rs.RecordCount <> 0 Then
        DoCmd.OpenForm "frmExtSingles", acDesign, , , , acHidden
        Forms!frmExtSingles.Form.[xLabel Label].Caption = "Composer"
        DoCmd.Close acForm, "frmExtSingles", acSaveYes
        DoCmd.OpenForm "frmExtSingles"
    Else
        MsgBox "No Matches"
    End If

All other code in the If clause is redundant (and some of it was incorrect)

Also, it would of course be much better to just name the field as Composer in the SQL rather than calling it Label (which of course is a reserved word). If you did that, none of the code would be needed!

 

[Isaac]

I think I have a dumbed version. Open Form1 and click button. Another Form opens and one field is headed "Label"
I want to change this to "Composer" in Function ChooseComposer (if recordCount > 0).
I couldn't put Form_frmExtSingles.Label.Caption = "something" as it errored with Method or data member not found so I remmed it out.
HasModule is Yes.

Well, that was easy. Check out your database. There is now a button called "new button". It successfully loads the referenced form and changes its label caption to "something", using exactly the method I suggested.

And why in the world do you have spaces in the name of your controls? Never do that.

 

[Pat Hartman]

I still can't figure out what you are trying to do 20 posts in. I gave you an example of how to change a form's caption. But, you never addressed my initial comment. Are you trying to make a permanent change or are you trying to change a caption on the fly based on some piece of data?

It would be helpful if you said something like:

I would like to change the caption on controlXYZ to "Composer" if the typeCD = "Composer". Otherwise it should be "XYZ"

IF you are trying to make a permanent change using VBA, stop. You can't be updating objects on the fly. This is seriously poor practice.

Therefore, we are left with changing the caption of a field based on some data value somewhiere. This should ALWAYS be done within the form's own code module, not from some other object.
This Code:
Form_frmExtSingles.[xLabel Label].Caption = "Composer"
is referencing a caption from OUTSIDE of the form. It will NOT work if the form you are changing is not open. That is why the change should be coded within the form itself. Either pass in the value you want to use as the caption or find some other way for the form go obtain the information.

Where is the data value? What are the options for the Caption name?

Your code will ultimately look like:

If somecondition Then
    Me.SomeLabel.Caption = "Composer"
Else
    Me.Somelabel.Caption = "somethingelse"
End If

You said you weren't trying to change the form's caption and yet that is exactly what Isaac's example is doing. It just uses a lot more code. Call me confused but don't call me late for dinner

 

[kirkm]

Colin and Isaacs changes/suggestions don't do anything if frmExtSingles is Open with view the required acFormDS.
I've since found those 4 label controls aren't actually used and can be removed. Access put them there when teh Form was designed.

Pat, the change is conditional. I did try to explain it by saying one field is headed "Label" I want to change this to "Composer" if the recordCount > 0 meaning the Form was wanted).

Someone said the field heading or caption takes on the controlsource of a bound control. This suggested the instruction !Label5.ControlSource = "Composer" would also change the column heading to 'Composer'. (I'd first thought it got this from the label controls caption.) Both are wrong. The field heading is the Name of the control.
So all that is needed to to rename the control. (Which I'm not sure how to do yet).
Why not name it so to start with? Because the same Form is used for something else. Maybe the ideal solution is two separate Forms.

 

[Galaxiom]

Colin and Isaacs changes/suggestions don't do anything if frmExtSingles is Open with view the required acFormDS.
I've since found those 4 label controls aren't actually used and can be removed. Access put them there when teh Form was designed.

Sounds like you deleted the system generated labels that are associated with the controls then added your own labels and expected them to be displayed as the datasheet labels. The associated labels are defined in a control's Control Collection which only has one read only item.

 controlname.Controls(0)

Someone said the field heading or caption takes on the controlsource of a bound control. This suggested the instruction !Label5.ControlSource = "Composer" would also change the column heading to 'Composer'. (I'd first thought it got this from the label controls caption.) Both are wrong. The field heading is the Name of the control.

The field headings in datasheet definitely are normally the associated label captions. I expect the Datasheet label is the ControlName only when there is no associated label.

 

[kirkm]

> I expect the Datasheet label is the ControlName only when there is no associated label.

OK, can try that. How is a label associated with a control and where in the Form does it go, the detail or somewhere else?

 

[Galaxiom]

How is a label associated with a control and where in the Form does it go, the detail or somewhere else?

The associated label is defined in the control's own Controls Collection (different from the Form's Controls Collection). I mentioned this in the previous post.

From a programming point of view this is handy because the label properties can be modified as a property of the control when making changes to the control.

Me.controlname.Controls(0).Caption = "something"

When originally placed, the control and label are linked together and moving one moves both the control and associated label unless you grab the little handle at the top left corner of the control or label to move them separately. It doesn't matter where the label is placed on the form when it is being displayed in datasheet view.

Once you delete the associated label it cannot be reassociated. You would need to place a new control to restore the associated label.

BTW Many developers prefer a simulated datasheet implemented as a Continuous Form. Cut and Paste the labels from the details section into the header section. Then put the textboxes into a line and at the top of the detail and bring up the bottom of the details section up to just below them.

An advantage of this structure is you can manage fully the appearance of the controls, even have what is effectively a two line datasheet and have other controls in the header and footer.

 

[kirkm]

I removed the control and reapply it with it's label. This greatly simplified the column header change

Private Sub Form_Load()
    If Left(Me.Caption, 12) = "Composer for" Then
            Me.Label13.Caption = "Composer"
        Else
            Me.Label13.Caption = "Label"
    End If
End Sub

I will experiment with continuous Forms. Never used them so far. Looks a good idea.

 

[Pat Hartman]

That's essentially the code I suggested in post 11 - twenty-five posts ago! You said you were not changing the caption of the form.

 

[Uncle Gizmo]

Sounds like you deleted the system generated labels

If Greg is correct you can reconnect a label like this:-

Attach a Label – Nifty Access

www.niftyaccess.com

 

[kirkm]

Thanks for the tip about attaching. Pat that code worked after I'd associated the label with the text box control. Originally it wasn't,
It doesn't change the Form caption though, but the label caption.

 

[Galaxiom]

If Greg is correct you can reconnect a label like this:-

Ah yes I had forgotten about the unattached label dialog (which I assume your video is about.). I turned of that warning in the Access settings a long time ago because I used unattached labels.