Placing the Same Value In Multiple Forms

[SHPsalm139]

We have an Access 2K application that uses multiple forms. We
currently hardcode the release number in a label on each form but this
gets tedious to do each time there's a new release. I thought it would
be easy to put the new release number in one spot (a table) and have
each form reference that table/release number in a text box that I
would add to each form but, apparently, it's not as simple as I
expected it to be. Would appreciate anyone who could shed further
light on how to do this.
Thank you, Sheldon Potolsky

[Anthony England]

<SH********@aol.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

We have an Access 2K application that uses multiple forms. We
currently hardcode the release number in a label on each form but this
gets tedious to do each time there's a new release. I thought it would
be easy to put the new release number in one spot (a table) and have
each form reference that table/release number in a text box that I
would add to each form but, apparently, it's not as simple as I
expected it to be. Would appreciate anyone who could shed further
light on how to do this.
Thank you, Sheldon Potolsky

There will be other solutions, but it might be easier to change from using a
label to a textbox. If you want to change the caption of a label you have
to write code in each of these forms. However, with a texbox, you have a
datasource property so the version number can be looked up. Don't worry
about the look of a textbox - we can change that to make it look like a
label.

Create a new code module and in it palce the following function:

Public Function GetVersionNumber() As String
GetVersionNumber = "1.01"
End Function

Compile and save the module. Next place a new textbox on a form and delete
its associated label.
Name=txtVersionNumber
Special Effect=Flat
Back Style=Transparent
Enabled=No
Locked=Yes
Datasource is "=GetVersionNumber()" without the quotes

Now you have the textbox on one form, just copy and paste it on to each of
the subsequent forms you wish to update. Obviously the old labels should
just be deleted.

This approach stores the version number in code - it should be faster and it
keeps it away from the tables where non-coders might touch it. If you felt
you had to store it in a table, then you could set the textbox's datasource
to a dlookup function to extract it - but I would stick with my suggestion.

[Wayne Morgan]

Yes, you can do as you state. Place the release number in the table along
with the date that it goes into effect. You can even place them in the table
ahead of time. In the Load event of the form you can assign the Caption
property of the label or you could create a textbox that looks like a label
and make it a calculated control. If you go the textbox route, you may want
to set the Locked and Enabled properties of the textbox to Yes.

