Array of a Form? 1

[jgarry]

Array or not to Array?

Thanks in Advance!

I want to create a module to process data on different forms. I’m thinking about making an array when a form is opened of the beginning data. Then if a specific button is pressed, create another array of form data and compare the two. Noting the changes between the first and second. Placing this information in a table for later use. Multiple changes can be made on the first form so a separate entry in the table would be made.

Currently I am using the before_update, and run a mod called auditchange. This works using the oldvalue and the newvalue. what I would like to do is do this on a form basis instead of each object. Taking a picture prior to adding or changing data on the form then comparing that to the finished form (if I choose).

Also I would like to write a module to do the array building so I do not have to do it for each form.

One last thing, I am using Access 97.

Please any thoughts or suggestions on this are welcomed.

Current:
In the form:

Private Sub PayFrequencyCode_BeforeUpdate(Cancel As Integer)
auditchange
End Sub

Private Sub PlanID_BeforeUpdate(Cancel As Integer)
auditchange
End Sub

Private Sub ReviewScore_BeforeUpdate(Cancel As Integer)
auditchange
End Sub

Private Sub auditchange()
On Error GoTo err_AuditChange

If IsNull(Me.ActiveControl) Then
' skip
Else
Set ctlCC = Me.ActiveControl
CGLOG txtTableName, publink, ctlCC, txtFormname, txtName
End If

exit_auditchange:
Exit Sub

err_AuditChange:
GoTo exit_auditchange
End Sub


**************** Module

sub CGLOG(txtTableName As String, publink As Long, pubCTL As Control, txtFormname As String, txtName As String)
Dim txt_new As String
Dim txt_old As String
Dim txt_Field As String

txt_Field = pubCTL.NAME

If IsNull(pubCTL.OldValue) Then
txt_old = "None"
Else
txt_old = pubCTL.OldValue
End If

If IsNull(pubCTL.Value) Then
txt_new = "None"
Else
txt_new = pubCTL.Value
End If

WAU txtTableName, publink, txt_Field, txt_old, txt_new, txtFormname, txtName

End Sub



Sub WAU(txtTableName, lngRecordNum, txtFieldName, OrgValue, CurValue, txtFormname, txtName)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("AuditTable")
rs.addnew
rs!TableName = UCase(txtTableName)
rs!RecordPrimaryKey = lngRecordNum
rs!FieldName = txtFieldName
rs!LoginName = (Forms![Global]![LoginName])
rs!MachineName = Forms![Global]![txt_G_MachineName]
rs!User = (Forms![Global]![txt_G_CurrentUser])
rs!FormName = UCase(txtFormname)
If IsNull(OrgValue) Or OrgValue = "" Then
OrgValue = " "
End If
rs!OriginalValue = OrgValue
rs!NewValue = CurValue
rs!EmpName = txtName
rs!dateAdded = Now()
rs.Update
rs.Close
db.Close
End Sub




Thank you Jim.

 

[hwkranger]

Did you hear the word "array" one day, look it up and decide this was some kind of something fun you'd like to try? (I'm kidding of course. You're trying to do this the hard way)

Try making a "Data Access Layer"

Here's an example of an one that I wrote (that I really didn't since I automated it):

'*******************************************************
'*                   AUTOGENERATED CODE FOR                                *
'*                       tblNCCBanks CLASS
'* '*                 BUILDER BY RANDALL VOLLEN                               *
'******************10/20/2005 11:45:40 AM***********************


'*************** BUISINESS DATA TYPE **********************'


Private Type biztblNCCBanks_TYPE
	intBankNumber AS INTEGER
	strRegion AS STRING
	intGroupBank AS INTEGER
	strCompany AS STRING
	strChargeGL AS STRING
	strCreditCost AS STRING
End Type


Private type biztblNCCBanks_STATE_TYPE
	BankNumber as Boolean
	Region as Boolean
	GroupBank as Boolean
	Company as Boolean
	ChargeGL as Boolean
	CreditCost as Boolean
End Type


Private adtbiztblNCCBanks_DATA as biztblNCCBanks_TYPE
Private adtbiztblNCCBanksBefore_DATA as biztblNCCBanks_TYPE
Private adtbiztblNCCBanks_STATE_DATA as biztblNCCBanks_STATE_TYPE
Private cn as new adodb.connection
Private blnFetched as boolean

