if statement

[DogLover2006]

I am trying to reference a textbox on a form but have trouble with it.

Eample
strName = Year(now())+1
If IsObject(Me.Temp_Federal.Controls(strName)) = True Then
Dim msg As String
msg = MsgBox("This year already exists.", vbOKOnly)

Else
Call UpdateTable

end if

Help please

 

[rjoubert]

Two questions...

1. What is Temp_Federal?

2. Is this code behind your form?

 

[JoeAtWork]

I don't think your use of IsObject can be used for controls, see the online help:

IsObject is useful only in determining whether a [COLOR=red]Variant[/color] is of VarType vbObject. This could occur if the Variant actually references (or once referenced) an object, or if it contains Nothing.

Besides that, I'm having a hard time figuring out what you are trying to accomplish. Is it that you want to check if the next year is loaded in the textbox?

Perhaps you could elaborate?

 

[TheAceMan1]

How aare ya DogLover2006 . . .

Your attempt at detecting the textbox is superfluous as well as what you want to do with it!

To get a proper answer you need to be more specific!

See Ya! . . . . . .

 

[DogLover2006]

Hello Everyone,

Here is what I am trying to do. Basically the if statement to check to see if there is a textbox called strName(strName = year(now())+1). if it is there end else run the code. The code is behind a button onclick property. Temp_Federal is the name of the form actualy it a subform on a form. I hope this clears up all the puzzling minds about what I am trying to do. I have to also come up with code that renames the textboxs every five year (maybe we will see)Maybe I will just use a array for that but for now I am stuck on my If statement.

 

[traingamer]

I have to also come up with code that renames the textboxs every five year ...

Why? Keep the same name for the text box control forever. Change the text in the label / caption, change the text in the tool tips, but keep the text box name. You could (and probably should) change those other things based on table entries somewhere.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[JoeAtWork]

I have a feeling you are trying to support an unnormalized database to extreme levels, right up to form design. The fact that you are checking to see if a textbox has a name that looks like data, and the fact that you need to periodically rename your controls, fuels my suspicions.

Field names should not be part of the data, they simply identify what type of data is held in the field. For example, you should not have a field called TotalSales2006 to represent total sales in 2006, you should have one field called "Year" and another called "Sales".

Hopefully one of the people who has the link to the "Database Normalization" article handy can point you in the right direction.

 

[DogLover2006]

The nameing is not the problem. The textbox name could be year1, year2, year3 etc... It is doing the if statement to do a search for a textbox to see if it there. I just want it to look at the textbox's name. Is it possible or is not? Why would this be a unnormalized database? If statements are normal used and as far as renameing the textbox. That is nothing I may just call the textbox 1, 2 ,3 etc. and have controlsoucre change. The only reason I am using a string is because there are 5 boxes and the name ends in 2,3,4,5,6 and that will change to maybe 3,4,5,6,7. That is why the string name.

 

[JoeAtWork]

To answer your question, yes, it can be done, here's one possibility:

    Dim strTag As String
    
    On Error Resume Next
    Err.Clear
    
    strTag = Me.Controls("txtID").Tag
    
    If Err.Number = 0 Then
        MsgBox "txtID exists"
    Else
        MsgBox "txtID does not exist"
    End If

Another way might be to loop through your Controls collection looking for the name.

However, it still seems like a bad idea to me. Yes it's normal to use "If" statements, but to compare data, not the names of controls. The code you posted suggests that the control name holds part of the data (i.e. the year that the data is associated with).

 

[DogLover2006]

Hello,

Thank you for all your help. The data is dollars done each year. So I name the textbox 2006. This is how this company want it and I am not the one to say "they can't have it" They just get mean. I am just a pee on. I do what I am told. They deal with money by years and months. I will try the code and thank you all for your help.

 

[JoeAtWork]

Here's a trick if you want to display the Year as a label for the dollars textbox. Assume you have fields [Year] and [Dollars].

1. Place both fields on the form (textboxes)
2. Delete the labels for the textboxes
3. Place the [Year] textbox in front of the [Dollars] textbox
4. Set the following properties of the [Year] textbox
Back Style = Transparent
Locked = Yes
Tab Stop = No
Back Color = (same Back Color as the detail section of the form)
Border Style = Transparent

This will make the [Year] textbox look like a label. This will save you having to check the names of controls, renaming controls, etc. And at the same time, your users will get the presentation they want.

 

[DogLover2006]

JoeAtWork,

Thank you, sounds good