Access VBA公共变量无法保留值的问题求助

我正在开发一款跟踪潜水器下潜事件的Access数据库应用，通过嵌套表单和VBA代码实现功能，包括记录潜水器发射、抵达海底等事件。应用需要从潜水器日志中读取当前经纬度与深度，当日志不可用时，用户需通过弹出表单LatLonDataEntry手动输入经纬度，支持十进制度数、度分、度分秒三种格式，代码会自动转换为十进制度数（如30.2345、-88.1234）。

为了让LatLonDataEntry表单计算后的经纬度值能在关闭后被调用，我在标准模块中声明了公共变量DecDegLat和LonDecDeg，但实际使用时这两个变量始终为空。相关代码如下：

按钮点击事件代码 #

Private Sub SetLaunchTimeNow_Click()
' Launch Now button should make a new dive note record and put in the phrase "launch" and put in the current date/time, lat/lon from the user entry form.

Dim nowtime As String

DoCmd.OpenForm "LatLonDataEntry", acNormal, "", "", , acDialog

'set the time, date/time, lat, lon, and depth in the splash note
nowtime = Now()
Me!Time_Launch = Format(nowtime, "hh:nn:ss")
Me!Date_Time_Launch = nowtime
Me!Latitude_SITE = DecDegLat
Me!Longitude_SITE = DecDegLon

'move the focus to the dive note subform
Form_frm_tbl_DiveNote.Form![Date_Time_Notes].SetFocus

'add a new dive note record and then set all of the variables to the same lat, lon, and depth that you added to the splash note
'add the note "Launch" to the Habitat Description
Form_frm_tbl_DiveNote.Recordset.AddNew
Debug.Print "Added a new Record for Launch"
Form_frm_tbl_DiveNote!SplashID = Me!SplashID
Form_frm_tbl_DiveNote!ExtraNote_ID = Form_frm_tbl_DiveNote!DiveNote_ID
Form_frm_tbl_DiveNote!Date_Time_Notes = nowtime
Form_frm_tbl_DiveNote!Event = "Launch"
Form_frm_tbl_DiveNote!Latitude = DecDegLat
Form_frm_tbl_DiveNote!Longitude = DecDegLon
Form_frm_tbl_DiveNote!HabitatDescription.SetFocus


End Sub

公共变量及日志读取模块 #

Option Explicit
Public db As Database
Public puttylog As Recordset
Public rst As Recordset
Public spl As Recordset
Public currcruise As String
Public currlat As String
Public currlon As String
Public currdepth As String
Public DecDegLat As String
Public DecDegLon As String
Public lasttransect As Integer
Public transectstarted As Boolean
Public NSHemisphere As String
Public EWHemisphere As String


Public Function RetLatLonDepth()

Set db = CurrentDb()
Set puttylog = db.OpenRecordset("Putty_log")

'move the first record (only record) and read in the current lat, lon, and depth
puttylog.MoveFirst

currlat = Str(puttylog![lat_GPRMC])
currlon = Str(Format(puttylog![lon_GPRMC], "00000.0000"))
currdepth = Trim(puttylog!Depth)

' reset the recordset and database to nothing to release it
Set puttylog = Nothing
Set db = Nothing

End Function

LatLonDataEntry表单代码 #

Option Explicit
Dim lat As Integer
Dim lon As Integer
Dim min As Integer
Dim latmin As Single
Dim lonmin As Single
Dim DecDeg As Single
Dim decmin As Single
Dim minsec As Single
Dim decsec As Single
Dim numspaces As Integer



Public Sub LatInput_AfterUpdate()

If IsNumeric(LatInput) = True Then
    
    ' the latitude was entered in dec degrees
    If LatInput > 90 Or LatInput < -90 Then
        GoTo LatError
    Else
        LatInput = Fix(LatInput * 100000) / 100000
        If LatInput < 0 Then
            NSHemisphere = "Southern Hemisphere"
        Else
            NSHemisphere = "Northern Hemisphere"
        End If
    End If
    
    LatDecDeg = Str(LatInput)
    lat = Fix(LatInput)
    latmin = Round(Abs(LatInput) - Abs(lat), 5)
    decmin = latmin * 60
    LatDegDecMin = Format(Str(lat), "00") & " " & Str(latmin * 60)
    
    min = Fix(decmin)
    minsec = Round(Abs(decmin) - Abs(min), 6)
    LatDegMinDecSec = Format(Str(lat), "00") & " " & Format(Str(min), "00") & Str(minsec * 60)
    Debug.Print LatDegMinDecSec
