I have a form which I want to load before it starts up a vba function. How can I do that? Right now I use form_load(), but the problem is that, it loads the msgbox before loading form.
Code:
rivate Sub Form_Load()
Dim sFolderSpec As String, sCSVSource As String, dFileDate As Date
sCSVSource = "MDM_Syndicator.csv"
[Code]....
Got a problem here:
Timer Interval = 5 Seconds
Private Sub Form_Timer()
My code here..
End Sub
The code repeats every 5 seconds, what I want to do is to execute it only once.
I'v defined an update query as a string in my VBA code.
Then I execute the update query with : CurrentProject.Connection.Execute
however, I want the query out of the code and into the query folder where the other query's are.
How can I execute the query from within the VBA code when I do this ?
Can I execute a query I have stored in my Access database by using ASP from a webpage?
Thanks,
Been at the for 3 hours.
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
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.
Thanks in advance.
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);"
Thanks,
CRhodus
I have a stored query in my db. The query gets the recordcount of all the items in a table.
In my VBA, rather than code a new db conection, set a recordset etc, I want to execute the query using DoCmd
=======
DoCmd.OpenQuery stDocName, acNormal, acEdit
=======
The above line displays the results of the query.
What I really want is to run the query and store the recordCount in a variable - without displaying the results.
Can this be done?
Thanks all
Can anyone help...
I am writing a query in which, amongst other things, I also want to perform a dateadd calculation.
I have the following
expr:dateadd("d", [lastduration], [lastlandingslot])
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??
Tried to search but network is really slow
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.
thanks
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 have a list box (1L) in a form (1F) that list member IDs from a table. Then I have a query (1Q) that use (1L) member ID to get data from a table another related table. When I change the 1L and run the query 1Q the results is correct. So I added a new list box (2L) in form 1F that show the results of the query. When I open the form 1F the 2L is populated correctly by the query. Here is my problem. I need to have list 2L update when I change 1L. Here is what I did. I created an On Change event for 1L that has a code Me.2L.Requery, but nothing happens.
View 1 Replies View RelatedA new query I am building, after entering an expression, gave the " You tried to execute a query that does not include the specified expression.." error. The new query is based on and uses expressions from another query. The error message listed the expressions from the query upon which this query was based. Copying in all the fields referenced in the error message did not work.
Here is the SQL code, the offending expression in red:
SELECT [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber, Count([Test Grade Results].[Test Number]) AS [CountOfTest Number], Avg([Test Grade Results].[41Grade]) AS AvgOf41Grade, Sum([Test Grade Results].Wghtd41) AS SumOfWghtd41, [GPA]=Sum([Test Grade Results]![Wghtd41])/[Test Grade Results]![CuumWeightDivisor] AS Expr1FROM [Test Grade Results] INNER JOIN [Test Parameters] ON [Test Grade Results].[Test Number] = [Test Parameters].TestNumberGROUP BY [Test Grade Results].[Class Number], [Test Grade Results].StudentNumber;
Wghtd41 is the expession that is included in the error message.
Dear Access Expert
I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
How can you determine the name of a table that has/is going to be created by a make-table?
View 2 Replies View RelatedI'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
Code:
sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "
CurrentDb.Execute sSQL, dbFailOnError
This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:
THIS GIVES ME NO ERRORS:
Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;
When I make the following changes to the SELECT section, I get this error:
You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.
Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...
Any insight is greatly appreciated.
UNION Query will not execute properly. Remove "WHERE" statement query runs.The WHERE statement is used to locate all duplicate records, based on serial number, in a table. Also, it excludes certain data contained in two of the four tables. There are four separate queries for four separate tables. They all work until incorporated into a UNION QUERY.
Question, is there another way to structure the "WHERE" statement for this UNION Query and achieve the same results?
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code:
Call func1("Z", 8)
Public Sub func1(b As String, a As Integer)
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _
& "FROM jabberwocky " _
& "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _
& "HAVING ScanDate=" & J & " and Scanhour=" & a & ""
End SUb
When using a make table query, can you include an ID tag. in the output table? if so how
View 2 Replies View Related:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?
Field NameExcel DataFinal Data
Model_NameLATITUDE D600D600
Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?
Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!
View 2 Replies View RelatedI have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.
I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)
SELECT tblProviderRate.* INTO tblProviderRateSave
FROM tblProviderRate;
The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.
This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??
Thanks,
CRhodus
How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?
View 4 Replies View RelatedDear friend
I need your help desperatly. In my db, I am making a "make table query" to match some text.
Help me build this please.
First a form which has text box, where I will put in some words..
Then a make table query, which will have =[form]![name of form]![control name]
A report on same table.
Means when some one puts some words in text field of form, on pressing hit button, a like statement is automatically created in one field of query which matches those words and a table is generated from them. Result is report.
Hope this is clear, could you please help me build this?