Error In Code ?

[Paul R]

Can anyone let me know why I am getting an error in the following bit of
code "Set chkOption = False"
also why the stOption is not passing into the Filter.

Dim stDocName As String
Dim db As Database
Dim stOption As String
Dim chkOption As Boolean

Set db = CurrentDb
Set chkOption = False (error here) ?

stDocName = "rpt_membership_card"

If Me.chk_a = 1 Then stOption = "A*" + stOption = "B*" + stOption =
"C*"
DoCmd.OpenReport stDocName, acPreview, , stOption
End

Many Thanks
Paul

 

[Newbie]

Have you tried removing Set i.e.
chkOption = false

HTH

 

[Paul R]

Thanks,
That maybe the answer for this error "Set chkOption = False (error here) ?".

Do you have any idea about the "stOption " ?

Paul

 

[Andrew Smith]

Paul,

What are you expecting to happen with the stOption bit?

Firstly, what is chk_a? I assume it is a check box. If it is, then it can
have the values True or False and these are stored as -1 or 0. Therefore it
will never have a value of 1, so the expression "Me.chk_a = 1" will always
return "False", and the bit of code that sets the value of stOption will
never run.

(Try typing the following into the intermediate window, and see what gets
returned:
?True = True
?True = -1
?True = 1)

In fact you only need to put:

If Me.chk_a Then ...

as the value of chk_a will be True or false, and the expression that comes
after "If" just has to be something that returns a value of True or False.

Now, if you get the code to run and set the value of stOption, what is going
to happen? I assume you want to concatenate some strings to get a suitable
Where clause for opening your report. What you've actually put is:

stOption = "A*" + stOption = "B*" + stOption

I think this will be interpreted as:

stOption = "A*" + (stOption = "B*") + stOption

The middle term will be treated as a logical expression and will return a
value of "False", and I would expect this to give Run Time error 13 (type
mismatch) as you are trying to add two strings and the value False. If you
want to concatenate strings and numbers or values then the & operator will
do it, but if you just rewrite it as:

stOption = "A*" & (stOption = "B*") & stOption

Then this will just return "False" - I'm not quite sure that I can explain
why, but really it's not important as I doubt this is what you want to do.

Can you post back with what you think the value of stOption should be, and
what you want it to do? Then, I'm sure someone will be able to help you
further. If you type the following into the intermediate window:

DoCmd.OpenReport stDocName, acPreview, , "Some text here"

(replacing "Some text here" with what you want stOption to be), then you can
test that it does what you want without needing the whole procedure to work.

Also, what is the purpose of these variables in your procedure?

stDocName As String
db As Database
chkOption As Boolean

 

[John Vinson]

Can anyone let me know why I am getting an error in the following bit of
code "Set chkOption = False"
also why the stOption is not passing into the Filter.

Dim stDocName As String
Dim db As Database
Dim stOption As String
Dim chkOption As Boolean

Set db = CurrentDb
Set chkOption = False (error here) ?

Remove the word SET. That's used for Object references, not for simple
variables.

db is a Database object so you need to "set" a pointer to it;
chkOption is a local variable, so you just assign it a value.

 

[Paul R]

Andrew,
Thank you for your coincide answer.

What I am trying to do is, I have a membership database that I need to print
out 1400+ membership cards. The report is called "rpt_membership_card"".
They are a number of problems why we cannot print all records at the same
time. So I have created a form that has a number of check boxes.

chk_a which I would like to assign all surnames that start with A, B, C
chk_b which I would like to assign all surnames that start with D, E, F
and so on.

what I would like to do is to allow the user to tick anyone of the 8 check
boxes and only those surnames that are appropriate to those check boxes
prints out, then write it to a table that the membership card has been
printed for that member.

The purpose of these variables in your procedure?

stDocName As String "set the report name"
db As Database "set the database"
chkOption As Boolean "I will use this to check if a check box has
been set"

I hope this will help you.

Thanks again
Paul R

 

[Kelvin]

If Me.chk_a = 1 Then stOption = "A*" + stOption = "B*" + stOption = "C*"

This line of code is doing a Boolean check to see if "A*" = "B*" = "C*",
which will never be true. If you are trying to pass 3 conditions, the
correct syntax would be

If Me.Chk_a = 1 Then stOption = "A* or B* or C*"

If * is suppose to be a wildcard then

If Me.Chk_a = 1 Then stOption = "Like A* or Like B* or Like C*"

Kelvin

 

[Kelvin]

If Me.Chk_a = 1 Then stOption = "(([FieldToCheck] Like A*) or
([FieldToCheck] Like B*) or ([FieldToCheck] Like C*))"

Kelvin

 

[Andrew Smith]

OK, I understand what you want now.

You don't need the variables db or chkOption, and you don't really need
stDocName as you only use it once (but at least you do use it!).

Here's my go at this code - not tested so you'll need to debug it:

'Start of code:
dim strWhere as String
dim strField as String
dim strDocName as String

strField = "NameOfYourField"
strDocName = "rpt_membership_card"

if me.chk_a then
strWhere = strWhere & strField " Between 'a' And 'd' OR "
end if

if me.chk_b then
strWhere = strWhere & strField " Between 'd' And 'g' OR "
end if

if me.chk_b then
strWhere = strWhere & strField & " Between 'g' And 'j' OR "
end if

(and so on for the other check boxes)

if len(strWhere) then
strWhere = left$(strWhere,len(strWhere)-4)
DoCmd.OpenReport strDocName,acPreview,,strDocName
Else
'Nothing selected so nothing to do!
End if
'End of code

This code allows you to check more than one check box if you want, and get
the results for all the checked boxes. If you specifically don't want this
to be possible then I'd suggest that you don't use check boxes. Either use
an option group containing the 8 check boxes, or use a list box. The code
will need modifying in these cases, but will actually be simpler and it will
be much easier to ensure that only one option can be selected.

For example, if you use an option group then your code will need to check
for the value of the option group and set the value of the variable
accordingly - probably using a Select Case statement:

'Start of code:
dim strWhere as String
dim strField as String
dim strDocName as String

strField = "NameOfYourField"
strDocName = "rpt_membership_card"

Select Case Me.NameOfOptionGroup
Case 1
strWhere = " Between 'a' And 'd'"
Case 2
strWhere = " Between 'd' And 'g'"
Case 3
strWhere = " Between 'g' And 'j'
Case 4
strWhere = " Between 'j' And 'm'
Case 5
strWhere = " Between 'm' And 'p'
Case 6
strWhere = " Between 'p' And 't'
Case 7
strWhere = " Between 't' And 'w'"
Case 8
strWhere = " >='w'"
End select
end if

if len(strWhere) then
strWhere = strField & strWhere
DoCmd.OpenReport strDocName,acPreview,,strDocName
Else
'Nothing selected so nothing to do!
End if
'End of code