VBA CheckBoxes

[RiderJon]

Hi:

I want to write a code for a "Check-Box" based on a data within a SubReport.

The problem I have is referring to the dataSource eg:

If tbl.Val = "0000" Then
chkID = True
End If

But I get an error for the "If tbl.Val = "0000&quot


Any suggestion?

 

[lhite]

Make sure that the NAME of the check box is chkID and not just the caption. It sounds like the statement works fine for false values, but for true it bombs.

-Larry

 

[WayneRyan]

RiderJon,

If tbl.Val = "0000" Then

You didn't say what the error # or description was.
If tbl is not a textbox then you would get the
"type mismatch" error.

If Access can't find a control matching the name tbl
then it will be unhappy.

Need more info ...

Wayne

 

[RiderJon]

Sorry for the lack of info. Here's more:

I have a Subreport that gathers data frm a table called "tbl" in the column named "Val". The values are: "0000", "0354" or "some other 4-digit number".

If the values are "0000" or "0354" I want to check the check box called "chkID". (and the name of the check box and NOT the caption is "chkID&quot. My problem lies in the "IF" statement mentioned above. I cannot refer to the original table i.e. "tbl" since it has a lot more data than needed. Also, I am extracting the "val" only for data with a matching "ID" from another table.

I know how to print the value "val" in the table "tbl". But I want it displayed in a checkbox.

Appreciate your help.

 

[lhite]

Could there be an extra space on the numbers, so that instead of "0000" the table actually has "0000 "? This is not unheard of.

What event do you have this code tied to? Also, you never answered RiderJon's question: what error message you were getting? That might help solve the problem.

-Larry

 

[RiderJon]

Larry:

I am new to VBA, so bear with me. What I did was:
1) open up the VBA editor, while I still had my "Report" in design view.
2) From the drop-down, I selected "Detail", since "Detail" is where my Checkboxes are.
3)Under what the ACCESS created for me by default, I typed in my code:

If tbl.Val = "0000" Then
chkID = True
End If


THE ERROR, I got was something like "tbl.val" not found. I am off from work currently, so I will post the exact error msgs tomorrow.

But can you tell me if my code is right. I mean (as I read more) do I need something like Reports!rpt.tbl.val or Me!Val or something along those lines?

Thanx.

 

[RiderJon]

Hello Wayne and Larry:

The error I get (today) is: "Compile Error: Variable not defined". But I believe my "Select Case tbl.Val" is wrong too.

The code (slightly changed since yesterday) is below - I added a second checkbox called "chkDes".

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
chkID = False
Select Case tbl.val
Case "0000"
chkID = True
Case "0354"
chkDes = True
chkID = True
Case Else
chkDes = True
End Select
End Sub

 

[RiderJon]

I just defined the chkID As Boolean. Now I get the same error for the "Select Case tbl.val" ..... I don't know how to define it or refer to it?

 

[lhite]

Does [tables].[tbl].[Val] work when it is substituted for tbl.val?

-Larry

 

[allanon]

I think the problem that you are having is not a logic problem but a referencing issue. You cannot reference a table field in this way.

If you are attaching the table to a form then you would reference the form field attached to the table field.

IE. Form MyForm is for selecting tbl records. Field MyVal is attached to tbl.val

The statement 'Select Case tbl.val' is invalid.

The statement needs to be 'Select Case Forms!MyForm!MyVal'

If you are not attaching this to a form but you are trying to reference this field strictly though code then you must assign the table to some sort of record source.

IE (Using DAO)

DIM rs as DAO.Recordset

Set rs = CurrenctDb.OpenRecordSet("tbl", dbOpenSnapShot)

then your statement would read 'Select Case rs!val'

In either case you do not actually reference the table directy in code.

 

[RiderJon]

Actually the data I am working with is a filtered set of "tbl". My data actually comes frm a Report (subreport) and NO queries or tbl exists for it.

I have tried below to no avail, so I moved to the Table 'tbl' (which I found is wrong):
Select Case Report!("rptCommunity Report&quot.val

I get error saying: "Compile Error: Type-declaration character does not match declared data type"

To reiterate, I am trying to read values from my sub report "rptCommunity Report", which is a subset, or filtered data, of the "val" column in table "tbl" (the same subreport also has the Check boxes.)

Sorry for the confusion (I am learning stuff myself as I am new to VBA).

 

[allanon]

You cannot reference fields on a report. If you go to a code module and type the code:

Reports("rptCommunity Report&quot.

You will see in the code completion that report field are not in the list. This means that they are not available for referencing.

If anyone can tell me I am wrong here please do so cause I have never seen such an instance successfully done.

If you want to reference these values you will have to create a query that mimics the results of the subreport.

 

[RiderJon]

Allanon,

that didn't work either. I tried that before like I said earlier (guess we posted at the same time).

I have tried:
Select Case Report!("rptCommunity Report&quot.val
Select Case Report!("rptCommunity Report&quot!val

And Keep Getting:
"Compile Error: Type-declaration character does not match declared data type"

 

[DonQuichote]

I think (without reading all the posts, sorry!) that the error is in the dot.

tbl.val

means that the object "tbl" has a property or method called "val".

tbl!val

means that the object "tbl" has a default method or property (in this case the collection of fields) which you pass the string "val" as a parameter.

So I think you need the bang operator (!) here instead of the dot. If that still causes a problem because

Val

is a reserved word, put "val" in square brackets.

Hope this helps

 

[allanon]

Sorry RiderJon. I will assume that you are meaning my post prior to last. As I said, you cannot reference a report field outside of the report. If you have code in the report code module then you can reference the report fields but only then.

 

[RiderJon]

Allanon:
No problem Allanon. But how do you reference a report field also called "val" in the same report module. My VBA code IS in the same report module i.e. "rptCommunity Report".

I know making a query would solve the issue, but I don't want to create an extra query just for 2 CheckBoxes representation. If all fails, guess will have to make a query for it.

Thanx.


Don:
I have tried both - bang and periods - and it failed. See my posting before this one. Any suggestions on referencing a "report field". Thank you.

 

[allanon]

Try Me.val

 

[RiderJon]

Strange.

I tried that and it says:
"Run Time Error: 2465
Community can't find the field 'val' referred in the expression."

However, I see the values (including the correct headings) when I check the "report-data" in "DataSheet" view.

PS: Community is the name of my DataBase.

 

[allanon]

Sorry. Once again I missed the boat. The reason that you cannot reference the field is because it is in the subreport. You can only reference a field in the same report as the code. The subreport is a different report. You cannot access the subreport fields from the parent report.

 

[DonQuichote]

There is an "s" missing in

Select Case Report!("rptCommunity Report")!val

For it is the Reports collection that contains the report. I also can imagine that its datatype is "Control" and that the

Select Case

is not too happy about that. You could use a conversion function for that (

Cstr

or

Cint

, for example)

Hope this is of any help to you
Best regards and a happy new year!