Query table not updating as expected

Hi,

I have a table concerning Lab Tests which I present using a form. One
of the fields in this table is called Test Status where the user can
select the status via a combo box. Under this combo I have a label box
which shows a summary of the status for the entire table e.g. OK: 10,
NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a
function to change this label. This function does something like this
-

Private Sub UpdateLabelTestScriptStatus()
    Dim recordSetTestSuiteStatus As DAO.Recordset
    Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select *
from qryTestStatusCount")

    captionText = ""

    recordSetTestSuiteStatus.MoveFirst
    firstWhileIteration = True
    Do While Not recordSetTestSuiteStatus.EOF

        If firstWhileIteration <> True Then
            captionText = captionText & ",  "
        Else
           firstWhileIteration = False
        End If

        captionText = captionText &
recordSetTestSuiteStatus.Fields("StatusType") & ": "
        captionText = captionText &
recordSetTestSuiteStatus.Fields("CountOfStatusType")

        recordSetTestSuiteStatus.MoveNext 'move to next record in
recordset
    Loop

    LabelTestScriptStatus.Caption = captionText

End Sub

The problem is that the line -

Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select * from
qryTestStatusCount")

is always returning back the same values. It seems to me that the
table concerned with the query qryTestStatusCount needs to be updated
at the begining of UpdateLabelTestScriptStatus(). If so, how do I do
this?

Thanks for your help,

Barry.

0
bg_ie
5/21/2007 7:39:43 AM
access 16762 articles. 3 followers. Follow

2 Replies
664 Views

Similar Articles

[PageSpeed] 47

