Modules & VBA :: Loop Fields Of Crosstab To Create Query

Nov 22, 2014

the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?

Code:
Set db = CurrentDb
Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")
indexx = 0

[code].....

View Replies


ADVERTISEMENT

Modules & VBA :: Loop Through Table And Create File Names

Oct 22, 2014

I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.

But I run this report 30 times for 30 different parameters. The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke

-I always name the PDF file according to this value.
-I always choose the same folder.
-I always overwrite the reports I produced last month (copies have been sent elsewhere by then).

how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?

Code:
Private Sub lstSpecialties_DblClick(Cancel As Integer)

At the moment I am running this code by double-clicking an entry in the Specialty listbox, 'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table '(currently 30 but could be more in time)

'Warnings off
DoCmd.SetWarnings False
'Make the tables needed for the cumulative queries
DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" Each of these queries
DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" uses the Specialty selected above
DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" as a parameter to get
DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" the right data for the report

[code]....

View 5 Replies View Related

Modules & VBA :: Create A Loop To Add New Records And Decrease Amount By One?

Apr 13, 2015

I have the following code that works fine:

Dim db As DAO.Database
Dim rs As DAO.Recordset, i As Integer, ii As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("PatientPrescriptions1")
ii = [RefillAmount]
For i = 2 To ii

[Code] .....

However I am trying to make it decrease the value in [RefillAMount] each time it loops through the addnew function and I can't figure out how to do the rows keep saying the same number:

If I put Refill 3 it creates 2 extra rows and all these rows now say Refills = 2. What I want it to say is

Refills 3
Refills 2
refills 1

And end there. how to do this?

View 2 Replies View Related

Crosstab Query Sometimes Loses Fields

Nov 20, 2007

Hi Folks
Not sure if this should be in the Queries or Reports forum?

I have a MakeTable Query which selects data between 2 dates to create the following Table.

IncID, IncType, IncDate, AggType
15, Agressive, 23/02/2006, Verbal
23, Agressive, 14/05/2006, Threat
72, Agressive, 18/11/2006, Physical
89, Agressive, 20/12/2006, Verbal

I then have a crosstab query that counts the different "AggType" like this.
(This data is output to a report as both data and a chart)

Verbal, Physical, Threat
2, 1, 1

This all works great IF the date range is large enough that all 3 AggType fields are included. BUT, I my date range was from 01/05/2006 to 31/05/2006, I would only have 1 record (23, Agressive, 14/05/2006, Threat
) so the crosstab would result in ONE field only. (Threat)

The problem is that my report is looking for 3 fields (Verbal, physical and Threat)

How can I stop the report from giving an error if fields are missing from the crosstab result?

Thanks

View 2 Replies View Related

Modules & VBA :: Run A Loop Through Each Record In Query?

Apr 30, 2015

Is it possible to run a loop through each record in query, but refresh the query in between loops?

The query I am looping needs to be updated before moving to the next record.

t can't figure out how to get the query to refresh before looping.

View 3 Replies View Related

Queries :: Adding Fields To Crosstab Query?

Oct 9, 2014

I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?

View 6 Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

May 31, 2006

:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....

First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.

When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)

The SQL was written by Access not by me. :)

Here is an example of the Crosstab SQL (which is using a previous query):

TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;

-----------
qryTest2 SQL: (Grouping by to remove dups)

SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;

Thanks for you time! :)

View 1 Replies View Related

Modules & VBA :: How To Loop Update Query On All Records Of A Table

Sep 2, 2014

I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:

Code:
Private Sub doDataSegm_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("Table1", dbOpenTable)

[Code] .....

View 3 Replies View Related

Modules & VBA :: Loop Through Query Results To Output Emails

Jan 29, 2015

I have a query that randomly selects records from my table:

SELECT TOP 6 *
FROM SM_Import
ORDER BY rnd(INT(NOW*id)-NOW*id);

I would like to loop through those results, and create emails

I can get one email formed (displayed) with the code below but don't know how to get both working together to display the (6) emails:

Private Sub Command3_Click()
Dim ol As Object
Dim CaSubject As String

[Code].....

View 4 Replies View Related

Modules & VBA :: Loop Through Table Or Query And Export To Excel

Jan 25, 2014

I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.

Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2

Dim strsql As String
Dim strsql2 As String
Dim strfilename As String
Dim strpath As String
strpath = "C:UsersDesktopHome"

[Code] ....

View 6 Replies View Related

