Making Yes / No Fields Execute Different Filters Or Functions
Jun 27, 2013
I have a form named frmBuilds_All. This is a continuous form and has 4 yes/no fields. They are "WaitingFab", "WaitingPaint", "WaitingParts", and "Completed". The labels however are just "Fab", "Paint", "Parts", and "Completed". I would like to make it so that when one of these check boxes are checked, the label for that box has it's background color change.
Filter From List
On a separate form named fromBuilds_Today, a list of all builds for today are displayed. I would like to make it so that when the check box for "Completed" is checked, the record in question is filtered off of this list.
I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...
I have a form that I want to make all fields required so that the user cannot leave that form until all fields are filled out. I have the Required property in the table set to yes, and this works fine if I go to the form from the database window. However, If I go to the form from the main switchboard (In Add Mode), the form can be closed without all of the fields being completed. I know it's probably something pretty simple, but I haven't been able to figure it out. Any help would be greatly appreciated. Attached is a picture of the form if it would help at all.
I have a MS Access 2000 database with 136 data tables in it. What I would like to do is execute a piece of VBA code which will list for me in another table, (for example: Field1: TABLE NAME Field2: FIELD NAME 1 Field2: FIELD NAME2 etc), starting left to right, how many fields would have to be combined in each table to represent a unique record.
For instance:
TYPE ID TEXT 1 1 "Cats" 1 2 "Dogs" 2 1 "Rabbits"
In this example a combination of the fields TYPE and ID give a unique record.
To start, I am going to generalize to a database of Cows. the fields are:
Name (enforced unique/"Primary Key" in access language?),
size, color, age.
The goal is to have a form with check boxes for the fields that the user selects. Scenario is one cow buyer cares about size, while another may care about color and be curious on age. So rather than guessing at what reports the users will want, have a form that allows them to select the fields they want then print a report. An intermediate step would be a query to filter the data.
See the picture below ....
Also, from my perspective, this is the basis of the best code i would write; if someone wanted to add a column for cow gender you wouldnt have to change all your forms/queries/reports:
Global import table.Cows
Form 2 in picture (form with check boxes): for ( i=0, i<width(Cows), i++) form.create.checkbox cb.append(i) cb.append(i).label(Cows[i])
Then create the report based on cb0, cb1, cb2, etc. queries
I have an address field containing "City", Street Name, and street Address. How do I link these fields together on a form so that when I enter data into the "City" field the rest of the fields making up the address fills in?
Can I keep my queries in the C drive and execute it against tables that are in the LAN network drive ? Right now the queries are also in the netowrk drive. So each time when I refresh the data into C drive, I have to import the queries too.
Is it possible to keep the queries in the C drive and execute against data in the netowrk ? Please let me know.
Is there a way to execute two statements in one query in access? I tried the following but received the following: "Characters found at the end of the SQL statment".
Here is what I have in my qryUpdateRecords file:
"UPDATE dbo_CE, tblCE_Details SET tblCE_Details.LIC_NUMBER = [dbo_CE].[LICENSE_NUM], tblCE_Details.Credit_Hours = [dbo_CE].[CREDIT_HOURS], tblCE_Details.Course_Sponsor = [dbo_CE].[COURSE_SPONSOR], tblCE_Details.Course_Name = [dbo_CE].[COURSE_NAME], tblCE_Details.Auth_Code = [dbo_CE].[AUTH_CODE], tblCE_Details.Category = [dbo_CE].[CATEGORY], tblCE_Details.Course_Date = [dbo_CE].[COURSE_DATE], tblCE_Details.ImportDate = Now() WHERE ([dbo_CE].EXPORT_TIMESTAMP IS NULL ) ; UPDATE dbo_CE SET([dbo_CE].EXPORT_TIMESTAMP = NOW() WHERE ([dbo_CE].EXPORT_TIMESTAMP IS NULL);"
Populating the table in db3 with a subset of records from db2.
I've gotten as far as opening the db2 and copying the table structure. Can't figure out how to run the query using execute rather than docmd.openquery.
Code: Dim appAccess As Access.Application Set appAccess = New Access.Application appAccess .OpenCurrentDatabase "DbPathString" 'copy the table structure to dbQn, overwrites any previous with same name
I got the error message cannot execute command after we upgraded from Access 97 to Access 2000. There are multiple users connected to one database on Windows 2000. Some people are able to get access, but there is a good chunk of people that get the error message " Cannot execute Command".
This fails to run as the expression is too complicated, I suspect this is because a lot of the records will not have a [lastduration] and its therefore null. I have ran the code below sucessfully. expr:dateadd("d", 1,[lastlandingslot])
How can I get this to execute only if the [lastduration] is not null??
SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE
FROM TBL_FCST_WKLY AS FCST, TBL_ACTUAL_WKLY AS ACTUAL
WHERE (FCST.TYPE=ACTUAL.TYPE) AND (FCST.REGION=ACTUAL.REGION) AND (FCST.PART_NR=ACTUAL.PART_NR) AND
FCST.REF_DT = (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) AND
FCST.MONTH_DT = (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION);
i am querying two tables TBL_FCST_WKLY which has 29500 records and TBL_ACTUAL_WKLY which has 798222 records. When i run this query it sites there forever. Is it because i dint optimize my query or is it because the number of records is too much for access? can someone help plz.
Because my tables were already large I split them up by environment to speed up data maintenence and retrieval. Thus I have table names which contain the environment as part of the table name (ex: tbl_TEST_MyTable, tbl_PROD_MyTable). Once the user sets the environment they want to use (by clicking on an option button) I update the form's recordsource to the appropriate table. My form has 5 subforms within it.
Even with the split loading the form was taking a long time because some of the queries the subforms are based on are joining tables so that they can be linked (parent/child) to the form. I created indexes to speed up the data access which helped a little but it was still running too slow. So, I attempted to change the subform's queries to only join the necessary data by re-creating the queries each time the user changes the form's current record.
For example, what I had originally was taking too long:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt ON tb.Name = pt.TName ORDER BY tb.Name;
and the parent/child link fields are CollId, ProgName, and QueryNo.
This is the change I made:
SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt ON tb.Name = pt.TName WHERE pt.CollId = 'BATCH_COLLECTION' AND pt.ProgName = 'PROGRAM1 AND pt.QueryNo = 123 ORDER BY tb.Name;
where the pt fields are changed dynamically each time the user navigates to the next record in the form. This resulted in much faster access time; however I discovered that the form was always displaying the query that existed before the form was opened. I have not been able to figure out how to get the subforms to display the latest created query.
While debugging I discovered that the form's OnCurrent event was being executed 3 times before the form is opened. I don't know what I'm doing that's causing this.
I also discovered that the subform events are executed before the form's events, so I think that's why the latest query isn't being used. I attempted to requery the subform in the form's OnCurrent and OnOpen event, but to no avail.
I seem to be having a problem opening a file if there happens to be any spaces in the file name. If I open a .pdf no problem acro reader opens it but if it is in word or excell or any other tells me that the file can not be found and seems that only the first part of the file name before a space in the name and the file extention. example: "Test it.doc" would appear as "Test.doc". somewhere it seems to be trimming the file name.
Is there a way to run a query, macro, and/or report in the "background". By this I mean in a way that frees up the current operator to do other things in the database while the query, macro, report keep on running?
I am using MS Access 2007 and I am trying, from my VBA code, to execute (or run) a Batch file. Now the batch foile works, but not from my code.
I used to be able to just do this --> Call Shell("S:AccountingAPTex FilesFile List Generator.cmd ", 1) And it would runit. But using this I get a message telling me that it is an invalid procedure call. So upon looking around on the net I found tha ton place said that with MS Access 2007 you need to do this:
But while it does not error out it dooes noting eitheer. What the batch file does, when it works, is produces a list of all the file names in a folder.
