Changing names and labels

[Geewaagh]

Hi all,



I have a very simple database for scheduling. I have 14 columns, mon-fri, then mon-fri.

I manually change the titles of these columns before I do scheduling. Example of my column titles:
Mon-12, Tue-13, Wed-14, Thu-15, Fri-16….

Here is the problem. If I change the name of the field from Mon-12 to Mon-19, all my queries still work, however, when I use an existing report, my label for the field has not changed, it is still Mon-12.
Anyway to link for force a label to match the field name that changed?

Thanks
Greg

[TheShabz]

I'm not sure if you can put a formula into a label (I've never had to) and I'm not in a position to test this theory but try putting an iif statement as the label
=iif(field=mon-12, "Mon - 12", "Mon - 19")

Replace "field" with whatever the proper syntax is for whatever is holding the name of the field.

[maximus]

I assume that your report is bound to a table.

I have done the following:
1) Table Products
2) open table and Change the name of the first column BarCode.
3) the label over the text boxes will automatically change when the name of the field are changed.

What have I done:

1) change the name of all the Fields to Text1, Text2, Text3.
2) Remember Change the name and not the ControlSource.

No apply the following code on to the OnOpen Event of the report:

Private Sub Report_Open(Cancel As Integer)
Dim strlabelCaption As String
Dim strLabelCaption1 As String
Dim strLabelCaption2 As String
strlabelCaption = Me.Text1.ControlSource
strLabelCaption1 = Me.Text2.ControlSource
strLabelCaption2 = Me.Text3.ControlSource
Me.Label12.Caption = strlabelCaption
Me.Label13.Caption = strLabelCaption1
Me.Label16.Caption = strLabelCaption2
End Sub


Explanation:

I have used the ControlSource of the Field Text Boxes as the caption of the labels.

Refer the attached mdb:

How to use open report Products. See the Labels. Close the Report open, table Product Change the Field Names and open the report again and see the effect.

[maximus]

Please mark the thread solved if this solves the problem.

[Geewaagh]

I assume that your report is bound to a table.

I have done the following:
1) Table Products
2) open table and Change the name of the first column BarCode.
3) the label over the text boxes will automatically change when the name of the field are changed.

What have I done:

1) change the name of all the Fields to Text1, Text2, Text3.
2) Remember Change the name and not the ControlSource.

No apply the following code on to the OnOpen Event of the report:

Private Sub Report_Open(Cancel As Integer)
Dim strlabelCaption As String
Dim strLabelCaption1 As String
Dim strLabelCaption2 As String
strlabelCaption = Me.Text1.ControlSource
strLabelCaption1 = Me.Text2.ControlSource
strLabelCaption2 = Me.Text3.ControlSource
Me.Label12.Caption = strlabelCaption
Me.Label13.Caption = strLabelCaption1
Me.Label16.Caption = strLabelCaption2
End Sub


Explanation:

I have used the ControlSource of the Field Text Boxes as the caption of the labels.

Refer the attached mdb:

How to use open report Products. See the Labels. Close the Report open, table Product Change the Field Names and open the report again and see the effect.

Maximus,

Thank you for the response. I am somewhat of a newbie to Access, and this answer is somewhat confusing.

I have names in a column named Mon-12, Tue-13, etc.

