Reply To: Data Retrieval/Update Form based processing

[sfritz]

You have been very helpful as usual and I am much further with my form processing and I thank you. I have one minor issue and have attached the code for you to look at. It is in the update logic, the record’s old value is not being kept, can you tell me why?

‘——————————————————————————-
‘ Insert your comments here
‘——————————————————————————-

Sub Main(Client, GWEvent)
dim oADOObj

set oADOObj = nothing

‘ if not OpenDataBase(oADOObj) then
‘ exit sub
‘end if

Menu.showmodal

‘CloseDataBase(oADOObj)
set oADOObj = nothing

End Sub

‘ Open Database connection
function OpenDataBase(byref aADOObj)
‘ << Setup your connection string and other process relate to opening database >>
OpenDataBase = false
Set aADOObj = CreateObject(“ADODB.Connection”)
if isobject(aADOObj) then
iConnnectString = “Driver={MySQL ODBC 3.51 Driver}; Data Source=Formativ”
aADOObj.Open iConnnectString
if err.number = 0 then
OpenDataBase = true
msgbox “Database is open”
else
call msgbox(“Failed to create ADODB.Connection object.”, vbCritical, CAPTION)
end if
end if
end function

‘ Close Database connection
function CloseDataBase(byref aADOObj)
if aADOObj is nothing then
exit function
end if
aADOObj.close
end function

function clear_form
FRM1.Name_data.text = “”
FRM1.ModalResult = MROK
end function

‘ Add record as the connection is already opened so no need to open in here
Sub AddNewRecord(byref aADOObj)
dim iSQLstring
dim iName, iRST, iUserid

if aADOObj is nothing then ‘** error Object required
exit sub
end if

iName = FRM1.Name_data.text
iUserid = groupwise.EnvUserID
iSQLString = “Insert into children (name, userid) values (‘” & iName & “‘,'” & iUserid & “‘)”
Set iRST = aADOObj.Execute(iSQLString)
Set iRST = Nothing
end sub

Sub TestButton2Click(Sender)
FRM1.Showmodal
End Sub

Sub NameButton1Click(Sender)
if FRM1.NameButton1.Caption = “Update Record” then
call UpdateExistingRecordDialog(iADOObj, old_name)
Clear_form
exit sub
end if
if not OpenDataBase(oADOObj) then
exit sub
end if
AddNewRecord(oADOObj)
Clear_form
CloseDataBase(OADOObj)
End Sub

sub StoreExistingRecordsDialog(byref iADOObj, byref iUpdateDlg, byref iListBox)
dim iRST

set iUpdateDlg = Utilities.NewControlBoxDialog
with iUpdateDlg
.Caption = CAPTION
.Title = “Existing records”
.Description = HRT & “Select an existing record to update”
end with

set iListBox = iUpdateDlg.AddListBoxControl
iListBox.Caption = “Records:”

iSQLString = “SELECT * FROM Children”
Set iRST = iADOObj.Execute(iSQLString)

Do Until (iRST.EOF)
iListBox.items.add(iRST(“Name”))
iRST.MoveNext
Loop

iRST.Close
Set iRST = Nothing

end sub

‘***** Problem area ****
‘aExtName is empty’

sub UpdateExistingRecordDialog(byref iADOObj, aExtName)
dim iNameUpdate, INameOld
iNameUpdate = FRM1.Name_data.text
iNameOld = aExtName
‘ If we press the Ol button to update
‘ iSQLUpdate = “UPDATE SET Name ='” & Name.Text & “‘ WHERE Name ='” & aExtName & “‘”
‘ Set iRST = iADOObj.Execute(iSQLUpdate)
msgbox iNameOld
msgbox iNameUpdate
call msgbox (“record updated.”, vbInformation, CAPTION)
end sub

Hope you have a great weekend.

sub EditRecord(byref iADOObj)

dim iUpdateDlg
dim iListBox
dim iNameSelected

cExit = 999
cEditRecord = 201
cDisplayAllRecords = 101
Cmd = cDisplayAllRecords

call StoreExistingRecordsDialog(iADOObj, iUpdateDlg, iListBox)

do while Cmd <> cExit

if Cmd = cDisplayAllRecords then
select case iUpdateDlg.execute
case Btn1 Cmd = cEditRecord
case else Cmd = cExit
end select
end if

if Cmd = cEditRecord then
if iListBox.Selected <> “” then
iNameSelected = iListBox.Selected
FRM1.Name_data.text = iNameSelected
FRM1.NameButton1.Caption = “Update Record”
FRM1.Showmodal
Cmd = cExit
else
call msgbox (“Select a record to update.”, vbInformation, CAPTION)
Cmd = cDisplayAllRecords
end if
end if
loop

end sub

Sub MenuButton1Click(Sender)
if not OpenDataBase(oADOObj) then
exit sub
end if
EditRecord(oADOObj)
CloseDataBase(OADOObj)
End Sub

Sub name_dataEnter(Sender)
dim old_name
old_name = FRM1.Name_data.text
msgbox old_name ‘ data is in this field
End Sub