'*************** Let/Get Properties **********************'
'*      Change these to public if public access is required.               *


Public Property Let BankNumber(intBankNumber as INTEGER)
	adtbiztblNCCBanks_DATA.intBankNumber=intBankNumber
End Property
Public Property Get BankNumber() as INTEGER
	BankNumber=adtbiztblNCCBanks_DATA.intBankNumber
End Property
Public Property Let Region(strRegion as STRING)
	adtbiztblNCCBanks_DATA.strRegion=strRegion
End Property
Public Property Get Region() as STRING
	Region=adtbiztblNCCBanks_DATA.strRegion
End Property
Public Property Let GroupBank(intGroupBank as INTEGER)
	adtbiztblNCCBanks_DATA.intGroupBank=intGroupBank
End Property
Public Property Get GroupBank() as INTEGER
	GroupBank=adtbiztblNCCBanks_DATA.intGroupBank
End Property
Public Property Let Company(strCompany as STRING)
	adtbiztblNCCBanks_DATA.strCompany=strCompany
End Property
Public Property Get Company() as STRING
	Company=adtbiztblNCCBanks_DATA.strCompany
End Property
Public Property Let ChargeGL(strChargeGL as STRING)
	adtbiztblNCCBanks_DATA.strChargeGL=strChargeGL
End Property
Public Property Get ChargeGL() as STRING
	ChargeGL=adtbiztblNCCBanks_DATA.strChargeGL
End Property
Public Property Let CreditCost(strCreditCost as STRING)
	adtbiztblNCCBanks_DATA.strCreditCost=strCreditCost
End Property
Public Property Get CreditCost() as STRING
	CreditCost=adtbiztblNCCBanks_DATA.strCreditCost
End Property

Public sub FetchRecord()
	dim rst as new adodb.recordset
	rst.open GetFetchSQL , cn, adOpenKeyset, adLockReadOnly
	If not isnull(rst(0)) then 
		adtbiztblNCCBanks_DATA.intBankNumber=rst(0)
	End If
	If not isnull(rst(1)) then 
		adtbiztblNCCBanks_DATA.strRegion=rst(1)
	End If
	If not isnull(rst(2)) then 
		adtbiztblNCCBanks_DATA.intGroupBank=rst(2)
	End If
	If not isnull(rst(3)) then 
		adtbiztblNCCBanks_DATA.strCompany=rst(3)
	End If
	If not isnull(rst(4)) then 
		adtbiztblNCCBanks_DATA.strChargeGL=rst(4)
	End If
	If not isnull(rst(5)) then 
		adtbiztblNCCBanks_DATA.strCreditCost=rst(5)
	End If
blnFetched = true
End Sub

Public Sub InsertRecord()
	cn.execute GetInsertSQL()
End Sub

Public Sub UpdateRecord()
	cn.execute GetUpdateSQL()
End Sub

Public Sub DeleteRecord()
	cn.execute GetDeleteSQL()
End Sub


Private Function GetFetchSQL() as string
	dim strSQL as string 
	strSQL = strSQL & "Select * from viwtblNCCBanks" & vbcrlf 
	strSQL = strSQL & "Where BankNumber = "&adtbiztblNCCBanks_DATA.intBankNumber & vbcrlf 
	GetFetchSQL = strSQL
End Function

'*** MUST EDIT INSERT WHEN USING AUTOINCREMENT ***
'* Fetches String for Inserting *
'* Edit this if auto numbers are used *
Private Function GetInsertSQL() as string
	dim strSQL as string 
	strSQL = "Insert Into viwtblNCCBanks("
	strSQL = strSQL & "[BankNumber],"
	strSQL = strSQL & "[Region],"
	strSQL = strSQL & "[GroupBank],"
	strSQL = strSQL & "[Company],"
	strSQL = strSQL & "[ChargeGL],"
	strSQL = strSQL & "[CreditCost])"
	strSQL = strSQL & "Values( "
	strSQL = strSQL & adtbiztblNCCBanks_DATA.intBankNumber & ","
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strRegion)) & ","
	strSQL = strSQL & adtbiztblNCCBanks_DATA.intGroupBank & ","
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strCompany)) & ","
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strChargeGL)) & ","
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strCreditCost)) & ")"
	GetInsertSQL = strSQL
