audit trail code and VBA nameing conventions

[Stinky Pete]

Morning,

I've managed to successfully pop the Ausit Trail code from Allen
Browne into a couple of files now with great success. However, a new
file I have been modifying is causing me some furstration. So far,
all the tables in any of the files I have been working with have been
named with either no spaces of have included the _ as part of the file
name.

The table (which I did not name) causing me the greif is named
"NCF_Table New" which VBA is having a hissy fit with due to the
space. As I have never had to do it before, how do you make VBA
accept names with spaces such as this. Forms I understand in VBA as
they are in the [...] - is VBA the same way for tables? I do not want
to have to rename the table as this table is included in quite a
sustantial number of SQL and other queries and in forms and reports.

Hopefully as simple q from simple bloke ;-)

Stinky Pete ;-)

[Albert D. Kallal]

You will have to show the context of how you are using the table name that
fails.
if you using the tables collection, then

currentdb.tabledefs("NCF_Table New") should work fine.

Post the one line of code you have that is failing....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Stinky Pete]

On Mar 27, 10:09 am, "Albert D. Kallal"
<[e-mail]:

You will have to show the context of how you are using the table name that
fails.

if you using the tables collection, then

currentdb.tabledefs("NCF_Table New") should work fine.

Post the one line of code you have that is failing....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Albert, fair comment.

Anyway, the first line where this fails is

Function AuditDelBegin("NCF_Table New" As String, aud_Scales_tmp_tbl
As String, ID As String, lngKeyValue As Long) As Boolean

which is called from

Call AuditDelBegin("NCF_Table New", "aud_NCF_tmp_tbl", "ID", Nz(Me.ID,
0), bWasNewRecord)

but the code also have lines such as

"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3,
" & NCF_Table New & ".* " & _

that I need to fix up as well.

>From what I read in your intial post, it seems (and I hope) that I

need to declare the table name. Am i close??

Regards,

Stinky Pete ;-)

[Allen Browne]

Include square brackets around the name that contains a space, e.g.:
Call AuditDelBegin("[tblInvoice]", ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

On Mar 27, 10:09 am, "Albert D. Kallal"
<[e-mail]:

>You will have to show the context of how you are using the table name
that
fails.

if you using the tables collection, then

currentdb.tabledefs("NCF_Table New") should work fine.

Post the one line of code you have that is failing....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Albert, fair comment.

Anyway, the first line where this fails is

Function AuditDelBegin("NCF_Table New" As String, aud_Scales_tmp_tbl
As String, ID As String, lngKeyValue As Long) As Boolean

which is called from

Call AuditDelBegin("NCF_Table New", "aud_NCF_tmp_tbl", "ID", Nz(Me.ID,
0), bWasNewRecord)

but the code also have lines such as

"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3,
" & NCF_Table New & ".* " & _

that I need to fix up as well.

>>From what I read in your intial post, it seems (and I hope) that I

need to declare the table name. Am i close??

Regards,

Stinky Pete ;-)

[Don Leverton]

There is a utility program called "Find and Replace" by Rick Fisher
Consulting.
http://www.rickworld.com/
"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11*********************@p77g2000hsh.googlegro ups.com...

Morning,

I've managed to successfully pop the Ausit Trail code from Allen
Browne into a couple of files now with great success. However, a new
file I have been modifying is causing me some furstration. So far,
all the tables in any of the files I have been working with have been
named with either no spaces of have included the _ as part of the file
name.

The table (which I did not name) causing me the greif is named
"NCF_Table New" which VBA is having a hissy fit with due to the
space. As I have never had to do it before, how do you make VBA
accept names with spaces such as this. Forms I understand in VBA as
they are in the [...] - is VBA the same way for tables? I do not want
to have to rename the table as this table is included in quite a
sustantial number of SQL and other queries and in forms and reports.

Hopefully as simple q from simple bloke ;-)

Stinky Pete ;-)

[Stinky Pete]

On Mar 27, 12:53 pm, "Don Leverton"
<[e-mail]:

There is a utility program called "Find and Replace" by Rick Fisher
Consulting.http://www.rickworld.com/

"Stinky Pete" <[e-mail] in message

news:11*********************@p77g2000hsh.googlegro ups.com...

Morning,

I've managed to successfully pop the Ausit Trail code from Allen
Browne into a couple of files now with great success. However, a new
file I have been modifying is causing me some furstration. So far,
all the tables in any of the files I have been working with have been
named with either no spaces of have included the _ as part of the file
name.

The table (which I did not name) causing me the greif is named
"NCF_Table New" which VBA is having a hissy fit with due to the
space. As I have never had to do it before, how do you make VBA
accept names with spaces such as this. Forms I understand in VBA as
they are in the [...] - is VBA the same way for tables? I do not want
to have to rename the table as this table is included in quite a
sustantial number of SQL and other queries and in forms and reports.

