I have several tables that I would like to be seperate from the forms. Bottom line is that I want the program stuff to be seperate from the tables seperate as in two different files.
I have not messed around with this yet and am wondering how big of a pain this will be to do with the code that I have already written?
Any thoughts ideas etc.?
Hi All,
I am a novice at MS Access but I am learing new things every day.
I am using MS Access 2000 for this DB.
If you need to see the DB let me know and I will strip the sensative data out of it and post or send a copy. The DB is for a small retail sales store.
What I want to do is something like this::
If [Tables]![Sales Entry]![Item Tax] [= $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Non-Taxable] [Else[If [Tables]![Sales Entry]![Item Tax] [> $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Taxable]]] End If
I want to split up the Item Price field by checking to see if Tax was applied or not and copy the Item Price to the proper new fields in the query called "Taxable" and "Non-Taxable". Then in my Report I can sum each of the new fields by date.
Thanks
Ron
Sorry for this totally stupid question but my memory is just not what it once was.
I need to separate one field into two, in a table that was imported from excel
Basically it has a size and a unit in each field
Example:
24.67 sy
26.84 sf
Thanks in advance for jogging an old man's memory.
I want to do this in a query not code if possible I remember something about a formula you can enter into the update to box in a saved query.
How can I do this? There doesn't seem to be an member of Field called format. Why is this? I want to make the display on a Boolean field, in a Table made from a Make Table Query, to Yes/No, rather than default 0/-1.
View 7 Replies View RelatedWe have an access program that unlinks a table after certain functions, and relinks it in others. Is there a need for this? For one user, the table does not link, so we have to manually go over and link it for her everyday.
I am wondering why it was built to unlink and relink - shouldn't the link be "real time" and get up-to-date data?
I am also not sure why the TableDefs.RefreshLink is necessary (though it is not in our code - what situation would warrant refreshing the link?)
I am trying to design a database for the company i work for, and I am wondering what is the best program to use.
My database will be shared on a network, and many different users will be using it at once. Also, I need this database to allow vpn connections so that certain people can access the db from outside of the office. I am not sure if db programs would deny a vpn user access, it might not make a difference how people are accessing info, but i think its worhtwile to mention that.
I appreciate your help, even though you opinions may be just a little biased. but thanks none-the-less.
Hi all!
I have created a database with auto-archiving features. I have "working tables" that are populated when the product is moved to the correct process, in this case when it reaches the test department. When the product is moved out of the relevant process, the database runs several queries which archive the relevant data and deletes the records from the working table.
There are two working tables in the test department, one for test failures and one for rework activities. The question at hand is WHEN should I populate the working table for rework activities:
Option 1: Populate the table when the product moves to the relevant process. This will create and delete thousands of records from the working table every day. Using this method will cause my table to bloat to 50,000+ records that are constantly updated, appended, and deleted.
Option 2: Populate the table when (and IF) the product fails a test. This will keep the table to a much smaller size (around 2500 records) but requires the database to run several queries in the middle of the data entry process, significantly slowing my program.
So which is the faster/better process: Filtering a table of 50,000+ records or appending the working table when you need the data?
Hi,
I have a delimma and am not sure how to proceed. It falls under the "You want what? When?" category. Basically, I have created an Access program with several (13) related tables and numerous forms. This program mimics an interview we are using for research and has a lot of VBA coding behind it with quite a few skip patterns, value checking, etc. During the testing phase, I was asked to allow the user to be able to go back and check a response to any previous answer at any given point in time. I hear numerous clicks of people running away from this question. This one has me frustrated and not too happy. I am wondering if there is some way I can show a copy of any form and the data contained within it for the present ID? IF it is a copy then the data, I presume would be locked and the user would not be able to change it. Also, I would want to bring the user back to the point where they were prior to going backwards. Feel my pain yet? Of course as I ponder this I can envision that they are going to want to change a value, because maybe a respondent "changed their mind" about a previous question. But, I am sure they would not want to work their way back to where they were, but want to jump back (forward), all the while I need to insure that any skip patterns that may have changed due to a change in data are followed and if a question that was answered before is now skipped then that data needs to be cleaned. I am concerned that as I begin to meander through this issue that I will have "duplicate ID" issues because as I move backward, possibly through several forms and tables and then move forward again the DB will think I am adding two items rather than changing the one. I.e., it will think I have two identical IDs because it already has one from the original pass and as it moves forward and runs through the automated saves that the second pass would represent a duplicate ID, thus crashing the program.
I know this is a multi-pronged question, but I would like to hear some suggestions as to how others with more experience would handle this. I am grateful for your time in reading this and would be even more so for any responses.
Peace!
I have a bunch of files where I have to copy one piece of data from a field and paste it into a different field on many records...in sort copy once, paste many times. Currently I use Ctrl C for copy and Ctrl V for paste. I'd write a query to do this for me, but not every record is going to recieve the data.
I'm getting tired of having to press Ctrl V on the appropriate records. Is there way to program a function key to do the Ctrl V thing? That way, I'd only have to press one key as opposed to pressing two keys.
Any help offered is truly appreciated. Thanks!!
Following program i have written in access. what it does is from the query i have created picks up the sizes of doors. calculates hit1 by qty then it needs to put the value into another table alongside the correct sizes. the problem im facing is how do i update the value into another table. heres the listing.
Option Compare Database
Option Explicit
Dim Db As Database
Dim Rs1 As Recordset
Dim Rs2 As Recordset
Dim lf, rh, drw1, drw2, drw3, drw4 As String
Dim q1, q2, q3, q4, q5, q6, qty As Integer
Dim hit1, hit2, hit3, hit4, hit5, hit6 As Integer
Private Sub Command70_Click()
Dim rs1fieldcount As Integer
Dim counter As Integer
''Assign a mdb
''+++++++++++++++
Set Db = CurrentDb
counter = 0
Set Rs1 = Db.OpenRecordset("custdoorsize")
Set Rs2 = Db.OpenRecordset("cusdoorsbase", dbOpenDynaset)
rs1fieldcount = Rs1.Fields.Count
If Rs2.RecordCount = 0 Then
MsgBox "No record found in query"
Else
this is where the value will come from
Do Until Rs2.EOF
If Rs2.Fields!left_door_size <> Empty Then
lf = Rs2.Fields!left_door_size
hit1 = 1
q1 = hit1 * Rs2.Fields!qty
End If
If Rs2.Fields!Right_door_size <> Empty Then
rh = Rs2.Fields!Right_door_size
hit2 = 1
q2 = hit2 * Rs2.Fields!qty
End If
If Rs2.Fields!draw1_size <> Empty Then
drw1 = Rs2.Fields!draw1_size
hit1 = 1
q3 = hit3 * Rs2.Fields!qty
End If
If Rs2.Fields!draw2_size <> Empty Then
drw2 = Rs2.Fields!draw2_size
hit4 = 1
q4 = hit4 * Rs2.Fields!qty
End If
If Rs2.Fields!draw3_size <> Empty Then
drw3 = Rs2.Fields!draw3_size
hit5 = 1
q5 = hit5 * Rs2.Fields!qty
End If
If Rs2.Fields!draw4_size <> Empty Then
drw4 = Rs2.Fields!draw4_size
hit6 = 1
q6 = hit6 * Rs2.Fields!qty
End If
Do While counter <> rs1fieldcount
This is where it finds the correct sizes where it will display the qty value
If Rs1.Fields(counter).Name = lf Then
Rs1.Edit
Rs1.Fields(counter).Value = q1
End If
If Rs1.Fields(counter).Name = rh Then
Rs1.Fields(counter).Value = q2
End If
If Rs1.Fields(counter).Name = drw1 Then
Rs1.Fields(counter).Value = q3
End If
If Rs1.Fields(counter).Name = drw2 Then
Rs1.Fields(counter).Value = q4
End If
If Rs1.Fields(counter).Name = drw3 Then
Rs1.Fields(counter).Value = q5
End If
If Rs1.Fields(counter).Name = drw4 Then
Rs1.Fields(counter).Value = q6
End If
counter = counter + 1
Loop
If Rs2.EOF Then
Set Rs1 = Nothing
Set Rs2 = Nothing
Set Db = Nothing
MsgBox "Finished ....."
Exit Sub
Else
Rs2.MoveNext
End If
Loop
End If
End Sub
please help
I am using Microsoft Developer and wish to email the program to my customer. With Developer, there are tons of files and a few folders, not just one exe. I realize I must zip to send a exe, but the folders are a problem, since WinZip, which is what I have, will not accept the folders, only files. I could ship like that but it would be a mess on my customer's end. Any suggestion would be helpful.
The reason I want to send this way is just for speed. Of course I can copy to CD and send that way.
Sharon
I have a database made in MS Access. It has forms that allow the user to do certain things, such as add to the database, and so on. I only want the user to be able to run a program that will run the one, main form i've created it (as well as any other form that the main for lnks to). how do I do this?
View 14 Replies View RelatedHello. I have tried using the search function on the board but I cannot find an answer.
Is it possible to create an executable program to run the database?
I would like to have a computer that does not have Access installed, to be able to load and run the user interface that allows them to manipulate the database, as well as write to it. It doesn't matter if the tables are encrypted or not.
Thanks for your time.
EDIT: It appears that after writing this post, I realized I hadn't used the search term "executable." I found an answer here (http://www.access-programmers.co.uk/forums/showpost.php?p=586646&postcount=2).
hi,
whenever i key in some data into a form i will transfer the data into a table.
whenever i want to change some data in the form i will delete all the records in the table and re-add all the data from form into the table.
the abv method works. however, i find that it is not effiencent because it every time delete all the records and re-add in records.
how shall i code the program to make it only edit/delete the specific records only instead bulk delete and bulk add?
refer to (program.zip)
Private Sub cmdSaveFltQpi_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim f As Form
Dim i As Integer
Dim k As String
Set f = Forms!frmFltQpi
Set Db = CurrentDb()
Call DeleteRecords 'delete all the previous records
sql = "SELECT * FROM [tblFltQpiMonthly] Where tblFltYear = " & f.cboFltQpiYear
For i = 1 To 12 'transfer the data from form to table
Set rs = Db.OpenRecordset(sql)
If rs.RecordCount >= 0 Then
If IsNull(f("txtTotalJobs" & i) Or f("txtTotalRecurring" & i) Or f("txtTotalAirWC" & i) Or f("txtTotalAF" & i)) = False Then
rs.AddNew
rs![tblFltYear] = f.cboFltQpiYear
rs![tblFltMonth] = i
rs![tblFltTotalJobs] = f("txtTotalJobs" & i)
rs![tblFltTotalRecurring] = f("txtTotalRecurring" & i)
rs![tblFltTotalAirtestWC] = f("txtTotalAirWC" & i)
rs![tblFltTotalAirtestFailure] = f("txtTotalAF" & i)
rs.Update
End If
End If
Next i
Db.Close
End Sub
Private Sub DeleteRecords()
Dim sql As String
sql = "DELETE * FROM [tblFltQpiMonthly] Where tblFltYear = " & Me.cboFltQpiYear
DoCmd.SetWarnings False
DoCmd.RunSQL sql
End Sub
hi!
I'm doing a reminder program this program alerts you if you have things to do each day it has a code that I obtained on the internet wich retrieves the user name. My problem is that when I enter as a user a window shows up say my username and the number of things I have to do but I can't get it to count the number of things to do. I tried a query but it doesn't work (either it shows "#name" or it goes all blank)please help.
thanks in advance, newbiepr
I'm just wondering if anyone has made a very advanced budget management program using just access?? I tried to make one for myself but I just want it to advanced for my little knowledge of access. I would love to be able to keep track of all my expenses including everything bought from groceries to hair cuts. I would love to be able to take my receipt from walmart and enter everything bought and the price for each item. Then I would love to be able to at anytime search for soap and thing with soap in the Item name will be displayed with the location and the date and price. This way I can keep track not just what I spend on groceries but what I spend on mountain dew or what i spend on hamburger in a given time. This is just way to complicated for me! I hope someone can help me out here maybe give me a shell and let me try to fill in certain parts or if someone already has one that works kind of like this maybe i could just change a few things to the way i want it! I really appreciate this!!
Bass
I'm, pretty sure the answer to this is a resounding NO, but I thought I'd try it anyway.
I have an AutoID field that records the record number which I then use as a "CaseNo" in the database to track the progress through the system.
Is it possible to program this AutoId to include the year? That is CaseNo 1 would become CaseNo 2005/1.
It would also help to start at 1 again for the first case entered in 2006.
Thanks for any assistance.
Before I start on this seemingly endless journey of learning Access 2003, I thought it appropriate to ask the question;
Will this program do what I need it to do?
I apologise in advance if this is not the right place to ask the question, I am a complete beginner to Access 2003.
I want to make an electronic log book for the guys at work, there are 5 people of different trades who would need to keep the log.
A typical entry would be as follows
Mon 22/3/06 Alan Smith B Shift
Compressor house (heading)
No 1 compressor (plant)
Oil pump (component)
Filter cleaned (action taken)
Personel (heading)
John Jones (personel)
Phoned in sick at 15:00hrs, will phone tomorrow to update
Boiler house (heading)
No 4 boiler (plant)
Water vlave leak (problem)
Tightened gland (action taken)
There are many headings, probably hundreds of plant, thousands of components and actions taken
We would need to view events to particular plant or areas between certain dates, some personel would need to enter data, some would only view the data without being able to enter it, few would need to alter the data.
Is Access 2003 capable of this, should I start the journey?
regards
culedude
Hello all!
Please forgive me for being a COMPLETE novice at Access, but I am trying!
What I need to do, is have an ordered list, which has check boxes along side each item.
When the relevant items have been checked, I need to be able to save or export to CSV, with ONLY the items which have been checked!!
I need the list to be pulled from a seperate source (like an excel spreadsheet) so that it can be updated easily...
Can anyone help?
so...
Item 1 [X]
Item 2 [ ]
Item 3 [ ]
Item 4 [X]
Item 5 [ ]
Item 6 [X]
would produce a CSV file:
Item 1, Item 4, Item 6,
Simple enough?
Greetings:
I am looking for a program that will repair a MsAccess 2000 .mdb file. So far all I have been able to find is those that repair the tables, does anyone know of a program that will repair the entire file including the forms?
Thanks
Mickey
well i am trying to make a very simple program that would help me learn the capitals of each country. yes it is a weird thing to do but i just it might help.
anyway i started doing it using access and made a table with three columns primary key number, country and capital.
I started making a form and im tryin to make it so the program would randomly pick a country and i would have to choose from a list box the capital and i would have a command button which when i click will tell me if i am right or not and if i am not then it would say the right capital.
How should i do this?
p.s ino the basics of using the code builder
help much appreciated.
I believed that Access is a stable DB program. It looks like I may have to change this view. Yesterday, due to some fault in power supply (SMPS), my PC suddenly went off while I was working in my Access DB. My DB is a personal DB (single user). Today, after replacing the SMPS, when I opened my DB, Access gave a message that my DB is corrupted while it was being used last time. It asked to attempt repair with Yes/No button. I clicked the Yes button and it did nothing. In my third attempt, I was able to open my DB but I am worried whether it will work as before. Could the experts in this forum throw some light about Access DB' stability and measures to prevent corruption? Grateful for advice.
View 9 Replies View RelatedI made an Access DB program in my PC. I have 17 inches monitor and so I designed everything to fit in that screen. When it was deployed in the user's computer, it did not fit in his screen as he has only 14 inches monitor. The command buttons and other things were not visible and were out of view of the screen. I changed the screen resolution but it made the fonts look much smaller. I have used 12 pt fonts in my Prg. Is it possible to correct my forms so that it automatically fits in all sizes of screen?
View 2 Replies View RelatedI'm trying to run some VB code (in an Access module). The code executes for 10 seconds or so, and then Windows XP robs it of any CPU. I've tried (many times) using task manager to set the priority to high or real time, but still MSAccess shows as using only 2 or 3% of cpu.
I've got in intel core 2 due, running Windows XP SP2.
I've got a table with about 2 million rows. When I try to append the 2m into another table with an append query, or if I burn through the table with a do loop in VB using recordset.append and then recordset.update it takes forever (hours). What burns me up is that the CUP sits there near idle, varying from 2 to 4 pct usage.
Thanks for any ideas..
Steve
I would like to place a button on a form that maps to a program on a user's computer. For example, the button says PROGRAM and then the user clicks on the button and it opens an executable located at c:PROGRAMprogram.exe
I am assuming that I can do that with access, but how?
Thanks for any help!
I am an estimator for an industrial and commercial insulation contractor. I have compiled an enormous amount of data over the years but I am still figuring all of my estimates by hand.
For instance, I might need to figure that a 3 pipe will be insulated with 2 thick fiberglass pipe covering. This pipe is outside, so it will need to be jacketed with .016 thick aluminum secured every 4 with sheet metal screws.
The 90 and 45 degree elbows need to be insulated with preformed fiberglass elbows and jacketed with preformed aluminum elbow covers. Every 15 feet there will be a pipe support. This line is a schedule 40 steel pipe and it has valves on it and each valve has a flange at both ends so the insulation for the valves will have to be big enough to fit over the flanges. Where the 3 pipe might require 2 of insulation, a 1 pipe might only require 1-1/2 of insulation. Each size and thickness has its own production figures. This is the same with prices for the types of insulation and jacketing.
I have compiled all of this information into tables. Where before I had to get out my books and look each up, no I go to my tables, find the amount per day, and calculated that by the number of items. I can look up each type of insulation for each type of fitting or valve and I can calculate how much time all of this will take. I can also look up to see what size covering will fit on a 3 flanged valve. I have another table that will tell me how many square feet of jacketing the pipe, fittings and valves will require. I have prices for all of the associated materials.
The same happens when I try to figure equipment or ductwork. We deal with many kinds of insulation and just as many jacketing and finishes.
I bought Access 2007 and have brought my tables in. I want to have a spreadsheet or form that will allow me to link this information together and make the proper calculations. I want to be able to change the pipe sizes or materials from a list and have the calculations adjusted accordingly.
Again, I have all of the figures; I just don't know exactly how to go about getting all of this linked together in one place. I have only dabbled in computer code writing and I really don't know very much about it. That is why I picked Access because I figured it would have about everything in it that I need. I know the layout of the form I need, I just dont know where to go from there.
Any guidance would be appreciated.