I have a report that prints from a query based on that table (i just want to query an employee for those days.

The report list there name on all the collumns they show up.

I go in a change the column name in the table to Mon-17. However the report does not change the caption of the column to the new name. It stays as the title of the column before I changed the name.

Any ideas?

Thanks for the help
Greg

[Geewaagh]

is there a way to just tell the caption in a text label to equal the name of a field?

[TheShabz]

is there a way to just tell the caption in a text label to equal the name of a field?

That's exactly what his code does.

[Geewaagh]

That's exactly what his code does.

Did i menton I was a newbie?

1. What is "first column BarCode" ?
2. How do I create an "OnOpen Event " ?


Just at a lost at to what to do with this information.

Thanks again
Greg

[TheShabz]

1. Open the "properties" dialog box of your report while in design view. Make sure it is the properties for the whole report and not just an object in it.
2. event tab
3. go to the On Open and from the dropdown select [event procedure]
4. click the ellipses to the right. this will open up a VBA editor with a header "private sub (your reportname)_Open()"
and footer "end sub"
5. between those paste in the code in the above post:
Dim strlabelCaption As String
Dim strLabelCaption1 As String
Dim strLabelCaption2 As String
strlabelCaption = Me.Text1.ControlSource
strLabelCaption1 = Me.Text2.ControlSource
strLabelCaption2 = Me.Text3.ControlSource
Me.Label12.Caption = strlabelCaption
Me.Label13.Caption = strLabelCaption1
Me.Label16.Caption = strLabelCaption2

6. In the code, Text1 should be replaced with the name of your first textbox and Label12 with the name of that textbox's label.
Text2 and Label13 are paired the same way as are Text3 and Label16. The names can be found in the properties box when you click on each one.
7. save and close the VBA window
8. save and close your report
9. open your report and test.

[maximus]

Thanks TShabz for explaining the code so well. I made this post at 11.30 P.M. so I was in a sort of a Hurry.
Actually There is nothing to explain coz TShabz has done a wonderful Job here.

Suppose You have a Field in a table Mon-12
You Prepare a report with this table.
The Report will have a field Mon-12

Now every Field on the Report which I will call a Text Box Control Has a Name and a ControlSource.

When you use the report Wizard to create a Report and you report is Bound to a Table or a Query Both the Name and the ControlSource of the Text Box Controls are the same. in your Case both will be Mon-12

What is the Name of a TextBox?
It is an identification of the control that we use to refer to that specific control when we write codes. Its just like our names.
What is ControlSource?
ControlSource of a TextBox Box indicator to which Filed of the Table or a Query it is Linked to.

Now how you should proceed

Follow TShabz explaination, and you will make it.

Thanks again

[Datagopherdan]

Greg,

It sounds to me that your database is not normalized. You should not be having column names that you constantly have to change with the dates. It should be setup, so it's dynamic. If it's always a 2-week period then you should have something setup like a query that covers a 2-week period where the minimum value for a given day would be the in the first week and the maximum value for a given day would be in the second week. This way, you do not need to manually be changing fields.

Dan
Access Development

Hi all,

I have a very simple database for scheduling. I have 14 columns, mon-fri, then mon-fri.

I manually change the titles of these columns before I do scheduling. Example of my column titles:
Mon-12, Tue-13, Wed-14, Thu-15, Fri-16….

Here is the problem. If I change the name of the field from Mon-12 to Mon-19, all my queries still work, however, when I use an existing report, my label for the field has not changed, it is still Mon-12.
Anyway to link for force a label to match the field name that changed?

Thanks
Greg

[maximus]

Dan

You are absolutely right. changing names of fields is not a good practice. I have provided a solution to the problem but i too completely say such an exrercise is not a good practice.

[Geewaagh]

Greg,

It sounds to me that your database is not normalized. You should not be having column names that you constantly have to change with the dates. It should be setup, so it's dynamic. If it's always a 2-week period then you should have something setup like a query that covers a 2-week period where the minimum value for a given day would be the in the first week and the maximum value for a given day would be in the second week. This way, you do not need to manually be changing fields.

Dan
Access Development

Dan,

In excel, I have 14 columns with the dates in them. I have patents as rows. I then put in intials of my staff that needs to see those patents.

Every week, I open up excel, rename my columns to match current week, then modify my data to match the week's appointments. Most of them stay the same. Bu we have staff that calls in sick, new patents, etc. So, just tweaks.

I was hoping that access would make this easier. I have no good way of printing out individual staff schedules, look at a single patents, assign specials codes to patents, etc.

How would you set this up?

Thanks,
Greg

[Datagopherdan]

The way that I would set this up and I think Maximus and TheShabz would probably agree would be to get rid of Excel all together unless theres some absolute reason that you need it. The Excel portion should be setup in the database, so it can be in a relational model. This makes your reporting efforts much easier and alot more automated and efficient.

You have all your data in one location where it can be easily accessed and manipulated to give you the reporting requirements that you need.

Dan
Access Development

[Geewaagh]

The way that I would set this up and I think Maximus and TheShabz would probably agree would be to get rid of Excel all together unless theres some absolute reason that you need it. The Excel portion should be setup in the database, so it can be in a relational model. This makes your reporting efforts much easier and alot more automated and efficient.

You have all your data in one location where it can be easily accessed and manipulated to give you the reporting requirements that you need.

Dan
Access Development

Dan,

I agree but I am struggling to figure out how to make it work. Can you give me an idea of how I could set this up so when I sit down to make a schedule, the table/query shows the right dates and when i print out reports for staff, it shows the correct dates?

Sorry to ask such basic questions...

Greg