Hopefully as simple q from simple bloke ;-)

Stinky Pete ;-)

Hi all,

Well, I've been popping square brackets as and where needed, however I
am still coming unstuck. Each time I make a record change in the form
the app halts and I fire off to the VBA error thingy. I'm going to
have a good look at all my syntax and typing to make sure I have not
made any silly mistakes and will pop a note back if I am still stuck.

Thanx once again for all the feedback and comments. I have again
learnt heaps although it might be a while as Optusnet are arguing with
my Visa card.

Stinky Pete ;-)

[Stinky Pete]

On Mar 27, 12:03 pm, "Allen Browne" <[e-mail]>
wrote:

Include square brackets around the name that contains a space, e.g.:
Call AuditDelBegin("[tblInvoice]", ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"StinkyPete" <[e-mail] in message

news:11**********************@e65g2000hsc.googlegr oups.com...

On Mar 27, 10:09 am, "Albert D. Kallal"
<[e-mail]:

You will have to show the context of how you are using the table name
that
fails.

if you using the tables collection, then

currentdb.tabledefs("NCF_Table New") should work fine.

Post the one line of code you have that is failing....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Albert, fair comment.

Anyway, the first line where this fails is

Function AuditDelBegin("NCF_Table New" As String, aud_Scales_tmp_tbl
As String, ID As String, lngKeyValue As Long) As Boolean

which is called from

Call AuditDelBegin("NCF_Table New", "aud_NCF_tmp_tbl", "ID", Nz(Me.ID,
0), bWasNewRecord)

but the code also have lines such as

"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3,
" & NCF_Table New & ".* " & _

that I need to fix up as well.

>From what I read in your intial post, it seems (and I hope) that I

need to declare the table name. Am i close??

Regards,

StinkyPete;-)- Hide quoted text -

- Show quoted text -

Hi Allen,

Well, I am getting there after a forced abscence. I have made the
corrections to the relevant sections of the form as you mentioned
above. Howvever, the code keeps on failing each time as it reckons an
expected identifier is not correct in the Function. I have changed
the Forms Call and Module Function code as follows:

Call AuditDelBegin("[NCR_Table New]", ....

Function AuditDelBegin("[NCR_Table New]" As String, .....

>From my simple understanding, in the Function, does the table name

with a spece need to be in aquare brackets as per the the forms Call
syntax? I'm asking as that is where the compiler always indicated the
expected identifier is wrong.

Regards,

Stinky Pete ;-)

[Allen Browne]

Yes, that's correct.

Include the square brackets around the name if it contains a space or other
odd character.

Do this for the table, the temp table, and the key field name.

The last argument is a number, so you don't use the square brackets there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Stinky Pete" <Pe**********@symbionhealth.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

On Mar 27, 12:03 pm, "Allen Browne" <[e-mail]>
wrote:

>Include square brackets around the name that contains a space, e.g.:
Call AuditDelBegin("[tblInvoice]", ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"StinkyPete" <[e-mail] in message

news:11**********************@e65g2000hsc.googleg roups.com...

On Mar 27, 10:09 am, "Albert D. Kallal"
<[e-mail]:
You will have to show the context of how you are using the table name
that
fails.

>if you using the tables collection, then

>currentdb.tabledefs("NCF_Table New") should work fine.

>Post the one line of code you have that is failing....

>--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

Albert, fair comment.

Anyway, the first line where this fails is

Function AuditDelBegin("NCF_Table New" As String, aud_Scales_tmp_tbl
As String, ID As String, lngKeyValue As Long) As Boolean

which is called from

Call AuditDelBegin("NCF_Table New", "aud_NCF_tmp_tbl", "ID", Nz(Me.ID,
0), bWasNewRecord)

but the code also have lines such as

"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3,
" & NCF_Table New & ".* " & _

that I need to fix up as well.

>>From what I read in your intial post, it seems (and I hope) that I
need to declare the table name. Am i close??

Regards,

>StinkyPete;-)- Hide quoted text -

- Show quoted text -

Hi Allen,

Well, I am getting there after a forced abscence. I have made the
corrections to the relevant sections of the form as you mentioned
above. Howvever, the code keeps on failing each time as it reckons an
expected identifier is not correct in the Function. I have changed
the Forms Call and Module Function code as follows:

Call AuditDelBegin("[NCR_Table New]", ....

Function AuditDelBegin("[NCR_Table New]" As String, .....

>>From my simple understanding, in the Function, does the table name

with a spece need to be in aquare brackets as per the the forms Call
syntax? I'm asking as that is where the compiler always indicated the
expected identifier is wrong.

Regards,

Stinky Pete ;-)