Modules & VBA :: Financial Database - Loop Query Until Empty

Oct 4, 2013

I'm an accountant and I'd like to build my own personal financial database in Access. One thing I like about MS Money is the 'Forecast Cashflow' feature, which is based on a schedule of recurring transactions, amounts, next due date, and frequency. I want to replicate this.

So, I created a dummy database for the sake of learning the code behind this process. I've got 2 tables (but no microphone.

1) tbl_InitialPoint (which is my schedule of recurring transactions, amounts, frequency (in days))
2) tbl_Register (where I want forecasted transactions to wind up)

I've got 2 saved queries:

1) qry_MaxDate (looks for any transaction @ 'tbl_InitialPoint' and finds the last date of that transaction in 'tbl_Register'
SELECT MAX(tbl_Register.PostDate) AS LastDate, tbl_InitialPoint.Description
FROM tbl_InitialPoint INNER JOIN tbl_Register ON tbl_InitialPoint.Description = tbl_Register.Description
GROUP BY tbl_InitialPoint.Description;

2) qry_InsertTransactions (populates 'tbl_Register')
INSERT INTO tbl_Register ( PostDate, Description, Amount )
SELECT qry_MaxDate.LastDate + tbl_InitialPoint.Frequency AS DateSeries, tbl_InitialPoint.Description, tbl_InitialPoint.Amount
FROM tbl_InitialPoint INNER JOIN qry_MaxDate ON tbl_InitialPoint.Description = qry_MaxDate.Description
WHERE qry_MaxDate.LastDate + tbl_InitialPoint.Frequency <= [Forms]![HomePage]![DateHorizon];

And I've got a form called 'HomePage' that has

1) A textbox where I input a date horizon (this is where I want the eventual 'Forecast Cashflow' to stop
2) A button to start the process

It seems to me that qry_InsertTransactions needs to be run over and over (looped) until the query is empty. And it seems to me that this can only be done via VBA (not sure if I could structure my SQL to avoid VBA in this case, but I need/want to learn the vba to accomplish this).

I'm having trouble getting VBA to run qry_InsertTransactions a single time. I'm pretty sure if I could get the thing to run once I could get it to run through a loop. So, here's what I came up with.

Public Sub InsertTransactionsBttn_Click()
Dim db As Database
Dim qdfNew As QueryDef
Dim qdfInsertTransactions As QueryDef
Set db = CurrentDb()

[Code] ....

View 11 Replies View Related

Modules & VBA :: Loop Through A Query To Show Results On Form

Jun 28, 2015

I basically want to Loop through a query (or if not possible trough a table) and show the results in my form. For each record in the table I am doing additional checks when loading the form and showing that result accordingly. I do not want to do these checks in additional queries - I think it is more efficient doing it in the form directly. Here is the code I have:

Dim dbsSR As DAO.Database
Dim rstValQry As DAO.Recordset
Set dbsSR = CurrentDb
Set rstValQry = dbsSR.OpenRecordset("qry_val_tbl_ind_rec-rev_import")
With rstValQry
While (Not .EOF)

[Code] ....

The Loop seems to work because in debug it is going through it 3 times (the number of records I have in the query), but the result is always the same - it seems the above code is not checking / refreshing based on the individual query records.

View 14 Replies View Related

Modules & VBA :: Query Loop - Unable To Convert To Form Reference

Sep 9, 2013

I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"

To elaborate: CB1 contains the following values

Code : elephant, giraffe, bufffalo, tiger, lion

Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals

Code : **SELECT elephant, tiger FROM dbo_animals**

Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as

tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)

It looks like

Code : SELECT Elephant from dbo_animals

This is how I want. But, since i am looping through CB controls, I have it set up as

Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"

and this shows up as

Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals

Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.

This makes sense, since it is unable to evaluate the text as a reference.

How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?

View 4 Replies View Related

Modules & VBA :: Access 2010 / Loop Through Query To Assign A Value To A New Field?

Aug 16, 2013

Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.

I have my query connected to VBA code.

I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.

Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String

[code]....

View 14 Replies View Related

Modules & VBA :: Loop Through Query Results To Extract Email Addresses

Jun 27, 2013

I am trying to loop through a query results to extract email addresses from a query result.

I have the following code

NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String

[Code] .....

If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?

Someone on another post said that sql uses US date format so i tried converting to us date and still no results?

View 6 Replies View Related

Queries :: Crosstab Query That Keeps Each Contract On One Line And Separate Fields For Each Contact