End Function

'* Fetches String for Updating *
Private Function GetUpdateSQL() as string
	dim strSQL as string 
	strSQL = "Update viwtblNCCBanks SET "
	strSQL = strSQL & "[Region] = "
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strRegion)) & ", "
	strSQL = strSQL & "[GroupBank] = "
	strSQL = strSQL & adtbiztblNCCBanks_DATA.intGroupBank & ", "
	strSQL = strSQL & "[Company] = "
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strCompany)) & ", "
	strSQL = strSQL & "[ChargeGL] = "
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strChargeGL)) & ", "
	strSQL = strSQL & "[CreditCost] = "
	strSQL = strSQL & SQLPrepWithQuote(cStr(adtbiztblNCCBanks_DATA.strCreditCost)) & ", "
	strSQL = strSQL & " Where BankNumber= " & adtbiztblNCCBanks_DATA.intBankNumber
	GetUpdateSQL = strSQL
End Function

Private Function GetDeleteSQL() as string
	dim strSQL as string 
	strSQL = strSQL & "Delete * from viwtblNCCBanks" & vbcrlf 
	strSQL = strSQL & "Where BankNumber = " & adtbiztblNCCBanks_DATA.intBankNumber & vbcrlf 
	GetDeleteSQL = strSQL
End Function


Private Sub Class_Initialize()
	set cn = currentproject.connection
End Sub

Private Sub Class_Terminate()
	Set cn = Nothing
End Sub


Public Function Dirty() as boolean
	If adtbiztblNCCBanks_DATA.intBankNumber <> adtbiztblNCCBanksBefore_DATA.intBankNumber then 
		Dirty = true
	ElseIf adtbiztblNCCBanks_DATA.strRegion <> adtbiztblNCCBanksBefore_DATA.strRegion then 
		Dirty = true
	ElseIf adtbiztblNCCBanks_DATA.intGroupBank <> adtbiztblNCCBanksBefore_DATA.intGroupBank then 
		Dirty = true
	ElseIf adtbiztblNCCBanks_DATA.strCompany <> adtbiztblNCCBanksBefore_DATA.strCompany then 
		Dirty = true
	ElseIf adtbiztblNCCBanks_DATA.strChargeGL <> adtbiztblNCCBanksBefore_DATA.strChargeGL then 
		Dirty = true
	ElseIf adtbiztblNCCBanks_DATA.strCreditCost <> adtbiztblNCCBanksBefore_DATA.strCreditCost then 
		Dirty = true
	End If
End Function

In this example -- you would fetch a record inside the class, you can modify the object however you want, then if it's DIRTY you can either prompt to make the changes or not.

This is a bare bones example -- i'm not ever sure if it work 100% since I took it from code generated in my temp directory.


Randall Vollen
National City Bank Corp.

 

[jgarry]

Thanks I've always done it the hard way. I used arrays a lot in foxbase and cobol, this object stuff is easy if you can retrain the brain. but why do things simple.

Thanks for your response. I'm going to take a good look at what you sent. looks very intresting. and more then likly the what I want to go.

Thanks again

 

[formerTexan]

A second opinion. A couple of arrays sounds like a fine idea and could be done in a generic enough manner to serve as a basis for any number of forms.

On the load event pass the form's recordset to a sub for loading into an array Alas, but I don't believe the GETROWS function is available in A97, so you will have to code a loop through the form's recordset.

Then repeat the process after any changes in the form's data (a second loop) and then run a comparision between the two arrays (a third loop).

Actually each loop will have another embedded loop as you will have to handle columns as well as rows.

If you are already comfortable with arrays, then most of the hurdles should be behind you. Check back on any issues with the coing.

Cheers,
Bill

 

[jgarry]

Ok here is what I have done.

I declare the arrays public in the form.

I send the form , and first arrays to the module to be created.

return to the form for data input.

Upon completion of the form I send the form name and second array to be created. I then can compare the array elements to each other Identifying what has been changed.

