Tables And Forms And Macros Oh My!
Jul 17, 2007
Hello everyone! I’m new at building databases; actually, I’m learning as I go as I’ve never had any formal training and this is my first significant attempt. So with that said, I’m hoping someone out there may be able to help me and I apologize in advance for how long this is. A little back round—I’m writing a database to create a report showing the loan history on an account—basically it shows how many loan payments came in with the dates they were processed and amounts of each payment. I created a table that is supposed to populate after entering the policy number, original loan amount and original loan date into a form. I have a macro that when a button is pushed on the form, should run a query (this query is made up of the table I created as well as tables from our data warehouse and when ran, gets the loan payments, dates processed, amounts, etc), opens a report in print preview (showing this loan info), and then deletes the record from the table that I created via a delete query (I do not want a history of each policy number that is entered into the form). Now to the issues—when I enter the info into the form, it does not automatically populate the table that I created, therefore the report does not show any loan data and obviously the delete query would not work. If I move to the next blank record, the table does populate with the info I just entered in the previous record, the report runs, but the delete query still does not work. I’m not understanding why my table won’t populate right away—the record source on the form is my table. I assume the delete query not working could have something to do with my macro? Here is how I have my macro set up:
Action: Echo Action Arguments: (Echo On) No (Status Bar Text) blank
Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryFormInfo (In Database Window) Yes
Action: OpenReport Action Arguments: (Report Name) rptFormInfo (View) Print Preview (Filter Name) blank (Where Condition) blank (Window Mode) Normal
Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryDeleteRecord (In Database Window) Yes
Action: Echo Action Arguments: (Echo On) Yes (Status Bar Text) blank
So if any of you made it this far, can anyone see anything obvious that I have wrong? Just a warning—I’m not good with VB code. I appreciate any input/suggestions you may have.
Thanks for your help!
View Replies
ADVERTISEMENT
Jun 7, 2013
I have a .mdb file (access 2003) which has ballooned in size to 1.2GB and I'm not sure why. I tried deleting some older tables (which were copies and had about 38,000 rows each) but it hasn't made a dent. Is there a way to show all the database elements tables, forms, macros, modules etc and list their sizes so I can see what has caused the size to increase?
There are 3 tables linked via ODBC which have tens of thousands of rows but as these are linked I wouldn't have thought they would have increased the size at all?
View 10 Replies
View Related
Feb 16, 2005
I have a list of objects (forms,queries,macros,reports the number varies but could be up to 100) that need to be deleted from several other databases (about 20).
I could do this by hand (go into each database and select and delete each object) but this is slow and error prone.
I could create a macro and import it into each database and then execute it (I know how to do this)
or (!!!!!!)
there should be some way to be in one database (HOME) and programatically (VBA) delete in the other database (OTHER).
I know how to
Dim db As Database
Set db = DBEngine.Workspaces(0).OpenDatabase("c:other.mdb")
What do I do next. I could use the db.execute command for tables (which I don't want to delete) but what about forms,queries,macros, and reports ???? I tried to modify the MsysObjects table but ran into permission problems.
Any Ideas
View 1 Replies
View Related
Feb 3, 2015
I've been developing a new db and have been adding buttons to forms all along without any issue.
I would create a button and under the event tab in the properties sheet all the buttons used to indicate "On Click" would produce an [Event Procedure]. Suddenly the "On Click" now indicates an [Embedded Macro] is going to run, which is not what I really want to have happen......
View 1 Replies
View Related
Mar 31, 2008
Okay, so I just got a big project dumped into my lap to rebuild a database that was lost. This DB would import data from a couple excel spreadsheets and a text file, compare them to find any records that are missing from one or the other then spit that information back out in two reports. To make things harder, the person who uses this DB isn't the most computer literate and needs to be able to do everything with the push of a button. For this project I'm using Access 2003 with a possible need for 2000 backwards compatibility
First Issue: Importing. Is there ANY way to save an import so a macro could run it without the user doing anything? The other option is linked tables, but is there any way to update the data in those links because some of it needs to be changed. Also, can linking combine two excel spreadsheets into one linked table?
Second Issue: Information updating. As I mentioned before there is some information that needs to be updated before we can do comparisons. There is a field in both record sets that could be used for comparisons except for the fact it is formated all wrong.
In the excel spreadsheet it is set up as just a string of numbers like "3125" or "55879", but in the text file they added some preceding digits to this number code. The text file adds "HM" and a number of 0s to each number so that the field is a uniform 10 digits long. Like "3125" would become "HM00003125" and "55879" would be "HM00055879". So each record needs to have this data either added to it or trimmed off. The problem here is the variable number of 0s. I created a macro that would use the Len function to test how many zeros need to be added and update the field, but it can only run on one record at a time.
What I need to do is have either a trimming or adding updater for that field that runs in the background (either through the macro button or when loading a query) to make those fields compatible. It also would be preferable to work through linked tables.
Thankfully, there isn't any sort of rush on this paticular project. If you have any advice on any part of this project, please post
Thanks in advance
View 14 Replies
View Related
Sep 22, 2004
hI,
I have a form which has several checkboxes and several textboxes and a command button. the user selects some checkboxes inside the form. when he clicks the command button, i need to run the select query. the select query would be like "select col1,col2,col3 from tablename where check1 = (checkbox1 value) and check2 = (checkbox2 value) and check1 = (checkbox1 value) and so on. ". i have tried this query using VBA. it is working fine. the problem is i have run another query which is very long in characters(like 15 lines involving self join). when i tried to run this query inside VBA i am not able store the query inside a string. Is there a way to come around this problem? I think it would be easy if i can use macros to build this query. please suggest me the ways to do this.
Thanks,
Sridhar!!
View 1 Replies
View Related
Mar 11, 2005
I inherited this database where there is very little code, most of the commands are in macros. I know nothing about macros. I am having a problem with the database. Some of the records are not printing when I print the schedule(this is a scheduling database with dates and job quantities). There is a display checkbox that gets checked when we want a job printed on the schedule. Sometimes it will not print it if we do not have a date for when the files are due or when material is due, so if we put those dates in they will show up on the schedule. Lately though I can't even get it to print some of the jobs if I put in the date when the files and materials are due and when they come in. They just don't want to show up. If anyone could help, I would greatly appreciate it!! Thank you
learnasugo
PS I am working on getting db small enough to paste. Don't know how else to make it so you can see db.
View 9 Replies
View Related
Jul 26, 2005
I am trying to invoke a microsoft Word recorded macro ("Macro1") in the microsoft Access VB code. I am trying to do this after I open the Word application and the file. This is my code so far:
Dim objword As Object
Set objword = CreateObject("Word.Application") '
objword.Visible = True
objword.Documents.Open ("I:FIRST DRAW CHECKLIST.doc")
objword.Run Macro1
The bolded line is the one supposed to tell word to play the recorded macro but after it opens the document, Access gives me a message that says it can't run the macro. Can anyone tell me the correct syntax that will help my code work?
View 2 Replies
View Related
Jun 28, 2005
When you need to run a series of Queries.
And they must be sequential.
Is it better to use a Macro or VBA to call the queries?
Thanks Everyone.
Joe
View 2 Replies
View Related
Oct 4, 2004
I've got a project where an existing access db is being converted for use on a web site.
Our problem is that several of the macros apparently reference an 'S:' drive.
Is there any way I can edit these macros to change / remove the drive reference?
View 1 Replies
View Related
Aug 18, 2004
I have a report that spans 4 years, and for each year it takes about 25 queries to produce the report. The queries for each year are only different in one number in the criteria, so what I'm trying to do, instead of having to manually copy and edit all of those queries every year, is make a macro that will do it instead.
I can make it copy all the queries and rename them, but I can't make them change the criteria in the queries.
View 3 Replies
View Related
Sep 19, 2005
I'm leaving my job of 21 years soon to finish school and hopefully moving on to bigger and better things doing what I like (hopefully something with DBs) and I've got 2 DBs I've built for them. The other day I combined them into one and really started to make sure that there was no need for them to ever get into design mode (not that they could without me -thanks sbaxter).
So while I was testing all the cmd buttons, there's some reports that were made in the early stages that are fed by a parameter query, and are opened by a macro. I've found that the more I'd learned, the less I use macros now, but I did more in the beginning. Anyway, when the input box pops up looking for the parameter, when I hit cancel, I get the "Action Failed" box and then the cancelled by user box. How can I avoid them getting the first error box when they decided they don't want that option?
View 4 Replies
View Related
Jan 17, 2006
I am using Office 2003 on XP. I have a number of reports generated by macros in the database. Once I moved the application to a different pc the macros don't respond, I have set the macro security to low and still no. If I operate the application through the network all works fine but using at the pc no. Any suggestions?
View 2 Replies
View Related
Mar 1, 2006
Hi there, forst post here so go easy.
What i need to do is copy the contents of certain fields in a subform onto empty fields on the primary form, can i do this, i gather it wold use a macro, but i checked no the list and i cant see anything wihch immediately jumps out at me as doing the job, any help
Thankyou, dragon2309
View 2 Replies
View Related
Jun 5, 2006
I have built a fairly large access database (office 2003) and unfortunately I was a bit careless about naming my controls and macros. I would like to go back over all of my form controls and macros so that they have more descriptive names, but I'm afraid this will break alot of dependencies. How can I have the macros autoupdate to compensate for renamed controls and vice versa?
View 2 Replies
View Related
Aug 10, 2006
Hope I am posting my question in the correct place. First of all I am entry level user in access, but knowledgeable in excel. As I am working with data containing over 65,000 rows I decided to use access however I don’t know how create a function that will work give the same result as (vlookup) in excel. Scenario case: first column “Serial Number” contain a list of serial numbers, 2nd column will be fed manually or by a bar-scan, the third column should show the value of “1” if the value in the 2nd column exists in the first column else a value of “0” should appear
The validation formula in excel looks like: =IF(ISERROR(VLOOKUP(B2,$A$2:$A$65000,1,FALSE))=TRU E,0,1)
Serial Number Confirmed Serial Validation
141614661
141614662
141614663
141614664
141614665
141614666
Thanks in advance,
Tarek
tarek_habal@yahoo.com
View 1 Replies
View Related
Aug 23, 2006
Hi,
I am using a macro to export a table to an excel spreadsheet. It seems like I have to write the absolute path in the macro's file name property, otherwise it doesn't work. The problem with that is that I have to change the path name whenever I move the folder to another location.
Any help will be very much appreciated.
B
View 3 Replies
View Related
Mar 7, 2007
Just started with 2007. For some reason I can not import any of my macros from 2003. I get a message "You don't have permission to import, bla, bla,
In the same message it also say:
"you must have read design and read data permissions".
It sounds like that I don't have full rights, although Im the admin of the DB.
Now I can create new macros in 2007 but just can't import any.
Any ideas.
View 4 Replies
View Related
Dec 8, 2005
hi guys!
Is there a way to run a query with macros? I don't want to open it. I just want to run it, generate a report which will then be sent via email. How do I go about it?
Thx a mill!
cheers:D
View 1 Replies
View Related
Sep 23, 2004
I want to run a existing query residing in my Access database using macros (MS Access 97)
Can someone suggest me the steps to go ahead ?
View 11 Replies
View Related
Feb 11, 2005
Hello all,
Ok, let's see if i can explain this so that anyone trying help can understand. I have a form that's unbound (Music) with a subform (Arist Info)that is bound. The subform also contains two other Subforms (bound). The subform (Artist Info) contains a few Macros set up to do
some general searches with some Option groups and also one cmb box with vb code that access wizard wrote up. They all work great, with no problems at all when I open up the subform all by itself. It's when Main form is open where i run into some glitches. In then option groups all tgl & radio buttons work with exception to the two named ALL. I get a ballon message stating There is no field named
'GenreGroup or AlphaGroup' in the current record. Genre and AlphaGroup are the names of the option groups. The cmb box dosen't even work at all. There ars some other problems also but i'll ask for help later if i can reslove this. LOL I've attached a bare bone sample db to look at. I thought I was doing great learning new things every day, till I ran into this one.
Thanks for your help
Rich M.
View 4 Replies
View Related
Apr 7, 2005
Hi guys,
I’m working on a report that I want to show on the web as a Snapshot file. I created a Macro using OutputTo method to export the report. If I type the file name and location on the Output File it works perfectly, but the problem is that I want Macros to read the file name from a combo box since the file name will change every day. Here is the code I got so far.
C:Test””&FORMS!frm_FORM2!cbo_Name&”.snp”
Every time I run this Macro I got the following message:
“The report snapshot was not created because you don’t have enough free disk space for temporary work files.”
Now!, when I type the file name to that specific location Macro does the job perfectly.
Is there any way I can get the File name from a combo box located in FORM2.
Thank you so very much in advanced. Your help is always a blessing.
Mosquetero
View 2 Replies
View Related
Aug 8, 2007
Okay, here's what I'm trying to do... I have a form, and I want to be able to automatically log the time that users are working on that particular project. This is my thought process, and I just can't get it to work. I'm using Access2000, and I'm still somewhat new to figuring it out.
1st Time button - start time - this would be a macro that goes and updates a table that has the start time, end time, project id and an autonumber field (so it will just keep adding to the table). There would be a date & time stamp that goes into the start time column.
2nd Time button - end time - Same basic function - it would put a date/time stamp into the end time field.
I want to use this to create a record to show how long the user was working on the project (end time - start time = time worked). I've tried setting up macros, and I can't get them to actually update the table. I've had little experience with macros and I could really use some help.
Thanks!
Gretchen
View 6 Replies
View Related
Feb 13, 2014
Is there a way to have a form pop up with a button when you open the database that when you click on the button it enables all macros?
The issue I'm having is that I have a database on a SharePoint and there is a macro in one of the forms that the user enters information into. If the user doesn't enable macros then the code will not work. Is there a way to "force" the user to do so when they open the database each time?
View 8 Replies
View Related
Feb 20, 2007
Ive searched the forum and not found any threads relating to this. Some help would much be appreciated!
View 3 Replies
View Related
Jul 2, 2007
Hello,
I have a 'legacy' Access DB which I need to get into.
Unfortunately there is a problem, whenever I try to open it (using MS Access 2003) there is an error (Disc/Network error 3043) and the database closes immediately.
How can you open an MS Access db with all macros/vba etc disabled, so that the code causing the error does not execute and I can find out what is going on ?
I have not been able to find any such options in the MS Access 2003 open dialog or menus.
Thanks,
Andrew
View 5 Replies
View Related
