Hi
How do you run a procedure that activates at a set time?
I want to run a procedure that moves a log file to an archive directory at midnight (or at anytime after this when the PC is next turned on).
Thanks for any help...
Mat
I need to stop already ran procedure or function with vba code . So I use END command ans everything stops.. in some cases that is useful, but some times need I to do something else after I use END command, but after END everything stops...
LIKE THIS :
SUB ()
END
ME.CTL.SETFOCUS
'OR
CALL function
EXIT SUB
I am trying to run a query in Access 2010 but I am getting an "Invalid Procedure Call" error. I searched online and found that this error can be caused by broken references.
I opened the VB editor (Alt F11) to search for "Missing" references but I do not see any. These are the 4 that are checked.
1. Visual Basic for Applications
2. Microsoft Access 14.0 Object Library
3. OLE Automation
4. Microsoft Office 14.0 Access database engine Object Library
Hi, I have a procedure in a form that I need to run on the first day of each month at 5:00 am. Is there a way or VBA codes that can get this procedure to be run automatically at that time? Thanks for any help!!!
View 14 Replies View RelatedI am trying to call a stored procedure from access ,but it is giving me this runtime error :
Code:
2147217900
Syntax error or access voilation
I am doing this first time so i dont know about how to pass parameters (IN and OUT)..
My code is
Code:
Public Sub createDataToAnalyze()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
[Code].....
I have just made a change to one of the forms by adding a button (by copying the only other button on the form) to cancel any changes and close the form. However, as soon as I added it I started getting the error message in the title. Please attachment LA Err1 for the full message. I also changed the caption on the other button on the form from "Close Form" to "Save && Close Form" this button is now giving the same error.
I have Compacted and repaired the DB on several occasions to no avail. I have deleted the procedures from the module and recreated them using the properties window - still get the error. I have deleted the buttons from the form and recreated the both via the object wizard and without it. Nothing I have tried has made any effect.
Hello,
I'm trying to calculate some running sum queries in preparation for some charts on a report.
I have:
tbl: Spencerdaily
fld1:datein
fld2:milesdriven
fld3:vehiclenumberdaily
I am trying to calculate the running sum of milesdriven for each year per vehicle. If I use the following I get the same set of sums repeated for each vehicle:
SELECT spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year]) AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
I tried this but it didn't work:
SELECT spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily AS vehiclealias, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year] & " AND [vehiclenumberdaily]<=" & [vehiclealias] & " ") AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
What am I doing wrong?
Thanks,
Gifford
I am trying to put together this report where I can see how many hours the printers were running in a month's time. The sum property for this is working up until it reaches 24 hours then it starts all over again. How can I get it to keep adding hours when it gets to 24?
For example: 20:35 + 6:25 = 3:00
learnasugo
Good morning,
I am having some kind of problem. A query in Acces that is used to find results in the DB is asking 2 time's te input.
And there is no way that the input boxes is asking to do this twice..
here is the Query,
SELECT tblHotlines.Hotlinenummer, tblHotlines.Vestigingsnummer, tblDealers.Postcode, tblHotlines.Datum_hotline, tblBestellingen.Chassisnummer, tblBestellingen.Onderdeelnummer, tblBestellingen.[Uitleverings datum], tblBestellingen.[Vervangende auto ingezet?]
FROM (tblHotlines LEFT JOIN tblBestellingen ON tblHotlines.Hotlinenummer = tblBestellingen.Hotlinenummer) LEFT JOIN tblDealers ON tblHotlines.Vestigingsnummer = tblDealers.Vestigingsnummer
WHERE (((tblHotlines.Vestigingsnummer) Like [Geef het vestigingsnummer op] & "*") AND ((tblDealers.Postcode) Like [geef evt een gedeelte van de postcode:] & "*") AND ((tblHotlines.Datum_hotline) Like [Geef de datum op:] & "*") AND ((tblBestellingen.Chassisnummer) Like [Geef het chassisnr op:] & "*") AND ((tblBestellingen.Onderdeelnummer) Like [geef evt het onderdeelnr op] & "*"));
Qhat could be the problem that Access is running a Query twice before getting results
hi there,
I'd like to ask the solution for my problem.
I made a new project using ms access 2003,crystal report 9, and visual studio 2005.
I just made a simple access dbase with 1 table(Table 1) and protected with password, o I forgot this table has 2 field(id and desc).
After that I made simple report that using DAO connectivity(save data with report option is disabled).
At last I made simple program(windows app) using visual studio 2005.
I put the CrystalReportViewer component, and these is the code :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace prj
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void crystalReportViewer1_Load(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
crystalReportViewer1.ReportSource = System.Environment.CurrentDirectory + "\Report1.rpt";
crystalReportViewer1.LogOnInfo[0].ConnectionInfo.UserID = "Admin";
crystalReportViewer1.LogOnInfo[0].ConnectionInfo.Password = "12";
crystalReportViewer1.LogOnInfo[0].ConnectionInfo.DatabaseName = System.Environment.CurrentDirectory + "\db.mdb";
crystalReportViewer1.LogOnInfo[0].ConnectionInfo.ServerName = System.Environment.CurrentDirectory + "\db.mdb";
}
}
}
and the problem is when I move the access database file to other location, the viewer shows a box to be confirmed by my UserID and password,
my question is how must I write in the program so that the box doesnt come out.
thanks
I want to run an external application (a batch file) ... and want to run it by clicking on a command button on a form. That part is all fine, using the Shell command. I can determine the start time of the application (also fine with now() function) .. but want to find out how long the external application ran and log that "run time" into my database.
The part I don't know about is the .. really the ending time. How do I determine how long the application really ran for / ended ? I imagine I can use datepart to determine the difference in the start and end times. Or would I use a timer control. (but I cannot find the timer control in Access 2010).
I am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem.
However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
I am trying to select the records I want displayed by what category is chosen in the combo box.
Say there are two fields in the combo box: Red / Blue
I want the query to only display the records associated with the Red Category.
If I set the criteria in the query to [Show only Red] and type in Red when the query is run I get blank lines.
Is there an issue when choosing a category from a combo box for the query to be specific on?
My other issue is.... does the problem originate because I am trying to run a query from a table that is being filled in by typing the
information into a form? I wouldn't think so, but I am not sure.
Thanks,
ChrisB37
I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.
ID Duration Difference
1 30 0
2 45 10
3 15 20
4 60 10
5 15 10
What I would like to achieve is the following:
ID Duration Difference running total
1 30 0 30
2 45 10 85
3 15 20 15
4 60 10 85
5 15 10 110
So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20. I have the following:
Code:
Public Function Cont20(MyVal As Long, MyDif As Long) As Long
Static OldValue As Long
Dim NewValue As Long
If MyDif >= 20 Then
NewValue = MyVal
OldValue = 0
[Code]....
Which I put as Cont20([Duration],[Difference])
The problem seems to be on the return aspect, its not calculating correctly. I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g. In cell A7 the formula would be A6+B7+C7 and so on.
how I can have a combo box on a form, and once a value is picked, return a query that displays all the data based on that value. I have tried using sub forms and the after update but I cant seem to figure it out.
View 2 Replies View RelatedI've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
Hi,
I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.
Got any examples of code I could have a play with
I am struggling here so need any help I can get!!
Cheers
Paul
I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success.
View 5 Replies View RelatedHello All,
I do hope somebody can help this newbie :)
Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...
Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.
i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.
I do hope this make's sense I'm starting to confuse myself.....
Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.
So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...
Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?
Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.
Regards,
Andrew
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I'm using 2003 and have some VBA ability.
I have DB in access 2007. I have a report that is uses a select query to generate the information for the report. It has been working great, But however lately like maybe with in the last month, it has been causeing Access 2007 to crash. I am having the same issue with another DB that uses the same information but that information is imported in. both Databases have worked great up until two months ago. Microsoft states that it is because of the program. I have tried to repair the DB by using the Repair option. I am confused as to why this would be happening. I can create a new report and it seems to work. but I do not want to change all the DB on everyone's computer just for this reason. I also have two buttons on my report that utilize macros to close or print the report.
View 6 Replies View RelatedI am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
Hello.
I have been given a set of pricing tables in Excel that, based on the type of work done, show a dollar amount for time ranges. In other words, if WorkType = A, the appropriate pricing table is referenced and the amount of time in minutes is found within ranges ("0 - .3333", ".3333 - .6500", etc) shown in hour decimals (.3333 = 20 minutes), then an associated dollar amount is reported back. (I know... very awkward, but not my choice). The time ranges are not consistent in size either within a particular pricing table nor across tables. The person using these tables is doing this manually now.
What I need to be able to do is enter the WorkType and duration, and get the dollar amount from the table, even though the duration is not going to exactly match the value in the table. In Excel's VLOOKUP, you can put in amounts that not exact, and the function finds the highest value less than your entry. Can Access do that? I tried reading up on DLOOKUP, but I don't think that is what I need (or maybe I just don't understand it). I'm not sure of an efficient table setup or how to get the correct output.
Thanks for all your help!!
Tom
I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
Thanks,
I'm making a dispatch call database that will be sorted at the end of the month based on call types and which shift that call was originated on. Our shifts are 6a-2p, 2p-10p, 10p-6a.
I am figuring that the time would be converted to minutes and then the display would be figured based on which times the minutes occured between. IE: the call origintaed at 0100hrs = 60 minutes. It would figure that 60minutes translates to "Night Shift".
How would I convert time into minutes and how would the if/then be established?
Thanks so much for all your help
Dave
Hey all,
I'm trying to find a solution to a problem. Is there any way using VBA to generate a random number, based off the current system date and time? The theory is that a random number seeded by the system date/time will be truly unique and the possiblity of it ever occuring again will be nearly nil.
Or is there a better way to create unique ID's (such as member ID's, or something similiar) based off the system date/time that can be used as a primary key in a relational db.