Help With Macro Function
Nov 22, 2004
I'm creating a simple database to track some data that needs to be cleared out every 4 months or so.
I have all of the data stored in a single table, and I'd like to create a button that would do the following:
Step 1: copy the current data into a backup table for disaster recovery
Step 2: delete all of the current data in the table...but leave the table structure in place for new data
Step 3: compact and repair the database
The name of the table is StudentData.
Any suggestions?
Can all of this be done with one command?
Thanks a ton!
cmbehan
View Replies
ADVERTISEMENT
Feb 23, 2007
http://www.access-programmers.co.uk/forums/showthread.php?t=123538
Following on from that thread, I would really like to tidy up other areas of my front end. I am using the idea that Roy suggested and it works very well.
At the moment, I have a form, that has buttons on it that people click when they want to generate specific sheets from excel using data from access using VBA on the form. These work fine, however, now that the database is going live and all features need to be added, I think it would be nice to have one small form, with a list box.
I have converted a copy of the code on the form and placed in a module by declaring then functions instead of private subs.
At this stage, I tried doing it with macros running the code by using a series of runcode. I then created a table that has the macro name stored in it along with a decriptive bit of text detailing the excel chart that gets produced to make it easy for the user.
The list box populates itself correctly, but the problem I am having is I do not know how to make the 'go' button look at the list box, pull the hidden macro/function name from the list, then go and run the macro/function.
I have tried looking about, but have not found anyhthing.
View 4 Replies
View Related
Dec 20, 2004
Is it possible to create a macro in Access that opens up a word document from a shared drive? Within Access, I don't see a macro available to do this.
View 1 Replies
View Related
Jan 23, 2014
I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).Before you go there, I have already tried the navigation form and set all forms as subforms.
View 5 Replies
View Related
Dec 8, 2013
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]....
View 1 Replies
View Related
Dec 11, 2013
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 Related
Jan 30, 2014
I 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
View 2 Replies
View Related
Jun 9, 2005
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
View 9 Replies
View Related
May 25, 2006
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.
View 1 Replies
View Related
Dec 5, 2005
Hi.
I have a macro (that runs a bat file) that I would like to run for the first time ,and only the first time that a form is run. The bat file will then copy over certain files the db needs.
Is this possible?
Thanks.
Frank.
View 1 Replies
View Related
May 19, 2005
I have a database and in order to get the correct values you need to run a few queries/make tables/delete tables ect. I was wondering if there was a way to code something so that on command click button it would run through all the neccessary steps so people dont have to manually do this.
View 1 Replies
View Related
Mar 31, 2006
Hi,
Here's a sample of the database that I'm working on. I'm trying to help teachers take attendance of all of their students and so have the following tables.
tblAdmin - List of Teachers and their IDs
tblAttendance - Courses, Student IDs, the Date and the Status (tardy, etc..)
tblCourseInfo - List of Courses and the Teachers teaching the course
tblEnrollment - List of the Courses and the students enrolled in them
tblStudents - Student IDs and their names
The form that I want is a "Course Information Form" that will
(1) Display all the students enrolled in their course
(2) Allow the teachers to take attendance everyday while keeping all the previous records
I've attached my sample database. I really appreciate ANY help any of you can suggest.
Thanks :)
View 13 Replies
View Related
Feb 27, 2006
I am running a Macro that is running 3 query and saving inbetween each query.
My issue is that when the macro runs I have to hit ok when the message boxes pop up saying "its about the run a Query" and then again when it says "your about to update the records". These two messages occur for each query and save. So I have to hit OK 6 times
Is there a way I can set the macro to NOT show these message boxes?
THanks in Advance
View 9 Replies
View Related
May 2, 2006
Hi all,
I usually search for the answers to my problems, but as im not sure what the problem is i havnt been able to do so here.
Basically i have a pretty simple database, it works fine in the UK. The company i have designed it for are moving to Budapest, i have just had an email this morning saying an error message pops up when the try using a form, i have attached the message. The macro just runs a query based on a table in the database asking the users to choose a record number edit. Im assuming it must be something to do with is being used in Budapest as it works fine here, but they are accessing it the same as they would do here, just from a different location.
Any help would be appreciated, thanks
View 5 Replies
View Related
Dec 2, 2006
I use an autokeys macro to allow users to openforms with a hotkey directly without going through the menu system.
I would like the users to be able to modify their own setttings for this, but I could not find a way of writing to the macro autokey table.
Anyone know of a way?
View 4 Replies
View Related
Jul 30, 2007
I have a program thats work perfectly in some computers useing the network but when i try to open it in other computers i get an error and when i try to debug the error it highlit the line that i am calling the micro that open the mainform in it, i hope some one have an idea for what is going on with the program
View 12 Replies
View Related
Dec 3, 2007
Hi,
Task: to extract data from table 1 of a database (tied to form 1) to create a record in another table 2 of the same database (tied to form 2).
Besides, I need to make it simple to use for an end user.
On the form 2 I have a command button that activates macro. Macro makes a query to run and extract data from table 1 and append a table 2.
Now I want the user to see the record on the screen (form 2) that has just been created. For that purpose I add "go to last record" step in the macro.
Problem: that doesn't work. :) For some reason it brings back same record from the middle of the table which is not the last. And what is even more interesting is that it doesn't tie to the record ID on the bottom of the screen (access generated).
Challenge: the record ID field in the table 2 is a primary key auto-numbered field (i know this is not perfect but I am not the one who created the database) and some records have been deleted over time. Might that be connected to that or that is something else?
Thank you!!
View 1 Replies
View Related
Jan 11, 2008
I have inserted two combo box in the form with lookup in the table. Now I want to select values in these boxes and filter the records having those values on click of a command button placed on the form.
How can it be done without using VBA? Is it possible with use of macro?
Kindly guide.
View 2 Replies
View Related
Mar 25, 2008
I hope you might be able to help.
I've managed to successfully setup my first Access database.
I have imported data from Microsoft Excel into an Access Database and Table within that (EmptyHomesTable).
The data relates to empty properties and every month at work we receive a list of empty homes in the month. Every month, this new data will be imported into a TemporaryImportTable.
I am then running an update query to compare the data in the EmptyHomesTable with TemporaryImportTable and 'close' those which are no longer empty (i.e. update their status in the EmptyHomesTable if the account reference number doesn’t match).
I am then running an append query to compare the data in the EmptyHomesTable with the TemporaryImportTable and add any new empty properties (i.e. add those which aren’t in the EmptyHomesTable by looking at the account reference numbers and adding them if they don’t match).
This gives us a working database of empty properties but doesn't delete those which are no longer empty (rather they are marked as closed).
What I'm trying to do is to run a Macro to automate all of this on a monthly basis.
Macro is as follows:
1. Delete Query to delete the data in the TemporaryImportTable but keep the table structure;
2. TransferSpreadsheet to import the latest Microsoft Excel file into TemporaryImportTable;
3. Update Query to close properties which are no longer empty in EmptyHomesTable;
4. Append Query to import those new empty properties in EmptyHomesTable;
The macro almost runs fine but I have a couple of questions to help finish it:
a).I’ve run the macro to update the February list to the January list which works fine. Running the macro to update the January and February list (combined) is almost fine but I’m 2 entries out. I can’t manually check as we’re talking about 1,500 entries. Is there another way?
b). Is there any way for the TransferSpreadsheet query to ask at each time of running the macro for the location of the Excel spreadsheet or do I need to go into the macro every time and change the file location?
c). One of the fields in the table is empty date (i.e. the date the property became empty). Is there a quick way to filter the entries before a certain date (i.e. only show those empty before 30 September 2007 for example)?
Thanks for your help.
View 3 Replies
View Related
May 17, 2005
Hello,
I have a procedure which I undertake and wonder whether it can be automated in any way.
I have a field on a form for Purchases (frmPurchases) for a Purchase Order number. To get the order, I click on a command (cmdpo) which opens another form and clicking a command on this form (cmdgetpono) produces a unique Purchase Order number. I then manually copy the number given and paste it into the field on frmPurchases (PONo).
I have not used Macros before but cannot see that there are the options to achieve this. If someone could suggest the ones I should uses fro the list it would be appreciates.
Alternatively, is there another way of looking at this?
Thank you
Lin
View 2 Replies
View Related
Dec 14, 2005
Hello,
I have a button that runs a macro to delete records in 12 tables. I want to create a message box before that macro runs warning that you are deleting records in 12 tables and are your sure you want to run the macro.
I need a message box with an ok and cancel button. Where do I put the msgbox funtion in this code?
Thanks !!
This is my code:
Private Sub cmdRunDeleteMacro_Click()
On Error GoTo Err_cmdRunDeleteMacro_Click
Dim stDocName As String
stDocName = "mcrSemesterStartRecordDELETE"
DoCmd.RunMacro stDocName
Exit_cmdRunDeleteMacro_Click:
Exit Sub
Err_cmdRunDeleteMacro_Click:
MsgBox Err.Description
Resume Exit_cmdRunDeleteMacro_Click
End Sub
View 5 Replies
View Related
Feb 8, 2006
Hi, hope someone can help!
I have a macro written that when clicked on it opens a web browser. Is there a way this can then point to a specific website url? I could make the default url in the browser point to a url but each time i click on the macro button the url needs to be different. all of the urls sit in a table within the database.
Hope this all makes sense!!
Thanks
B
View 5 Replies
View Related
May 31, 2006
Hi everybody!
I have got a small question about the "set value" macro.
I am trying to use this macro in a form, so that the user doesnt have to type or lookup that value.
So what I want is a macro, that copies a value from a controlfield of a form and inserts this into another form and new record.
What i have is a inventory list of books. Some writers have written more than one book, so I created forms for <Add new book to existing writer>. But until now it doesnt work. Maybe somebody has a better idea.
Thanx for the help!
john:D
View 2 Replies
View Related
Sep 15, 2004
Ok. I have a database which tracks the sales and wherabouts of my artwork. Mostly I sell works on a sale or return basis, so I have a form which finds the delivery and opens the delivery details in a subform. This way I can tick off individual items in the delivery as they sell.
I also sell things outright. However, when I am paid for these sales, I want to be able to tick a 'sold' check box in the main form which then automatically fills in the date sold field (on both form an table) as the current date for all the items in that delivery, i.e. I want to tick the box once in the form causing the date to be entered into each record in the subform automatically.
I have tried making a command button on the form with a SetValue macro. I cannot get this to work with a date (maybe I have got the expression wrong) and also it only works with the first record on the subform.
Can anyone solve my problem or do I just have to be patient and enter each date individually?
View 2 Replies
View Related
Nov 6, 2004
Hi!
I know how to set up a switchboard so that it appears on the screen when the file is opened.
Does anybody know the line of code for a macro that you'd put in the switchboard menu so that the command opens a query named "QUERY1" so that it runs that query and lets the user click on "Tools," then "Office Links," then "Merge with Word Document" so that the Word file appears on the screen, the user clicks on the "<< >>" symbol, and then saves the merged document as an RTF file (so it can't be merged again)?
Essentially, all I want the macro to do is run the query. The user can do the rest.
The switchboard will open on startup have three choices on it. The choices will be "Open File in Add Mode," "Run Macro for Newest Person," and "Close File."
Thanks,
PFDJR
View 2 Replies
View Related
Mar 15, 2005
Hi,
I have been told that there used to be an option to record a macro in access '97 so that it would record your steps and then this can be saved as a macro and run when needed.
I need to be able to have relationships automatically added between three tables and thought the best way would be to use this record macro option. It doesnt seem to be part of Access now..
does any one know anything about this.. or how I could get Access to assign relationships to the tables automatically?
Thanks
-x-
View 1 Replies
View Related
