Determining if a form has a table or query recordset source

T

Terry Reardon

Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag property for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the result
set for insertion into an audit trail table, and right now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
----------------------------------------------------------
Public Sub PostAudit(actionCode As String, Optional frm As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty(frm.Tag, "tableHint")
recidHint = GetProperty(frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces(recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add, if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID, recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "=" Then
PostAuditField audID, ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null, auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
 
T

TC

Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has one?".

For example, the query: SELECT DateOfBirth FROM tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I suspect that it is not
"the primary key of a query" :)

HTH,
TC


Terry Reardon said:
Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag property for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the result
set for insertion into an audit trail table, and right now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
----------------------------------------------------------
Public Sub PostAudit(actionCode As String, Optional frm As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty(frm.Tag, "tableHint")
recidHint = GetProperty(frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces(recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add, if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID, recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "=" Then
PostAuditField audID, ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null, auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
Click to expand...
 
T

Terry Reardon

No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products and a I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the Tables
collection with this table name and get the primary key
throught the indexes collection. These object libaries are
available to me.

Say now I have form called products and I used a Query as
the .RecordSource Property of a form. This Query is based
on several tables, the query's name is called qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.


-----Original Message-----
Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has one?".

For example, the query: SELECT DateOfBirth FROM tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I suspect that it is not
"the primary key of a query" :)

HTH,
TC


Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag property for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the result
set for insertion into an audit trail table, and right now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
-------------------------------------------------------- --
Public Sub PostAudit(actionCode As String, Optional frm As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty(frm.Tag, "tableHint")
recidHint = GetProperty (frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces(recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add, if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID, recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "=" Then
PostAuditField audID, ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null, auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
-------------------------------------------------------- ---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca
Click to expand...


.
Click to expand...
 
T

TC

What can I say? There is no such thing as the primary key of a query. You
need to explain what you actually mean by that term.

HTH,
TC


Terry Reardon said:
No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products and a I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the Tables
collection with this table name and get the primary key
throught the indexes collection. These object libaries are
available to me.

Say now I have form called products and I used a Query as
the .RecordSource Property of a form. This Query is based
on several tables, the query's name is called qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.


-----Original Message-----
Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has one?".

For example, the query: SELECT DateOfBirth FROM tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I suspect that it is not
"the primary key of a query" :)

HTH,
TC


Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag property for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the result
set for insertion into an audit trail table, and right now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
-------------------------------------------------------- --
Public Sub PostAudit(actionCode As String, Optional frm As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty(frm.Tag, "tableHint")
recidHint = GetProperty (frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces(recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add, if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID, recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "=" Then
PostAuditField audID, ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null, auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
-------------------------------------------------------- ---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

Never mind buddy.

T.
-----Original Message-----
What can I say? There is no such thing as the primary key of a query. You
need to explain what you actually mean by that term.

HTH,
TC


No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products and a I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the Tables
collection with this table name and get the primary key
throught the indexes collection. These object libaries are
available to me.

Say now I have form called products and I used a Query as
the .RecordSource Property of a form. This Query is based
on several tables, the query's name is called qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.


-----Original Message-----
Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has one?".

For example, the query: SELECT DateOfBirth FROM tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I suspect that it is not
"the primary key of a query" :)

HTH,
TC


Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag
Click to expand...
property
for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the result
set for insertion into an audit trail table, and
Click to expand...
right
now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
-----------------------------------------------------
Click to expand...
---
--
Public Sub PostAudit(actionCode As String, Optional
Click to expand...
frm
As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty (frm.Tag, "tableHint")
recidHint = GetProperty (frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces (recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary
Click to expand...
index
key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for
Click to expand...
Add,
if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID, recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "=" Then
PostAuditField audID, ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null, auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
-----------------------------------------------------
Click to expand...
---
---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca



.
Click to expand...
Click to expand...


.
Click to expand...
 
T

TC

I'll ignore your tone & ask you one final question.

What is the primary key of the query:

select dateofbirth from tblpersondetails ?

TC


Never mind buddy.

T.
-----Original Message-----
What can I say? There is no such thing as the primary key of a query. You
need to explain what you actually mean by that term.

HTH,
TC


No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products and a I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the Tables
collection with this table name and get the primary key
throught the indexes collection. These object libaries are
available to me.

Say now I have form called products and I used a Query as
the .RecordSource Property of a form. This Query is based
on several tables, the query's name is called qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.



-----Original Message-----
Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has
one?".

For example, the query: SELECT DateOfBirth FROM
tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I
suspect that it is not
"the primary key of a query" :)

HTH,
TC


"Terry Reardon" <[email protected]>
wrote in message
Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly routines do
string manipulation for me.

You will notice that I use "Hints" in the tag property
for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this hint. My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset that is
being added,modified or deleted.

With tables I do not need to worried about this, but
with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not on a
table? I need to determine the primary keys of the
result
set for insertion into an audit trail table, and right
now
the only way I figure to do this is a work-around in the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
----------------------------------------------------- ---
--
Public Sub PostAudit(actionCode As String, Optional frm
As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or
actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not
audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table, use
hint information
tableID = GetProperty (frm.Tag, "tableHint")
recidHint = GetProperty
(frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit
error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces (recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index
key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" & frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add,
if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID,
recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1) <> "="
Then
PostAuditField audID,
ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null,
auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" & Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
----------------------------------------------------- ---
---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca



.
Click to expand...


.
Click to expand...
Click to expand...
 
T

T

Listen, I appreciate feedback and can take constructive
critism when it comes to improving. But..
Obviously. You have not read nor/understood any of the
code and text I posted here, if you did . . . I really
think you wouldn't have ask that silly question.

Here is a real response to my question:
-----Original Message-----
i think it is possible to find primary keys by reading query sql, analyze
from clause to get list of tables used there and then using tabledef object
get primary key filed(s).
but not sure this can help you, because you also need to know which primary
key you have to store if you have more then one table.
i think the best is to store primary key fields name also in tag or other
place, and you write it there by yourself
HTH

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
T.


-----Original Message-----
I'll ignore your tone & ask you one final question.

What is the primary key of the query:

select dateofbirth from tblpersondetails ?

TC


Never mind buddy.

T.
-----Original Message-----
What can I say? There is no such thing as the primary
Click to expand...
key
of a query. You
need to explain what you actually mean by that term.

HTH,
TC


No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products
Click to expand...
and a
I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the Tables
collection with this table name and get the primary key
throught the indexes collection. These object
Click to expand...
libaries
are
available to me.

Say now I have form called products and I used a
Click to expand...
Query
as
the .RecordSource Property of a form. This Query is based
on several tables, the query's name is called qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.



-----Original Message-----
Are you asking, how to obtain the primary key of a query?

If so, the generic answer is: "who says any query has
one?".

For example, the query: SELECT DateOfBirth FROM
tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I
suspect that it is not
"the primary key of a query" :)

HTH,
TC


"Terry Reardon"
Click to expand...
wrote in message
Hi all:

My Problem:
I have an audit routine that monitors all user activity
while online with in the access 97 app. An example the
PostAudit routine is below, other utilitly
Click to expand...
routines
do
string manipulation for me.

You will notice that I use "Hints" in the tag property
for
forms that have Querys that are the record source for a
form. Forms based on tables do not have this
Click to expand...
hint.
My
problems lies in the fact that I need to determine the
primary key or index for a perticuar recordset
Click to expand...
that
is
being added,modified or deleted.

With tables I do not need to worried about this, but
with
queries I do.

So, my question is:
How can I determine the primary or index keys of a form
with a recordset that is based on a query and not
Click to expand...
on
a
table? I need to determine the primary keys of the
result
set for insertion into an audit trail table, and right
now
the only way I figure to do this is a work-around
Click to expand...
in
the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
--------------------------------------------------
Click to expand...
---
--- Optional
frm
As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As String
Dim dbs As Database, tdf As TableDef, idx As Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and record
index
If actionCode = "D" Or actionCode = "M" Or
actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not
audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a
Click to expand...
table,
use
hint information
tableID = GetProperty (frm.Tag, "tableHint")
recidHint = GetProperty
(frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit
error
tableID = Null
auditComment = actionCode & ": Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the parsed
recidHint field
recordID = ""
For i = 1 To NumPieces (recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary index
key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":"
Click to expand...
&
frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for Add,
if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID,
recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1)
Click to expand...
Then
PostAuditField audID,
ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null,
auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID)
Click to expand...
& ":" &
Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " & Err.Description
Resume exit_PostAudit

End Sub
--------------------------------------------------
Click to expand...
---
---
---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
T

TC

You misunderstand my purpose entirely. I am not trying to criticize you, at
all. And I have a very good idea of what you are trying to do.

I could easily have given you code to do what I >THINK< you are trying to
do. But in general I do not waste my time suggesting code, until I am
confident that I >KNOW< what the poster is trying to do. This saves
everyones' time & frustration - mine & yours alike.

You phrased your question using a concept ("primary key of a query") that
simply doesn't exist. All that I have tried to do, is to make you state your
problem more clearly. If you can't be bothered to do that, why should I
suggest any code?

Cheers,
TC


T said:
Listen, I appreciate feedback and can take constructive
critism when it comes to improving. But..
Obviously. You have not read nor/understood any of the
code and text I posted here, if you did . . . I really
think you wouldn't have ask that silly question.

Here is a real response to my question:
-----Original Message-----
i think it is possible to find primary keys by reading query sql, analyze
from clause to get list of tables used there and then using tabledef object
get primary key filed(s).
but not sure this can help you, because you also need to know which primary
key you have to store if you have more then one table.
i think the best is to store primary key fields name also in tag or other
place, and you write it there by yourself
HTH

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com
T.


-----Original Message-----
I'll ignore your tone & ask you one final question.

What is the primary key of the query:

select dateofbirth from tblpersondetails ?

TC


Never mind buddy.

T.

-----Original Message-----
What can I say? There is no such thing as the primary key
of a query. You
need to explain what you actually mean by that term.

HTH,
TC


"Terry Reardon" <[email protected]>
wrote in message
No. Not just any query. Only the ones that are used for
the .RecordSource Property of a form, where users are
editing records. Say I have a form call products and a
I
used the Products Table as the .RecordSource property.
Well, then genericly using VB I can go through the
Tables
collection with this table name and get the primary key
throught the indexes collection. These object libaries
are
available to me.

Say now I have form called products and I used a Query
as
the .RecordSource Property of a form. This Query is
based
on several tables, the query's name is called
qryProducts.
And it has multiple keys.

Now I can not use the .RecordSource Property to get the
Table name because it no longer is a table. This name
will
not be in the TableDefinitions.
So in the .Tag Property for the form I put hints, like
so..
:tableHint;MyPrimaryTable:KeyHint;MyINDEX:

Anyways, I just may leave it and move on. It works, I
just
thought their was an object library I could use to
genericaly find it.

Thanks.
T.



-----Original Message-----
Are you asking, how to obtain the primary key of a
query?

If so, the generic answer is: "who says any query has
one?".

For example, the query: SELECT DateOfBirth FROM
tblPersonDetails does not
in any sense have a "primary key".

So you'll need to clarify what you actually want. I
suspect that it is not
"the primary key of a query" :)

HTH,
TC


"Terry Reardon"
Click to expand...
wrote in message
Hi all:

My Problem:
I have an audit routine that monitors all user
activity
while online with in the access 97 app. An example
the
PostAudit routine is below, other utilitly routines
do
string manipulation for me.

You will notice that I use "Hints" in the tag
property
for
forms that have Querys that are the record source
for a
form. Forms based on tables do not have this hint.
My
problems lies in the fact that I need to determine
the
primary key or index for a perticuar recordset that
is
being added,modified or deleted.

With tables I do not need to worried about this, but
with
queries I do.

So, my question is:
How can I determine the primary or index keys of a
form
with a recordset that is based on a query and not on
a
table? I need to determine the primary keys of the
result
set for insertion into an audit trail table, and
right
now
the only way I figure to do this is a work-around in
the
tag property.

Example Code Executed on Delete: PostAudit "D", Me
-------------------------------------------------- ---
---
--
Public Sub PostAudit(actionCode As String, Optional
frm
As
Form, Optional auditComment = Null, Optional
requestComment = False)
On Error GoTo err_PostAudit

' Standard Audit Action Codes
' I = Login
' O = Logout
' U = Update Application
' M = Modify Record (automatically changed to A or E)
' A = Add New Record
' E = Edit Existing Record
' D = Delete Record
' R = Remove Record from List
' V = View Record
' S = System Function
' X = Audit Error, description place in comment field
' B = Background

Dim i As Long, errMsg
Dim audID As Long
Dim formID, tableID, recordID, recidHint As
String
Dim dbs As Database, tdf As TableDef, idx As
Index,
fld As field

Set dbs = CurrentDb
formID = Null
tableID = Null
recordID = Null
If varTableIDs = "" Then
InstallTableIDs
End If

'if form related audit, get form, table, and
record
index
If actionCode = "D" Or actionCode = "M" Or
actionCode
= "V" Then
formID = frm.Name
If actionCode = "V" Then
'viewing of a new (blank) record is not
audited
If frm.NewRecord Then
Exit Sub
End If
End If
'determine table name and record index
On Error Resume Next
Set tdf = dbs.TableDefs(frm.RecordSource)
If Err.Number <> 0 Then
On Error GoTo err_PostAudit
'record source is a query, not a table,
use
hint information
tableID = GetProperty
(frm.Tag, "tableHint")
recidHint = GetProperty
(frm.Tag, "recidHint")
If tableID = "" Or recidHint = "" Then
'if no hint information, report audit
error
tableID = Null
auditComment = actionCode & ":
Missing
audit hints in form: " & formID
actionCode = "X"
Else
'determine record index from the
parsed
recidHint field
recordID = ""
For i = 1 To NumPieces
(recidHint, ":")
recordID = recordID & ":" & frm
(GetPiece(recidHint, ":", i))
Next i
recordID = Mid(recordID, 2)
End If
Else
Set tdf = dbs.TableDefs(frm.RecordSource)
On Error GoTo err_PostAudit
tableID = frm.RecordSource
'determine record index from primary
index
key
recordID = ""
For Each idx In tdf.Indexes
If idx.Primary Then
For Each fld In idx.Fields
recordID = recordID & ":" &
frm
(fld.Name)
Next fld
Exit For
End If
Next idx
recordID = Mid(recordID, 2)
End If
End If

'if M action is to a new record, record A for
Add,
if
to an existing record, record to E for Edit
If actionCode = "M" Then
actionCode = IIf(frm.NewRecord, "A", "E")
End If
'write audit record
audID = PostAuditRecord(actionCode, tableID,
recordID,
auditComment, requestComment)

'if A or E action, write field changes
If actionCode = "A" Or actionCode = "E" Then
Dim ctl As Control
'check each valid data field on the form
For Each ctl In frm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is
ComboBox Or TypeOf ctl Is ListBox Or TypeOf ctl Is
CheckBox Or TypeOf ctl Is OptionGroup Then
'ignore calculated data fields
If Left(ctl.ControlSource, 1)
Click to expand...
Then
PostAuditField audID,
ctl.ControlName,
ctl.oldValue, ctl.value
End If
End If
Next ctl
End If


exit_PostAudit:
Exit Sub

err_PostAudit:
errMsg = Err.Description
On Error GoTo err_PostAudit_Abort
MsgBox "ERROR: PostAudit [1] .. " & errMsg
audID = PostAuditRecord("X", Null, Null,
auditComment
& ":ERROR:" & Nz(formID) & ":" & Nz(tableID) & ":" &
Nz
(recordID) & ":" & errMsg)
Resume exit_PostAudit

err_PostAudit_Abort:
MsgBox "ERROR: PostAudit [2] .. " &
Err.Description
Resume exit_PostAudit

End Sub
-------------------------------------------------- ---
---
---
Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca



.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form Result Source ->Query/Table (Or) 2
Log changes code 2
Audit code check 1
Transaction deadlock on inserting into a table 49

Top