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
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.
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.
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
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.
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?
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....
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?
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...
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? :(
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
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.
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".
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?
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.
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!!
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.
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?
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?
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.
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.
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.
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")
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?
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:
My question is simple: I've got a database of my book collection. If i view it in a style sheet, it is something like
Author Title etc.
A 0 C 1 B 3 F 2 D 5 D 4 A 8 F 9
I would like to order the Author and Title columns (permanently). Particularly, I would like to order in alphabetical order all the authors and, if I've got the same author, order his/her book alphabetically, something like:
I have a need to convert the export of an Access report to an ASCII file.
The export needs to have a specific format - described below:
Field Position | Field Length | Field Name | Field format
1-6 | 6 | Member # | text - right justified 7-12 | 6 | Trans Date | YYMMDD 13-15 | 3 | Pay code | Alpha/ NUM - Left 16-26 | 11 | Amount | -9999999.99 (*) 64-71 | 8 | Check # | Right
general comments and questions:
This report needs to interface with some sort of transaction accounting system - No Idea what it is, but this format of the report will interface.
The field positions go from 1 to 73. I need only supply the data above, so the check # is way out of sequence. Do I need to have the space inbetween "Amount" and Check Number in the report? Will a simple text file work with all the fieds together? or will I need the space in there? Is there a way to design a report in an ASCII format in access?
Specific Questions
I think I need to change the format of the amount and date fields. I am currently using the short date format 8/30/04, but need to return the date as YYMMDD - 040830. How can I change this format to reflect the ASCII format. The form is also set with the default "=Date()" so the user doesn't have to enter the date everytime - it can also be edited for older data. I would like the date to appear the way it is on the form 08/30/04 but get entered to the table as 040830. Is there a way to do that?
The amount field is odd - I'll include the exact criteria that is desired:
"All Amount Fields must be right justified and space filled. If the amount is a credit, show the "-" sign at the beginning of the number. Payment should be entered as positive amounts. If zero, assign the fied as 0.00 (space filled)"
Is this just a mask that needs to be added? Currently, The field shows $7.00 when 7 is entered to add cent, you must type in 7.50, tab will put in the "$". I need to get rid of the $ and space fill the field?
