version control text string comparison

[TraceSL]

Hello
The macro I wrote is not working so I am back to using the VBA code.

Here is the full code I have embedded on the form property OnLoad.

It appears the code is not recognizing the two tables and field names I have defined in the string.
Also I already have code for this event so do I rename form_load unique as shown?
Also if the condition of the versions does not match i assume the message box would appear put the system modal once the user clicks message box the app would close, correct?

If the condition DOES match the message box would not appear and the form would load to allow user to proceed using the database.

I appreciate the help. :banghead:

Option Compare Database
Option explicit

'existing code to load default dates
Private Sub Form_Load()
Me![txtCY] = DLookup("[gCurrentYear]", "a_tbl_SkyView_DefaultDates")
Me![txtNY] = DLookup("[gNextYear]", "a_tbl_SkyView_DefaultDates")
Me![txtVersion] = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal")

End Sub


Private Sub Form_Load2()
Dim strVersion As String
'Local table with front end version is "xx_tbl_SkyViewFP_SysVersionLocal.LocalversionNbr
'SQL backend table for version control is
"xx_tbl_SkyViewFP_SysVersion.versionNumber
'version number in both table is text in format yyyymmdd

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal") & ("[versionNumber]", "xx_tbl_SkyViewFP_SysVersion")
If strVersion <> "" Then

MsgBox "Version does not match with verion on table"
Application.Quit

End If
End Sub

 

[Pat Hartman]

What event is Form_Load2()? I'm pretty sure this isn't valid. If you want something to run when the form loads, it needs to go into the Form_Load() event. There is only a single load event for a form.

The DLookup() is not formatted correctly:
strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal") & ("[versionNumber]", "xx_tbl_SkyViewFP_SysVersion")

I'm not sure I can identify which of your names to use where so I'll substitute.

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & yourremoteversionnumber & "'")

Even though there is only a single row in the version table, this code is using criteria to return that row ONLY if it matches the remote version number. That means that you need to either add a preceding DLookup() to get yourremoteversionnumber OR substitute an embedded DLookup() to get it in the expression like this:

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & DLookup("RemoteVersionNbr", "xx_tbl_SkyViewFP_SysVersionRemote") & "'")

As with all air code, this may contain syntax errors.

 

[TraceSL]

Thanks Pat
Going to give this a try. I am having a learning curve therefore studying at night to sharpen my skills. I appreciate the help and patience. I work with the syntax which has been part of my issue as well. Tracy

What event is Form_Load2()? I'm pretty sure this isn't valid. If you want something to run when the form loads, it needs to go into the Form_Load() event. There is only a single load event for a form.

The DLookup() is not formatted correctly:
strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal") & ("[versionNumber]", "xx_tbl_SkyViewFP_SysVersion")

I'm not sure I can identify which of your names to use where so I'll substitute.

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & yourremoteversionnumber & "'")

Even though there is only a single row in the version table, this code is using criteria to return that row ONLY if it matches the remote version number. That means that you need to either add a preceding DLookup() to get yourremoteversionnumber OR substitute an embedded DLookup() to get it in the expression like this:

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & DLookup("RemoteVersionNbr", "xx_tbl_SkyViewFP_SysVersionRemote") & "'")

As with all air code, this may contain syntax errors.

 

[TraceSL]

Here is the code almost working, now receiving runtime error 94 invalid use of Null. appears to be on the string dlookup line. I see other examples on web using Nz or IsNull …

these two tables will only have one record, neither have a null in the version number field in table at this time

the two table names and field names are shown properly in the string below.
I wonder if the string is proper with the identifier on end & ""

at the moment I comment out the action till I get this code working properly

I have the code running on a test form..

TY for help and patience

Option Compare Database
Option Explicit

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

What event is Form_Load2()? I'm pretty sure this isn't valid. If you want something to run when the form loads, it needs to go into the Form_Load() event. There is only a single load event for a form.

The DLookup() is not formatted correctly:
strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal") & ("[versionNumber]", "xx_tbl_SkyViewFP_SysVersion")

I'm not sure I can identify which of your names to use where so I'll substitute.

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & yourremoteversionnumber & "'")

Even though there is only a single row in the version table, this code is using criteria to return that row ONLY if it matches the remote version number. That means that you need to either add a preceding DLookup() to get yourremoteversionnumber OR substitute an embedded DLookup() to get it in the expression like this:

strVersion = DLookup("[LocalversionNbr]", "xx_tbl_SkyViewFP_SysVersionLocal", "LocalversionNbr = '" & DLookup("RemoteVersionNbr", "xx_tbl_SkyViewFP_SysVersionRemote") & "'")

As with all air code, this may contain syntax errors.

 

[Pat Hartman]

change the dim for strVersion to Variant. Strings cannot hold null values and the dlookup is not returning a value.

copy the inner DLookup() and paste it into the debug window:

put a stop in the code on the dlookup line so you can run the code in the debug window.
Print dlookup(...)
Does that return the correct value?

Then do the same thing with the whole DLookup()

Also, Please use the code tag around your code so it will retain its formatting and be easier for us (and you) to read.

 

[TraceSL]

Hi Pat
You hit the nail on the head, data definitely not coming through as TEXT.

I have other technical issues going on that may be impacting the VBA code - data types. I am testing ODBC driver updates for SQL 2016 due to Access issues with linking to SQL2016 data types.

Probably a bad time for me to try to implement this code until I resolve these issues and finish the SQL 2016 upgrade. I am working on upgrading FMS system from SQL2008 to SQL2016.

Meanwhile I am going to continue testing/using the code eliminating the other tech components I am managing at this moment in other systems. Jet/ODBC and SQL database upgrades are the biggest challenge for me at the moment I should of realized this sooner. My apologies..

:banghead:

Thanks for the help. Tracy

 

[Pat Hartman]

When I have control over the SQL Server BE, I stick with data types that the old SQL Server driver can handle. It is a PITA to have to distribute new drivers to all users. Hope your IT people are willing to push the updates for you so you don't have to visit each computer and do it by yourself.