I'm working on a database that is quite large. I foreign key that connect all the table is project_id. When I start a new project there a certain table that will not be used for quite some time. I would like them updated when I start the project with the same autonumber that is created for the project table.
There is an input section to add to the project and a view section to navigate through to view all sections of the project ir budget, financing, schedule. The navigate function wont work properly unless I have all project_id's updated.
Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
I'm supporting some Access/VBA code which includes the following behavior:
When an update is made in a (linked) child form, the current date/time is written to a textbox on the main form via
Me.Parent!txtMod = Now
Likewise, the BeforeUpdate event of the main form updates the same field (and then does a bunch of form validity checks, possibly followed by a Save).
When we upgraded to Access 2003 from Access 2000, a problem erupted:
When the user returns to the main form (by clicking on any control outside the subform), the following message is issued: "This record has been changed by another user since you started editing it (etc.)..."
At this point the user must choose between "Save Record", "Copy to Clipboard" and "Drop Changes".
Conceptually, I see why this is happening (though I wonder why Access 2000 didn't flag it). The subform is the "other user". When the main form is returned to, it is "Dirty" (unsaved update), so the main form's BeforeUpdate event fires, and attempts to update the field again (Me.txtMod = Now). Since there is a pending change to the field, an error dialog pops up.
As a quick fix, I replaced the code in the subform with a flag ("Hey, I've been updated!"), which the main form checks on the Exit event of the subform control. But that seems a horrible solution. Surely there's a better way?
Notes:
1. It would be preferable to have the timestamp updated right away by the subform, though having it updated when the subform loses focus is better than nothing.
2. Since a subform is a control from the main form's perspective, I'm surprised that a change to that control doesn't render the main form "Dirty". I guess from a database standpoint it makes sense (the form's table isn't getting updated at all; just the subform's). But then, how does the main form know that the subform has been updated?
3. I tried catching the error via Form_Error. I can make the dialog not appear via "Response = acDataErrContinue", but unfortunately it defaults to dropping the changes (old and new), rather than saving the new.
I have a combo box that display three columns but I want the middle column to be the value that displays on the combo box after the selection not the first column. How do I achieve this ?
Project Name Project Number Project Description
I want the Project number to be displayed in the box after selection.
I am working on a project that requires data transfer using TCPIP sockets. I am using a 3rd party Library (Ostrosoft) that handles the wsock32.dll api calls.
The project calls for the creation of a header that logs into a user account.
The first part of the header is a marker, with a value of 4,275,878,552. It is to be supplied in a Binary format of length 4.
My quesion is "How do I create this marker".
What I have tried so far is a string variable - strMarker thus:
I am creating a database that will need to track contractors to projects and subcontractors to parent contractor and project. I would like to make it possible to have unlimited tiers of subs on a project and track the "tree" or "orgchart" if you will. The main purpose of the database is to track labor compliance violations to resolution. The violation parts are easy. How do I set up my tables so that I can have unlimited tier levels of subs. I will need a report that generates a form letter to the violating (sub)contractor and then CC that letter to each of the contractors up the chain. Thanks in advance. Rich
I’ll do the best I can to explain what I would like my program to do, maybe you can give me a little bit more guidance.
Alright – I have a database with the following 3 tables
Assets – Asset ID (PK) Inventory – no PK Preventative Maintenance – no PK
I created corresponding forms for each table, each with the same name.
Here is what I can do as of now:
Assets form - I am able to enter the asset #, description, serial #, hours between PM (preventative maintenance), and starting hours (same as an odometer) which I have set to 0.
Inventory form – Able to enter Part#, Asset ID (this allows the user to distinguish which asset this part was made for), Part Type, Part Description, Vendor, Location, Quantity, Individual Price, Total Price, Tax (Of the currency fields I just mentioned, I have it setup calculate the Total Price by multiplying the quantity * individual price. I also set it up to calculate 7% sales tax within the form)
Preventative Maintenance – The first field is PM# (auto number), Asset ID (distinguish which asset is being worked on), Date Checked In, Time In, Time Out, Current Hours, PM Performed, Part #, Quantity Used.
Well hopefully I haven’t just thrown a lot of information your way. So now that I have given you what my project can do, let me try and explain what I want it to do. And please keep in mind that I have been trying every which way I can to create the proper relationships, or foreign and primary keys, but that concept still passes by me at times.
I want to be able to know that if I have 10 tires for asset 1500, and I perform a PM on Asset 1500 and use 2 tires, that I will have 8 remaining. Maybe I just am missing the simple solution here, but I have been trying for days. Also, that value 8 should be the new on hand value within the inventory. I need it to update.
If I have 10 tires with part # TIR1500, and then I receive 2 more a few days later, how can I get the quantities to correspond? It seems as though I was able to work this out through queries to get the correct info, but due to the relationships I tried setting up, when I got to the PM and the quantity used, it threw my totals off.
1)Projects table: - Project ID - Number field (Primary Key) - Project Name - Location - (other fields)
2)Working details: (this table has a form based on it and it's used to enter data by users) - Project ID (this field has a lookup for the same field in the first table) - Project Name (this field has a lookup for the same field in the first table) - (other fields)
My problem is : in the second table, Project Name field has "Number" data type because the relationship between the table is on the Project ID which is number field. I want the Project Name to be "text" data type in the second table.
I have tables and forms setup but I have 3 general questions on how to finish the project.
1. Should I create a folder where all the files reside for instance, create a phone log folder, put the DB file into the folder and export XML's into the same folder?
2. How do I get the form to launch as a self-contained form without the Access application in the background?
3. How can I get this form and table to automatically update an XML spreadsheet whenever records are added?
4. How can I get the form to sort all records according to time logged? The time will be entered in manually and we need all the records to sort automatically by time in ascending order.
I have a master list of projects, with project reference number, project name, and nature of project.
I have also got a form for individuals to fill in details of project events, with date, time, name, and two or three other fields - also included are project reference and name. I'd like the name field to be auto filled when the user selects the project reference from a combo box; I think? (the list only shows open projects).
I'd did something similar some years ago in Access 2003 (I think) but cannot figure it out in the version I'm currently using 2010.
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
In access (2003) I am setting up a table with Employee # (Pri. Key), senority numbers..and so on. However I cannot figure out how to reset the senority numbers when one leaves. For example Bob is #235 senority and leaves (By retiring, quits or gets fired)..well Mary was #236 and now should be #235 (because Bob is no longer at the company)..and so on with everyone else below Bob getting their Senority number changed by one.> Do you have any ideas..I tried autonumber, but it will not do that.
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. The company does most of its business via a network of vehicle dealers around the country. If someone comes in to buy a motorcycle, boat, or recreational vehicle at a dealership they need insurance to take it home, and our dealers send the quotes to us.
The dealers, in turn, receive payment from us each month for their efforts. Some are paid a % commission on the premium, some are paid for each quote they send regardless of whether the policy actually sells or not, and some are paid a set amount per sold policy. (Yes, that is relevant information!)
We already have reports that tally the amounts due each dealer based on their payment scheme, but last month our bookkeeper had to write about 650 checks manually because the check writing is not automated. She'd look at the report, and then enter name, address, and amount (in digits and words) into Quick Books and print the checks from there, a horribly tedious process. I've been asked to print the checks from Access. Basically one click would print all 650 checks.
I've opted to use a Make Table query to move the commissioned dealers amounts to a single location, and then to run two append queries to add the records from those paid per quote and those paid per policy. At the end of the day, one table contains all the information necessary to print the checks...except one.
The check number.
I need a way to sequentially number each record in the new table with a user generated starting point, the first check number.
By the way, the check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.
I think the best way to accomplish this is from the report itself. I've created a blank field on each record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once. That way, should print ever be interrupted, it will be easy to take up where we left off.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
i have 4 tables in access and i need to be able to add information in one table and it to appear in the other 3 without me having to go in to each individual table and enter it. Is this possible?
Good morning, I'm a newbie....and I inherited this db.
I have an existing table. I need to add two rows of information. When I add the rows, they are empty. I have tried running a query with all the fields from the table plus the two rows I need, but I loose 90+ records. What am I doing wrong???
I have a button on a form that queries my table for observations that meet a certain set of conditions, and then updates a field to mark these observations. But I keep getting the error message 'Compile error: Method or data member not found.' I have used the code successfully in a previous database and cannot figure out why it won't work here. Thanks for any suggestions. Here's the code:
Private Sub OK_Click() Dim db As Database Dim rst As Recordset
Set db = CurrentDb Set rst = db.OpenRecordset("Appointment Letter JW Q")
With rst .MoveFirst Do While Not .EOF .Edit ![Status] = "08" ![DateMail] = Date .Update .MoveNext Loop End With End Sub
Need help and advice desperately, I am trying to update about 10 tables each time a visitor visits my site.. however there are often missing counts, meaning the total data in each table are not the same frequently They are supposed to be since all the tables get updated each time. Im using MS Access 2003. What could be the problem?
Below is an example of one of the function that updates one of the tables in my DB. ************************************************** ***** function GetIdRes(sName) 'Get ResID sUserID = Request("UserID") sSQL = "SELECT ResID, ResName, Total, UserID FROM Resolutions WHERE UserID = '"&sUserID&"' AND ResName = '" & sName & "'" rs.Open sSQL,,,adCmdTable if rs.eof then rs.AddNew rs("ResName") = sName rs("Total") = 0 rs("UserID") = sUserID end if rs("Total") = rs("Total") + 1 rs.update GetIdRes = rs("ResID") rs.close end function ************************************************** ****
I've got another 10 similar tables. The Column "Total" is often different among the tables.
I have one table (call it tblMax) which holds a number designating a maximum amount for items. I have a second table (call it tblItem) which holds the items current inventory, along with several other values about the item. Is there a way to reference fields from both of these tables on one form, AND have the record be able to update? I've used a query to bring them all to one form, but the record is not able to update using that method. The tblMax will be used for reference only, after the initial values are entered. I also tried leaving the form's Control Source blank and typing in Control Sources for each field, but was unable to get that to work. TIA.
I have a table called Primary Employees and another table called Secondary Employees. I also have a form where you can add new employees, which updates the Primary Employees table. What I would like to do is, after I add a new employee have the new data update both the Primary and Secondary table at the same time. These tables have the same information within them. These two tables are currently relational to one another, but I still have to go in and manual update the Secondary Employee table after I add a new employee. I have to have two databases because of other form requirements. Any help you could provide would be greatly appreciated.
Hi Im quite new to access, but Ive got a little database running thats contains balances of customers account over 30 days 60 days etc and logs querys on there accounts, Im given a spreadsheet each week which shows that the customer may have paid something to their account so I need to import these new balances into the table and update the record. Can anyone give me any pointers as how Id achieve this, as append query or import dont seem to fit what I need.
I have a front-end database with a linked (data) database. Problems arise when this goes onto other PC's on different directories and I get the error that it can't find the linked database. This is easily fixed by updating with the linked table manager, but as I am not sure which directories the database will be run from on other PC's, is there a solution that easily updates the linked tables? (the two database files will always be in the same folder). I realise that if I can identify a common directory that can be used on all potential PC's, then this problem can be avoided, but I can't guarantee this.
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
I will be updating a table in my database every week, via imports from a xls file. What I have going on now is a very inefficient way of updating. What I do is import into a table (x) and just keep adding to the table through the import wizard saying add to exsiting table. Then do a make-table query where I do "group-by" to get the unique ones and then run it to get the new table. I am sure there is an easier way to do this. Possibly, find duplicates in the import and then just append to the main table? Don't know how to do that one? Also I wanted to create a button that will ask for the xls file and then do the appending and clean up once the file has been asked for...any help please!
Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