The formula for the lookup would then be:
DLookup("[ReleaseNumberField]", "[TableName]", "[DateField] =
DMax(""[DateField]"", ""[TableName]"", ""[DateField]<=Date()"")")

Note the double quotes, they are because you need to pass quotes to the
embedded function without making the outer function think that it has
reached the end of its string.

Another option would be to use a query instead of the table. If you sort the
query descending on the date field, DLookup will stop at the first record it
finds that meets the criteria. You could then use the following statement:

DLookup("[ReleaseNumberField]", "[QueryName]", "[DateField]<=Date()")

--
Wayne Morgan
MS Access MVP
<SH********@aol.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

We have an Access 2K application that uses multiple forms. We
currently hardcode the release number in a label on each form but this
gets tedious to do each time there's a new release. I thought it would
be easy to put the new release number in one spot (a table) and have
each form reference that table/release number in a text box that I
would add to each form but, apparently, it's not as simple as I
expected it to be. Would appreciate anyone who could shed further
light on how to do this.
Thank you, Sheldon Potolsky

[SHPsalm139]

Thanks for the suggestions. I went the Text box route, ensured that
Enabled and Locked were set to "yes", only used the release number (no
date) and added the following code on the form activation and was
successful -
Private Sub Form_Activate()
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVersion;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
Text2.Value = VarRS("Version")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
End Sub

Sheldon Potolsky

[Wayne Morgan]

If you only keep one release number in the table at a time, that will work.
If you have more than one release number in the table, you will need some
way of distinguishing which one is the current one.

--
Wayne Morgan
MS Access MVP
<SH********@aol.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Thanks for the suggestions. I went the Text box route, ensured that
Enabled and Locked were set to "yes", only used the release number (no
date) and added the following code on the form activation and was
successful -
Private Sub Form_Activate()
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVersion;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
Text2.Value = VarRS("Version")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
End Sub

Sheldon Potolsky

[Anthony England]

<SH********@aol.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Thanks for the suggestions. I went the Text box route, ensured that
Enabled and Locked were set to "yes", only used the release number (no
date) and added the following code on the form activation and was
successful -
Private Sub Form_Activate()
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVersion;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
Text2.Value = VarRS("Version")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
End Sub

Sheldon Potolsky

No, no, no! That's madness! You plan to write this code in every form? If
it was bad enough mainting a single label caption for each form, then how
much worse is this?
You should still use my suggestion of creating a function to do this called
GetVersionNumber(), and set the control source to this, then you only need
to cut and paste the single textbox, and any modifications you make to the
function are made only once.
By the way, what advantage do you think you get from storing the version
number in a table as opposed to a code module?

[SHPsalm139]

We only have about 4-5 screens and it would probably take me 15 minutes
tops to copy/paste the code and textbox to each of those screens as I
have it working on one screen (in test mode). From that point it would
require updating the one record in the one table each time there's a
new release. I thought updating the table was simpler than updating
code but it's obviously not a big difference. There are about a dozen
users and though they would never think of changing the version #
field, I locked that field anyway. I will try your method too Anthony
to see how it works and learn more as I try it. Thanks to both of you
for the suggestions.

Sheldon Potolsky

[David W. Fenton]

"SH********@aol.com" <SH********@aol.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

We have an Access 2K application that uses multiple forms. We
currently hardcode the release number in a label on each form but
this gets tedious to do each time there's a new release. I
thought it would be easy to put the new release number in one spot
(a table) and have each form reference that table/release number
in a text box that I would add to each form but, apparently, it's
not as simple as I expected it to be. Would appreciate anyone who
could shed further light on how to do this.

I store it in a custom property of the MDB (which I update manually
from FILE | DATABASE PROPERTIES | CUSTOM).

Then I lookup up the custom property in the OnOpen of my splash form
and display the version number there. If you want to display it on
all forms, I'd suggest some global method of caching the value so it
doesn't have to be looked up every time.

I'd probably use a Static variable in a public function and have it
lookup the value if the Static variable is uninitialized.

But, generally, I put the version number only on the Splash screen
or the main menu.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"SH********@aol.com" <SH********@aol.com> wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

Thanks for the suggestions. I went the Text box route, ensured
that Enabled and Locked were set to "yes", only used the release
number (no date) and added the following code on the form
activation and was successful -
Private Sub Form_Activate()
Dim MyDB As Database
Dim VarRS As Recordset
Dim strSQL As String
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblVersion;"
Set VarRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
VarRS.MoveFirst
Text2.Value = VarRS("Version")
VarRS.Close
Set VarRS = Nothing
Set MyDB = Nothing
End Sub

Why not just use DLookup(), as was already suggested? It's *much*
simpler, since you'd be able to get the same thing with just one
line of code.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[SHPsalm139]

I just used the DLookup() method which worked fine but the version # on
the textbox on my form has a flashing block cursor on it. Is there a
way to get rid of this?

Sheldon Potolsky

[David W. Fenton]

"SH********@aol.com" <SH********@aol.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

I just used the DLookup() method which worked fine but the version
# on the textbox on my form has a flashing block cursor on it. Is
there a way to get rid of this?

That has nothing to do with DLookup.

All it means is that:

1. your textbox is enabled/locked, or not disabled at all, even
though it's not editable (it should be disabled and locked).

2. your textbox has the current focus.

Fix #1 and #2 won't be able to happen at all.

I don't use textboxes for this -- I use labels. That way I don't
have to muck around with disabling/locking at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/