Hello All
The problem that I am faced with is that I am try to automate a function that I have created that exports my access tables into XML. The function works great but now I want to take it to the next step. What I want to do is when a person exits my database I want the db to run this function before exiting so that all the new information is updated in the xml files. Any help would be highly appreciated.
Hi,
How can i run some vba cose directly after i click the move next button on the navigation but at the bottom of the form.
Thanks
k0r54
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
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
I bring up the database (Access 2003 / XP) by holding down the shift-key. As designed, the shift-key by-passes running the autoexec macro automatically. I then run the autoexec macro with the vba code viewer up so I can F8 down through all the code. The autoexec macro is one statement - RUNCODE which executes a vba FUNCTION Startup(). Everything seems to work great, but when the function goes to EXIT FUNCTION I assumed it's completed. BUT the next F8 GOES TO THE Startup() FUNCTION again and runs through the code over and over.
I don't think it does this when the program actually runs normally (don't by-pass the autoexec), but why it's running the function over and over again when I'm F8'ing through the code.
Is it something peculiar with the AUTOEXEC in MS ACCESS? Can I trust that it really does ONLY RUN ONE TIME?
One of the users of my databases uses a laptop. Whenever he exits the database, he leaves behind a trash file. Does anyone know what causes this?
View 2 Replies View RelatedI've got a database written in Access 2010 that shows many reports. When the report is selected and data is available, it is shown as a full screen preview with the print dialog box displayed over it. However, I am having a problem in that when there is no data, a message box is displayed that's invoked from the 'on no data' setting but when it's dismissed the report is still displaying. How do I prevent this from happening?
View 8 Replies View RelatedI need to update a field with the contents of 2 other fields when exiting a record. The fields (ItemNo) and (LotID) need to create or update the field (LotCode).
i.e.: ItemID (GM-235), LotID (26) will result in LotCode (GM-235-26).
I currectly use a Update Query that uses the expression:
[products].[MasterPN] & "-" & [lotid] in the lotCode Field - "Update to".
Unfortunatly, I am using a button on my form that executes the query, but it updates all the records and although it runs very fast, I would rather have it perform this task automatically when I leave the record.
I have my main form which is f_main.
On there is a Subform called subfrmFront and that has a source object of the form f_front
A button on f_main opens up a popup. In this popup, the fields I am updating all relate to the same records that are being displayed in the subform. Everything updates OK in the popup (i.e I can see in the table that the updated information is in there), but the subform back on f_main still has the old data in it.
I need to requery that subform to show the new data I just inputted.
If I close f_main and re-open it, the latest data is in there, but surely there is a way to make sure it updates on the close of the popup form.
Background info:
Split database
Back end on network
Front end on individual machines
I have a main menu form that opens up when opening Access.What I'm thinking is have some vba in the OnLoad Event of the main menu that Grabs the User and Time and track this to a table.When the database closes(Is there an OnDatabaseClose Event?), I'd like to track the User and time as well.
LogID(Autonumber)
User(text)
TimeIn(Date)
TimeOut(Date)
I need to fill a pre existing table with data based in an array.
View 2 Replies View RelatedHow do you make certain fields "required" to be completed before exiting?
View 3 Replies View RelatedCan 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!!
Many thanks in advance.
Peter
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
Hi
I want to open a DAO.Recordset from an opened DAO.Recordset.
My code is like this
Dim db As DAO.Database
Dim rs, rsMarks As DAO.Recordset
Dim strSQL, strMarks As String
Set db = CurrentDb()
strSQL = "SELECT Name, SurName, Marks from Table1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
strMarks = "Select Sum(Marks) As MarksSum from rs"
Set rsMarks = db.OpenRecordset(strMarks, dbOpenSnapshot)
rs.MoveFirst
Me.txtName = rs!Name
Me.txtMarks = rs!Marks
Me.txtSurName = rs!SurName
rsMarks.MoveFirst
Me.txtMarksSum = rsSur!MarksSum
rs.Close
rsSur.Close
db.Close
Set rs = Nothing
Set rsSur = Nothing
Set db = Nothing
But I got an error message “The Microsoft Jet Database engine can not find the input table or query ‘rs’. Make sure input table or query exist”
While without this second 'rsMarks' my first SQL 'rs' works well
I will be grateful if any one help me.
Regards
Rahulgty
Is there a function for cumulative row for use in querys (calculated field) or in a code - something like "running sum" in reports?
View 2 Replies View RelatedForgive 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.
Thanks.
I am using Access 2003 but it is to slow, what can I do?
View 1 Replies View RelatedHi Folks,
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
Cheers Oldtimer
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?
DT
Hello,
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)...
View 3 Replies View RelatedHi,
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?
I'm using WXP and A2K3
Thanks in advance.