In news:1179733183.455776.51930@n15g2000prd.googlegroups.com,
bg_ie@yahoo.com <bg_ie@yahoo.com> wrote:
> Hi,
>
> I have a table concerning Lab Tests which I present using a form. One
> of the fields in this table is called Test Status where the user can
> select the status via a combo box. Under this combo I have a label box
> which shows a summary of the status for the entire table e.g. OK: 10,
> NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a
> function to change this label. This function does something like this
> -
>
> Private Sub UpdateLabelTestScriptStatus()
>    Dim recordSetTestSuiteStatus As DAO.Recordset
>    Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select *
> from qryTestStatusCount")
>
>    captionText = ""
>
>    recordSetTestSuiteStatus.MoveFirst
>    firstWhileIteration = True
>    Do While Not recordSetTestSuiteStatus.EOF
>
>        If firstWhileIteration <> True Then
>            captionText = captionText & ",  "
>        Else
>           firstWhileIteration = False
>        End If
>
>        captionText = captionText &
> recordSetTestSuiteStatus.Fields("StatusType") & ": "
>        captionText = captionText &
> recordSetTestSuiteStatus.Fields("CountOfStatusType")
>
>        recordSetTestSuiteStatus.MoveNext 'move to next record in
> recordset
>    Loop
>
>    LabelTestScriptStatus.Caption = captionText
>
> End Sub
>
> The problem is that the line -
>
> Set recordSetTestSuiteStatus = CurrentDb.OpenRecordset("Select * from
> qryTestStatusCount")
>
> is always returning back the same values. It seems to me that the
> table concerned with the query qryTestStatusCount needs to be updated
> at the begining of UpdateLabelTestScriptStatus(). If so, how do I do
> this?

It's not clear how the form and combo box are related to the results of 
qryTestStatusCount, but it may be that you need to force the current 
record to be saved before running that query.  You could do that by 
executing

    Me.Dirty = False

after the combo box is updated, before you call 
UpdateLabelTestScriptStatus().

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
5/21/2007 9:28:40 AM
On 21 Maj, 11:28, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com> wrote:
> Innews:1179733183.455776.51930@n15g2000prd.googlegroups.com,
>
>
>
>
>
> b...@yahoo.com <b...@yahoo.com> wrote:
> > Hi,
>
> > I have a table concerning Lab Tests which I present using a form. One
> > of the fields in this table is called Test Status where the user can
> > select the status via a combo box. Under this combo I have a label box
> > which shows a summary of the status for the entire table e.g. OK: 10,
> > NOT_OK: 1, NOT_TESTED: 5. When the combo box is changed, I issue a
> > function to change this label. This function does something like this
> > -
>
> > Private Sub UpdateLabelTestScriptStatus()
> >    Dim recordSetTestSuiteStatus As DAO.Recordset
> >    Set recordSetTestSuiteStatus =3D CurrentDb.OpenRecordset("Select *
> > from qryTestStatusCount")
>
> >    captionText =3D ""
>
> >    recordSetTestSuiteStatus.MoveFirst
> >    firstWhileIteration =3D True
> >    Do While Not recordSetTestSuiteStatus.EOF
>
> >        If firstWhileIteration <> True Then
> >            captionText =3D captionText & ",  "
> >        Else
> >           firstWhileIteration =3D False
> >        End If
>
> >        captionText =3D captionText &
> > recordSetTestSuiteStatus.Fields("StatusType") & ": "
> >        captionText =3D captionText &
> > recordSetTestSuiteStatus.Fields("CountOfStatusType")
>
> >        recordSetTestSuiteStatus.MoveNext 'move to next record in
> > recordset
> >    Loop
>
> >    LabelTestScriptStatus.Caption =3D captionText
>
> > End Sub
>
> > The problem is that the line -
>
> > Set recordSetTestSuiteStatus =3D CurrentDb.OpenRecordset("Select * from
> > qryTestStatusCount")
>
> > is always returning back the same values. It seems to me that the
> > table concerned with the query qryTestStatusCount needs to be updated
> > at the begining of UpdateLabelTestScriptStatus(). If so, how do I do
> > this?
>
> It's not clear how the form and combo box are related to the results of
> qryTestStatusCount, but it may be that you need to force the current
> record to be saved before running that query.  You could do that by
> executing
>
>     Me.Dirty =3D False
>
> after the combo box is updated, before you call
> UpdateLabelTestScriptStatus().
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)- D=F6lj citerad text -
>
> - Visa citerad text -

Excellent, that was my problem thanks.

0
bg_ie
5/21/2007 12:28:09 PM
Reply:

Similar Artilces:

After windows update, .jpg coming through as .dat files?
Using Vista Office 2007 and did som recent updates. Now when i send emails with pictures from my computer, they are coming through as .dat files? I go to my webmail and that's how they are being received from my webmail provider. Called them and they're not aware of any fixes or if it's related to Microsoft updates. Any suggestions? You are using RTF message format. See http://www.slipstick.com/problems/alwaysrtf.asp -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstic...

Query Problems
I have a table of ecards that is populated from a website and includes receiver, sender, and Team leader. The problem I am having is names can be entered into the table like this "Tom Thumb", but Team leader is a dropdown menu that is constant and lists name like this: "Tom S. Thumb". When I make a query to find all the members of Tom S. Thumb's team, I get everyone except Tom Thumb, who is member too. Tom Thumb has his own higher team leader, so I can't change that. How do I get Tom Thumb to be included when I enter "Tom S. Thumb" as Team Leader? ...

Excel web query returns no data
When I enter a web addreess into IE it shows the page When I set up a web query in Excel it allows me to select the relevant tables, but then returns no data The same Excel query worked on previuous versions of Excel Web address for query http://www.bmreports.com/servlet/com.logica.neta.bwp_PanBM DataServlet?param1=T_CNQPS- 4&param2=&param3=&param4=&param5=2001-04-01&param6=* Any help appreciated ...

Windows update site problems
So whats happening with the windows update site ?I keep getting a message telling me there is a problem with the web page (am trying to update new installation of XP Home),is it me or the site,and when will it be sorted out? -- Be happy,Strat4axe "stat4axe" <strat4axe@hotmail.com(donotspam)> wrote in message news:36618CFD-EF82-4F82-A7C3-90CF2DDCCEA5@microsoft.com... > So whats happening with the windows update site ?I keep getting a message > telling me there is a problem with the web page (am trying to update new > installation of XP Home),is it me o...

Query to Mail Merge issue
I am running Access2003 on WinXP SP2. I send out dues letters for members of a retiree group. When I try to do a mail merge to my Dues Query all goes fine except the telephone number in the query which is (555)555-1234 comes out 5555551234. If I copy the query into an excel spreadsheet and use that as the datasource for the mail merge it works fine. Is there something I need to do to format the query so it merges with the right format. Thanks Billa In the query format the field to include the parentheses and dash. In query design view, instead of the field name put an expression alo...

RE: Use that security update
--dedtgpho Content-Type: multipart/related; boundary="yzkfzsfqtv"; type="multipart/alternative" --yzkfzsfqtv Content-Type: multipart/alternative; boundary="qeviqcxczwaquy" --qeviqcxczwaquy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to hel...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

Too many problems while updating information online
Several of my accounts are unable to update information online using MS Money 2007 Deluxe - ING Direct - Bestbuy Card (HSBC) - MBNA - Bank of America This is frustrating!! Anyone else with the same problems? Just keep trying, eventually it will work. If you're in a bind, download the QIF or OFX files from the institutions web sites -- B of A works better that way anyway :) Paul McKenna <Paul McKenna@discussions.microsoft.com> wrote: >Several of my accounts are unable to update information online using MS Money >2007 Deluxe > >- ING Direct >- Bestbuy Card (HSBC...

Outlook 2003 Folder & Favorite-folder synch/update problems
I've been using Outlook since 1998 and I make my living at software support, so I'm usually hard to stump, but I'm sucking wind on this one. The problems below all started on the remote Outlook copy, so I have to imagine they're connected to the remote setup and OST files. But I've been through the settings and tried to re-synch, cleared the folder store and recreated it, etc., and nothing seems to correct the problems. Can anyone suggest a systematic way to diagnose and fix what's happening? Here's the story: I'm running Outlook 2003 (all current patches: ...

Pivot Table with Quarter Subtotals across the top
Hello, I'm constantly creating pivot tables that show sales information by date across the top from left to right. I would love to insert a subtotal at the end of each calender quarter to subtotal the last three months. As it stands right now I just drop in the "Month" field from the data into the pivot table and the months flow through the pivot table. Is it possible to insert a quarter subtotal, say "1Q07" that will sum Jan-07, Feb-07 & Mar-07? Thanks in advance, Dza In 2007- Select any date heading, on the options ribbon click Group Field in the Group g...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

Query Criteria issue
Access 2003: Trying to create a demographics report that will allow the following. Gender example On the form "frmStudentDemographics" is a drop down field named "cboGender" When the user makes a selection "Male" a report is generated for only Males. But in some cases the user will want to run a report for both (all) genders. So I added a check box next to the drop down named "ckAllGender" My idea is that if that box is checked then the report will show information on Both (all) genders. So in the query that generates the report, in the c...

Suggest a solution
Hi all. I have a table named Articles that has the fields: ArticleID, ArticleName, Price and so on.... What I want to do is to make a new table that would hold some related Property records for each article. For example... Color, Weight, Length,.. This is not a problem, but what I want is to be able to change fields names for the second table (like PropertyID, PropertyName) and be able to search among those values from query. This is the most important part: ---------------------------------------------------------------- I would like to have them as a field in query from ...

auto filter data within pivot table
Hello, Does anyone know how can we filter data within a pivot table? or sort the data from ascending to decending order withing a pivot table??? Can we do that? It doesnt seems to work?! Thanks. Elaine. You can add fields to the page area, and use them to filter the pivot table's data. To sort a field, select a cell in a column, and click the A-Z button on the Excel toolbar. elaine wrote: > Does anyone know how can we filter data within a pivot table? or sort > the data from ascending to decending order withing a pivot table??? > Can we do that? > > It doesnt se...

getting the error when updating file xls file.
Hi. We are using exchange 2003 server and office XP but know we start to Upgrade our office to office 2003 Know we are face problem. When our user try to make changes to file in public folder there can not make the changes it say “file is read only” but that user can make the changes or edit that same file from the P.C were office XP is installed. Do I have to install some patch if yes can you please send that patch because in trying from last two day and I can not find it. Note: If possible can you reply on parag.lopes@madinatjumeirah.com Just a guess... Are you s...

Comments entered on Task Updates to Manager: Where does it get sto
There is a checkbox option on the My Tasks page for team members to select and send comments about task updates to their managers. Anyone know where these task comments can be reviewed other than when the PM opens it up to approve? It does not seem to be retained anywhere like the Notes get retained. Hi Kahuna, PM can see the comments with Approvals -> Task Updates -> Go To -> Applied Requests and Errors, clicking on the task name. The approval history is listed there including comments. A team member can see them with clicking in the task name in 'My Tasks&#...

Spontaneous 'Compile Error' In Queries
I've got a half-dozen queries that contain this expression: IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName]) Haven't touched any of them, but suddenly they were all throwing "Compile error. in query expression 'IssuerName: IIf(IsNull([tblIssuer].[IssuerID]),"[Issuer Unknown]",[tblIssuer].[IssuerName])'." tblIssuer.IssuerID is still there in a link. My kneejerk was to somehow force recompiles of all the queries, so I did a Compact/Repair. After the compact/repair, all was well: the errors went away. C...

look way to map form at CRM frontend to tables at CRM database
How can I find out what tables which new record has been added on when I enter data through a form in CRM front end? I try to find way to map form in the CRM frontend to the tables at the CRM database at backend? It is CRM 3.0 Thank in advances for help! ...

Update from Money 2007 Premium
I currently use Money 2007 Premium. I plan on installing Money Plus Deluxe. They look to be pretty much the same. Will anything be lost going from 2007 Premium to Plus Deluxe? Thanks, Bob K The services provided by Premium. (Gainskeeper, et al.) "Bob K" <BobK@discussions.microsoft.com> wrote in message news:8C0C34A6-F03E-49AC-8B54-BAFF63BC538A@microsoft.com... > Will anything be lost going from 2007 > Premium to Plus Deluxe? "Dick Watson" wrote: > The services provided by Premium. (Gainskeeper, et al.) > > "Bob K" <BobK@discus...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

I am having trouble keeping numbers formatted in a Pivot Table
I have a pivot table that won't stay formatted. The data is formatted as currency in the worksheet that the Pivot Table is drawing off of. It shows up as a general number in the Pivot. I've tried formatting it in the Pivot but it doesn't stay when I refresh the data. "Preserve formatting" is checked on the Pivot table, so it SHOULD be preserving any formatting I apply to the pivot table, but that doesn't seem to be happening. I know I could record a macro to format this, but it is happening on multiple Pivot tables in the same workbook and that would be a pain ...

UPDATE KB979202 MS silverlight problem
Hello, I was able to have all updates install, but i'm stuck with this KB979202. When I install it, a box popup (windows Installer) and ask for "enter path to folder containing installlation package silverlight.msi". ??? I try to uninstall silverlight and same message appear and ask for silverlight.msi?? Can anyone help? Thank in advance. By the way, I'm using Windows XP SP3 PRO. "powersupply" <powersupplydot007@gmaildotcom> ���g��l��s�D:O0dCAgz%23KHA.1892@TK2MSFTNGP05.phx.gbl... > Hello, > > I was able to have all updates i...