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 RepliesHi
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 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.
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
Hi guys. i guess i am half way through. just like final step left still trying to figure it out how to create a VB procedure. anyways this is my theory and this is how much i have achieved. my God i have searched i guess every forum trying to get an answer it should'nt be that hard. I have a database. what i am trying to do is that i have a button for view reports which prompts the user to another form with 3 buttons
1) Issue(based on my query)
2) Issue Resolved(based on my query)
3) Issue not resolved(based on my query)
i am using this onclick command
Private Sub Issue_Resolved_Click()
DoCmd.OutputTo acReport, "Issue resolved", acFormatXLS
End Sub
same thing for my other options. works fine but when i open the report in Excel the whole formatting is bad so with my research i found this code very valuable. a standard module code which is as folllow.
' // Generically formats a worksheet
Private Sub GenericXLFormat(ByRef xlApp As Object, ByVal strDescription As String)
On Error Resume Next
With xlApp
.Rows("1:1").Font.Bold = True
.Cells.EntireColumn.AutoFit
With xlApp.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.PrintArea = ""
.LeftHeader = ""
.CenterHeader = strDescription
.RightHeader = ""
.LeftFooter = "&""Arial""&8WF COF/SIP Database " & APP_VERSION
.CenterFooter = ""
.RightFooter = "&""Arial,Bold""&8CONFIDENTIAL"
.LeftMargin = Excel.Application.InchesToPoints(0.5)
.RightMargin = Excel.Application.InchesToPoints(0.5)
.TopMargin = Excel.Application.InchesToPoints(0.8)
.BottomMargin = Excel.Application.InchesToPoints(0.75)
.HeaderMargin = Excel.Application.InchesToPoints(0.5)
.FooterMargin = Excel.Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
End With
End Sub
i tried to debug it and compile it no errors. now this is what i want and i will be done. what i want is when a user clicks on my report button which is (Issue) i want this Xlformat module to be run with my onclick button. i can't find or cannot think of any procedure since my skills in VB is not that very good. all i wanted to do is create some kind of procedure with this Xlformat module so then a user will be able to save the report as an excel file and then when he opens it the report in excel should be in perfect format. columns, width should be alligned automatically. i have been searching days and night with different answers to create a template or do this and that but nothing works plz help guys. i really appreciate it.
Thanks
Can someone help? I am working on an event database (based on Microsoft 2003 event database template). At present when I register attendees, I can preview an invoice which is generated based on the information I have entered.
I have set up another option to preview a Letter of Confirmation based on this same principal. As I am "Visually Basic" challenged, I simply 'copied & pasted' the event procedure and made the changes as needed to ensure the correct report was opened (ie not the Invoice report but Confirmation Letter report).
However, when I click the button to open preview the letter, the "Print Invoice" box also opens up (as it does when previewing the invoice). I don't want this box to open as I don't need to enter any details.
Can someone please have a look at my VBA event below to see what I would need to delete to stop the "Print Invoice" box from popping up.
Private Sub LOC_Click()
On Error GoTo Err_ConfirmationLetter_Click
If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "Enter attendee and registration information before previewing the Confirmation Letter."
Else
DoCmd.OpenReport "ConfirmationLetter", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID]
End If
Exit_ConfirmationLetter_Click:
Exit Sub
Err_ConfirmationLetter_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_ConfirmationLetter_Click
End Sub
Many thanks for any help.
Kath Price
Auckland, New Zealand
PS - Below is the original 'Preview Invoice' event that I copied:
Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click
If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "Enter attendee and registration information before previewing the invoice."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "Invoice", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID]
End If
Exit_PreviewInvoice_Click:
Exit Sub
Err_PreviewInvoice_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_PreviewInvoice_Click
End Sub
In my database I track a program called Bridge. Once you open bridge you can have anywhere from 1 to 7 "Sessions". Each bridge is assigned a number IE LT175A. If there are mulitple sessions they are numbered LT175A, LT175B, LT175C etc. I have a check box in my database that shows if bridge is installed on that PC. If it is then my section for session numbers are visiable, if bridge is not checked then the session numbers section isn't visiable. I currently have my Event Procedure in the After Update section. My code works because if I unclick on bridge my sessions disappears. However when I go to the next record if bridge is checked then my sessions are visiable but on the next record where it isn't checked it still displays the session section. So it isn't adjusting itself from record to record. Does my code need to go someplace else or am I doing something else wrong?
Here is the code I have:(and it's under After Update)
Private Sub Bridge_AfterUpdate()
If Me.Bridge = False Then
Me.BridgeSession_1.Visible = False
Me.BridgeSession_2.Visible = False
Me.BridgeSession_3.Visible = False
Me.BridgeSession_4.Visible = False
Me.BridgeSession_5.Visible = False
Me.BridgeSession_6.Visible = False
Me.BridgeSession_7.Visible = False
ElseIf Me.Bridge = True Then
Me.BridgeSession_1.Visible = True
Me.BridgeSession_2.Visible = True
Me.BridgeSession_3.Visible = True
'Me.BridgeSession_4.Visible = True
'Me.BridgeSession_5.Visible = True
'Me.BridgeSession_6.Visible = True
'Me.BridgeSession_7.Visible = True
End If
End Sub
Thanks,
Rick
I have the following code in my VB:
DoCmd.GoToRecord , [Response Tasks], acGoTo = [Response_ID], "08"
This is not working however.... The table name is Response Tasks, the Column name is Response_ID and I am trying to pull the info from the one named "08". What am I doing wrong in the procedure?
Thanks in advance
Hi,
I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
Thank you.
In one of my forms, i have this event procedure:
Private Sub Gross_Com_LostFocus()
If Gross_Com.Value >= 1000 Then
Full_Com.Value = [Gross_Com] * 0.2
Else
Full_Com.Value = 0
End If
End Sub
Is it possible to put such code in a report and query? If so, how?
Thanks a million!
Cheers!
Sheila
I have a procedure which can put in a Restocking Charge, it works good but you have to type it in each time here it is:
Dim strInput As String
Dim strMsg As String
strMsg = "Enter Restocking Charge?" & vbCrLf & vbLf & _
"Enter percentage."
Select Case MsgBox("Is There a Restocking Charge?", vbYesNo Or vbExclamation Or vbDefaultButton1, Application.Name)
Case vbYes
Beep
strInput = InputBox(Prompt:=strMsg, Title:="Value")
Me.RestockingChg.Value = strInput
Case vbNo
Me.RestockingChg.Value = ""
End Select
I tried using the txtbox DefaultValue property and setting.DefaultValue and niether work very well. Is there a way to set the pop-up to display 15 or 15% when it opens and then be able to type over it? I can do that with a form, but the form open all ever the place and I couldn't get it to hit enter with out tabing or clicking the cmdButton. Thank you in advance to anyone helping out on this little bug. :eek:
Greeting wise one...
I have implemented a logon script found in this forum - many thanks.
In essance, the script opens a form with info based on a table.
Could I filter the records of the form by only only including those records that are associated with the UserID of the user that just logged on?
The script:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this
'matches value chosen in combo box
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then
lngEmpID = Me.cboEmployee.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmMain"
Perhapt a filter here?????
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub
I know this has got to be a simple reference problem, but I just can't figure it out. I am trying to pass a nested subform to a procedure, but I keep getting a type mismatch error. Here's an example:
Call MyProcedure(Forms!MainForm.Form!Subform1.Form!Subf orm2)
Public Sub MyProcedure(frm as Form)
.
.
End Sub
Hopefully I can explain this right, I have a text box (text7) that is bound to my table. This text box stores a 2 didgit code between 10 and 99 to the main table. I want to be able to use a random function =Int((99-10+1)*Rnd+10) and have the generated number output to text7 only after I initiate the onclick command for the button the function is behind. Does this require a macro to output the number to the bound textbox or is there another way that someone knows of to do this?
Thanks All, Narack
How do i create a Function procedure that calls the Sub procedure from vis Basic? ?
View 1 Replies View RelatedCan you step into an Event Procedure line by line in Access? I toggled the Breakpoint at the End Sub of the Event Procedure, clicked in the middle of the sub, and pressed F8. Nothing! I do this all the time in Excel.
View 2 Replies View RelatedHi,
Is it possible to call a procedure that exists in one form on another form please?
Regards,
B
why aint this working ?
Expr1: Mid([MinuteCode];1;InStr(1;[MinuteCode];".")-1)
Try this one guys,
I have a form based on a Query with Comboboxes reading a Table.
the Combo's populate Textboxes with code as:
Private Sub Combobox1_AfterUpdate()
Me.Textbox1=Combobox1.Column(1)
End Sub
Combo bound to column 1,Row source=table.
This was created in Access 2000 and works fine, but when tried in '97 Textboxes are not populating.
I cannot figure out what is wrong.
(Need it in '97 for work purposes)
I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form.
@contractid = contractid
@start_date = startdate
@end_date = enddate
@hours = duration
Can anyone help.
I have a large search form, in which I am building a query in the code.
I went to compile the code, and was suprised to receive the error "Code too long for procedure". I had no idea there was a limit, but now I know.
So, I believe I will have to break this code up into chunks, stored on at least one if not more code modules, and call the functions.
To do this, I would have to pass the values entered into the form, to the code module. Build that portion of the query Where Clause, and then return that string value back to the code on the Search form.
Has anyone attempted this before? If so, could you give me a small example of how to pass a value from a form, to a code module, process it on the code module, then pass a resulting value from the code module back to the code in the form.
Thank you for your time!
T.J.
Does this happen on any key or can I specify the return key. If so how?
Thanks
I am trying to figure out how to manipulate the On Delte event of a subform.
I can delete the record but I need to have fields on the main form reflect the changes. Can any one help me?
Error Message:
The expression On Delete you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.
*The expression may not result in the name of a macro, thename of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.
Basically I think this message is telling me that I can not modify the deleting procedure as I wish too. If this is true can you tell me where I can do that.
here is the very basic code that i am using.
This is the subform code
Option Compare Database
Dim main As Form_frmOrders
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo err
Dim wght As Double
Dim pallet As Double
wght = .Total_Weight.Value
pallet = main.Total_Pallets.Value
If Weight.Value <> 0 Then
wght = wght - Weight.Value
MsgBox wght
Else
pallet = pallet - Quantity.Value
MsgBox pallet
End If
err:
MsgBox err
Exit Sub
End Sub
Thank you,
Teri
I created a button on a form that executes a procedure: MyButton_Click().
The button works fine.
Now I want to have a menuitem execute the same procedure, but
when I fill in the OnAction property with MyButton_Click(),
=MyButton_Click(), Me.Form.MyButton_Click(), and any number of
other combinations, and then click on the menuitem, I am told that
Access "can't find the function".
Is this just a matter of specifying the procedure correctly, or what?
thanks,
roy