I then can write the changes to a change table.

a majority of the code is reusable for any form. Im working on getting the code on the forms to as little as possable.



I have used this in other languages, I just didn’t know the correct syntax in vba 97.


On the form :

Option Compare Database
Option Explicit
' in files
Dim aInControl() As String
Dim intInCount As Integer
' Dim X As Integer
Dim aInValue() As String
' out files
Dim aOutControl() As String
Dim intOutCount As Integer
Dim X As Integer
Dim aOutValue() As String
Dim intCount As Integer
Dim strFormName As String

‘ Create the first Array from the current form.
Private Sub Form_Current()
intInCount = FormToArray_TSB(strFormName, aInControl(), aInValue())
End Sub


Private Sub checkChange()
Dim strRecKey
intOutCount = FormTo(strFormName, aOutControl(), aOutValue())

strRecKey = Me![Social Security Number]

For X = 0 To intOutCount - 1
If aInValue(X) <> aOutValue(X) Then
MarkFormChange aInValue(X), aOutValue(X), strFormName, strRecKey
End If

Next X
End Sub



At the Module:

Option Compare Database
Option Explicit
Function FormToArray(strFormName As String, arrIn() As String, strValue() As String) As Integer
Dim frmInTmp As Form
Dim ctlTmp As Control
Dim intCount As Integer
Dim intCounter As Integer
Dim fNeedsClosing As Boolean

On Error GoTo PROC_ERR

Set frmInTmp = Forms(strFormName)
intCount = frmInTmp.Count

ReDim arrIn(0 To intCount - 1)
ReDim strValue(0 To intCount - 1)

For Each ctlTmp In frmInTmp.Controls
arrIn(intCounter) = GetControlName(frmInTmp(intCounter))
strValue(intCounter) = ctlTmp
intCounter = intCounter + 1
Next ctlTmp
FormToArray = intCount

PROC_EXIT:
Exit Function

PROC_ERR:

If Err.Number = 2427 Then
strValue(intCounter) = "Cmd Button"
Resume Next
Else
MsgBox (Err.Number)
FormControlsToArray_TSB = 0
On Error Resume Next
End If

End Function
Function GetControlName(ctlIn As Control) As String
' Comments : returns the type of the passed control.
' Parameters: ctlIn - control to check
' Returns : string control type
'
Dim strType As String

On Error GoTo PROC_ERR

Select Case ctlIn.ControlType
Case acLabel
strType = "Label"
Case acRectangle
strType = "Rectangle"
Case acLine
strType = "Line"
Case acImage
strType = "Image"
Case acCommandButton
strType = "Command button"
Case acOptionButton
strType = "Option button"
Case acCheckBox
strType = "Check box"
Case acOptionGroup
strType = "Option group"
Case acBoundObjectFrame
strType = "Bound object frame"
Case acTextBox
strType = "Text box"
Case acListBox
strType = "List box"
Case acComboBox
strType = "Combo box"
Case acSubform
strType = "SubForm / SubReport"
Case acObjectFrame
strType = "Unbound object frame or chart"
Case acPageBreak
strType = "Page break"
Case 124
strType = "Page"
Case 123
strType = "Tab"

Case acCustomControl
strType = "ActiveX (custom) control"
Case acToggleButton
strType = "Toggle button"

End Select

GetControlName = strType

PROC_EXIT:
Exit Function

PROC_ERR:
GetControlName = ""
Resume PROC_EXIT

End Function

 

[StarPassing]

Why the second array? I use “Before(Form Name)” and “After()” functions in a module. The Before function requires a form name and it fills an array with all the control values on that form that the user may change. When exiting the form I call “After”, which compares the current control values on the form with the values in the Before array. If any has changed, it returns True otherwise its False.

Also, why worry about control names/types? You are only concerned with controls the user can change (i.e., Text Box, List Box, Combo Box, Option Group, Check Box.)

 

[jgarry]

StarrPassing, This is why I posted to get information. Good points. I will look into them. I seem to be trying to do things the hard way.

I had the name/types I think just for my enjoyment becase I wanted to see them. as I have been working on it I have discoverd that is is a wast of memory to even look up the fields.

Thanks for your opinion and suggestions.

Jim