Modules & VBA :: Determining New Month And Run A Procedure

Aug 6, 2013

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?

View Replies


ADVERTISEMENT

Modules & VBA :: Procedure Declaration Does Not Match Description Of Event Or Procedure

Jul 31, 2014

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.

View 5 Replies View Related

Modules & VBA :: Determining If Another Form Is Loaded In Add Mode

Feb 2, 2014

I have two related forms.One is a list of transactions and the other is used for both inquiry and adding new transactions. If the Inquiry form is just sitting in Add mode and the List form tries to open it in Edit mode, it won't open. Is there a way I can tell from the List form that the Inquiry form is in Add mode and either close it or tell the operator about the issue?

View 1 Replies View Related

Modules & VBA :: Determining Number Of Users Of Split Database

Nov 1, 2013

We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.

However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.

For requesting funding, guiding future development, etc., I need to get some metrics:

Total number of unique users
Avg uses per day

Total number of simultaneous users (daily, weekly, and monthly maxes and mins)

What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?

View 3 Replies View Related

Modules & VBA :: Get Label Name Into Procedure?

Apr 28, 2014

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]....

View 7 Replies View Related

Modules & VBA :: Returning A Value From Stored Procedure

May 11, 2015

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

View 4 Replies View Related

Modules & VBA :: Access Not Responding After Procedure

Mar 21, 2014

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.

View 10 Replies View Related

Modules & VBA :: Multiple If Statements Into One Procedure

Jun 26, 2013

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] .....

View 2 Replies View Related

Modules & VBA :: How To Call Up Another Event Procedure

Aug 15, 2015

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 Related

Modules & VBA :: Event Procedure On Selection

Jun 27, 2014

I 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.

View 2 Replies View Related

Modules & VBA :: Passing Combo Box Control To Procedure

Nov 21, 2013

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.

View 7 Replies View Related

Modules & VBA :: Calling Stored Procedure With Parameters

Dec 2, 2013

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"

View 2 Replies View Related

Modules & VBA :: How To Stop Running Procedure Or Function

Jul 13, 2014

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

View 5 Replies View Related

Modules & VBA :: Shell And Invalid Procedure Call Or Argument

Feb 23, 2014

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

View 4 Replies View Related

Modules & VBA :: Run Stored Procedure Providing Variables From Form

Jan 15, 2015

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)

View 4 Replies View Related

Modules & VBA :: Event Calls Seemingly Unrelated Procedure

Apr 11, 2014

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.

View 6 Replies View Related

Modules & VBA :: Passing Recordset To Sub Procedure - Type Mismatch Error

Jul 13, 2015

I am trying to pass a recordset to a sub procedure as follows

Code:
Private Sub AddNewSProdStatus(rsTemp As DAO.Recordset)
rsSProdStatus.AddNew
rsSProdStatus![ProdID] = Me.Recordset![ProdStatusID]
rsSProdStatus![TitleID] = Me.Recordset![Title]
rsSProdStatus![ProdDealType] = 1

[Code] ....

However, whenever I call the procedure as below

Code:
AddNewSProdStatus (rsCSales)

I get a type mismatch error.

All Recordsets are declared as DAO and contain data...

View 1 Replies View Related

Modules & VBA :: Calling Stored Procedure From Access Gives Run Time Error

Nov 27, 2013

I 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].....

View 1 Replies View Related

Modules & VBA :: Invalid Procedure Call Or Argument Using Shell Command?

Feb 6, 2015

I'm developing an application where I want to call the keyboard up on the screen when a user enters a field. This is my setup:

Windows 8.1 32 Bit, Access Runtime 2010.
Exact lines of code are:

Dim RetVal
RetVal = Shell("C:Program FilesCommon Filesmicrosoft sharedinkTabTip.exe")

These lines of code work perfectly fine on my development PC which is running Windows 7 64 bit, Access/Office 32 bit.

