Hello,
I have a general table question. I have a VB programmed user interface logging information to a MS Access Database Table. I want to automate it so when you open up the table to view what was logged -- any table entry older than 2 weeks is removed from the table. Will this be difficult? Any suggestions on how to start? Are there any access functions that will do something similar to this? Thanks in advance.
Zak
Hi, I have a DB that is basically a big electronic form. Each table is a section of the form and the main central table stores the ID's from the other tables. Most of the relationships are one to one but without referential integrity because sometimes sections need to be skipped.
It's been growing fairly rapidly lately as bit's keep getting added but now a weird deletion problem has started appearing. Every once in a while when a user goes to complete the form (right at the end) it suddenly dissapears. The records in the related "section" tables are still there but the main table storing all the ID's loses the record.
There are NO cascade update/delete's on any of the relationships, also no-error message appears it just goes. I think there is a problem in actually saving the record because I moved a DoCmd.RunCommand acSaveRec command from the complete button to a slightly earlier process and the point at which the record dissapeared also moved.
Does anyone know what may be causing it? It only happens to about 1 in 30 forms and there is no obvious link between the problem and
a) the machine it happens on
b) the person using it
c) the data entered into the form
If it happens the same user can input exactly the same data in exactly the same way and it saves it fine.
It really is random!
People are starting to get really frustrated. I was tring to "sell" the idea to a new department as well and had 15 people sitting in front of a projector when it happened to me. I was mortified :eek:
Any ideas would be greatly appreciated.
thanks,
Tom
Hi all, new member with a newbie question. I have just started working with Access, so pardon if this question is unclear or pitifully basic. We have a basic database comprised of client names, amounts paid to-date and balance due. It also has a field named date last paid, so here is the question. When you enter or change the amount in paid to-date, can you automatically have the date of the change (ie. Todays Date) entered in the "Date last paid" field.
Thanks all for the help in advance.
Chris
I have a main form where the form record property is set to
Allow Deletions: No
I then have a subform, but I do not want it to inherit the permissions of the main form. Basically I can't delete on the subform either???
After running an UPDATE or DELETE query I get the typical Yes/No screen (ie. Do I want to procede to carry out the changes?). Is there anything than can be added to the query that would automatically answer YES, and eliminate the Yes/No screen from appearing.
I am trying to automate an import function for my database. I recieve a disk that has about 200 little spreadsheets on it every month. I need to either import these or set-up link tables to them in a more automated system than doing it all manually. (It takes a lot of time to do manually) Please can anyone help.
Note: The files are all named 3000 to 3200. The following month they are called the same. I'm hoping this will not cause to much of a problem.
PLEASE HELP
I currently have the following query written:
DELETE * FROM TABLE1
When runs, it tells me the number of rows to be deleted and I have to click "Yes" for it to run. Is there a way to run the query and not have the prompt so it will run without human input?
Not sure whether this is possible. I want to use a command button on a form to set the criteria for a particular field in a query. I have created a combo box listing the possiblities I require, but don't know where to go now, I have tried using SETVALUE, but it doesn't seen to like this. Any Clues?
Thanks
When I export or do a make table query from Oracle to Access, the new table has some of the datatypes changed. Many of my numberic fields are decimal instead of Long Interger. I then need to go and manually cahnge them back. If I forget or miss one, it crashes another application we use.
Has anyone come up with a solution to automatically go through the table structure and change the datatype from one format to another? This would be a great help.
I am using Oracle 9i and Access 2002.
Thanks for you time
Sherriff
I would like to automate this daily process: In this order import or link, (not sure which is the best option) a fixed width table, add an ascending number to the end of each of the records, reverse the order, and finally export to fixed width .txt documents depending on the quantities in one field. There would be 3 text files exported, max.
View 4 Replies View RelatedHi All!
I have Access 97 client and server databases linked together. My system is setup so the database is aware of how many users are currently logged in so Ill know when it is being used exclusively. What I want is some code to check every week that a repair and compact has been performed and if not start one on both the client and the server databases (assuming no one else is in them). Do I need code to delete the LDB files beforehand aswell just in case?
I kind of imagine this check will be performed when the first person logs in of a morning.
Client: rentals_prod.mdb
Server: rentals_data.mdb
Regards,
Dalien51
I´m thinking of starting to use office XP for a small system I have. My question is:
Is office XP more compatible with internet explorer in terms of VBA automating? I have had to work around internet explorer quite a lot in office 97, and I haven´t been able to do what I want.
Fuga.
Hello All
is there a way (i guess using VBA) to import an excel spreadsheet into an access table without having to go through the wizards, if you know all the field names and other variables ??
Yours in anticipation
DJS
I am a new Access user. I have created a database to track and inventory animals brought to a local animal shelter. The database includes information such as when the animal was brought in, who dropped it off, when it was adopted (Hopefully!), etc. The database is being used by people who have limited computer experience and the challenge has been to automate it and simplify it as much as possible. I have created several Switchboard pages to try and accomplish this.
I have done research through Google and MS support and have found little help with a solution to this problem:
When an animal comes to the shelter, it is held for three days before it becomes available for adoption. I have created seperate fields for Date In Day, Date In Month and Date In Year. There is also a field detailing the animals adoptability status. This field is left blank in the intake process, and is then manually updated to Adoptable, Biter, etc., after the three day period. Is there a way to automate the Adoptability Assessment Field (which is a Lookup Table Field) so that it changes from blank to Adoptable after the three day period? (We have very few animals come in that are marked as Biters, so I just need the field to change over to Adoptable after three days have passed. Unless a solution can be presented to filter animals with Biter status out of the solution.)
I truly appreciate any help that can be offered.
Ive been following the code on the Microsoft support site for automatically creating a PowerPoint presentation using data from Access.
The sample code takes one field from the sample table and displays this on the PowerPoint slide - it then assumes the user can apply this knowledge and add more fields to the slides themselves. I have tried to do this but am getting nowhere.
Please can somebody advise how I add additional fields to the slides via the code. I am afraid Im not very confident with VBA so am stumbling a bit blindly here. Here is a sample of the code that I think I need to add to:
With ppPres
While Not rs.EOF
With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
.Shapes(1).TextFrame.TextRange.Text = "Hi! Page " & rs.AbsolutePosition + 1
.SlideShowTransition.EntryEffect = ppEffectFade
With .Shapes(2).TextFrame.TextRange
.Text = CStr(rs.Fields("LastName").Value)
.Characters.Font.Color.RGB = RGB(255, 0, 255)
.Characters.Font.Shadow = True
I tried to replicate the last bit here by adding Shapes(3) and the new field name, but I must be doing something wrong because Access throws up an error message.
If anybody can give me some pointers I would be grateful. Also, if anyone can advise how I can determine the position of the fields on the slide I would be grateful (they seem to be centering at the moment).
The link for the full code is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/officeaccess2powerpoint.asp
Many thanks,
Adam.
Hi –
Situation: I pay all of my recurring bills (mortgage, insurance, utilities, medical, credit cards, etc..) via internet accounts. A dubious decision on my part which was driven home when my ISP (the only game in town out here in the woods)...reassigned their remote servers without informing their help desk, and failed to calibrate the new settings to equal the previous settings. Result: I was without DSL access for six to eight weeks while the help desk folks repeatedly referred to their documentation and instructed me in establishing a new connection, which was an absolutely worthless effort. Why, do you ask, did you not request for a technician to be sent to yiour site? Cause, they (the help folks people) apparently receive bonuses for blocking on-site service calls.
Having said that, my 14 – 17 accounts have varying UserID/Password creation and input criteria. It’s quite easy to assign an HTML field which, when clicked, takes the user to the initial site. In some situations, I’m allowed to click a “Fill And Submit” button, which brings up and submits previously saved criteria.
In other cases, the user is required to submit both UserID and Password, and God help you if the information is case sensitive and you screw it up.
Bottom line: The UserID/Password has been captured for each and saved in a separate database. It’s thus possible—but a real pain—to refer back in each instance to ensure the correct data is being submitted. A Google on “Automate Password” shows that this problem has been previously addressed, but not in an Access environment.
My Question: Have any of you run into this, and is there an Access-related solution?
Thanks,
Bob
Good morning to everyone.
I am hoping that you will be able to help me with a table that I am working on. I have almost no experience with access, so I am useless when it comes to this.
I have attached a condensed version of the database that I am working on (removed everything except the one table that I am working on) so that you can see and edit what I am working on.
The requirements are simply this.
-If "Status" = 'new inquiry' and has not been update in 6 months, automatically changes to 'inactive' and "probability of order" automatically changes to 0%
-"Initial Inquiry Date" is set to the date the Inquiry was added to the table (but user has ability to change it manually)
-*bonus* If ship quarter has passed todays quarter then change "Status" to 'inactive'.
I hope I have attached enough for everyone to be able to help.
Thank you very much in advance, I really appreciate it. :)
I'm using a query that selects all customers from a Customer table who haven't made any payments for a particular week and then sends a letter to them.
I would like to use a command button on a form which the user can press and then view the letters for all the customers- they would only have to specify the week.
Is this possible?
Thanks
I have a report, which shows all unsold items for a particular auction date, and I have created a form using this report.
I'd like my sister in law, to be able to go through and easily select which items to relist, and which to return.
I've tried using a subform, but I've either not done it correctly, or its not quite the right tool for the job. I've tried listbox control but can't get it to work properly either.
Once she has gone through the list ticking one or the other, I'd like to be able to automate with a button "Relist" and a button "ReturnItem".
I'd like Items to be returned - to automitcally close item off so it no longer incurrs a weekly listing fee and change status to "ReturnToSeller" and the relist ones, to create a new entry line into the main item table, with replicated information, except it clears out the "lot number", as they enter that manually when the generate a new list for the next auction, my ID numbers remain current and unchanged but the tableID is automated to a new one which is my key number.
then on my invoice, I get for instance, 3 rows for one Item, which could have 3 different lot and ID numbers, and get a listing fee of £1.20 for each week you see.
How can I automate the actions to relist and Return to Seller?
I've got a database, that I'm trying to update for my sister in law.I've got 2 tables, Items and Customers.As I've been working with the id's they have used so far, these aren't currently automated.
for customers, they use their number in the auctions, to place bids etc, so I can't change them.But once I hand over the database, I want to automate the generate of Customer ID numbers from that point on, but not override the history.With the items, what I have done, as an item can be relisted up to 3 times, I created a LogID auto number which is also my key number.
Before I hand it over, I need to do a big update there with them, to pull in the latest customer and item information.But after that, I'd like the new items added, to be given a new automated number, but the logid still needs to be generated automatically as well, as the login allows no duplicates, whereas the itemID does allow duplicates.
Its the item ID that is used to generate an invoice and show how many times its been listed to pick up correct number of fee charges.Customers can be both buyers and sellers, and I had to allocate 2 different fields, and the buyerID just replicates the sellerId number. So the Seller number is the main number on the table, I didn't want 2 tables of buyers and sellers separately, its one table.
I have a very large query that includes order data for about 100 clients. There are two smaller queries that take data from this one (to group by a "job code" and by a "job category"). I'm looking for a way to automate the process of running the series of three queries and producing an output (table or report) that gives the relevant data I need. I want to run this process for all ~100 clients, so I don't want to manually edit each query and copy and paste the results every time, but I'm having trouble creating a macro that will accomplish this.
View 10 Replies View RelatedSorry for the long winded question but want to be sure i clarrify exactly what i'm after.
What I currently do with my database is each time we send an email or a letter or have a phone call with a contact it is logged in our correspondence database. This is achieved by creating a new record in the correspondence form by filling in the contactID of who the correspondence was made with and then filling out the rest of the details as per the correspondence fields below. This works fine at present but occasionally we do mass mailouts of information to say all the centres in a state. Instead of someone having to enter a correspondence record for each of the 400 centre contacts we have in a given state and repeat the date, time, and details information each time so that an individual record appears for each centre, I would like to automate this process.
What I envisage is something like the picture I’ve attached. Where the user could filter by either the state or maybe even select specific centres from a listbox (filter by state is the most important one) and then fill out the rest of the correspondence record and have Access automatically create an Individual record for each of those centres selected, in the Correspondence tbl (from research it sounds like I need some sort of a loop task performed?). I need these records to be added to the bottom of the existing correspondance table
Unfortunately I have no idea how I can achieve the actual creation of all these records. Creating the form and queries based on a form I am fine with.
My table structure is below
Tbl_Correspondance
ContactRefNum (PK)
CDate
CTime
StaffID
ContactID
CentreID
CouncilID
CompanyID
ContactMethod
Project
Details
FollowupReq
FollowupCompleted
ContactID,CentreID,CouncilID,CompanyID are all linked fields to their own tables.
I am a novice user so the simpler the solution the better. Any assistance you could give in this task would be appreciated.
Hi
Each month I use Access to import text files and export cleansed data to Excel.
The information contains (i.e. forecast data) that has 6 months of data. Each month, the data is rolled over to include the new month and delete the oldest month of data.
Is there a query(s) or module that can automate this process?
Would greatly appreciate any tips on this one!!
Lucas
I need to regularly use officelinks from within Access, but would like to make a command button on a form to automate the process for me, I have no idea where to start.
I have a template saved and a query built but am unsure of the code to automate this process.
I've had a search around but have been unable to find any examples of how to do this.
Is it possible? :confused:
We receive a daily csv file from another database and we are trying to automate this process as much as possible.
At the moment, the other system will extract any new records, save a file as importddmmyyyy.csv in a specified location e.g. c:importpendingimportddmmyyyy.csv and then make a call to open access giving access the file path as follows
masaccess.exe "C:mydatabase.mdb" /nostartup / Excl/ cmd Filepath="c:importpendingimport09082013.csv"
I have configured access to automatically open a form, "ImportForm" when it opens, and I have configured this form to automatically run an event procedure on loading "importdailyfile".
My problem lies in writing this procedure. Basically I need it to run the Transfer Text command (I have already create an import specification)
DoCmd.TransferText acImportDelim, "myimportspec", "table to append the data to", filepath, true
The problem I am facing is that as the filepath changes on a daily basis given the naming conventions (which i cannot change) and my knowledge of VBA s pretty basic, I don't know how to make the procedure pick up the filepath that the other system is providing in the call to open access.
I am having the strangest results with my automated mailmerge. Basically it does work, but not all the time. The basic idea is to allow the user to dynamically create a query that produces a result list which fills a temporary table. The use then selects a prebuilt merge template and merge is executed against the temp table. The merge template are of the .doc type, but sme have been converted to .docx; the .doc files tend to work most often, but all of the will eventually get a Table is locked message... However if I run in test mode with code breaks and manually step through the process it always works... here is the heart of the code ...
Err_Pos = 10
Code:
DoCmd.SetWarnings False
' if tmp tbl left over from last run kill it
DoCmd.RunSQL "Drop table Word_Merge_Tmp_TBL"
Err_Pos = 12
[Code].....
There are many error cases in the error catch routine. That I have managed to make Access stop hanging when word has a problem or the table is locked. But I can't get the table to be free consistantly and why does it always work when I manually step through the code.