473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access VBA cannot find "variable"

Hello,
I am having trouble with my database in Access. The problem lies in my report. I have created a report based off a query. The query contains all values that I'll need for my report- some will be directly on the report in text boxes, etc and others will be needed for calculations only and so are not directly on the report. My problem is when I'm writing the code for my text box "TriagePercent" which should, based on the AuditNum (pk) calculate the triage percent for each audit. The triage percent is basically checking to see if fields are true or false. If they're true, I'm adding one to my counter. At the end, I'm going to divide the counter by the number of items it counted (which will be hard-coded, never going to change) to get the Triage percent. Then I want that value to be shown in the text box on my report. Right now to simplify things, I'm just trying to get the counter value to display. I have spent hours trying to figure this out, I'm new to Access and VBA but have experience in coding in Java. I'm not sure if I put this under the correct Event, that's been something that's really difficult for me to determine.
Here's the shortened, tester version of VBA:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TriagePercent_BeforeUpdate(Cancel As Integer)
  2.     Dim numYes As Integer
  3.     numYes = 0
  4.     If Me.TriagNum.Value = True Then
  5.         numYes = numYes + 1
  6.     End If
  7.     If Me.PulmStatus.Value = True Then
  8.         numYes = numYes + 1
  9.    End If
  10.     Me.TriagePercentLabel.Caption = numYes
  11. End Sub
Nothing shows up at all, and when I try to compile the code, it says "method or data member not found".
Help, please? I feel like this should be very very simple coding but it's turning out to be difficult for me.
Mar 25 '09 #1
5 2857
ChipR
1,287 Expert 1GB
Add a field to your query with

myFieldAsNumber: iif([myField] = 'True', 1, 0)


Then, stick a control in a header/footer that is

=Sum([myFieldAsNumber])


I'm not sure what part of your code the error is related to, but that event wouldn't normally be triggered on a report.
Mar 26 '09 #2
NeoPa
32,556 Expert Mod 16PB
You've done a pretty good job of laying out your question but there are a couple of issues that could be clarified :
  1. Which line of the code does the error get reported on?
  2. Is this a report or a form? I don't believe there is an Before Update event for a report or any of its controls.
Mar 26 '09 #3
NeoPa, the error is triggered on line 4, If Me.TriagNum.Value = True Then .
If I comment out that line, the error still occurs, but on line 7, If Me.PulmStatus.Value = True Then . This is a report, TriagePercent is a text box. The report is not called TriagePercent, so no confusion there. As far as I know, there is a Before Update event since it appears in my program as a possible selection choice... ?

ChipR, I tried your suggestion but then realized it's not really what I'm looking for. I need a list of all of the audits, sorted by therapist, with each audit having these values. So, as far as I know, I can't put the sum in the header/footer. I really need it to be in the detail section of the report with all of my other calculations.
Mar 27 '09 #4
ADezii
8,834 Expert 8TB
  • Reports do not have a BeforeUpdate() Event
  • I'm having a little trouble understanding this one, but is something like this what you are looking for as far as an Aggegate Total per Audit?
    Expand|Select|Wrap|Line Numbers
    1. DCount("*", "qryAudits", "[AuditNum] = <VALUE> And [TriagNum] = True") + _
    2. DCount("*", "qryAudits", "[AuditNum] = <VALUE> And [PulmStatus] = True")
Mar 28 '09 #5
NeoPa
32,556 Expert Mod 16PB
@accesstruggle
Very curious.

I've looked and cannot find any circumstances where a TextBox control on a Report has any events at all, and nothing on a report has a BeforeUpdate event as far as I can see.

Can you explain why there appears to be one available to you in your project?
Mar 31 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Phil Powell | last post by:
print_r("From index.php 20: $prefix: stateXML = $stateXML<P>"); foreach (array('state', 'country') as $prefix) { print_r("From index.php 21: stateXML = $stateXML<P>"); ${$prefix . 'XML'} =...
2
by: \Dandy\ Randy | last post by:
Hello everyone. I have been following misc posts, as well as reading several FAQ's on this issue, unfortunatley I cannot locate a solution. I am hoping that someone will be able to provide me with...
2
by: Lu | last post by:
Hello, I am wondering how to protect a global variable in a header file from external access. So I googled and found: "The keyword 'static' has two different uses, depending on whether it is...
2
by: Gentian Hila | last post by:
Hi, I am a beginner with Visual C++ (I am using .NET 2002) MY problem is that sometimes the main dialog class dissapears from the class view ( so if my project is named Test, I cannot see CTestDlg...
4
by: Friday | last post by:
Being an Old L.A.M.P guy, I beg you to please excuse my ignorance of dot.net (and all things Windows, for that matter). As part of an experiment (to learn enough ASP/VB.net to port a series of ...
2
by: Kevin Frey | last post by:
Is it possible to get c# perform a using statement where the namespace for the using is not specified literally but instead comes from a variable, a token, a predefined value etc. We have...
11
by: gg9h0st | last post by:
i saw a code refactorying onload event listener window.onloadListeners=new Array(); window.addOnLoadListener=function(listener) { window.onloadListeners=listener; } why declare the...
2
by: John Kelsey | last post by:
I am an old, longtime C programmer surprised and confused by an error message I'm getting from my VS2005 compiler... "Cannot pass 'Item' as a ref or out argument because it is a 'foreach...
3
AnuSumesh
by: AnuSumesh | last post by:
Hi, I have created two web pages say first.asp and second.asp. first.asp has a link of second.asp and username is embedded in url of second.asp.(second.asp?username=dvhsdghsdg) Second.asp has...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.