version control text string comparison

[TraceSL]

Hello
I use a version number on the main form in my access systems to track versions. We have users who are dragging the shared access database onto the windows computer desktops instead of the shortcuts. To help ensure users are using the latest version I been asked to track versions in a table than compare the version number to the one listed on the main form. If the version numbers do not match pop up a message to end user and exit application.

We do not use Access security or log on features. These are basic Access systems.

I created a SQL table to track the date related version number. Version number format we are using is mm.dd.yyyy. This version number is displayed on main access form as a label. The other version number is stored in the SQL table as short text.

I want compare the two version numbers and if they don't match give user error message and stop the app.

I was going to add this to the form properties open form event. I would prefer to use a VBA routine which would include a message box if comparison does not match.

I have tried to write this myself in VBA using the do.cmd on open form but it is not working.

Any guidance on how to implement this feature would be appreciated.

Thank you, Tracy

 

[theDBguy]

Hi Tracy. I am not sure what you are asking here. Did you say your version number is actually a date value? Were you having problems how to look up the version number in the table?

 

[Gasman]

I used to expose only the latest accde to everyone.
On starting the DB, it would check to see they are running the accde, if not it would close.
I also used Bob Larsen's auto update code.

 

[TraceSL]

Hi Tracy. I am not sure what you are asking here. Did you say your version number is actually a date value? Were you having problems how to look up the version number in the table?

my access systems are basic with no login required and no security in place.
i use a password on the database to control who can open and use the database.

the databases open to a main form. the main form displays a version number in this format "mm.dd.yyyy" in a label.

I created SQL table to track versions and notes on changes. the SQL field is short text contains the version number in same format "mm.dd.yyyy."

When the user opens the database and the main form displays I need to have an event that compares the two values for the versions. If they do not match notify the user with message box.

Is this simple enough? I can change how I display the version number on the main form if needed to use in a comparison to the SQL table.

Please forgive me non tech format, I am a Senior Staff Accountant who has been building access systems to meet office needs, my position has changed to analyst and moved to IT to allow me to fully focus on the tech elements I been working with the past 20 years.

Thank you your patience, understanding, and help! Tracy

 

[arnelgp]

putting code to the Open event of the form is too early.
the labels and other controls are not yet initialized, or still does not exist.
try moving your code to the Load event.

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable") & ""
If strVersion <> "" Then
    If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
       Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
       Msgbox "Version does not match with verion on table"
       'add action here
    End If
End If
End Sub

 

[theDBguy]

my access systems are basic with no login required and no security in place.
i use a password on the database to control who can open and use the database.

the databases open to a main form. the main form displays a version number in this format "mm.dd.yyyy" in a label.

I created SQL table to track versions and notes on changes. the SQL field is short text contains the version number in same format "mm.dd.yyyy."

When the user opens the database and the main form displays I need to have an event that compares the two values for the versions. If they do not match notify the user with message box.

Is this simple enough? I can change how I display the version number on the main form if needed to use in a comparison to the SQL table.

Please forgive me non tech format, I am a Senior Staff Accountant who has been building access systems to meet office needs, my position has changed to analyst and moved to IT to allow me to fully focus on the tech elements I been working with the past 20 years.

Thank you your patience, understanding, and help! Tracy

Hi Tracy. Thank you for the clarification. Assuming the version number is entered into the label's caption in design view, then you should be able to use the DLookup() function to compare it to the one from the table. For example:

If Me.LabelName.Caption <> Nz(DLookup("VersionNumberFieldName","TableName"),"") Then
    MsgBox "Version Numbers don't match!"
 End If

PS. The above DLookup() expression assumes the version number table only has one record in it.

 

[TraceSL]

Funny somehow I knew this I was trying to use onload first but moved to the open event.

what action do I use to exit app in this line?
"'add action here"

putting code to the Open event of the form is too early.
the labels and other controls are not yet initialized, or still does not exist.
try moving your code to the Load event.

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable") & ""
If strVersion <> "" Then
    If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
       Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
       Msgbox "Version does not match with verion on table"
       'add action here
    End If
End If
End Sub

 

[arnelgp]

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable") & ""
If strVersion <> "" Then
    If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
       Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
       Msgbox "Version does not match with verion on table"
       'add action here
       [COLOR="Blue"]Application.Quit[/COLOR]
    End If
End If
End Sub

 

[TraceSL]

the SQL version table will have other records in it so maybe I should use a query to pull the last record from the table than compare

how would I handle this in the sub routine?