Jun 4, 2014

I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:

Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally

I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.

View 7 Replies View Related

Modules & VBA :: Loop Through Values In ComboBox / Set Query Criteria / Export To Excel

Jul 24, 2014

I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:

Code:

Option Compare Database
Option Explicit
Public Function CreateQCChartsforReports() As Boolean
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String
Dim BookName As String
Dim BookName2 As String
Dim intCounter As Integer
Dim cboCode As ComboBox

[code]....

View 1 Replies View Related

Modules & VBA :: Loop - Append Query To Work Referring To Quantity Value For One Record

Jul 29, 2015

I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1

Start with summary
--================
Part Qty
111000 2
222000 3

End result all Qty=1
--================
Part Qty
111000 1
111000 1
222000 1
222000 1
222000 1
--================

I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.

For n = 1 To [Forms]![MainScreen]![Text7]
DoCmd.OpenQuery "qry_Update_Qty"
'DoCmd.GoToRecord , , acNewRec
Next n

maybe a do while or some other approach?

View 3 Replies View Related

Create A Loop That Searches Through A Table And Increments A Variable

Apr 22, 2012

Im trying to create a loop that searches through a table and increments a variable every time it finds a record with a specific field set to a specific value. Below is the code but its not working. I then use the variable for another field.This is used in a form btw.

Dim counter As Integer
counter = 0
Set rst = CurrentDb.OpenRecordset("TableName")
Do Until rst.EOF
If rst(10) = "True" Then
counter = counter + 1
End If

rst.MoveNext
Loop
Me.Text24 = counter

View 12 Replies View Related

Modules & VBA :: Save Report As PDF Using Specific Fields To Create Name Of File

Jun 2, 2015

I want to create a Macro saves the report in a specific location, and uses two fields within the report to generate the name of the file. For example, "[Sales Rep Name] + [PayPeriod].pdf" and it should be saved in a predetermined folder.

View 1 Replies View Related

Queries :: Loop To Create New Tables With Unique Field Values

Sep 20, 2013

I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.

Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c

View 5 Replies View Related

Reports :: Writing VB Code To Loop Through A List Box And Create Separate PDFs

Aug 19, 2015

I have a sales report that I generate each week based on a query, which relies on the input from a combo box on a form. This works great - but I have select each salesperson, one at a time and run their reports separately.

I'd like to have some code that will run through each person on the list, generate their report, create the PDF, and email (or save) the file.From my initial research, it appears I will need to utilize a List Box (vs a combo box), so I can select multiple salespersons for the report.

I've found a few examples of code to use...but a little lost on how to implement. I create a new form with a list box that queries the property table for the list. I then added a button to execute the code, and attempted to add this (with my control and reports names substituted). Getting all sorts of errors -- so not sure I'm on the correct path.

'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003 with a Combo Box

Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![ YourControl ]
'If Your Data Type is Numeric use this section

[code]...

View 14 Replies View Related

Crosstab Queries?...Is There A Way To Create More Than One Column?

Nov 12, 2004

Hey..

If there was a way I could get the crosstab query in Access to allow more than one column I would solve a major problem. Is that possible?...help anyone?

View 1 Replies View Related

Reports :: Create Letter Using Fields In Query

Mar 14, 2014

All, using access 2010. I am working with an existing database that have a letter programmed using fields in a query. I have to add to it but having a trouble. Merge in Word was not used. I can't find any examples. The letter is designed in a report design. Using the fields from a query and filtered by "IIF Statements".

View 3 Replies View Related

Loop Thru Fields And Then Spell Check?

Nov 4, 2005

I have 15 fields beginning with the word Comment. (i.e. comment, comment1, comment2 etc)


What Im trying to do is create a spellcheck command button that will only check these fields and only on the current record.

I've managed to find this piece of code to do the actual check:


With Me.FIELDNAME
.SetFocus
.SelStart = 0
.SelLength = Len(Me.FIELDNAME)
End With
DoCmd.RunCommand acCmdSpelling



Question is, how do I get this piece of code to loop through any field beginning with the word 'comment' and check their spelling?!

Any help would be much appreciated

View 7 Replies View Related

Crosstab Query Based On Crosstab??

Sep 21, 2007

Hi all, I am utterly unsure if what I want to do is even possible:

I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).

There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.

I want to make another crosstab query which performs a calculation. To keep it simple:

If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit

Please help!!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved