Querydefs Collection

Mar 27, 2006

I'm probably trying to be too clever here, but I have an update process that uses many queries to update the various tables in the project.

In general, these queries must be run in the correct sequence. So I name them "update 01 chg Field1" "update 02 get Field2-3 from table2" ... "update 15 make summary table".... etc...

In VBA, I then run 'docmd.openquery ...' for each query in the list.

But as the project evolves, I have to change the query sequence, or rename the descriptive part of the name. I then have to go in to the code and change the sequence/correct the names for each.

I had this "Better Idea":

for each qrydef in db.querydefs
' only run if name starts in update
if mid$(qrydef.name,1,6)="update" then
docmd.openquery grydef.name
' log that the query ran, etc...
end if
next

This seems to work wonderfully! The qrydef are coming out in alphabetical order, which is what I want, but I can't find anywhere in the docs that says that they HAVE to come out in alphabetical order. Nothing that says they will and nothing that says they may not.

I've tested creating the queries in arbitrary sequence, or renaming the queries, and so far they are always in alphabetical sequence.

SO, is what I am doing safe? Will querydefs always return the queries in alphabetical sequence, or am I likely to have a nasty data destroying missequence at some time in the future?

Any advice would be greatly appreciated.

Thanks,
David

View Replies


ADVERTISEMENT

Count Of Collection Records And Also Increment Value Of Collection Number

Nov 14, 2012

I have a master/child forms. in master form sale_id,collection notes are placed. and in child form all collection details like collectionnumber,date,etc., child form look like datasheet view.

When i select the record from the list depending on the sale_id all the collection records shown in datasheet view form. If no record is there ok. if more than one records are there in collections of sale_id.i need count of records in collection depending on sale_id and also if i select one row in collection, notes to be shown for that record when i clik on shownotes button.

If more than one record in collection automatically it increment. how many collections for the sale_id. collection number would be increment. How and also show notes depending on collection number and sale_id.

View 3 Replies View Related

Subforms And Querydefs

Feb 10, 2005

First I'm an average access user, but don't know much about VB. I've created a subform with a querydef that works fine standalone, but as soon as I move my subform onto my main form the the querydef no longer runs.

Am I missing something simple?

View 3 Replies View Related

Modifying Access Pass Through Query Using Querydefs

Feb 8, 2012

I have a pass through query which I want to add a parameter to the end.

The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

I do not want to retype or try and paste all of this code into a vba module.

Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

A simple way of looking at this could be

querytemplate:-

"set nocount off

Select UWREF
from policy
where UWREF like '"

parameter:- (picked up from a text box on a form)
"ABC123"

additional text to follow:-
"%' set nocount on"

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

View 1 Replies View Related

Data Collection

May 4, 2005

I'm sorry if this isn't the correct forum to be posting in, but I wasn't sure where "I'm very familiar with Access, but I need to do X and I'm not even sure where to begin"-questions go.

I need to create a database that collects monthly metrics for an entire team.
So I setup tables

met_Metrics
Metric_ID
Metric
Assigned_ID

Reviewer
Reviewer_ID
Reviewer

met_Month
Month_ID
Month (formatted MMMM YYYY)
Metric_ID

I've sat and stared at this for far too long and I'm no closer. Each Metric will be assigned to a Reviewer. They will be responsible for entering the data for each Metric assigned only to them on a monthly basis.
Any suggestions would be greatly appreciated.

View 11 Replies View Related

Fields Collection

Jul 14, 2005

Hi Guys,

My first post. Can you help me?
I want to loop through all the fields in all the tables and find any fields with a value of "UPGRADE" The code below takes me part way there because tdf.Name gives me the database name and fld.Name gives me the field name. What I am having problems with is referencing the fields value with fld.Value. How do I dynamicaly reference the value of the field.

Any help would be greatly appreciated.

Best Regards,

Steve Wilson.

My Code:
Dim StringValue As String
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = DBEngine(0)(0)
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print "** " & tdf.Name
For Each fld In tdf.Fields
Debug.Print fld.Name & "+++ " & fld.Value
If InStr(1, fld.Value, "UPGRADE") Then
MsgBox (" Value = " & fld.Value & " Table = " & tdf.Name)
End If
Next fld
End If
Next tdf

View 1 Replies View Related

Music Collection

Sep 16, 2005

Hi,

I'm fairly new to Access and was wondering if it's possible to create a database that finds MP3's in a given folder and reads the ID3 details to create records, to save entering all the song details manually?

Cheers

View 3 Replies View Related

Help With The Controls Collection

Feb 14, 2005

Scenario:
I have a form that allows the user to enter various parameters to build a dynamic query behind the scenes. There are 'flavors' to this query process. I have a combo box from which the user selects the query type, and then I want only the proper controls to display to support the query the user chose. I have attempted this using the following code:
Code:Dim ctrl as Control Select Case mdc_cboSelectChgType.ValueCase 1 'Cust Dist - AddFor Each ctrl In Me.ControlsIf InStr(ctrl.Tag, "mdc_ca") Thenctrl.VisibleEnd IfNext ctrlCase 2 'Cust Dist - Modify Case 3 'Cust Dist - Delete Case 4 'Pending Dist - Add Case 5 'Pending Dist - Modify Case 6 'Pending Dist - DeleteEnd Select
Each Case statement would be built out similar to that in Case 1. I use distinct values in the control's Tag property to determine which controls to perform the operations on.

The problem is, however, that the ctrl.Visible throws an error: Object doesn't support this property or method.

Any suggestions for how to get around this? I'm stumped...I really thought this was going to work well....

Thanks!

John

View 4 Replies View Related

Collection On Controls!!

Oct 19, 2006

I have 17 textboxes in my form and I want to make them disabled on a click of a button. But all this will take a lot of code writing, 17 names.enabled=false....

Is there a way to make a collection of all the 17 textboxes, like "controlsTEXT" in which I can add all of them at once and can disable all of them just by one statement like this...

controlsTEXT.enabled=false

Is this possible? or is there an alternative??

View 2 Replies View Related

MS Access Music Collection

Jul 8, 2006

In Office 97, Access database had a Music Collection database which I used and it was also in Sterling GBP
My PC crashed and since then I have upgraded to Office 2000, but the Music Collection database is un USD$