Hi Tracy. Thank you for the clarification. Assuming the version number is entered into the label's caption in design view, then you should be able to use the DLookup() function to compare it to the one from the table. For example:

If Me.LabelName.Caption <> Nz(DLookup("VersionNumberFieldName","TableName"),"") Then
    MsgBox "Version Numbers don't match!"
 End If

PS. The above DLookup() expression assumes the version number table only has one record in it.

 

[theDBguy]

the SQL version table will have other records in it so maybe I should use a query to pull the last record from the table than compare

how would I handle this in the sub routine?

Hi. If you do that, you can replace the "TableName" with the name of your query. Good luck!

 

[TraceSL]

this looks perfect but as another person stated may assume one record in SQL table, I will have more than one record forward. How should I handle this in the sub routine?

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable") & ""
If strVersion <> "" Then
    If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
       Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
       Msgbox "Version does not match with verion on table"
       'add action here
       [COLOR=blue]Application.Quit[/COLOR]
    End If
End If
End Sub

 

[arnelgp]

if you have autonumber field in the table:

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable", "autoNumberFieldName = " & dmax("autonumberfieldName", "versionTable")) & ""
If strVersion <> "" Then
    If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
       Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
       Msgbox "Version does not match with verion on table"
       'add action here
       Application.Quit
    End If
End If
End Sub

 

[TraceSL]

Thanks everyone for your responses. I plan to use the vba code next week got pulled onto another item at the moment!

 

[Pat Hartman]

I would keep a table on the server and a local table in the FE. Each table should have one and only one row and a column with the version and the date/time it was released.

Then use a query that joins the two tables on version. If a record is returned, then the versions match. Otherwise they don't so display a message and shut down.

 

[TraceSL]

I like this idea going to give it a shot now. TY

I would keep a table on the server and a local table in the FE. Each table should have one and only one row and a column with the version and the date/time it was released.

Then use a query that joins the two tables on version. If a record is returned, then the versions match. Otherwise they don't so display a message and shut down.[/QUOT

 

[TraceSL]

Hello All:
My VBA skills are very basic. I need a little more help!
I created two tables: SQL and one in the FE

xx_VersionLocal.localversionNbr this is the front end table and field name

xx_SQLversion.versionnumber this is the sql table

the version number is stored as text in this format yyyymmdd

I am trying to use the vba code but its not working properly. I did modify it from this original form

I will keep only record on both tables so max not needed, I was trying to define both tables in the DLookup but not sure how

can someone help pls TY tweak code below to work with my conventions, my code failed

I used this below to attempt to rewrite to match my object definitions,

Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("versionField", "versionTable", "autoNumberFieldName = " & dmax("autonumberfieldName", "versionTable")) & ""
If strVersion <> "" Then
If Format(Replace(Me.LabelVersion.Caption, ".", "/"), "yyyymmdd") <> _
Format(Replace(strVersion, ".", "/"), "yyyymmdd") Then
Msgbox "Version does not match with verion on table"
'add action here
Application.Quit
End If
End If
End Sub

 

[theDBguy]

Hi. If there's always going to be only one record (row) in the table, then you don't need a criteria. For example, a simple

DLookup("FieldName","TableName")

should work.

 

[TraceSL]

I understand this is basic VBA appreciate the help
so here is what I have still receiving error:

Two tables:
In Access FE - xx_tbl_SkyViewFP_SysVersionLocal. LocalversionNbr
SQL - xx_tbl_SkyViewFP_SysVersion. versionNumber

Versionnumber field specification: text
Version number data format: yyyymmdd

Code:
Private Sub Form_Load()
Dim strVersion As String
strVersion = DLookup("LocalversionNumber", "xx_tbl_SkyViewFP_SysVersionLocal"), = & ("versionNumber", "xx_tbl_SkyViewFP_SysVersion")& ""
If strVersion <> "" Then
MsgBox "Version does not match with verion on table"
'add action here
Application.Quit
End If
End Sub

Hi. If there's always going to be only one record (row) in the table, then you don't need a criteria. For example, a simple

DLookup("FieldName","TableName")

should work.

 

[TraceSL]

Thanks to everyone for the assistance

I was able to get this working as needed.

Using the two tables I created I built the comparison check in a macro than added the macro to form on onload event property. It is working as properly.

Tracy

 

[theDBguy]

Thanks to everyone for the assistance

I was able to get this working as needed.

Using the two tables I created I built the comparison check in a macro than added the macro to form on onload event property. It is working as properly.

Tracy

Hi Tracy. I was going to ask what was the error but glad to hear you already got it sorted out. Good luck with your project.