Create a label in form header using vb (1 Viewer)

[liddlem]

Hi all
The following code creates a label in the BODY of my form.

Set ctl = CreateControl(FormName:=strForm, ControlType:=acLabel,  Left:=MyWidth, Top:=0, Width:=1440, Height:=288)

But I want to add the label in the HEADER section.
Any ideas how I do that?

PS: While I'm at it, is there a way to script the records 'BeforeUpdate' event.(or edit the existing event it it exists)
I want the new field to be updated automatically each time that the user changes the record.

 

[ashleedawg]

Hi liddlem,

Is there a reason you need to create the form programmatically? Controls can only be added to a form in Design View, which might be easiest manually: Right-click the forms' background for options to display the header or footer sections. Then on the ribbon at the top of the screen, click on the Design menu. In the Controls section, click Label, and draw the new control in the Header section (or elsewhere as required).

If the label does indeed need to be added to the form by the user at runtime, you might better off to create the control in Design view, and set it to Visible=No until you need it to appear.

The CreateControl method you mentioned lets you choose which section to place a control, in this case: Section:=acHeader

CreateControl(formname, controltype[, section[, parent[, columnname[, left[, top[, width[, height]]]]]]])

The full description and examples are at: https://msdn.microsoft.com/en-us/library/office/aa221167(v=office.11).aspx

Let me know if this makes sense or if I misunderstood what you're trying to do!

 

[Cronk]

I've never seen need to create a new control on a form at run time. If I'm using a form in different scenarios where the control is needed or not, I set the visible property to yes/no at run time.

According to MSDN the syntax for CreateControl is
CreateControl( ** FormName, ** ControlType, ** Section, ** Parent, ** ColumnName, ** Left, ** Top, ** Width, ** Height** )

So you could try adding
Section:=acPageHeader

I don't understand what you mean by "to script the records ". What is it you are wanting to do?

 

[Galaxiom]

I'm guessing that you are trying to build a form dynamically to respond to changes in the RecordSource, after adding new fields to the underlying table.

If so, you have a serious problem with the data structure. One does not change the fundamental design of a form at runtime. Adding and removing controls at runtime is also limited by the maximum total number of controls in the life of a form (~750). Once this limit is reached the form can no longer be edited.

Tell us more about your project and we can determine if you are doing it in an appropriate way.

 

[liddlem]

Hi All. You Are Correct in saying That I Should Not Be Editing A Form By Script.
I'm Trying To Add A 'updated By User' Field So We Can Track Where Things Are Going Wrong.
I Have Updated All The Tables And Queries, But Now It's Time For The 200+ Forms.

I Also Want To Add Code To The 'before Update' Event Of Every Form.
Me.updatedby = Environ ("username")
Would Be Great If I Could Script That At The Same Time That I Add The Control.

 

[liddlem]

Hi All
After much to-ing and fro-ing, I eventually realised that I need to check each form individually.
So I had to inspect each form manually and decide whether
A. The additional field was required or not AND if so, then
B. If there was already code in the BEFORE UPDATE event, then where to apply the necessary code.

For those that might need to do something similar in future, I eventually got it sorted by 'STOP'ping my code at the appropriate places and editing the form accordingly.
In the end it was MUCH quicker than if had to open each for manually and add the field manually.

Function AddUsrField()
Dim strCtl As String
Dim frm As Form
Dim MyFrm As Object
Dim ctl As Control
Dim Ctlabel As Control
Dim strForm As String
Dim f As Object
Dim MyWidth As Integer

For Each MyFrm In CurrentProject.AllForms
strCtl = "UPDTUser"
DoCmd.OpenForm MyFrm.Name, acDesign
'Stop
Set f = Forms(MyFrm.Name)
MyWidth = f.Width
MyWidth = MyWidth + 50
strForm = f.Name
'CREATE THE TEXT BOX
Stop 'here to check if the new field is required on this form
Set ctl = CreateControl(FormName:=strForm, ControlType:=acTextBox, _
Left:=MyWidth, Top:=40, Width:=1100, Height:=288)
ctl.Name = strCtl
Forms(strForm).Controls(strCtl).DefaultValue = "=getWinUser()"
Forms(strForm).Controls(strCtl).Enabled = False
Forms(strForm).Controls(strCtl).TabStop = False
Forms(strForm).Controls(strCtl).ControlSource = "UPDTUser"
strCtl = strCtl & "_Label"
'CREATE THE LABEL
Set Ctlabel = CreateControl(FormName:=strForm, Section:=acHeader, ControlType:=acLabel, _
Left:=MyWidth, Top:=0, Width:=1100, Height:=288)
Ctlabel.Name = strCtl
Forms(strForm).Controls(strCtl).Caption = "Updated By"

Stop 'here to add the BEFORE UPDATE event..... Me.UPDTUser = Environ("username")
DoCmd.Close ObjectType:=acForm, ObjectName:=strForm, Save:=acSaveYes
Next
End Function

 

[ashleedawg]

I'm glad you got it sorted out.

Just out of curiosity, was it necessary for the username to be visible on the form? If not, you could have made a sub (ie, "logUser") to insert a record into a log table (one table for all forms, since the form name would be saved too) the name of the form, username, computername, date, recordID# etc, and then run a temporary "For Each MyFrm In CurrentProject.AllForms" sub like you did - to add the "logUser" sub to any of the current/load/unload/activate/etc events (whichever one was free).

Or, instead of updating all the subs, couldn't you have just added a UserName field to the tables with a default value of = Environ ("username") ? ...or perhaps if the tables are in a separate back-end that would only return who's logged on to THAT computer.

200 forms huh? Fun stuff!