Dynamically changing captions

[ryetee]

given a table of captions, this sort of logic - but it might be slowish for a lot of captions because of the number of dlookups

 form open or load event
  
dim ctrl as control
  
  for each ctrl in me.controls
    if ctrl.controltype = aclabel then
  
        'languagecodes, maybe English=1, german=2 French = 3
        newcaption = dlookup("caption" & languagecode,"captiontable","form = " & formname & " label = " & labelname)
         
        ctrl.caption =newcaption
     end if
 next

If i do this at load form time then max will be about 30 for the more complicated forms. If I do it once for each form at the beginning it will be a few hundred I would think!

 

[vbaInet]

DLookup() is going to be very sluggish and you don't want to keep your users waiting just to open a form. Still need the recordset.

You can't do this for all forms unless you open all the forms in design view, make the changes, then save and close the form. If you're packaging this to your users they most likely won't have Design View access.

 

[gemma-the-husky]

fwiw, I have a enhanced/standard MS Access switchboard with up to 25 items per screen

I have added code to the switchboard that checks each menu option against the setttings in user tables, to determine whether the item is visible/useable by that user.

this needs at least a couple of dlookups for each menu item.

The whole process is certainly slower that not having it, but it also doesn't take more than maybe 1/4 to 1/2 second to refresh all the options, so it's worth trying the concept to see if it is acceptable.

I am not sure whether obtaining a recordset of the relevant label captions, and then checking that would be quicker, instead of the dlookups (is that what you meant vbaInet?)

 

[vbaInet]

I am not sure whether obtaining a recordset of the relevant label captions, and then checking that would be quicker, instead of the dlookups (is that what you meant vbaInet?)

Something like that gemma-the-husky. As you know, a recordset will be lightning fast for this sort of thing. But there's no checking to be done here: just filter the recordset by applying the appropriate form name criteria in the WHERE clause, open it and loop through the recordset. Obviously no harm in trying the DLookup anyway.

As applications grow, one finds the need to perform a lot more custom setup prior to opening a form, so if at this stage you can make a process fast, then you might as well.

My other point is that, ryetee mentioned that he can do a bulk replacement on all the forms, however, this is only possible if each form is opened in design view, saved and closed, and as we all know most deployed applications cannot (or should not) be accessible in design view.

 

[ryetee]

Right I think I'm done
I have 3 tables with form names control names and languages (set this up by looping through all the forms and for each for looping around the controls and updating various tables if there was a caption present). From these tables I've created a query to give the values I need below.
For the system itself for each form load I pass form name and language to a a function.
I then use a recordset on the above query starting at the form name and ending if form name changes or I hit EOF. I then check to see if language is the same and if soI update the caption with fields from the query.
And it works!!
And lightning fast!!


Thanks for everyone that contributed to this especially to ivbanet and gemma etc

 

[vbaInet]

Good job!

 

[ryetee]

Good job!

Just hope the reports I need to do follow the same sort of logic!

 

[vbaInet]

Yes you should be fine but it depends on the way you created the Forms table. Did you use the MSysObjects table or did you actually create a separate table? If it's the former then it would have been a simple matter of entering the write criteria to filter for reports or forms. If it's the latter then you could include an extra field that will identify whether it's a form or a report.

 

[gemma-the-husky]

Something like that gemma-the-husky. As you know, a recordset will be lightning fast for this sort of thing. But there's no checking to be done here: just filter the recordset by applying the appropriate form name criteria in the WHERE clause, open it and loop through the recordset. Obviously no harm in trying the DLookup anyway.

As applications grow, one finds the need to perform a lot more custom setup prior to opening a form, so if at this stage you can make a process fast, then you might as well.

My other point is that, ryetee mentioned that he can do a bulk replacement on all the forms, however, this is only possible if each form is opened in design view, saved and closed, and as we all know most deployed applications cannot (or should not) be accessible in design view.

Yes, I see. Much better idea. Start from the table, rather than the form.

 

[ryetee]

Yes you should be fine but it depends on the way you created the Forms table. Did you use the MSysObjects table or did you actually create a separate table? If it's the former then it would have been a simple matter of entering the write criteria to filter for reports or forms. If it's the latter then you could include an extra field that will identify whether it's a form or a report.