Else
    numspaces = Len(LatInput) - Len(Replace(LatInput, " ", ""))
    If (numspaces = 1) Then
        ' the latitude was entered in deg dec min
        LatDegDecMin = Str(LatInput)
        lat = Int(Split(LatInput, " ")(0))
        decmin = CDbl(Split(LatInput, " ")(1))
        LatDecDeg = Format((lat + decmin / 60), "00.00000")
        
        min = Fix(decmin)
        minsec = Round(Abs(decmin) - Abs(min), 5)
        LatDegMinDecSec = Format(Str(lat), "00") & " " & Format(Str(min), "00") & Str(minsec * 60)
    Else
        If (numspaces = 2) Then
            ' the latitude was entered in deg min dec sec
            LatDegMinDecSec = Str(LatInput)
            lat = Int(Split(LatInput, " ")(0))
            min = Int(Split(LatInput, " ")(1))
            decsec = CDbl(Split(LatInput, " ")(2))
            decmin = min + decsec / 60
            DecDeg = lat + decmin / 60
            LatDecDeg = Format(Str(DecDeg), "00.00000")
            LatDegDecMin = Format(Str(lat), "00") & " " & Format(Str(decmin), "00.00000")
        Else
LatError:
            MsgBox ("You have entered an invalid format for Latitude or a value that is out of range (-90 to 90).")
        End If
    End If
    
End If

End Sub

Public Sub LatLonOK_Click()
    DecDegLat = LatDecDeg
    DecDegLon = LonDecDeg
End Sub

Public Sub LonInput_AfterUpdate()

If IsNumeric(LonInput) = True Then

    ' the Longitude was entered in dec degrees
    If LonInput > 180 Or LonInput < -180 Then
        GoTo LonError
    Else
        LonInput = Fix(LonInput * 100000) / 100000
        If LonInput < 0 Then
            EWHemisphere = "Western Hemisphere"
        Else
            EWHemisphere = "Eastern Hemisphere"
        End If
    End If
    
    LonDecDeg = Str(LonInput)
    lon = Fix(LonInput)
    lonmin = Round(Abs(LonInput) - Abs(lon), 6)
    decmin = lonmin * 60
    LonDegDecMin = Format(Str(lon), "000") & " " & Str(lonmin * 60)
    
    min = Fix(decmin)
    minsec = Round(Abs(decmin) - Abs(min), 6)
    LonDegMinDecSec = Format(Str(lon), "000") & " " & Format(Str(min), "000") & Str(minsec * 60)
Else
    numspaces = Len(LonInput) - Len(Replace(LonInput, " ", ""))
    If (numspaces = 1) Then
        ' the Longitude was entered in deg dec min
        LonDegDecMin = Str(LonInput)
        lon = Int(Split(LonInput, " ")(0))
        decmin = CDbl(Split(LonInput, " ")(1))
        LonDecDeg = Format((lon + decmin / 60), "000.00000")
        
        min = Fix(decmin)
        minsec = Round(Abs(decmin) - Abs(min), 6)
        LonDegMinDecSec = Format(Str(lon), "000") & " " & Format(Str(min), "00") & Str(minsec * 60)
    Else
        If (numspaces = 2) Then
            ' the Longitude was entered in deg min dec sec
            LonDegMinDecSec = Str(LonInput)
            lon = Int(Split(LonInput, " ")(0))
            min = Int(Split(LonInput, " ")(1))
            decsec = CDbl(Split(LonInput, " ")(2))
            decmin = min + decsec / 60
            DecDeg = lon + decmin / 60
            LonDecDeg = Format(Str(DecDeg), "000.00000")
            LonDegDecMin = Format(Str(lon), "000") & " " & Format(Str(decmin), "00.00000")
        Else
LonError:
            MsgBox ("You have entered an invalid format for Longitude or a value that is out of range (-180 to 180).")
        End If
    End If
    
End If

End Sub

问题原因及修复方案 #

1. 核心问题：变量作用域冲突
   LatLonDataEntry表单中的LatDecDeg和LonDecDeg未声明为公共变量，属于表单级私有变量。点击OK按钮时，无法将表单内的计算值正确传递给标准模块的公共变量DecDegLat和DecDegLon。

2. 修复步骤

   • 在LatLonDataEntry表单代码顶部，添加公共变量声明：

     Option Explicit
     Public LatDecDeg As String
     Public LonDecDeg As String
     Dim lat As Integer
     Dim lon As Integer
     ' 保留原有其他变量声明
     

   • 若标准模块有自定义名称（如Module1），需在OK按钮事件中明确指定模块名：

     Public Sub LatLonOK_Click()
         Module1.DecDegLat = LatDecDeg
         Module1.DecDegLon = LonDecDeg
         DoCmd.Close acForm, "LatLonDataEntry" ' 确保表单卸载，避免变量残留
     End Sub
     

3. 优化建议

   • 将经纬度变量类型从String改为Double，提升精度和计算效率，标准模块和表单中的对应变量都需调整。
   • 取消Format字符串转换，直接用数值赋值，例如LatDecDeg = lat + decmin / 60，避免字符串转数值时的潜在问题。

内容的提问来源于stack exchange，提问作者Susan Gottfried