Class Module

[Douglas J. Steele]

RA is a function. You can't declare a variable as a function, which means
your Dim RA As REDACTIVE line is incorrect.

When you saved your class module, you gave it a name. Let's assume that name
was DS. That means you declare the object as

Dim RA As DS

Then, in Test2SQL, you need

Set RA = New DS
If RA.REDACTIVE > "" Then

 

[DS]

This is my Class Module
Public Function REDACTIVE(strPath As String) As String
Dim strPath As String
strPath = Nz(DLookup("BackPath", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
End Function

Then at the top of the module on a form I have...
Option Compare Database
Option Explicit
Dim RA As REDACTIVE

Then in a procedjure I have...
Dim Test2SQL As String
If RA > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & RA & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

Any reason why this isn't working.
Thanks
DS

 

[DS]

Thanks Douglas

My Class Module Name is REDACTIVE

This at the top of the form module under Option Explicit
Dim RA As REDACTIVE


'Procedjure
Dim Test2SQL As String

Set RA = New REDACTIVE
If RA.REDACTIVE > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & RA & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If
End Sub

I get argument no optional on the if
Thanks
DS

 

[Douglas J. Steele]

Try renaming either the class or the function within the class. They can't
both be named the same thing.

 

[DS]

Try renaming either the class or the function within the class. They can't
both be named the same thing.

Still getting the argumant not optional.
Thanks
DS

 

[DS]

Once Again the class module.
Its name is REDACTIVE
Maybe the problem is here?

Public Function REDACT(strPath As String) As String
Dim strPath As String
strPath = Nz(DLookup("BackPath", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
End Function

ds

 

[AccessVandal via AccessMonster.com]

Hi DS,

First, I don’t think you can use the code in the Class Module. Use the
Standard Module instead.

Second, What do you want the Function to return?

Judging by the function in the code, you want to return a string of a path,
something like “C:\SomeFolder\SomeFile.XXXâ€. But in the Form’s module, your
code is very very very confusing.

What were you looking for in “RAâ€, Location of a file? Or a part of SQL
syntax?

However, your SQL update syntax is also wrong. What’s the “IN†for? ("UPDATE
table1 IN '" & RA & "' ").

The correct syntax is
UPDATE Orders SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK'
Or something like
UPDATE Orders SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry IN (Select Col1,Col2,..Coln From SomeTable Where someCol
Like ….)

 

[Douglas J. Steele]

You've said that you have to pass a path to the function. Why?

Public Function REDACT() As String
Dim strPath As String

strPath = Nz(DLookup("BackPath", "tblBackPath", _
"BackID =1 AND BackActive=-1"), "")

End Function

 

[DS]

Hi DS,

First, I don’t think you can use the code in the Class Module. Use the
Standard Module instead.

Second, What do you want the Function to return?

The Function should return the path to an external Database....

Judging by the function in the code, you want to return a string of a path,
something like “C:\SomeFolder\SomeFile.XXXâ€. But in the Form’s module, your
code is very very very confusing.

What were you looking for in “RAâ€, Location of a file? Or a part of SQL
syntax?

RA is the loction of the file....

However, your SQL update syntax is also wrong. What’s the “IN†for? ("UPDATE
table1 IN '" & RA & "' ").

The IN is pointing to the path of the file location in the external
database.

 

[DS]

You've said that you have to pass a path to the function. Why?

Public Function REDACT() As String
Dim strPath As String

strPath = Nz(DLookup("BackPath", "tblBackPath", _
"BackID =1 AND BackActive=-1"), "")

End Function

I'm getting confused!

The Class Module should get the path of the external database.
So...

Public Function REDACT() As String
Dim strPath as String
strPth = Nz(DLookup("BackPath","tblBackPath","BackID = 1 AND
BackActive=-1"),"")
End Function

This Function is used to get the path...it's saved as REDACTION.
Then on the forms I'm using...under Option Explicit I have
Dim RA As New REDACT

Then in the Procedjure of the forms I have...
Dim Test2SQL As String

If RA.REDACT > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & RA & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

Now the string that the class module is grabbing should be passed to the
SQL statement in the procedjure....Correct? Am I missing something.
This sounds simple enough. But...

Thanks
DS

 

[Douglas J. Steele]

I'm getting confused!

The Class Module should get the path of the external database.
So...

Public Function REDACT() As String
Dim strPath as String
strPth = Nz(DLookup("BackPath","tblBackPath","BackID = 1 AND
BackActive=-1"),"")
End Function

This Function is used to get the path...it's saved as REDACTION.
Then on the forms I'm using...under Option Explicit I have
Dim RA As New REDACT

Then in the Procedjure of the forms I have...
Dim Test2SQL As String

If RA.REDACT > "" Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & RA & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If

Now the string that the class module is grabbing should be passed to the
SQL statement in the procedjure....Correct? Am I missing something. This
sounds simple enough. But...

If the name of the class module is REDACTION, you need to use

Dim RA As REDACTION

I would advise very strongly against using the New keyword in your
declaration. Instead, in the procedure that's using it, put

Set RA = New REDACTION

immediate before

If RA.REDACT > "" Then


Pardon my bluntness, but I have to ask. Why are you using such a convoluted
approach? Having a class module doesn't buy you anything in this case.
Putting the function in a regular module, not bothering with the RA object
and just using

If REDACT() > "" Then

or, better,

If Len(REDACT()) > 0 Then

will accomplish the same results with far fewer headaches!

 

[DS]

If the name of the class module is REDACTION, you need to use

Dim RA As REDACTION

I would advise very strongly against using the New keyword in your
declaration. Instead, in the procedure that's using it, put

Set RA = New REDACTION

immediate before

If RA.REDACT > "" Then


Pardon my bluntness, but I have to ask. Why are you using such a convoluted
approach? Having a class module doesn't buy you anything in this case.
Putting the function in a regular module, not bothering with the RA object
and just using

If REDACT() > "" Then

or, better,

If Len(REDACT()) > 0 Then

will accomplish the same results with far fewer headaches!

Bluntness not minded! The reason this is happening is this.
I have my application just about finished. I need to make a duplicate
copy of the database in another database on a transaction by transaction
basis. The first db is linked, and I cannot link the duplicate database
because I don't want to change my table names so I'm using this
method;however the user needs to be able to change th epath that the
duplicate database is located in on the fly. I'm trying to avoid using
DLookup on every transaction to get the path of the duplicate databse.
So I thought if I use a Class Module the Class Module would grab the
path for me when the form (One of Many) is opened. This way I could go
directly into the statement

If blah blah > "" Then

As opposed to...
Dlookup...
If blah blah > "" Then

I also thought of setting the value on form open to either a dim or a
textbox whic\h seems to work fine. I'm still learning this so I do
appreciate your help and most of all your patience.
I just want to get the string as easy as possible without having to do
much for each transaction. A Class Module sounds good, but maybe it
isn't, maybe there are downsides that I don't realise. Speed? Like I
said I'm still relatively new and self-taught as well as a lot of help
from the wonderful people at these newsgroups!
Thanks
DS

 

[Douglas J. Steele]

What's the name of the module? If it's REDACT, rename it.

I mentioned to you the other day that modules cannot have the same name as
subs and functions. This is one of the many reasons why people use naming
conventions such as prefixing each module name with mdl or bas.

 

[DS]

OK So I have this behind a button on a form..

Dim Test2SQL As String
If Len(REDACT()) > 0 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & REDACT() & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
Else
End If


I have this in a module.....
not a form module but a regular module
its name is REDACT()

Public Function REDACT()
Dim strPath As String
strPath = Nz(DLookup("BackName", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
End Function

But i'm getting an onclick error,
Thanks
DS

 

[Douglas J. Steele]

Sorry the function name is REDACT()
The module name is mdl2

So what's the error message you're getting?

 

[George Nicholson]

Additions enclosed by *, which should be removed.

Public Function REDACT() *As String*
Dim strPath As String
strPath = Nz(DLookup("BackName", "tblBackPath", "BackID =1 AND
BackActive=-1"), "")
*REDACT = strPath*
End Function

HTH,

 

[DS]

What's the name of the module? If it's REDACT, rename it.

I mentioned to you the other day that modules cannot have the same name as
subs and functions. This is one of the many reasons why people use naming
conventions such as prefixing each module name with mdl or bas.

Sorry the function name is REDACT()
The module name is mdl2
Thanks
DS

 

[DS]

I was getting user type not defined, but that has since stopped, I
re-registered my DAO reference. I think its the code in the module.
Maybe it's the DLookup, I don't think it's returning a value. I tried
the imediate window, but I'm lost with that as well, nothing coming back.
I tried call REDACT()
I tried the DLookup in a textfield straight off and it worked.
If Len(REDACT())>0 Then
I get argument not optional.
Thanks
DS

 

[DS]

Just answered the immediate window answer.
I've never used that thing!
Is there anywhere that I can go to learn?
Once again
Thank You.
DS

 

[DS]

Douglas J. Steele wrote:
Hi Doug,
It works. See other post.
REDACT = strPath
was added at the bottom.
So I'm taking your suggestion with the module now that it works.
I'll just use Len(REDACT())>0 then
blah
else
end if
This is so simple it's scary!
Just what I wanted.
Thank you so very much for all of your help!
DS