Created a forms table.
After my unit testing (which works on most forms) I have encountered a problem!!
It doesn't work for subforms! My subforms all appear to be datasheets.

To set the caption on the forms that work I have
Forms(frmAny).Controls(control1).Caption = caption1
Fore sub forms I get an error 2450 - can not find the referenced form 'name'.

The name looks fine to me so is it because they are datasheets and if so what do I do or is it as simple as the form name should be the 'parent' form name and not the subform name?

 

[vbaInet]

Your subforms are also forms right? Here's a link on referencing:

http://access.mvps.org/access/forms/frm0031.htm

 

[ryetee]

Your subforms are also forms right? Here's a link on referencing:

http://access.mvps.org/access/forms/frm0031.htm

Well they're in the forms section and have the same wee little ICON as every other form in there. Only difference is default view is Datasheet on the sub forms.

I've got that link in my bookmarks! I'm not sure where to look in the table!! I'm basically in the subform and calling a function to update a control so i'm sub 1 in sub 1 so refence should be Me!ControlName which is the same being in a mainform and changing the control there. So same code shoudl work for forms and sub forms?? I note that there is an ! in there which I don't have.
I'll have a playaround!

Btw if I open the subform on it's own it works!

 

[vbaInet]

You should be in the main form and call the controls in the subform control. In your forms table you will need to somehow indicate those forms that are subforms and those forms that have subforms embedded in them.

From the main form:

Me.[COLOR="Blue"]SubformControlName[/COLOR].Controls("[COLOR="blue"]ControlName[/COLOR]").Caption

 

[ryetee]

You should be in the main form and call the controls in the subform control. In your forms table you will need to somehow indicate those forms that are subforms and those forms that have subforms embedded in them.

From the main form:

Me.[COLOR="Blue"]SubformControlName[/COLOR].Controls("[COLOR="blue"]ControlName[/COLOR]").Caption

mmm tricky as I've set the 3 tables up by looping through all the form objects tocreate a form row on my form table, and then for each of these i loop round the controls to set up the controls table and language caption table. In my load from the (sub) form logic I pass over the form name and do a find first with a recordset (a query) and then build up the expression i need with fields from the row. So basically I don't have the subform name available in the main form.
Does this make sense?

 

[ryetee]

OK Think I've cracked this
I have a separate function for subforms and instead of passing the name of the form as a string I pass over the name as a form itself (passing over Me).
Within the function I use formname instead of the string

So instead of
Call CaptionProcessing.fLoadForm(FormName, PublicPLorGB)
for Function fLoadForm(frmAny As String, fCountry As String)
with line Forms(frmAny).Controls(control1).Caption = caption1
I have
Call CaptionProcessing.fLoadFormforsubforms(Me, Formname, PublicPLorGB)
for Function fLoadFormforsubforms(subform As Form, frmAny As String, fCountry As String
and subform.Controls(control1).Caption = caption1

 

[gemma-the-husky]

Fwiw, the name of the actual form used in a subform is given by the sourceobject property. That should help you obtain the right captions.

 

[vbaInet]

Good to see that you're using your own initiative.

 

[ryetee]

Good to see that you're using your own initiative.

Every now and then I do! Just need pointing in the right direction sometimes!

Now for those reports.....

 

[ryetee]

Right got almost everything up and running! Most of the reports are displayed on screen and these all work fine (although I've not tested the one with a subreport yet!!) One of the reports is written straight to a spreadsheet
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFileName, -1

As per the forms I have in the On Load event a function that changes the headings/text on the report. This doesn't seem to work in this case and I'm left with the original text headings when the spreadsheet is open. I have stepped through the code and everything seems to work and in fact if I change this to be displayed on screen then the headings have been changed but when I subsequently export to a spreadsheet the headings revert to the default ones.

Any ideas?

 

[vbaInet]

OutputTo re-opens the report so any changes will get lost.

You will need to convert your labels to textboxes and use either a function that gets the new caption or hidden textboxes on a form by setting the Control Source of the textboxes to the function or hidden textboxes.

E.g.:

=GetCaption()

... will go in the Control Source of the textbox which replaces the label.