Re: Using VBA need to add a text box to a form

Works a treat .

Once again thanks for all your help. You were great as usual.

PS I also live in Perth.



"Allen Browne" wrote:

The loop will be created with:
Dim i As Integer
Dim strName As String
For i = 1 To 99 'however many
strName = "F" & i
Next

Since you already have a reference to txt, you can assign the format
conditions with just:
txt.FormatConditions.Add ...

I think if I were setting up this kind of import, I would create the form
with 255 text boxes correctly named but unbound and hidden. Then in the Open
event of the form, just assign the ControlSource of as many as you need, and
set their Visible property to True. You can select the whole lot and assign
a Format Condition through the interface at design time, and avoid the need
to do this programmatically at runtime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:35AAE46B-DE74-4EEF-A551-8FEA17007BD3@xxxxxxxxxxxxxxxx
Sorry Alan,

Let me try and explain it better. I have an import file that imports
excel
spreadsheets. Up to 200 at a time.

I have set up a system that gets the field header row. Since the
spreadsheets are not good at the best of times we have to import them as
F1,
F2, F3 etc.

Since we don't know how many fields each spread*** has over what we are
expecting we have a procudure written that just gets the header row.

This is what I am dealing with. I have the code that adds the fields to
the
row in a form but I don't have the Conditional Formating. What I am
trying
to do is a loop code for the conditional format set up

Code so far


Set lbl = CreateControl("tblExcel_Check", acLabel, acHeader, , ,
Leftt,
0, Widthh, Heightt)
lbl.Name = "Label" & Fieldss
lbl.Caption = "F" & Fieldss
lbl.TextAlign = Centre



Set txt = CreateControl("tblExcel_Check", acTextBox, acDetail, , ,
Leftt, 0, Widthh, Heightt)
txt.Name = "F" & Fieldss
txt.ControlSource = "F" & Fieldss
txt.SpecialEffect = Flat

XXXXXXXXXXXXXXXXXXXXXXXX

Need to replace F1 the a file name varible how do I do it?

as I loop through code next will be F2 Then F3 etc

XXXXXXXXXXXXXXXXXXXXXXXX

Forms!tblExcel_Check.F1.FormatConditions.Add acExpression, acEqual,
"[GroupOrderNo]=1"

Forms!tblExcel_Check.F1.FormatConditions.Item(0).BackColor = 15592886

Forms!tblExcel_Check.F1.FormatConditions.Add acExpression, acEqual,
"[F1YN]=0"

Forms!tblExcel_Check.F1.FormatConditions.Item(1).BackColor = 11051262




"Allen Browne" wrote:

txtCurrent is a string variable? You can only add format conditions to a
textbox or combo box.

I don't follow how the F1, F2, ... loop relates to the string variable.

I have no idea what error you are receiving when you do this.

I am not even sure you are doing this in design view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:80F0FA1A-9B37-4A5C-9C7B-0D5A1DF244A6@xxxxxxxxxxxxxxxx
Hi,

txtCurrent is a string and the Added fields are F1 to F unspecified
therefore have created a loop to add 1 on each pass. Loop goes F1, F2,
F3,
F4 etc to I need to add conditional Formattingf for each one in turn.

Have tried the following with no luck:-

Forms!tblExcel_Check.txtCurrent.FormatConditions.Add acExpression,
acEqual, "[GroupOrderNo]=1",
Forms!tblExcel_Check.txtCurrent.FormatConditions.Item(0).BackColor =
15592886

Hope you can help me with an answer. Manual is not a consideration as
it
will have to run automatically.

Thanks once again.

"Allen Browne" wrote:

Looks okay.

Form tblExcel_Check must be open in design view.
The form must have a textbox (or combo) named txtCurrent, and another
control named GroupOrderNo.
The acEqual operator will be ignored.

What error are you receiving?

Another way to get the result you are after is to manually add the
format
condition (via Format | Conditional Formatting), and then testing what
Access gave you. Open the Immediate Window (Ctrl+G), and enter:
? Forms!tblExcel_Check.txtCurrent.FormatConditions.Count
? Forms!tblExcel_Check.txtCurrent.FormatConditions(0).BackColor
and so on to understand what is there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B4F355F7-4197-4720-9CF5-7638B5D85293@xxxxxxxxxxxxxxxx
Hi,

Trouble's back

Still can't get this right.

Currently I have:

txtCurrent = "F" & Fieldss

Forms!tblExcel_Check.txtCurrent.FormatConditions.Add
acExpression,
acEqual, "[GroupOrderNo]=1",

Forms!tblExcel_Check.txtCurrent.FormatConditions.Item(0).BackColor
=
15592886


I have a defination problem.

Can you be good enough to correct it for me.

"Allen Browne" wrote:

This example adds a format condition to the text box named Amount
on
Form1,
to show negative values in red:

With Forms!Form1.Amount.FormatConditions
.Add acFieldValue, acLessThan, 0
.Item(.Count - 1).ForeColor = vbRed
End With

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1AF62A77-E21E-46C5-A6CE-0F6F616D14AF@xxxxxxxxxxxxxxxx
Sorry Alan,

Just a bit thick on this point. how do a do add method if
possible
give
an
example

Regards.

Trev.

ps I am always glad when you reply as you always give the best,
most
correct
and to the point answers.

"Allen Browne" wrote:

If the control is a TextBox or ComboBox, you can use the Add
method
of
its
FormatConditions collection to add up to 3 format conditions.

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:13EC3E5A-F8B6-4BFD-BA68-AE09164A0505@xxxxxxxxxxxxxxxx
Thanks for that.

How for the follow up.

Is it possible at the time of adding the item to put a
conditional
format
in
place.

If so, How.

If not how do I get around it.

Thanks

Trev

"Allen Browne" wrote:

You cannot do this while the form is in use, so this
technique
is
not
suitable for any database where you plan to release an MDE
for
users.

If you are designing some kind of wizard/tool for development
purposes,
you
can create a text box like this (assuming Form1 is open in
design
view):

Dim txt As TextBox
Set txt = CreateControl("Form1", acTextBox, acDetail, , ,
_
1440, 1440, 1440, 285)
txt.Name = "MyNewControl"

"Trever B" <TreverB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:B1EFA54B-E78D-4CD4-9E32-5B5732E185CF@xxxxxxxxxxxxxxxx
Hi,

Thanks in advance.

Using VBA I want to a boxes to a form.

I am ok with the loop but just how do you add a textbox to
a
form
using
code.









.

Quantcast