Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Why isn't this code changing the labels on a datasheet?

Posted on 2012-09-06
11
Medium Priority
?
416 Views
Last Modified: 2012-09-07
I have created a subform that contains a datasheet. I need to control which columns display and need to assisgn suitable labels on the form so I have created a table with the relevant data and I am using the following code in the ON LOAD event. I have renamed all the Labels as "Label" followed by the variable name. When I step through the code it seems to work but when the form displays the label names have not changed. Can anyone help me figure out what I am doing wrong?

Private Sub Form_Load()
    Dim rs As Recordset
    Dim sSql As String
   
    Set rs = CurrentDb.OpenRecordset("SELECT tblAbbreviations.* FROM tblAbbreviations ORDER BY Sequence;")
    sSql = "SELECT "
    With rs
        .MoveFirst
        Do Until .EOF
            Me.Controls("Label" & !FieldName).Caption = Nz(!Abbreviation, !FieldName)
            Me.Controls(!FieldName).ColumnHidden = Not !Show
            .MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing
End Sub
0
Comment
Question by:Rob4077
  • 6
  • 4
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38374669
can you upload a copy of the db?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38374674
The code you have should change the captions - provided that the labels are associated with the textboxes that contain the respective fields (ie: they are the default labels that are created automatically when textboxes are created).

If that association has been broken somehow (for example, by cutting and pasting the labels) then the datasheet captions will not change with the label captions.

Also worth checking that you are indeed getting the correct label names (you should be able to see this as you step through the code).
0
 

Author Comment

by:Rob4077
ID: 38374687
Hi Capricorn1 I will try to prepare a cut down of the db and add it later. I have to leave for an appointment now and it's a biggish application so I need to cut it down and that will take time.

mbizup, Is there are way to re-link the labels, in case they're not linked? I need to keep the names meaningful, rather than the default label23 type name. I would have thought they are linked because I have them laid out using tabular layout in design mode and the heading follows the textbox when i drag them to new positions.

NB I am using MS Access 2007
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Rob4077
ID: 38374694
I just tried adding a field (default name text72 and label73) then tried renaming the caption in VBA and it still didn't work.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38374723
If the labels move with the text boxes as you are describing then they are still linked, so there is something else amiss.
0
 

Author Comment

by:Rob4077
ID: 38375427
Ok, back on the job and it looks like the link wasn't working properly. I used the form wizard to create a new form for me, put my code in the load event (modified it slightly to pick up the labels correctly) and it works fine BUT, the wizard has turned my check box into a text box. If I try to add the same field again from the Field list it still delivers it as a text box. Any ideas????
0
 

Author Comment

by:Rob4077
ID: 38375428
BTW the source is a Union Query. If I run the query on its own the field in question shows as a check box.
0
 

Author Comment

by:Rob4077
ID: 38375436
oops, no it doesn't now. If I run the union query it is now a text box field even though the source table shows it as a check box. Is there any way I can change it back?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38375452
I hate the form wizard... Do you know how to add a checkbox manuall?

There should be a Design Tools box on the ribbon where you can click on a checkbox and add it to your form.  After doing that, just set the Control Source property of the checkbox to the correct field name from the recordsource (select it from the drop down list)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38375456
I dont have access in front of me but you may also be able to right click the textbox in Design view, select Change To... and see if Check box is among the options.
0
 

Author Comment

by:Rob4077
ID: 38375461
Hi mbizup, thanks for your comments. Your confirmation that my code should work led me to the try re-building the form and that led to the solution. I manually recreated the checkbox and it works. I don't know if the load event changes its name like the rest but I don't need to worry because it's one field name that never needs to change. Thanks for the solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question