I would like to have a macro run at a set time each day. I hoped to use .ontime but apparently that doesn't exist in access.
The macro must run at 10:00am each day, the db will be open from 8:30am until 16:30pm so no problem from that point of view +/- a few mins when the staff arrive.
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"
[code]....
Actually I need to select printer before printing report. That's why I need to call printer dialog to select printer using "PrintObject" in macro. But it's print the form not report. I need to print a specific report.
View 1 Replies View RelatedIs there a way to create some sort of an EXE or BATCH file in any Windows Explorer folder, that executes me a specific Excel Macro?
My problem is that I got several files split up by Departments, updated every day.
So far I have to open each file and update the format.
It would make my life much more easier if I could just run a file that opens each file and applies a format to them.
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I'm very new to access database and I'm trying to create a macro that allows the user to enter data after seeing a mistake in a form. I need a macro that will open the specific table and record of a piece of data. For example there is a student TestName3 whose grade on a competency is 30% but should really be 45%. I want a macro by the competency percentage to allow the teacher to edit that without looking through the entire table. The macro should prompt first to ask if the teacher is sure they would like to edit, second prompt asking for the student's unique ID number, and finally be taken to the specific record and table related to that competency.
I have tried to use a vastly overcomplicated DLookup and Order column to give me the value for a acGoTo search.
Here's what I have.
Option Compare Database
Dim answer As String
Dim response As Object
Dim gotoresponse As Object
Private Sub Command71_Click()
On Error GoTo Command71_Click_Err
[Code] ......
Hello, fantastic site you have here!
I have a form based on a table called TermWithPaysStoredData that shows a single row for everyone in the table with a small amount of info from the table and has controls for a user to enter dates which writes to that same table.
I have a subform that reads that same table but has much more information about each row. I would like to add a button to each row of the main form that says "More Info". When clicked, it should open the subform to the same employee.
The field I want to match is called ID. My main form is called fTermWithPays and my subform is called fTermWithPaysMoreInformation.
I tried using the OpenForm method but that either opens the form to the first record or filters and shows only one blank record.
I'd like to use a macro for this if possible because I don't know VB.
Any ideas? Let me know if more explanation is needed.
Thanks very much!
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
Hi, i cannot load posts from a specified date and time
PostDate = now()
'AID = accountID of the poster
'read post after added into DB
Set rsForum = Server.CreateObject("ADODB.Recordset")
strForumSQL = "Select * From tblPost where tblPost.PostDate = '"&PostDate&"' and tblPost.PostBy = "&AID&""
rsForum.Open strForumSQL, strForumCon,3,3
PostID = rsForum("PostID")
I gets an error when posting messages, i think is because: tblPost.PostDate = '"&PostDate&"'
PostDate is no text field, its a Date field and i tried to use this symbol: ' around postdate, i also tried to not use it, but then i gets another error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblPost.PostDate = 12/11/2005 10:32:50 AM and tblPost.PostBy = 6'.
but that is because the date/time format uses spaces.
How can i fix this?
Thanks in advance
I have a form for creating projects in a database. I originally set this up with 5 buttons for when the project is due to be at 1 hr, 2 hrs, etc. Now, they "management" want me change two of these for end of shift of on the current day and start of shift for the next day. This is the code I had before for the 6 hrs:
Private Sub Command152_Click()
Me.DueTime = RoundTime(Now() + 6 / 24, 1800)
End Sub
I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime
- BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])
Report
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?
View 2 Replies View RelatedI would like to design and build a database that can match one to one meetings between companies with specific time slots.
For example, we have two sets of people: buyers and sellers. A buyer could specify which sellers they would be interested in meeting. The database would then work out which buyers could meet with which sellers, and when.
stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.
Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]
[URL]
As you can see, the PRODUCT_ID column is not combining together according to their IDs.
[URL]
This how my Design View looks.
The SQL for my current query is:
SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
View 4 Replies View RelatedI have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have a form with a button which when pressed need to run a macro which sits on a different form.
i have tried run macro ad do cmd but none of these work.
Using Access 2007:
how to get macro properties using vba? I've attempted something similar to the below to try and divine macro properties:
Code:
Sub MacroDiscovery()
Dim mcr As AccessObject
Dim prop As AccessObjectProperty
For Each mcr In CurrentProject.AllMacros
Debug.Print mcr.Name
For Each prop In mcr.Properties
Debug.Print prop.Name
Debug.Print prop.Value
Next
Next
End Sub
Nothing comes up except the macro names. In my database, I have an AutoExec macro that I have manually added a description to; "This macro auto-executes when the database is opened by virtue of it's name." I can view the description by right-clicking on the macro and choosing 'Object Properties'.
Background:I am attempting to make a universal Access Database documenter for the many, many homemade databases on our network. I'm only interested in certain pieces of information and I would like to make a report to view the information how I want to view it. Thus, I don't care for the built-in documenter--I want to do it my way.
I have inserted a function via a module at the beginning of a Macro using the RunCode action:
Function Msgbox_Yes_No()
Dim Response As Integer
Response = MsgBox(prompt:="Select 'Yes' or 'No'.", Buttons:=vbYesNo)
If Response <> vbYes Then
End If
End Function
However, it doesnt stop the macro from continuing on its way to delete a load of records if the user chooses no.
I have 2 forms and a macro in Form1 which runs throught every record to update the records, however.I would like to run this macro from the main form (Form2)
I have tried
docmd.openform "Form1"
Run Macro
but it doesnt work
I'm working in 2003 (still) and have a macro to print the selected form the user has on their screen. I need this same macro to also print a PDF in a particular network folder or just exit if the PDF doesn't exist. I've tried RunSQL with "PrintDoc [Link2PDF]" as the argument. I've tried RunApp with "Acro Rd32.exe /t [Link2PDF]" as the argument but neither one works. The RunApp line will open the Adobe reader but nothing more.
View 4 Replies View RelatedI have a database thats open all day,
I need a macro to run from it at 9pm 12pm and 3pm
Is this possible?
I am trying to pass a date field from a Form and check if this date already exists within a table if so run this macro.
This is what I have so far...
Private Sub Command38_Click()
If DCount("*", "TblDietPlan", "[MealDate] = <> txtCusDate Then
stDocName = "McrPrint_LabelsWklyCR"
DoCmd.RunMacro stDocName
[Code] ....
I have an open form with buttons to open other forms and use an If statement to position them an example is below.
Code:
Private Sub Command31_Click()
DoCmd.OpenForm "Opener", acNormal
If Me.Frame54 = 1 Then
DoCmd.MoveSize 2500, 1000
ElseIf Me.Frame54 = 2 Then
DoCmd.MoveSize 25500, 1000
End If
End Sub
This works perfectly, however I need to put similar code as a line in a macro and cannot seem to link it.
Code:
Public Function totalmove()
If Forms.Scan_Data.Frame54 = 1 Then
DoCmd.MoveSize 2500, 1000
ElseIf Forms.Scan_Data.Frame54 = 2 Then
DoCmd.MoveSize 24000, 1000
End If
End Function
I get Error 438 object doesn't support this property or method...
I have two tables in my Access database, their fields are exactly the same (for now). One table is called Uncheched. The other one is called Checked. So what is need is a macro that takes selected records in table Unchecked and copies them to the end of table Checked. Actually CUTS from Unchecked and PASTES to the end of Checked table.
Sub MoveRecords()
DoCmd.RunCommand acCmdCut
DoCmd.OpenTable "tblChecked", acNormal, acEdit
DoCmd.GoToRecord , , acLast
DoCmd.RunCommand acCmdPaste
End Sub
I have a table it contains a list of opening and closing values along with a date and a couple control fields. What I would like to do is duplicate a few fields from records that meet a criteria onto the same table. After that is done there will be a few updates on the new records. Looking at it, it would be: 'duplicate' where field2 <> 0 and field5 = No and field6 = Yes. All other fields on the newly created records would be their default values.
View 7 Replies View Related