I know the path to the exe is good. I can navigate and double click it and it works great. but the shell command is resulting in the invalid procedure.

View 4 Replies View Related

Modules & VBA :: Setting OnClick Property Causes Immediate Launch Of Event Procedure

Feb 5, 2014

I tried to dynamically set image OnClick property to function with couple of attributes. Everything works well, but... it launches the function automatically without any mouse click! What is causing this? I want to execute the function only on the result of mouse click.

Piece of code:

Forms!Form1.Controls("Image" & i).OnClick = fclick(PiltID)

Private Function fclick(u)
DoCmd.OpenForm "Form2"
Forms!Form2.Image0.Picture = u
End Function

View 6 Replies View Related

Modules & VBA :: Write A Procedure To Send A Separate Email To Each Store That Contains Records Found In Table 2

Feb 9, 2015

I have two tables

1) has email address, and Store ID
2) has multiple records per store

I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).

View 3 Replies View Related

Determining Tables Etc. Used By .adp

Sep 7, 2005

I have an .adp file but I'm not sure which tables/mdb file(s) are used. Any easy way to determine this? I'm backward engineering because someone got fired. Any help would be appreciated.

View 1 Replies View Related

Determining Focus

Jan 4, 2007

Is it possible to use the "On Lost Focus" event to determine which control now has focus?

View 6 Replies View Related

Calendar, Code And Determining Value

May 8, 2006

Working on a calendar project. I've found some helpful code, but I don't want to use someone elses whole project. So while trying to understand how all this works I'm running into some problems. I am using to start one of the simpliest codes I have found.
```````````````````````````````
Private Function filldates()

Dim curday As Variant, curbox As Integer, Tbox As Variant

curday = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), 1) 'first day of month
curday = DateAdd("d", 1 - Weekday(curday), curday) 'back to sunday
For curbox = 0 To 36 'need to loop thru 36 textboxes
Me("D" & curbox) = Day(curday)
Me("D" & curbox).Visible = False
Me("T" & curbox).Visible = False
If Month(curday) = Month(Me!FirstDate) Then Me("D" & curbox).Visible = True
If Month(curday) = Month(Me!FirstDate) Then Me("T" & curbox).Visible = True
curday = curday + 1 'nextday

Next curbox

End Function
```````````````````````````````````````
'Note' I have added the "T" box for additional info.....

My problem is this...... I have 36 boxes that can contain variable dates. What I want to do is on the "Click" or "DoubleClick" event of say...... box "T14" I need to determine what date is the current value of "T14" and then I can open the other form to that date. This other form would be details of whatever date is currently in "T14"
Does that make sense???? Hope so! :)
Any thoughts??

Thanks
Curtis

View 3 Replies View Related

Determining Appropriate Const MaxRecords

Jun 7, 2005

I have an ASP / Access storefront, connecting through JET OLE, which is frequently crashing with '800040005 System Resource Exceeded'. The whole ODBC/Jet service must be restarted by my host manually.

Usually this error indicates an unclosed rs.open. I have however triple checked all code and there are no unclosed loops.

I am examining the basic connection variables and wondering if perhaps as my database has grown over the last year, the following original 'out of the box' settings are no longer suitable, and causing this error.. in particular maybe the 'Const MaxRecords' is no longer enough (or is too many?). How does one calculate what these Const settings should be?

Thanks for any help!

Code:Response.Buffer = TrueConst adEditAdd = 2Const adCmdText = 1Const adCmdTable = 2Const adOpenForwardOnly = 0Const adOpenKeyset = 1Const adOpenDynamic = 2Const adOpenStatic = 3Const adUseClient = 3Const adLockPessimistic = 2Const adLockOptimistic = 3Const maxRecords = 200

View 1 Replies View Related

Determining Computer Region/location

Jul 14, 2005

Anyone know of a way in Access/VB to determine what the region/location is set to on a user's computer? Need to determine whether to use commas or decimals as a monetary separator.

Thanks,

B

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved