Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / January 2006

Tip: Looking for answers? Try searching our database.

Placing the Same Value In Multiple Forms

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SHPsalm139@aol.com - 12 Jan 2006 13:34 GMT
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 - 12 Jan 2006 14:46 GMT
> We have an Access 2K application that uses multiple forms.  We
> currently hardcode the release number in a label on each form but this
[quoted text clipped - 5 lines]
> 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 - 12 Jan 2006 14:54 GMT
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()")

Signature

Wayne Morgan
MS Access MVP

> We have an Access 2K application that uses multiple forms.  We
> currently hardcode the release number in a label on each form but this
[quoted text clipped - 5 lines]
> light on how to do this.
>                                         Thank you, Sheldon Potolsky
SHPsalm139@aol.com - 12 Jan 2006 15:48 GMT
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 - 12 Jan 2006 15:52 GMT
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.

Signature

Wayne Morgan
MS Access MVP

> 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
[quoted text clipped - 15 lines]
>
> Sheldon Potolsky
Anthony England - 12 Jan 2006 15:56 GMT
> 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
[quoted text clipped - 15 lines]
>
> 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@aol.com - 12 Jan 2006 17:13 GMT
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 - 12 Jan 2006 22:54 GMT
> Thanks for the suggestions.  I went the Text box route, ensured
> that Enabled and Locked were set to "yes", only used the release
[quoted text clipped - 13 lines]
> 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.

Signature

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

SHPsalm139@aol.com - 13 Jan 2006 14:03 GMT
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 - 13 Jan 2006 14:13 GMT
> 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.

Signature

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

David W. Fenton - 12 Jan 2006 22:52 GMT
> We have an Access 2K application that uses multiple forms.  We
> currently hardcode the release number in a label on each form but
[quoted text clipped - 4 lines]
> 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.

Signature

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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.