I'm trying to develop a simple company price list. I have created a suppliers products table. But I want to use the suppliers products to make up our coded goods (A Bill of Quantities simply). But I'm caught in a certain area that I dont have any knowledge on.
I want to create a form / subform whereby I can create my company codes, add up the cost price and put mark on it.
I have attached a image of what I'm trying to do in excel. In excel all I do is enter the code and the descrition and price comes up in the cells automatically. I just enter the quantity I require and total (sum) it all up.
In the subform itself. I can't get the the rest of the text boxes to automatically update once I have entered the code in the combo box. I rekon I have to enter a event procedure. Can some
one help
I am trying to compare 2 tables in a mdb. One table is a linked xls file. Lets call it table A. The other is a standard table. Lets call it table B.
Table A is the "old" data that may need updated. Table B is the brand new data that will always be correct.
I want to find the differences in table B from table A. Keying off one field (ASSET_TTAG and T_TAG) if any of the attributes (8 attributes) of a record in table B are different from that matching record in table A then that record needs to be updated.
Also, if a record in table B is not in table A that will need updated also.
All of these discrepancies need to be displayed in a query along with all the attributes.
LCAMdump = Table A, SCANDATA_tbl = table B
SELECT SCANDATA_tbl.ASSET_TTAG, SCANDATA_tbl.BUILDING_NAME, SCANDATA_tbl.FLOOR, SCANDATA_tbl.DESK, SCANDATA_tbl.COLUMN, SCANDATA_tbl.FNAME, SCANDATA_tbl.LNAME, SCANDATA_tbl.SSO FROM SCANDATA_tbl LEFT OUTER JOIN LCAMdump ON SCANDATA_tbl.ASSET_TTAG = LCAMdump.T_TAG;
That is what i have so far. But it is returning a test record that i know is the same in both therefor should not be listed. :confused:
SELECT SCANDATA_tbl.ASSET_TTAG, SCANDATA_tbl.BUILDING_NAME, SCANDATA_tbl.FLOOR, SCANDATA_tbl.DESK, SCANDATA_tbl.COLUMN, SCANDATA_tbl.FNAME, SCANDATA_tbl.LNAME, SCANDATA_tbl.SSO FROM SCANDATA_tbl INNER JOIN LCAMdump ON SCANDATA_tbl.ASSET_TTAG = LCAMdump.T_TAG WHERE ((([SCANDATA_tbl]![USER _ID])<>[LCAMdump]![USER_LOGIN])) OR (((SCANDATA_tbl.BUILDING_NAME)<>[LCAMdump]![BUILDING])) OR (((SCANDATA_tbl.FLOOR)<>[LCAMdump]![FLOOR])) OR (((SCANDATA_tbl.DESK)<>[LCAMdump]![DESK])) OR (((SCANDATA_tbl.COLUMN)<>[LCAMdump]![COLUMN])) OR (((SCANDATA_tbl.FNAME)<>[LCAMdump]![USER_FIRST])) OR (((SCANDATA_tbl.LNAME)<>[LCAMdump]![USER_LAST])) OR (((SCANDATA_tbl.SSO)<>[LCAMdump]![LOGIN_SSO]));
I have also tried this but it is not quite what i am looking for. :( Not sure how hard this is, at first it did not seem like a difficult query but i am not getting anywhere fast.
Hi, I'm a student, I need an answer to what I suspect is a very basic question for experts or experienced Access users. I'm creating a basic booking system for a real-life client, a professional singer. I have a table with each gig she takes, with details of how much she will earn, and the date of the gig.
I want to be able to show how much she earns every month, so basically adding up all the booking fees up for each month. I'm pretty sure it's a report made from a query - with the query somehow separating fees earned from month to month. Let me know if you have any ideas...
I am working on a massave aplication that has been running in Access 2000, but recently several of the file sharing users have installed Access 2003 because of the limited availability of Access 2000. All the users are using the same file off the server.
The problem we are having is that when we reference a subform in the "[Forms]![FormName]![SubformName]![FeildName]" Access 2003 does not recognize it and returns an error. I have found that if I will modify it to "[Forms]![FormName]![SubformName].[Form]![FeildName]" it is recognized in both 2000 and 2003.
To try and change every instance of a subform reference will take forever and I am garuteed to overlook something. We reference subforms all over our program, missing any one of them would be a disaster. Before I went to the tedious task of looking through everything I just wanted to throw the situation out there and see if any of you had any great ideas on how to get it fixed efficiently. I would apreciate any ideas.
I am having problem linking data in a form to a subform.
What I have is a form that contains the name of a project and the description of the project. That form then includes a subform that lists the various progress reports for that project in a tabular form.
I have a database to keep track of the link between the project and the various progress reports that it can contain. My problem is when using the Form/Subform to create new progress reports, it is not creating the necessary values in the table to link the project and the new progress report together. Any help would be appreciated!
Here's a screenshot to give everyone a better idea:
I have a database that currently has security in place so that users input their own usernames and passwords to access the database. The users have recently been added to a windows 2000 server and I want to be able to use their windows 2000 server logon credentials to provide them with automatic access to the database. Is there anyone out there who knows how to set this up? It is a rather urgent request if you could get back to me either on this forum or via email
I am opening and closing a series of Excel 2000 Workbooks using Access 2000 VBA and want this sequence to be able to complete without any human intervention.
However, there are 2 instances when this stops and waits for a human option to be selected:
1. When the spreadsheet is password protected 2. When the spreadsheet has automatic links I get the message:
"The Workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook?"
How can I code it so that in situation 1 it skips this file and in situation 2 it automatically defaults to do not update?
If I have a report in MS Access 2000 generated based on the criteria selected of a project with work order "9999" with the labor costs, materials costs and the Totals of each crew1, crew2,... and I would like to have those expenditures populated in corresponding cel in Excel for each crew, HOW would I do it?
To think it out loud, could I create a button on a form, so when I select the criteria for the work order, and when I click the button, it should refresh/update the Exel file with the new data...?? How do I write VBA code for that...?
I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend.
I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified frontend.
This is when the problems started; We had two different sets of forms for accessing one of our databases systems - the log system, one is the original dynaset based form, and the other is a newer set which uses snapshot views and preforms updates via queries. Nobody uses the old dyanset system apart from my boss and one of the administrative team as they have things on that window which they need to see. About 30 minutes into the release of the new database the system frooze up on my bosses computer and nobody could create a new log (the server was timing out). I assumed this had something to do with the old dynaset's creating locks on the table.
I offlined the database and kicked everybody out of the front end, turned it on again and tied again, this time banning everybody from the dynaset system. Within 10 minutes another computer frooze up, again with a timeout on the insert query. I discovered that after you had added a new log to the database it would timeout on all subquent additions (something it hadn't done during testing) . Further investigatiion showed it was the snapshot list window causing the error, so coded the add log window to close the list before preforming the insert query and then reopen it afterwards. This allowed my machine to make multible additions without flaw. So I released a new client to everybody. 15 minutes later it was timing out again, but this time there you could not even make one new record.
I checked for locks on the table though the server management table and couldn't find any for the Log table. I have restarted the SQL server box and with no avil. So I reverted our backend to the access mdb file and told people to use the old client.
I am at a complete lose to why this is happening, if anybody has had any expericences like this or knows the cause please tell me.
Some information on the database in question.
It was made as an access 2 database all intergrated into one file, then it was seperated into two files (frontend and backend). Upgraded to Access 97, then to 2000 before this final update to MSSQL 2000.
The log system has two main tables. The first is the log title / info table which links (one to many) to a log entry table. This problem only occurs on the main log table and does not appear to be reoccurring anywhere else within the database. The main log table has just under 18,000 rows in it.
Hi all. Had a quick peruse and this place seems really helpful, lets hope you can help me out here.
I have a project to do which roughly described is engineers doing jobs on aircraft. Three main tables:
Engineer Booking Job
The job table denotes the work required on an aircraft, with a unique barcode for the "job card". The Engineer table contains information about our engineers, with a unique ID.
Now here's my problem. The booking table needs to store information from both of these tables, but store it seperately. So I need to take some information from Engineer (ID, name, trade etc) and some from the Job table (barcode, description etc) and store it again in the "booking" table along with a few other "new" fields unique to the booking table. When this "booking" is made, it must record the start time, furthermore when the record is returned to later and closed, the duration of it being "open" must be worked out (total job time).
Ideally I'd like to create a form whereby I drop down a combo box and select an engineer (or type and autocomplete) it fills in the rest of his details, I then drop down another box and select a barcode (or again autocomplete) and that completes the rest of the job details. I then type in the new fields I also need, and this all gets stored in the "booking" table.
Any help greatly appreciated :)
I'm working with A2K3 for clarification, and can post/email my current database if required.
I downloaded a database of historical baseball stats recently and having been playing around with Access for a bit although frankly, I haven't been able to learn or do much. Anyways, here's my question:
I have one table that is a master list of all the players. The two main things I'm worried about are the playerID and the birthyear (year of birth). In another table I have batting stats from the players from each year they played. However, it is missing the year of birth which I need to calculate the age in that year. So, I need to get the birthyear from the master table and put it into the batting table next to the corresponding ID. Most players played for multiple years so then for everytime a playerID occurs in the batter table, I have to insert the corresponding birthyear from the master column. Hope that makes sense.
As I said, I'm completely new to this kinda stuff so any help or tips would be greatly appreciated.
I have a simple table "Line" with Columns Key, OrderNumb, Panels, Gates, Posts, Color, Memo, Comp. A user enters the information into the powder.asp page. It is then added to the database. The Comp Column is populated NO by a hidden input type. The data is then displayed on a page in a table and the last column has a submit button. When the user Presses that button I would like it to update that record in the database to change Comp to YES. Some code snipits are below. The first is where the data is presented and where the submit button is. It then will go to the next snippet that hypotheticly will change just the Comp on that record to YES. I can't get it to work. Thanks Code:<form method="POST" action="powder_comp.asp"> <tr> <td valign="top" align="left"><%= rs("OrderNumb") %></font></td> <td valign="top" align="left"><%= rs("Panels") %></font></td> <td valign="top" align="left"><%= rs("Gates") %></font></td> <td valign="top" align="left"><%= rs("Posts") %></font></td> <td valign="top" align="left"><%= rs("Color") %></font></td> <td valign="top" align="left"><%= rs("Memo") %></font></td><td valign="top" align="left"><input type=hidden name="OrderNumb" value="<%=rs("OrderNumb")%>"><input type="submit" value="submit"></form></font></td> Code:<%OrderNumb=request.form("OrderNumb")accessdb="Powder" cn="DRIVER={Microsoft Access Driver (*.mdb)};"cn=cn & "DBQ=" & server.mappath(accessdb)Set rs = Server.CreateObject("ADODB.Recordset")sql = "SELECT * FROM Line Where OrderNumb=OrderNumb ;"rs.CursorType = 2rs.LockType = 3rs.Open sql, cnrs.Updatesql = "UPDATE Line Set Comp=Yes"rs.CloseSet rs = NothingSet cn = Nothing%><META HTTP-EQUIV="Refresh" CONTENT="0; URL=powder.asp"> I'm still very new to this so please go easy on me.
Ive come here to find out how to start making a date base from scratch,
I have had to change my life dramaticly over the last two years taking up new hobbies and interests, to try and keep my mind active and out of depression. My main interest is mine making and I want to create a megga database so that I can monitor recipies and ingrediants, against the wine cellar stocks.
It will need to be quite a comprehencive database with two or 3 elements working indevidually but also inconguntion with each other.
Sections I need to create inclue
Wine Celler, stock control and drink ability. Some wines are not drinkable or 12 months but will taste well improved after 3 years storage.
Recipie collection, I want to keep a data base of collectied recipies and all to do thith them but want recipies to link to Wine cellor stock control.
Can anyone please help me, I know it is a lot to ask but really dont know where to start.:confused::confused::confused:
Ive come here to find out how to start making a date base from scratch,
I have had to change my life dramaticly over the last two years taking up new hobbies and interests, to try and keep my mind active and out of depression. My main interest is mine making and I want to create a megga database so that I can monitor recipies and ingrediants, against the wine cellar stocks.
It will need to be quite a comprehencive database with two or 3 elements working indevidually but also inconguntion with each other.
Sections I need to create inclue
Wine Celler, stock control and drink ability. Some wines are not drinkable or 12 months but will taste well improved after 3 years storage.
Recipie collection, I want to keep a data base of collectied recipies and all to do thith them but want recipies to link to Wine cellor stock control.
Can anyone please help me, I know it is a lot to ask but really dont know where to start.:confused::confused::confused:
We are a small Library just opened in our community, and the first one in our local area.
We are putting together an access query that will help aleviate the problems when our main library systems go down.
The database is a library records db. I want the query to ask the user for an author and a site and then to list all the records that match that query. Ideally i would like the user to be able to select a number of sites (from a possible 5), perhpas from a drop down type menu or checkboxes, type in the Author and then click on a button to perform the query.
Any help would be gratly appreciated - we are all beginners with access here.
Do you need to see what I have done so far? if so I could upload it for you.
We are a small Library just opened in our community, and the first one in our local area.
We are putting together an access query that will help aleviate the problems when our main library systems go down.
The database is a library records db. I want the query to ask the user for an author and a site and then to list all the records that match that query. Ideally i would like the user to be able to select a number of sites (from a possible 5), perhpas from a drop down type menu or checkboxes, type in the Author and then click on a button to perform the query.
Any help would be gratly appreciated - we are all beginners with access here.
Do you need to see what I have done so far? if so I could upload it for you.
I have two database applications and they are: - the (A) application is for administration use. - the (B) application is for normal users use.
the idea is that: I made the (A) application for administrators who have full control over the database objects (tables, forms, queries, and so on ...).
the (B) application I have created for normal users who will have only to use forms to insert some data and display data only.
but the two applications has a respective table called "vacation request" table. where I linked them, so the both administrators and users can share the data.
The real question is that: How can I prevent the users from seeing the database objects in their application. I used the database options which have helped me in hidding the database objectives when the users open the application, but unfortunately they managed to access to the database objects by pressing the special keys.
I would like to have an access to the (B) application when I want to make some modifications to the forms and then lock it from users where they only have to use the forms for requesting vacations and view the vacations.
I upgraded a 2000 db to 2003 recently. When I ran the function below it gave me an error on the line in green. Any thoughts? Thanks. Const FIRSTROW = 13
Dim wsp As Workspace, dbv As DAO.Database, tblResolve As DAO.Recordset Dim tblVchs As DAO.Recordset, tblImpTmp As DAO.Recordset Dim ObjXLApp As New Excel.Application Dim FileToOpen As String, WhereCriteria As String, ImportTemp As String, tmpVch As String Dim TotalDupes As Integer, TotalGood As Integer, TotalRejects As Integer Dim TotalBlank As Integer, R As Integer, C As Integer Dim BadSheet As Boolean, ImpFail As Boolean Dim tmp, tmpType, tmpCtr, tmpRsn
If Forms![Import Block Vouchers]![Import Program] = "0" Then MsgBox ("You must select a Program to load.") ObjXLApp.Quit 'Exit from MS Excel Exit Function End If
I've created a database in access 2003 with the default file format being Access 2000. Everything works fine on my computer and many others, all of whom are running different versions of access (2000, 2002 & 2003).
I have not had any problems with this database until recently and in the last few weeks we have had 2 users, both of them being Access 2000 users who have had the same problem.
The problem they are having is that when they click on any of the switchboard items, the buttons depress an then come back up again when the cursor is moved away, but nothing else happens - no action, no error message - nothing!!! Its the same for all of the buttons on the switchboard.
Does anyone know what is causing this? I have another computer with Access 2000 on it and this works fine, as it does on several other computers runiing Access 2000, I just cannot understand why it is not working on these 2 particular machine.......
I have recently installed access 2003 w/access 97 installed both are on my hard drive. When I create a new db in access 2003 it saves it in an access 2000 format. Does any one know why it does this?
We have two computers in a work group. On computer1 I have the back-end and front-end database files in the shared folder and 2 users out of 4 can access the database no problem. When the other users try to access the database they receive the error message "Microsoft Access can't Find The Database 'C:Documents and SettingsAll UsersDocumentsdb.mdb' Make sure you entered the correct path and filename" On computer2 I have the front-end database files in the shared folder and pointing to the same back-end database on computer1. Again 2 users out of 4 can access the database no problem. When the other users try to access the database they receive the same error described above.
I expect that this is some sort of windows permissions issue? There are some clues to this fact .. The two users that can't access the database are limited accounts, but I tried making them administrators and this had no affect. Their shared documents folder does not have a hand underneath it?
Any help on this appreciated as it's really starting to annoy me now!
A few years ago I’ve made an access database on access 2000. The problem is that now I’ve installed the office 2003, and I can’t make the database work properly.
At first says that are missing those files: “tshoot.ocx”, “msioff9.ocx” and “msowc.dll”; so, I’ve copy them from another computer using office 2000 and now only complains about “msioff9.ocx” saying that my database contains a reference incomplete to it.
Is there no other way to convert my database do work properly with access 2003? What can I do?
The company I am consulting with uses Access 2000 and has no intentions of upgrading. I currently do not own Access on my laptop.
I was planning to purchase Access 2003 for my laptop but am concerned that files I create in 2003 might not be "editable" in 2000 even if I choose the 2000 format when creating new databases.
I'm just wondering if anyone has tried creating a file in 2003 (using the 2000 format - as I'm told you can choose what format you want to use) and then edited it in 2000. I want my client to be able to edit the files in 2000 once I've moved on. (I can get 2003 for about the same price as 2000 - so my preference would be to get 2003.) The other obvious reason for going for 2003 is that I could run into the reverse challenge if I own 2000 and my next client uses 2003. At that point, I'd be forced to upgrade to 2003.
My databases are not complex - but do include some customized macros.
