Creating text boxes in VBA code

[ray147]

Hi guys,

I would like to create text boxes using VBA code ....the reason is that I need a number of text boxes to be created that is equal to the number of rows that is generated by an SQL query.

Any suggestions?

Tnx!

 

[gromit]

Haven't done this on my own, so pardon if I just confuse things.

Take a look at the built in CreateControl function:
http://msdn.microsoft.com/library/d...baac11/html/acmthCreateControl_HV05186366.asp

Also, if you can create constraints on the number of additional text boxes, it may be cleaner to create the boxes in advance and hide/unhide as you go.

hth,

- g

 

[ray147]

hi,

thanks for your reply..appreciate

i tried it however it seems that method cannot be used during runtime, only in design view...

any other suggestions?!

 

[Summerwind]

Yes that's right, but it works.

Open your form in design view and hide it.
Create the controls (Text boxes) remembering that you have to provide all of the positioning and formatting details.

Close the form saving it in the process.

Open the form with the new controls and unhide it.

Simple ;-)

You can even run a macro when the newly created control is clicked etc, but what you can't do ( and its a pain that you can't) is run code when the new control is clicked if your code is password protected or you have an mde file.

 

[ray147]

hi tnx for yr reply..

i have form number 1 that feeds data into form 2. on form 1, after the user clicks a button, form 2 should open and construct text boxes according to the number of records generated by an SQL query..

i have the following on the OnClick event of form 1 in VBA, after reading yr post:

1. opened form 1
2. reading data from user
3. on button click , open form 2 in design view
4. hide form 2 using me.visible = false
5. create 1 control (to try) using CreateControl method

now i dunno how to close the form..am trying to use me.closeform but i get an error saying 'invalid use of property'...and also whilst form 2 is open and hidden in design mode, the 'me' does it refer to form 1 or 2?

tnx..

 

[mcirvine]

to clsoe form i think its

DoCmd.Close acForm, "Formname", ,

 

[ray147]

thanks guys!

i managed to get it to work...controls are being created dynamically on the form...now thing is that before the form closes from design view, it's prompting me to save changes or not, of course i would need to save or else the created controls will be lost..can i automate this save somehow?

also, let's say i manage to create all the controls as i require...is there some way that i can discard all the changes and remove all the controls again? coz i want these to be created dynamically each time the form is run, depending on the results of the SQL query i have...

 

[nateobot]

Be aware that there is a limit to the number of controls you can place on a form. I think it is around 750?

 

[Summerwind]

The close command you need is:

DoCmd.Close acForm,"formname",acSaveYes

You also need to set warnings off when you open your form in design view. Turn on again after you've saved your form.

Make the form visible, then open it.

I find its a good idea to refer to each form by its name when I'm manipulating more than one then VBA and I don't get co0nfused about which one I'm referring to. I have to do that because I'm just a poor Devon boy ;-)

Dealing with the temporary controls:
With the form that is going to receive the temporary controls, I set the Tag of each control that I always want to p and save the form.

When I open the form in design view and before adding the new controls I run a For Each loop on the controls in the form deleting any whose Tag is not = p. Then I add the new contols as above.

Hell!!! I should get paid for being this bloody clever

 

[ray147]

hi,

thanks for your replies!

i'm having a problem creating the controls though since i'm trying to attach a unique name to each to be able to identify them later...i have this:

RecCount = 1
If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF

Dim Create_Despatch("BookingType" & RecCount) As Object
Set Forms!Create_Despatch("BookingType" & RecCount) = CreateControl("Create_Despatch", acTextBox, acDetail, , , 270, 1400, 650, 270)

RecCount = RecCount + 1
rs.MoveNext
Loop



I'm getting a compile error 'Constant Expression Required'....but how can i attach a unique ID to each control if a constant have to be used?

 

[ray147]

summerwind,

i tried following your bloody clever tip by running a For Each loop to check the current controls and delete those that doesn't have the "p" tag...however for some reason not all of them are being cleared...it just deletes some random ones...the following is the code i have:

For Each ctl In Forms!Create_Despatch.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Tag <> "p" Then
DeleteControl Forms!Create_Despatch.Name, ctl.Name
End If
End If
Next

i tried putting :
If ctl.Tag <> "p" And Trim(ctl & "") = "" Or Trim(ctl & "") = 0 Then

but i get that the property is not available in design view


what should i do??!! tnx mates

 

[Summerwind]

Ah!!! I'm glad it happens to you too. VBA seems to miss a few controls in my apps as well. Seems it goes too fast and doesn't get them all. My rough and ready solution was to clear the form several times.

My code below clears out a form where I am using labels to simulate a bar graph, so there can be 100 or so controls. Here's my code:

Public Sub RemoveTemps()
On Error GoTo ErrorHere

Dim ctl As Control, i As Integer

DoCmd.OpenForm ("frmShowDay"), acDesign
For i = 1 To 5
For Each ctl In Forms!frmShowDay
If ctl.Tag <> "p" Then
DeleteControl "frmShowDay", ctl.Name
DoEvents
End If
Next
Next

DoCmd.Close acForm, "frmShowDay", acSaveYes


ExitHere:
Exit Sub
ErrorHere:
MsgBox Err.Description & " in procedure RemoveTemps", vbCritical, MessageHeader
Resume ExitHere
End Sub

 

[ray147]

btw guys,

managed to solve the problem of constant variable declaration...code looks as follows (Dim statement is not needed):

Set ctlText = CreateControl("Create_Despatch", acTextBox, acDetail, , , 150, tp, 650, 270)
ctlText.Name = ("BookingRef" & RecCount)



now still have the problem of how to delete the controls that are already on the form..as u can see on my previous post on this thread..

help obviously greatly appreciated!

 

[ray147]

hey summerwind,

tnx for your suggestion...this is so strange...i had to increase the 'i' value though coz of the number of controls..but yeah tnx!

but i still got a prob...i'm loading 12 controls per row of data output....it seems there's a limit on the number of controls coz i'm getting the error that Access cannot create any more controls on the form..

maybe the way i'm approaching the problem is not the best way? i've looked at continuous forms and tried it out..but thing is..i'm loading six fields through an SQL query (which can be done via continuous form) and another six blank fields to be input by the user..hence the 12 fields per row of output..

any ideas? really confused now....coz i thought this form looked gr8!