Dear Friends,


I am trying to get my EditInfo form to work so that it edits the Variation,
Annual Variation and Base Year in a database. I have spent a long time but
cannot do so.

Can anyone help please.

Kind Regards,

Dan.



This is the main form:



Option Explicit

'' Declarations for the database functionality
'Dim NavWorkspace As Workspace
'Dim NavDB As Database
'Dim NavRecords As Recordset
'Dim strSQL As String

'' Global public data for other forms to update so that new records can be
added
'' or current records can be edited.
Public g_Chart_ID As Integer
Public g_Variation As String
Public g_Annual_Variation As String
Public g_Base_Year As Integer

Private Type typeDirection
deg As Integer
min As Integer
dir As String
End Type

Public Sub ClearGlobalData()

' ***************************************************************
' Function to clear global data
' ***************************************************************

g_Chart_ID = -1
g_Variation = ""
g_Annual_Variation = ""
g_Base_Year = -1
End Sub


' ***************************************************************
' Function to load the database and set the record set.
' ***************************************************************
Private Sub Load_Database()

On Error GoTo TXTERROR
Set NavWorkspace = DBEngine.Workspaces(0)
Set NavDB = NavWorkspace.OpenDatabase(App.Path & "\Navigation.mdb")
Set NavRecords = NavDB.OpenRecordset("NAV_Navigation", dbOpenDynaset)

If 3 = 4 Then
TXTERROR:
MsgBox "The file Navigation.mdb must be in the same directory as this
executable for this file to work."
End If

End Sub



