How do I get the first value returned from a table into a variable

[homer2002]

Hi i'm creating a split database. I update the front end which needs to be sent out to users. In order to keep people using the latest version, I have created a table to hold one record (A Version number). When the front end opens up it's supposed to check that it is the current version (i.e. FrontEnd Version = BackEnd Table Value.

Unfortuantly I'm missing the key aspect of this.

I don't know how to return the value of the record into a variable to perform the check.


i need somthing like

Dim AVariable as Integer
AVariable = tables!VersionTable!FirstRecord.
This is obviously wrong, but can someone provide the syntax for me.

Thanks Homer.

 

[smed]

Homer

I had the same problem, and used to have to manually copy a new version to every user machine.

I one remeber seeing a small application which sort of worked, but the creator would not publish the source code.

So instead, I bought Visual Basic 6 and spent a couple of days writing my own solution (See Attachment).

I have included instructions in the zip along with example config files. If you require any help using it or the source code, let me know.

Smed

 

[ShaunWillmott]

Homer,

Another metjod I have used is to create a table (usually for parameters) with a single key field (Text) and only allow the text "KEY", then create fields for any other variables/constants, etc. you need (like version)

You can access the record using Key = "KEY" and read the version number in the Version field and check it against your coded constant.

Cheers

Shaun

 

[Dave Eyley]

To get the data from a 1 record table quickly use -

Dim MyField As String

MyField=DLookUp("[Version]","[TableName]")

Because there's only one field there's no problem.

Getting an updated value back in the table is harder though.

Hope this helps.

Dave E

 

[Opengrave]

I actually do this myself. You will need to change the SQL code but this should do most of the trick.
I use this to go out and grab the new version's WISE install package from the a mapped network drive.
I substituted YOURAPPNAMEHERE for the my actual app name so put whatever you like in that part.
I actually store ALL version info in my tbl_version_control that is why I use
a query to get the highest version#. The tbl_version_control contains release notes, version numbers, dates,
AND location of the new front end. This way I can put the front end anywhere and just specify
in the table where it can be found.

You could just do a 'Select * from tablename' (this code is going to be ugly due to the width)

Dim db As Database
Dim rst As Recordset
Dim Version As Variant
Dim VersionNeeded As Variant

VersionNeeded = 2.7

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT qryMaxVersion.MaxVer, tbl_Version_Control.Location FROM qryMaxVersion INNER JOIN tbl_Version_Control ON qryMaxVersion.MaxVer = tbl_Version_Control.Version_Number;")
With rst
    If .RecordCount = 0 Then
        MsgBox ("Please Contact Tech Support and ask them to Check the Version Control Number on YOURAPPNAMEHERE.")
        Application.Quit (acQuitSaveNone)
    End If
    .MoveFirst
    Version = .Fields![MaxVer]
    If Version <> VersionNeeded Then
        If MsgBox("Please Click Ok to Install the latest version of YOURAPPNAMEHERE, Version " _
              & VersionNeeded & ".", vbOKCancel, "Get Correct YOURAPPNAMEHERE Version") = vbOK Then
              Shell .Fields!Location
        End If
        Application.Quit (acQuitSaveNone)
    End If
    .Close
End With