Dear Friends,

I have a main form and an EditInfo form. The combo box is now loading.
However I can get the editinfo form to modify the records in the database.
Can anyone help please?
The code is:

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:





'thanks to Mianne for help on this

Private Sub cmbCombo1_Click()
' strSQL = "SELECT Chart_ID from NAV_Navigation where ChartID = [" &
cmbCombo1.Text & "]"
'
' ' Find the Chart ID
' Set NavRecords = NavDB.OpenRecordset(strSQL)
' If Not NavRecords.RecordCount = 0 Then
' Chart_ID = NavRecords!Chart_ID
' End If
' NavRecords.Close
End Sub



Private Sub Command4_Click()
Me.Hide
End Sub
Private Sub Command5_Click()

Dim errMsg As String

' Clear out the global data in the main form
frmMain.ClearGlobalData

'Set the data only if it is valid. If anything fails then do not
'call the update and reset the globals in the main form
On Error GoTo CHART_ERROR
frmMain.g_Chart_ID = txtChart_Id.Text

On Error GoTo VAR_ERROR
frmMain.g_Variation = txtNewVariation.Text

On Error GoTo ANNUAL_VAR_ERROR
frmMain.g_Annual_Variation = txtNewAnnualVariation.Text

On Error GoTo BASE_YEAR_ERROR
frmMain.g_Base_Year = txtNewBaseYear.Text


' Causes the update to occur
frmMain.Update

' Now make the window go away, no error to report.
Me.Hide

' Error reporting based on input. Only executed on error from 3=4
If 3 = 4 Then
CHART_ERROR:
errMsg = "Chart_ID format is invalid integer"
GoTo REPORT_ERROR
VAR_ERROR:
errMsg = "Variation format error."
GoTo REPORT_ERROR
ANNUAL_VAR_ERROR:
errMsg = "Annual Variation format error."
GoTo REPORT_ERROR
BASE_YEAR_ERROR:
errMsg = "Base year format error."
GoTo REPORT_ERROR

REPORT_ERROR:
errMsg = errMsg & " Update failed." & Chr(10) & "NOTE: All fields
are required."
MsgBox errMsg

'Clear the globals in main.frm
frmMain.ClearGlobalData
End If

End Sub






Private Sub Form_Load()

' 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 Sub



There is also a Module with the varaiables declared.




Kind Regards,


Dan.