' ***************************************************************
' Function that takes either database value for Variation or
' Annual_Variation and breaks down the data to degrees, minutes,
' and direction
' ***************************************************************
Private Function GetDirectionInfo(ByVal dbData As String, ByRef myDir As
typeDirection)
Dim deg As String
Dim tmp As Variant
Dim indexDeg As Integer
Dim indexMin As Integer
Dim indexDir As Integer
deg = """"

' Make sure the string has data
If Len(Trim(dbData)) = 0 Then
MsgBox "String has not data"
GoTo FAIL_TRY
End If

' Search for the " which indicates degrees
indexDeg = InStr(dbData, deg)
If indexDeg <> 0 Then
tmp = Mid(dbData, 1, indexDeg - 1)
myDir.deg = tmp
End If

'Search for the minutes
indexMin = InStr(dbData, "'")
If indexMin <> 0 Then
If indexDeg <> 0 Then
tmp = Mid(dbData, indexDeg + 1, indexMin - indexDeg - 1)
myDir.min = tmp
Else
tmp = Mid(dbData, indexDeg + 1, indexMin - indexDeg - 1)
myDir.min = tmp
End If
End If

'Search for direction
indexDir = InStr(dbData, "E")
If indexDir <> 0 Then
myDir.dir = "E"
Else
indexDir = InStr(dbData, "W")
If indexDir <> 0 Then
myDir.dir = "W"

Else
MsgBox "There was an error in database. No direction
given on Variation or Annual_Variation"
myDir.deg = 0
myDir.min = 0
End If
End If


FAIL_TRY:
End Function



' ***************************************************************
' Function to add a record to the table. If added through the
' frmAddInfo form then the data should be ok, but this function has
' it's own error catching and reporting mechanism.
' ***************************************************************
Public Function AddRecord()
If NavRecords.Updatable Then
MsgBox "Going to update record here"

On Error GoTo UPDATE_FAIL
NavRecords.AddNew
On Error GoTo UPDATE_FAIL_NEW
NavRecords!Chart_ID = g_Chart_ID
NavRecords!Variation = g_Variation
NavRecords!Annual_Variation = g_Annual_Variation
NavRecords!Base_Year = g_Base_Year
NavRecords.Update
Else
MsgBox "The NAV_Navigation table has been locked and cannot be updated."
End If

' Error reporting for clean up if neccesary
If 3 = 4 Then
' Remove the last record?
UPDATE_FAIL_NEW:
NavRecords.MoveLast
NavRecords.Delete
NavRecords.MoveFirst

UPDATE_FAIL:
MsgBox "Table update failed at AddRecord() cause unknown"
End If

End Function

Public Function EditRecord()

' ' fills the combo box with Chart ID's
' strSQL = "SELECT Chart_ID from NAV_Navigation"
' Set NavRecords = NavDB.OpenRecordset(strSQL, NavDBOpenForwardOnly)
' If NavDB.RecordCount > 0 Then
' Chart_ID = (NavDB!Chart_ID)
' While Not NavDB.EOF
' cmbChart_ID.AddItem (NavDB!Chart_ID)
' NavDB.MoveNext
' Wend
' NavDB.Close
' cmbChart_ID.ListIndex = 0
' End If

' ' fills the combo box with Chart ID's
' strSQL = "SELECT Chart_ID from NAV_Navigation"
' Set NavRecords = NavDB.OpenRecordset(strSQL)
'
' NavRecords.MoveLast
' NavRecords.MoveFirst
'
' If NavRecords.RecordCount > 0 Then
' g_Chart_ID = (NavRecords!Chart_ID)
' While Not NavRecords.EOF
' cmbCombo1.AddItem (NavRecords!Chart_ID)
' NavRecords.MoveNext
' Wend
' NavRecords.Close
' cmbCombo1.ListIndex = 0
' End If

End Function

' ***************************************************************
' Function to refresh the list control
' ***************************************************************
Private Sub LoadDatabaseDataToList()

Dim d1 As Integer
Dim d2 As String
Dim d3 As String
Dim d4 As Integer
Dim recNum As Integer
Dim aDir As typeDirection
Dim res


' Clear the list control for new data
'On Error go
List1.Clear

' Move to the first record
NavRecords.MoveFirst

' As an example run through the table rows and columns
' and just add the data to a list control
recNum = 1
Do While Not NavRecords.EOF
List1.AddItem ("Record Number: " & recNum)

' Show chart id
d1 = NavRecords!Chart_ID
List1.AddItem ("Chart_ID: " & d1)

' Get the variation, print out database value then show how the
' GetDirectionInfo function works and breaks down the data.
d2 = NavRecords!Variation
List1.AddItem ("Variation: " & d2)

aDir.min = aDir.deg = 0
res = GetDirectionInfo(d2, aDir)
List1.AddItem (" Degree is : " & aDir.deg)
List1.AddItem (" Minute is : " & aDir.min)
List1.AddItem (" Direction is : " & aDir.dir)

' Show same for the Annual_Variation content
aDir.min = 0
aDir.deg = 0
d3 = NavRecords!Annual_Variation
List1.AddItem ("Annual_Variation: " & d3)

aDir.min = 0
aDir.deg = 0
res = GetDirectionInfo(d3, aDir)
List1.AddItem (" Degree is : " & aDir.deg)
List1.AddItem (" Minute is : " & aDir.min)
List1.AddItem (" Direction is : " & aDir.dir)

' Show the base year
d4 = NavRecords!Base_Year
List1.AddItem ("Base_Year: " & d4)

List1.AddItem (" ")
recNum = recNum + 1
NavRecords.MoveNext
Loop

End Sub
' ***************************************************************
' Form load function. It gives an example of how to run through
' the database tables and extract the direction information from
' both the Variation and the Annual_Variation values for use in
' calculations. Types of variables may need to be changed.
' ***************************************************************
Private Sub Form_Load()
Load_Database
Load frmAddInfo
LoadDatabaseDataToList
List1.AddItem ("Finished searching")
End Sub

' ***************************************************************
' Function called when form unloads. It frees all DB stuff.
' ***************************************************************
Private Sub Form_Unload(CANCEL As Integer)
On Error Resume Next

NavRecords.Close
NavDB.Close
NavWorkspace.Close
Unload frmAddInfo
End Sub

Private Sub mnuAbout_Click()
frmAbout.Show
End Sub

Private Sub mnuAddRecord_Click()
frmAddInfo.Show vbModal
End Sub

Private Sub mnuEditRecord_Click()
frmEditInfo.Show vbModal
End Sub

Private Sub mnuExit_Click()
Unload Me
Unload frmAddInfo
End
End Sub

Private Sub mnuLoadCalculator_Click()
frmCalculator2.Show vbModal
End Sub

Private Sub mnuRefreshList_Click()

LoadDatabaseDataToList
List1.AddItem ("Finished searching at" & Now)
End Sub









EditInfo form:



Option Explicit

'' Declarations for the database functionality
'Dim NavWorkspace As Workspace
'Dim NavDB As Database
'Dim NavRecords As Recordset
'Dim strSQL As String

'' Global public data for other forms to update so that new records can be
added
'' or current records can be edited.
Public g_Chart_ID As Integer
Public g_Variation As String
Public g_Annual_Variation As String
Public g_Base_Year As Integer

Private Type typeDirection
deg As Integer
min As Integer
dir As String
End Type

Public Sub ClearGlobalData()

' ***************************************************************
' Function to clear global data
' ***************************************************************

g_Chart_ID = -1
g_Variation = ""
g_Annual_Variation = ""
g_Base_Year = -1
End Sub


' ***************************************************************
' Function to load the database and set the record set.
' ***************************************************************
Private Sub Load_Database()

On Error GoTo TXTERROR
Set NavWorkspace = DBEngine.Workspaces(0)
Set NavDB = NavWorkspace.OpenDatabase(App.Path & "\Navigation.mdb")
Set NavRecords = NavDB.OpenRecordset("NAV_Navigation", dbOpenDynaset)

If 3 = 4 Then
TXTERROR:
MsgBox "The file Navigation.mdb must be in the same directory as this
executable for this file to work."
End If

End Sub



' ***************************************************************
' Function that takes either database value for Variation or
' Annual_Variation and breaks down the data to degrees, minutes,
' and direction
' ***************************************************************
Private Function GetDirectionInfo(ByVal dbData As String, ByRef myDir As
typeDirection)
Dim deg As String
Dim tmp As Variant
Dim indexDeg As Integer
Dim indexMin As Integer
Dim indexDir As Integer
deg = """"

