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 can manually stop the AutoExec macro from running by holding down the shift button - how do you do it by VBA? I'm opening the db from Excel, and I don't want the Switchboard to display ...
View 1 Replies View RelatedI 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.
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 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
Ok - really need some help here. I have searched and searched but i think my problem is slightly different to the ones i am finding.
Basically i have a form and i want to force users to fill in one out of 4 tick boxes. If none of them are ticked when they press the close button i want the form to stay open and a message box come up.
I can get the message box part to work but the form still closes down.
The reason for this is that when the form closes it goes to another form that is linked to it by a unique ID number. This works by the following code.
Private Sub BTN_INVIS_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm closure"
stLinkCriteria = "[issue ID]=" & Me![Issue ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BTN_INVIS_Click:
Exit Sub
Err_BTN_INVIS_Click:
MsgBox Err.Description
Resume Exit_BTN_INVIS_Click
End Sub
What i have tried to do is insert a piece of code into the above to generate the message box so it now looks as follows:
Private Sub BTN_INVIS_Click()
If [Transport_Delivery_issue_] = False And [Process_issue_] = False And [Design_issue_] = False And [Supplier_issue_] = False Then
MsgBox "You must complete the liability box before you can close this issue"
Cancel = True
[Design_issue_].SetFocus
End If
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm closure"
stLinkCriteria = "[issue ID]=" & Me![Issue ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_BTN_INVIS_Click:
Exit Sub
Err_BTN_INVIS_Click:
MsgBox Err.Description
Resume Exit_BTN_INVIS_Click
End Sub
This code checks the boxes and generates the message box if none are ticked - that works great. However it doesnt stop the second part of the code (the move to the next form) from executing if the boxes arent ticked.
Would really appreciate it if someone could help me - i'm fairly new to coding so the whole database is a bit cobbled together (mostly with help from this forum!).
I have a form with tab set one tab called "Enter Receipt" and another that houses 2 queries called "Reconcile". My issue is when I open that form, I have an On Current Macro to go to NEW record for my Enter Receipt, but I am getting a delay while the query status bar runs the other queries. I was hoping not to have those ran until i enter the parameters and hit the run button on that "reconcile tab".
Everything else works, i just need the queries to keep from running when i load the form. my queries i moved from EDITED to NO LOCKS thinking the On Current new record may affected them, not change in delay.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedHow do i create a Function procedure that calls the Sub procedure from vis Basic? ?
View 1 Replies View RelatedHello 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
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?
I have a form with about 100 labels and would like something to happen when I mouseover. So I have a following code:
Code:
Private Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Label1.BackColor = vbBlue
End Sub
[Code]....
I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. This is what I have at the moment:
Code:
Dim strDate As String
Dim strWOStatus As String
Dim CurrentConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
[Code] .....
The problem I am having is this error:
Error: 424
Description: Object Required
The line of code it errors on is:
Code:
Set .Parameters("@PartsUSedMTD").Value = ParamReturn
And the value of ParamReturn is always Null after it hits the line before it.
So it seems like it's not really creating the parameter variable SQL Server needs to run
This is a real conundrum. The following procedure works well the first time through but on subsequent times the database just goes into "Not Responding" mode and has to be restarted. I have tried putting delays in but with little success.
The idea of the procedure is to rename a query, "Daily Logs" to include a specific date "TransDate" so that when the query is sent via Outlook the Excel file name appears for example as Daily Logs 21-03-14.
DoCmd.CopyObject "", "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), acQuery, "Daily Logs"
DoCmd.SendObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy"), "Excel97-Excel2003Workbook(*.xls)", "someone@dorsetglidingclub.co.uk", "", "", "Daily Logs " & Format([TransDate], "dd-mm-yy"), "", True, ""
DoCmd.DeleteObject acQuery, "Daily Logs " & Format(Me![TransDate], "dd-mm-yy")
This procedure might seem clumsy but was the only way I could think of the achieve the objective.
I've been stumped for the past couple of days trying to discern a method for a form to determine that it's a new month and run a procedure and only run it on month change.
My initial thought is:
Open recordset
move to the last record
compare the month in a date field within the last record to the current month
and run the procedure if the month is different.
I can't get this to work however and I'm not sure what I'm doing wrong. To be honest I typically fumble my way through VBA when it comes to opening recordsets. Here's what I got:
Code:
Dim rs As DAO.Recordset
Dim db as CurrentDB
Dim strSQL As String
Set rs = CurrentDB.OpenRecordset("FlightLog")
strSQL = SELECT [txtDate] FROM [FlightLog]
[Code] ....
txtReqNumb and txtFltNumb are the values to be reset at the beginning of each month.
Am I on the right track and just have the VBA wrong?
I'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer
Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then 'Works fine here
'The bit I can't get right
[Code] .....
I have 3 event procedure with 3 buttons to make them run.I would like to create another button that can run all procedures togehter. if I copy one of the procedures how do I tell it to run the other 2.
View 2 Replies View RelatedI have a form with a field called duration.
The user selects AM,PM or All Day from a list
If the user chooses All Day I want to create a duplicate record
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
However I would like the duration in the first record to be AM and the Second record to be PM
So something like Iif [Duration], "All Day" run procedure.
Else IIF do nothing.
On my form I have a combo box where the user can select the sex of a person. I had set the row source of the combo to a table, tblSex and this works fine. Now I want to programatically add the table entries ( simply male or female) to the row source, having set the combobox to value list. I cant see how to pass the combobox and tablename parameters and I am getting an error on form load "The expression you entered as the event property setting produced the following error: UserDefined type not defined" The calling procedure from frmPeople is
Code:
'---------------------------------------------------------------------------------------
' Procedure : Form_Load
' Author : Administrator
' Date : 21/11/2013
[Code].....
I think the way the parameters combobox and tablename are passed is incorrect. I want to write a robust procedure which I can use for each of the comboboxes on my form.
I have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"
I tried this but dont know how to stop the timer...I want to run the code that changes the Form color but want to be able to stop the Timer and reset it to start over..Will this timer work? Almost like an auction timer. But want to be able to reset the code at any stage...
Code:
Public Sub ExcelTimer()
Dim PauseTime, Start, Finish
[code]..
I have a form with a button to print preview a report. This report needs one input parameter before executing. The computer this will run on is a touch screen and does not have a keyboard. Windows 7 has a "on screen keyboard" program. I want this to run first so that my user can input the parameter.
I have the following which throws an "Invalid procedure Call or Argument"
Sub CallTeclado()
Dim RetVal
RetVal = Shell("c:windowssystem32osk.exe", vbNormalNoFocus)
End Sub
Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server
While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.
The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.
What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.
I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded. The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work.
How to run a stored procedure on an SQL server from access while also giving it the variables it needs?
Stored procedure name: spNearMissCalculation
Variables:
@SelectedDate (date format) (taken from a form field)
@SelectedVessel (nvarchar(max) format) (taken from a form field)
@SelectedVesselGroup (nvarchar(max) format) (taken from a form field)
I have two forms:
frmOpeartions
frmManagers
frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.
The code to open frmManagers is:
Private Sub lblNewManager_Click()
DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog
Forms!frmManagers!cboMoveTo.Visible = False
Forms!frmManagers!lblManagers.Visible = True
End Sub
Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:
Code:
Private Sub lblClose_Click()
DoCmd.Close acForm, "frmManagers", acSaveNo
End Sub
frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:
Code:
Private Sub Form_Close()
If CurrentProject.AllForms("frmPlants").IsLoaded Then
Forms!frmPlants!cboPlantManager.Requery
Forms!frmPlants!cboQCManager.Requery
[Code] .....
So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.