I need to update a ytd-budget field using the curmo-budget field. The table contains one record for each month. The ytd-budget for Jan would be the same as curmo-bud for Jan. Feb's ytd would be the sum of Jan + Feb...etc. Is there a way to update the ytd fields with a 'running sum' feature as found in the report options?
(... unfamiliar with VB). Thanks
I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.
I can save a little time using SQL update queries like this:
update personnel set Department = "Support Staff" where Jobtitle="Shipper" update personnel set Department = "Maintenance" where Jobtitle="Mechanic" update personnel set Department = "Regional" where Jobtitle="HR Manager"
I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.
What would be the best way to talk Access into running all the queries, without having to paste in every one individually?
Hi all I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this. Thanks in advance Craig
UPDATE (tblAFFIRMATION_REC_TOOL LEFT JOIN tbl_TZero_Spns ON tblAFFIRMATION_REC_TOOL.CptySPN = tbl_TZero_Spns.SPN) INNER JOIN Entity ON tblAFFIRMATION_REC_TOOL.ReferenceEntity = Entity.ReferenceEntity
SET tblAFFIRMATION_REC_TOOL.[Scope Reason] = IIf(IsNull(tbl_TZero_Spns!SPN) Or (Entity!Test='EM'),tblAFFIRMATION_REC_TOOL![Scope Reason],'TZero Trade')
WHERE ((([tblAFFIRMATION_REC_TOOL]![Scope Reason])="Affirmation Eligible"));
the way it right now, it's running for about half hour.. Could that be IIF statement that slows it down ?
When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.
The above query does work if you add the following code to the beforeupdate event of the orders subform: If Me.NewRecord Then DoCmd.SetWarnings False DoCmd.openquery "qryupdateQuantityinStock" DoCmd. Setwarnings True End If
Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.
I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.
Can you help me find a way to run the query from a button?
I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.
Any help would be greatly appreciated (sorry for the long post!). :)
I have 3 different "buttons" on my form that run individual update queries. I know these update queries are running correctly. Decided to make a macro that will allow me to run all these queries by clicking one macro button.
My question is: Is there some way to stop the pop up msg that advises I am about to run an update query and the next pop up msg that informs me of how many rows I am going to update?
These pop up boxes are starting to drive me crazy when I run the macro.
I need to create a message box or a form or something to flash on the screen to tell the user that a piece of "Update" code is running. the update code will be updated reports from marketing returns, but the 3 branches who use the information are separate so I am creating an update form to download and update the table.
The code for the update is already working, but can take a while, so I thought a message or splash screen would be useful as the update runs on start up.
It would have another use, I have a report which is made mainly of calculated fields on an onPrint event and also takes a while to work it out, so a similar screen would be more useful than my current spinning circle and blank screen.
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Forgive the Bonehead question. I know this is probably easy, and may have been answered before, but I'm under the gun and trying to get this done quickly.
I have a form that is populated from a table. Each record has a Yes/No value (called "Matched") and a $ amount (called "PaymentAmount"). When they open the form, all the "Matched" records are a No (or False) value. I want a Text Box called "Total_Matched" to keep a running total of all "PaymentAmount" when they check the "Matched" field.
Does this make sense?
I have done a query that gives a sum of PaymentAmount from the table where all values are "True", but I can't get it to requery each time they check the boxes. And I can get a Total of all values on the form (=Sum([PaymentAmount]) but I can't make it contingent on if the Matched field is true.
Any help is greatly appreciated. I'm off to search some more.
I want to automate the Nero burner from a Microsoft Office Access Database. I have the code to close the DB and then open the nero program, but i dont know how to actually run a complete burning action from the same click of a button in the DB. I'm working from a macro at the moment but open to suggestions... batch files etc
Question: Is it possible to see if a database has stopped running (errored out, timed out, or if code builder is open) thru another database. I know how to look for the .LDB file to see if the database is open...but is it possible to know if it's broken?
The reason I ask is that I have a database on a pc (can't be put on a network) that updates/refreshes every 5 minutes. Once in awhile someone will do something and cause the database to halt (code builder window opens). Currently the way I check to see if the database is running is to VNC onto the pc from my pc and see if it's running. Does this make since?
I have a report which is similar to a bank statement. I have a transaction date and then either a credit or debit depending on whether the customer made a payment or if I am invoicing them.
I have created an additional field in the report which calculates AmountDue - TotalPaymentAmount and called this field Balance. I then attributed a running sum based on this Balance field. This works great.................BUT!!!!! If a customer has 100 transactions the report will be well over 10 pages long. Is there a way I can limit the report to just perhaps the most recent 20 transactions or 20 days?
I tried and failed in the report's query retrieve the payments which occur >datenow(-20) (basically in the last 20 days). This worked ok in the sense that only the last 20 days worth of transactions appeared in the report......BUT!......the first running sum of the report does not carry over from the previous transaction..it starts from 0!!!!
Please help me get over the RUNNING SUM blues! Rob
Hi there!I am having a fair amount of trouble figuring this out, and I was hoping someone might know what I was doing wrong. Someone set up a switchboard (I think using the Switchboard wizard) in this Access DB. I've since taken on the project of automating a database process and I want to use a switchboard item so it's a one-click, otherwise mostly automatic process. I have added the switchboard item, and when I click on it I get a msgbox displaying with my test text, so I think that is set up OK.Right now, I am trying to have a click on the button launch a custom sub (that I still have to write). Right now all I have in that sub is another call to a Msgbox with other testing text.Following is the relevant VBA code - not all of it, but I think this is all that will be involved. runDeletions() is located in its own module "Module3":The Sub:Public Sub runDeletions() MsgBox "testing - runDeletions ran successfully" End Sub This is the code in the switchboard form that deals with the arguments taken - and that will call the above function. Code within Private Function HandleButtonClick(intBtn As Integer): '...have the argument set to 8...Const conCmdRunCode = 8 '... so this Case will run:Case conCmdRunCode ' optional, just tells me the value of the argument being used' MsgBox rs![Argument] 'actually is what tries to call the Sub Application.Run rs![Argument] I've found the Switchboard Items table, and added in the following record. It hasSwitchboardID as: 3 (this button is on its own page)ItemNumber as: 1Itemtext as: Run Pending DeletionsCommand as: 8Argument as: Module3.runDeletions()I'm pretty sure my Argument is wrong. I've tried a few things but can't figure it out. What could I use as my Argument so my sub runDeletions in my module Module3 will run when this button is clicked?Thanks!PS - I've also posted this question here: http://www.vbaexpress.com/forum/showthread.php?t=16611 and any differences in code are intentional, as I keep trying different things (but so far no luck)...
I'm sending an e-mail each time a spesific report is made. If the user has'nt opened Outlook, it is placed in Outlook's Outbox, and is sent first when the user opens Outlook. Can I somehow start Outlook using VBA so the email gets sent when the report is run?
Also, The user is prompted with a warning message that an application tries to send an email. The user has to allow this for the mail to be sent. I know this is a security matter so no applicatopn can send emails without the user knowing. Is there someway to do this without the warning?
Probably one of those numpy questions but does anyone know how to run a database minimised?
I have a scheduled task that needs to run every 30 minutes which it does beautifully, however the database window pops up over whatever I'm doing at the time.
Not a huge problem but if anyone has a solution?
Extra info: The scheduled task calls a .bat file that runs the database and transfers data to a web site.
Sorry I am to bother you. But I was wondering whether it is possible to create a running sum in a query (from each previous record just a simple add up)?
I know how to do in report (it is described in the help function) but for a query I cannot find anything.
I have another table, Transaction, that's based off the Loan Number field on the loans table.
Fields: LoanNumber Payment Type (2 choices, payment and advance) Amount
I have a user form where users can specify the payment type, and amount, based on a certain loan number.
I'm attempting to do this:
User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.
If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.
Here's what I have so far.
The userform updates the transactions table with the information correctly.
My next step is to update the principal balance.
I'm thinking I could use some sort of update query, but I don't know where to begin.
HELP!!
If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand. :o :cool:
If I created a query named qry1, then, I created another query (qry2) which include qry1. Is that meaning running 2 queries? Firstly, run qry1 result, then run qry2.
I have 2 tables "Summary Table" and "Select1Summary"
"Summary Table" shows projects, departments and the available hours for each department
eg.
In "Summary Table" for 71043-40 Project, there are 1808 hours available at the beginning
"Select1Summary" shows Project, department, Month and the hours each department will be booking
eg.
In "Select1Summary" Table(Actually its a summary query, but am putting it as a table)
for 71043-40 Project, Department 11 on 30/04/2007 will be booking 171 hours (ie in April dept. 11 books 171 hours for project 71043-40)
for 71043-40 Project, Department 11 on 31/05/2007 will be booking 135 hours
for 71043-40 Project, Department 11 on 30/06/2007 will be booking 108 hours
What i need to show is running totals in a new query like
for 71043-40 Project , Department 11 on 30/04/2007 Available hours = 1637 (ie 1808-171) for 71043-40 Project, Department 11 on 31/05/2007 Available hours = 1502 (ie 1637-135) for 71043-40 Project, Department 11 on 30/06/2007 Available hours = 1394 (ie 1502-108)