' Make sure the string has data
If Len(Trim(dbData)) = 0 Then
MsgBox "String has not data"
GoTo FAIL_TRY
End If

' Search for the " which indicates degrees
indexDeg = InStr(dbData, deg)
If indexDeg <> 0 Then
tmp = Mid(dbData, 1, indexDeg - 1)
myDir.deg = tmp
End If

'Search for the minutes
indexMin = InStr(dbData, "'")
If indexMin <> 0 Then
If indexDeg <> 0 Then
tmp = Mid(dbData, indexDeg + 1, indexMin - indexDeg - 1)
myDir.min = tmp
Else
tmp = Mid(dbData, indexDeg + 1, indexMin - indexDeg - 1)
myDir.min = tmp
End If
End If

'Search for direction
indexDir = InStr(dbData, "E")
If indexDir <> 0 Then
myDir.dir = "E"
Else
indexDir = InStr(dbData, "W")
If indexDir <> 0 Then
myDir.dir = "W"

Else
MsgBox "There was an error in database. No direction
given on Variation or Annual_Variation"
myDir.deg = 0
myDir.min = 0
End If
End If


FAIL_TRY:
End Function



' ***************************************************************
' Function to add a record to the table. If added through the
' frmAddInfo form then the data should be ok, but this function has
' it's own error catching and reporting mechanism.
' ***************************************************************
Public Function AddRecord()
If NavRecords.Updatable Then
MsgBox "Going to update record here"

On Error GoTo UPDATE_FAIL
NavRecords.AddNew
On Error GoTo UPDATE_FAIL_NEW
NavRecords!Chart_ID = g_Chart_ID
NavRecords!Variation = g_Variation
NavRecords!Annual_Variation = g_Annual_Variation
NavRecords!Base_Year = g_Base_Year
NavRecords.Update
Else
MsgBox "The NAV_Navigation table has been locked and cannot be updated."
End If

' Error reporting for clean up if neccesary
If 3 = 4 Then
' Remove the last record?
UPDATE_FAIL_NEW:
NavRecords.MoveLast
NavRecords.Delete
NavRecords.MoveFirst

UPDATE_FAIL:
MsgBox "Table update failed at AddRecord() cause unknown"
End If

End Function

Public Function EditRecord()

