Go through all the records....

[Guest]

I have the following code already there and it does what it needs to... sort
of.

Private Sub Form_Open(Cancel As Integer)
Dim strActiveNo As String
ctlActiveNo = Me.optActiveNo

If strActiveNo = 1 Then
Me.txtTermDate.Visible = False
Else
If strActiveNo = 2 Then
Me.txtTermDate.Visible = True
Else
End If
End If
End Sub

However, it only does the first record in the form.
How do I set it to go through all the records in the form?

Thanks,
Jennifer

 

[Guest]

If you move your code the the form's Current event and not execute it for New
records, I think it will do what you want.
I notice you dim strActiveNo as String, but you are comparing against a
number. This works, but takes Access a little longer to coerce the data
types. Besides, it is confusing to others reading your code. I would
suggest you do it this way:

If Me.NewRecord Then
'Dont know what you want for new records, but it goes here
Else
Me.txtTermDate.Visible = (Me.optActiveNo = 2)
End If

 

[Guest]

When you open this form some of the records are active accounts and some are
terminated accounts but we still need the other information in the form for
those accounts.

I have an option group on that form where 1 = Active and 2 = Terminated.
If the account is current (1), the Term Date text box is not visible. If
the account is Terminated (2) the Term Date text box is visible.
Using the code I listed below, when you open the form, if the first record
is active, it doesn't show the text box on any of the records, active or not.
If the first record on the form is Terminated, the text box is visible on
all records, active or not.

I want it to cycle through and evaluate the value of the option group for
each record and set the text box visible property accordingly.

 

[Guest]

Yes, I understood that. That is why I said the code should be in the form
Current event. It will do the evaluation before the record is actually
displayed in the form and set the control specific to that record. The
Current event fires for all records, including the first record and new
records. That is why I put the note in about a new record, so you would know
you need to set it like you want it for a new record. This is a very common
way to approach this problem, give it a try.

 

[Guest]

That works... but you already knew that obviously.
I guess it seems as I was second guessing your first answer, that was not my
intention, I don't understand in English what the line of code under Else
says and why it works.
Thank your for your help with this.
J

 

[Guest]

Of course it works! I wrote it!
just joking

I took no offense at your response. I understood you were not that familiar
with this technique. After you have been doing this for a while, you pick up
techniques that work for different situations. Any time you need to set
controls on your form based on values in the record, the Current event is the
place to do it.

Me.txtTermDate.Visible = (Me.optActiveNo = 2)

The line you are asking about works like this. It checks to see if
Me.optActiveNo is = to 2.
(Me.optActiveNo = 2)

If it is, it returns True, otherwise, it returns false. It then puts the
value of the result of that expression into the Visible Property of
Me.txtTermDate.

It is the same as writing:

If Me.optActiveNo = 2 Then
Me.txtTermDate.Visible = True
Else
Me.txtTermDate.Visible = False
End If

 

[Guest]

Well, that makes sense. I didn't realize it was checking for something.
Thanks for the help. I'm sure this will come in handy a lot during the
process of building this database.

 

[Guest]

Good luck.

This technique works in a lot of ways. Lets say, for example, you have a
checkbox on your form that is bound to a Yes/No field in your recordset and
you only want to allow the user to enter into a specific text box if the
check box is not checked. You would still use the Current Event.

One way would be:

If Me.chkSomeCheckBox = True Then
Me.txtSomeTextBox.Locked = True
Else
Me.txtSomeTextBox.Locked = False
End If

Or
Me.txtSomeTexttBox.Locked = Me.chkSomeCheckBox

 

[Guest]

Nice!!
I was thinking of doing that on one of our forms, but I was going to cross
that bridge when I got to it. Again... thank you.
Do some marketing in Memphis when you write a book on this stuff.
)

 

[Guest]

Thanks for the compliment!
When I go to Memphis, I usually go straigt to Beale st. and find a blues
group to jam with.

 

[Guest]

I think I may have been to Beale 8 times or so. It's odd how the things that
other people come to Memphis for, the people that live here hardly ever do.
I've lived here all my life and I've never been to Graceland.
J

 

[Guest]

So true, most New Yorkers have never been to the Empire State building unless
they have business there.

Beale to me is not so much a tourist attraction. Before getting into
computers, I spent many years as a professional musician and a lot of that
playing in Blues bands. I just love Blues music and Beale is one of the best
places in the country for that. Believe it or not, another good place is
Portland, Oregon.

 

[Guest]

Farthest west I've been is Arkansas. Haven't been to New York either, but
it's true, I'd go to the Empire State Building if I was there on a visit.

I'm about to get out of here and go home. It's been nice chatting with you.
And thank you soooooo much for all your help today. I'm sure I'll catch up
with you again in here. There will be many more questions before this thing
is finished I'm sure.
J