How can I get the Music Collection Db as per 97?
:(

View 1 Replies View Related

Newbie (collection Template Needed)

Apr 29, 2006

Hello all
As i say i know asolutely nothing in regard to access
other than it is a data base.
I was just looking for some way of keeping track of my videos cd's and dvd's
Am guessing that this program might be the go.
At this stage i am in no need of much more, so am just looking for a templete
or tutorial on how to create one such template if anyone could direct me to a video tutorial that would be great as i learn a lot quicker watching how it's done. Yeh i know I'm lazy and don't like to read Nah really it sinks in quicker
if i see it done.
Thanks Guys/Ladies

View 3 Replies View Related

Data Collection Through Email Question

Apr 1, 2008

If I send an HTML message to an individual and they reply back on Monday, can they still reply again on Tuesday to the same message and again on Wednesday, so on and so forth? Will this be adding multiple records to the table?

Also, please consider the same situtation for an InfoPath email. Thanks.

Chris

View 2 Replies View Related

Access Collection Of Files And Folders?

Nov 12, 2011

Is there a good way to let Access be the database for collections of files and folders (on a Windows PC) that would organize projects and give information about the files. When I say information I mean description, progress(check in & check out duration of work, and timers), and location. I think this would work with organizing our projects and knowing what file is the current one we should be working on when we have many. A while ago I was working in FileMaker and noticed that one of there template options said "Collection of Files".

View 1 Replies View Related

Referencing To Users Collection For A User Table Possible?

Jul 27, 2007

My search was snowballed with lot of results about dealing with split database, seeing who's online, etc. so I'm not sure if that is a good idea.

I want to make a table of Users because my users may need to put their name on some of reports, so I could use CurrentUser() to figure out who's who and put down their proper name after comparing the username via a query.

However, I'm not sure if it's possible for me to link the user table to the user groups of database to ensure that there's no ghosts or users that doesn't have a proper name to be placed on the reports or whatever. More of a validation, really.

Is that doable or maybe there's another way to do that?

Thanks!

View 2 Replies View Related

Music Collection Database - Form Problem

Apr 30, 2005

Hey guys,

Yous seem to know virtually everything about access, so hopefully some kind soul could help me out - I think it should be easy enough, but I'm a newbie.

Basically, I have to design a music collection database. I've 5 tables - Artist, Album, Recording Label, Category and Tracks.

What I want to do is essentially create a database similar to the template available on the miscrsoft website. In it, when you go into the Recordings form for example, if the artist if not yet entered (ie cant be selected from the drop-down list), then you can double click and get the Artist form to pop-up. The same goes for Music Category, which also displays a pop-up form if necessary. I'd like to know how to do this??

I apologise if I'm using incorrect terminology, not explaining this correctly, or asking this question for the millionth time, but any help anyone could give would be greatly, greatly appreciated. Thanks in advance.

View 3 Replies View Related

General :: Access Data Collection By Email

Jan 29, 2013

Completing the html wizard for Access Data Collection by e-mail is straightforward enough but I need to change the instructions underneath each of the data fields prior to sending out the e-mail, to ensure (so far as possible) that the forms are completed with the correct information - which, regrettably is not always proving to be the case.

I did this some time back with another e-mail ADC form I completed but for the life of me cannot remember how!!

View 3 Replies View Related

General :: Data Collection Using Microsoft Outlook

Jun 5, 2013

I want to use Access to collect data for our research department. I have created a number of forms to assist the department in collecting data. I created a query with all the fields from the relevant tables to be used to collect the data. I used the option Collect and Update data via email to generate the form for collecting the data and emailed the form to myself for testing (gmail account), however, I cannot edit any of the fields.

View 1 Replies View Related

Access Email Function For Data Collection?

Aug 14, 2012

I have set up an access database and want to collect data using the e-mail feature. I can use the feature but the form it sends to fill out is very basic and not that user friendly. I want to put in directions to the form, is there a way to change the style of the form?

View 1 Replies View Related

Send Emails For Data Collection With Attachment

Apr 20, 2012

I am trying to send out request-for-quote sheets to vendors and i need to send the image of the product as well. when i send emails for data collection, the attachment is not included. how can i attach images?

View 2 Replies View Related

Reports :: Memory Usage / Enforce Garbage Collection

May 1, 2013

I have build a database with which I can create quite a lot of reports. When creating them I run out of memory with run time error "2004".

When investigating I detected that a lot of memory is used when creating the reports in PrintPreview mode. When closing the reports the memory is not automatically freed.

Is there a way to enforce garbage collection? Local variables of called routines I set to Nothing.

View 3 Replies View Related

General :: Data Collection Emails Based On A Form?

Nov 21, 2012

I am trying to send out data collection emails to fill in my records but it will only let me select fields from single access tables, I want to be able to get them to fill out all the fields as entered in my forms, which contain sub-forms and therefore links to multiple tables.

View 1 Replies View Related

Forms :: Daily Data Collection With Constant Elements

Jan 26, 2014

I have a table that is populated everyday, with following columns:

1. ward (linked to the wards table)
2. date
3. number of patients

We have a total of 18 wards, wherein the daily number of patient in each ward should be recorded. The problem we face is that we find it counter-productive if the data encoder selects a specific ward (dropdown list), then puts the number of patients, and then moves to another field repeating the process. (the date is pre-selected using a combo-box and this will fill the date fields, thus the encoder selects the date only once).

I was wondering if there is a way where we can just automatically show all the wards, so that the data encoder would just proceed on putting the figures.

View 2 Replies View Related

Modules & VBA :: Adding Mail Documents To Lotus Notes Collection

Oct 13, 2013

I receive mails through LN and loop through a certain folder, reading DeliveredDate, Body, Subject and writing those items into an Access-table.This works fine, before I go for the loop I put the doc in another folder and remove the current doc from the folder.

Call LNDoc.PutInFolder("SMSBackup", False)
Call LNDoc.RemoveFromFolder("SMSResponse")
Set LNDoc = LNView.GetNextDocument(LNDoc)
Loop

Without any error, without anything abnormal in the mails the code exits after a few loops, sometime after more or sometime after less read mails.The problem ist the RemoveFromFolder, not the PutInFolder function.

So to avoid this behavoir, I changed my code to finish the loop without
Call LNDoc.PutInFolder("SMSBackup", False)
Call LNDoc.RemoveFromFolder("SMSResponse")

and added after the loop a NotesCollection which should do ALL at once.But this part of the code does nothing at all (allthough found on the web).

With LNCollection
.PutAllInFolder "SMSBackup", False
.RemoveAllFromFolder "SMSResponse"
End With

The complete code looks like this:

Set objNotes = GetObject("", "Notes.NotesSession")
Set LNdb = objNotes.GetDatabase("myServer", "MyNSF")
If Not LNdb.IsOpen Then LNdb.OpenDatabase
If Not (LNdb Is Nothing) Then
Set LNView = LNdb.GetView("SMSResponse")

[code]....

View 1 Replies View Related

Forms :: Data Collection From Survey - Prepopulated Form Fields

Jun 11, 2013

I have a form that collects data from a survey. The survey has been undertaken before and the equipment being surveyed may carry an ID number and I have the original survey data in a table. If the old survey ID is entered into a field it automatically populates several other fields with the original data....neat!

But that data is not necessarily correct and for this survey I am going to great lengths to ensure the data is as "normalized" as possible so the data is prepopulated into a field with validation. Normally when something is entered into these combo fields I validate with a not in list event and ask if the entry should be added. If I prepopulate with a me.xxxxxx = oldsurveydata the entry is accepted without question. Is there some way to fire the not in list event? I've tried me.repaint, requery etc. but no success.

View 2 Replies View Related

Queries :: Count Can Be Displayed From Collection Table Next To Correct Site Name

Sep 12, 2014

I have a master table with all of my Site Names in it. I have a collection table that when a barcode is scanned it records the site name as being received. I built a query that counts the number of times the site name has been received.

What I want to do is list all of my sites from the master then display the count next to the corresponding site name. This will identify those sites that did not send in an item. Is there criteria in the query I could use so that 1) I could list all of my sites from the master table and then the count can be displayed from the collection table next to the correct Site Name?

View 4 Replies View Related

Reports :: Setting Subreport Source Object To A Report In A Collection

Apr 6, 2013

I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine.

I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:

Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)

However, it keeps giving me the error 'Type mismatch'.

View 10 Replies View Related







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