' ' fills the combo box with Chart ID's
' strSQL = "SELECT Chart_ID from NAV_Navigation"
' Set NavRecords = NavDB.OpenRecordset(strSQL, NavDBOpenForwardOnly)
' If NavDB.RecordCount > 0 Then
' Chart_ID = (NavDB!Chart_ID)
' While Not NavDB.EOF
' cmbChart_ID.AddItem (NavDB!Chart_ID)
' NavDB.MoveNext
' Wend
' NavDB.Close
' cmbChart_ID.ListIndex = 0
' End If

' ' fills the combo box with Chart ID's
' strSQL = "SELECT Chart_ID from NAV_Navigation"
' Set NavRecords = NavDB.OpenRecordset(strSQL)
'
' NavRecords.MoveLast
' NavRecords.MoveFirst
'
' If NavRecords.RecordCount > 0 Then
' g_Chart_ID = (NavRecords!Chart_ID)
' While Not NavRecords.EOF
' cmbCombo1.AddItem (NavRecords!Chart_ID)
' NavRecords.MoveNext
' Wend
' NavRecords.Close
' cmbCombo1.ListIndex = 0
' End If

End Function

' ***************************************************************
' Function to refresh the list control
' ***************************************************************
Private Sub LoadDatabaseDataToList()

Dim d1 As Integer
Dim d2 As String
Dim d3 As String
Dim d4 As Integer
Dim recNum As Integer
Dim aDir As typeDirection
Dim res


' Clear the list control for new data
'On Error go
List1.Clear

' Move to the first record
NavRecords.MoveFirst

' As an example run through the table rows and columns
' and just add the data to a list control
recNum = 1
Do While Not NavRecords.EOF
List1.AddItem ("Record Number: " & recNum)

' Show chart id
d1 = NavRecords!Chart_ID
List1.AddItem ("Chart_ID: " & d1)

' Get the variation, print out database value then show how the
' GetDirectionInfo function works and breaks down the data.
d2 = NavRecords!Variation
List1.AddItem ("Variation: " & d2)

aDir.min = aDir.deg = 0
res = GetDirectionInfo(d2, aDir)
List1.AddItem (" Degree is : " & aDir.deg)
List1.AddItem (" Minute is : " & aDir.min)
List1.AddItem (" Direction is : " & aDir.dir)

' Show same for the Annual_Variation content
aDir.min = 0
aDir.deg = 0
d3 = NavRecords!Annual_Variation
List1.AddItem ("Annual_Variation: " & d3)

aDir.min = 0
aDir.deg = 0
res = GetDirectionInfo(d3, aDir)
List1.AddItem (" Degree is : " & aDir.deg)
List1.AddItem (" Minute is : " & aDir.min)
List1.AddItem (" Direction is : " & aDir.dir)

' Show the base year
d4 = NavRecords!Base_Year
List1.AddItem ("Base_Year: " & d4)

List1.AddItem (" ")
recNum = recNum + 1
NavRecords.MoveNext
Loop

End Sub
' ***************************************************************
' Form load function. It gives an example of how to run through
' the database tables and extract the direction information from
' both the Variation and the Annual_Variation values for use in
' calculations. Types of variables may need to be changed.
' ***************************************************************
Private Sub Form_Load()
Load_Database
Load frmAddInfo
LoadDatabaseDataToList
List1.AddItem ("Finished searching")
End Sub

' ***************************************************************
' Function called when form unloads. It frees all DB stuff.
' ***************************************************************
Private Sub Form_Unload(CANCEL As Integer)
On Error Resume Next

NavRecords.Close
NavDB.Close
NavWorkspace.Close
Unload frmAddInfo
End Sub

Private Sub mnuAbout_Click()
frmAbout.Show
End Sub

Private Sub mnuAddRecord_Click()
frmAddInfo.Show vbModal
End Sub

Private Sub mnuEditRecord_Click()
frmEditInfo.Show vbModal
End Sub

Private Sub mnuExit_Click()
Unload Me
Unload frmAddInfo
End
End Sub

Private Sub mnuLoadCalculator_Click()
frmCalculator2.Show vbModal
End Sub

Private Sub mnuRefreshList_Click()

LoadDatabaseDataToList
List1.AddItem ("Finished searching at" & Now)
End Sub



The combo box is loading but it does not edit. There is also a module which
declares the variables. What is wrong?



Kind Regards,

Dan.