I am still creating my first ever Access Database for our small business.
I would like to be able to use a table or form to update more than one table.
For example, by entering the name of a company in a form/table, it is automatically added into a table of companies (ie another table).
How do I do this?
Anyone, anyone??
Frustrated.
Deano T.
I'm working on an MS Access inventory database for something similiar to a convenience store shop. All items are bought by the employer, therefore I don't have to worry about suppliers, not any customers. I just want to keep a running total of what's on the shelf, what's in storage, and be able to update what has been sold daily.
I've been using the Northwind sample database and modifying it as I go, but whenever I change the quantity in the order details on an orders form, the count for that product does not change. Any idea how I can automatically update the numeric value of a field in one table (products) by entering a numeric value for a field in another table (orders).
Thanks
I'm fairly new to access and using access 2003.
I have a main table and 3 other lookup tables. i have created the form using the main table. there are about ten fields.
what i need is, when i select one field, another four fields should automatically update.
is this possible? if it is possible how do i go about doing it?
any help would be greatly appreciated.
thanks
dem125
This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.
I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.
How can I make the subform run a subroutine each time the parent form changes records?
I have a training database with multiple training codes. I would like to be able to update the training date for multiple codes. These trainings are normally completed at the same time and I want to be able to reduce data entry by auto updating the date of training for the employees for multiple codes automatically. How can I link certain codes together?
View 1 Replies View RelatedUsing A2010, for some reason, whenever I created a new Access Object and save it, the Navigation Pane doesn't automatically update to list the new object. Same thing for if I delete an object. This only started happening about halfway into the process of creating this database. For now, I've been compacting and repairing the database to "refresh" the file and then the Navigation pane updates.
View 4 Replies View RelatedI am working on a database at my workplace. We have a list of available IP adresses that can be used for printers, not all of which are in use currently.
I have a table for the printers at each branch, and that printer's IP address.
I have an IP addresses table that contains all available IPs, a domain name field, and a yes/no field entitled "In Use?"
I want my IP address table to check my printer table, and put 'yes' in the "In Use?" field if the IP address exists in both tables, or a 'no' in the "In Use?" field if the IP address is not currently assigned to a printer.
Can this be done, and if so, how?
Much appreciated,
~Mike
I am designing a form based on specific criteria from a table. I have one table "country/region" showing: Country, Region, and then primary numeric ID. I have another table called "contacts" with more info. What I am looking to do is create two combo boxes on the form, one for country and the other for region which will pull from the "country/region" table and feed into the "contacts" table". When the user writes in the specific country, the corresponding region in the combo box next to it should update automatically.
View 1 Replies View RelatedI'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
View 11 Replies View Relatedhow I can automatically change the Client settings either with a macro or code. Specifically for the three confirm actions (Record Changes, Document deletions and Action queries). I have a database that is distributed to end users in different offices around the world. The old way lazy way, was to go to each computer and set these settings to "off" manually, but this isn't possible. I want to be able to run delete queries and action queries in the back end without the end users knowing its happening and having to select yes all the time.
View 3 Replies View RelatedI have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
Hi, I have a problem, I have a table were I list networkservices and their logical ports and I have another table were I list IPaddresses used by different machines. I'm using a multivalued lookupfield to pick which services I'm using for each Ipaddress, and thats working fine, but now I want to make the ports which is listed in another column, to automatically show in it's own column in the IPaddresses table depending on which services I'm choosing for the different IPaddresses. Is this possible?, and if it is how can I make this happen?
View 1 Replies View RelatedI have a table which contains these fields
MAIN TABLE
Job_No - Looks up Job table
Source - I want this to be automatically filled from the record associated with the job number from the Job table
Test_Result - Looks up Result table PASS/FAIL etc
Technican - Looks up technican names table
JOB/SOURCE TABLE
Job_No 0001 0002 0003 0004 etc
Source Cust1 Cust2 Cust3 Cust1 etc
Note also that we also have different job numbers for the same customer
I know I can fill in a value on a form from a combo box looking up another table but dont know how to do this in the main table
Any ideas?
Paul
I have a table being filled everyday that contains the following:
ID
EntryDate
Ward_ID (linked to the Wards table)
Census
At present, the data encoder manually chooses the ward via drop-down list to identify the ward, and fill the census field. Since we have 20 wards, we find this exercise eating our precious time (since we have tons more of data to enter aside from the census). I'm wondering if there's a way to automatically list all the wards in the table in a specific date, so that the encoder would just proceed in filling the census per ward.
One idea thrown was to design the table like an excel sheet (each ward has its own column). Another idea was to make individual tables per ward and make a default value for the ward_id. However I think these two are not the right directions.
I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
View 4 Replies View RelatedHere's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.I have attached a screen shot (parts.jpg) showing the structure.I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.
You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system. I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.
Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......
I have two tables that are formatted identically....
Table1 = MasterTable
Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code:
DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields
I am trying to update a table with the value of a text box on the form where the table to update is as selected from a combo box on the form.I keep getting the following
Error message:
Run-time error 2465
Microsoft Access cant find the field & table_to_update & referred to in your expression..
But really can't see what I've done wrong. Have checked that the table_to_update string does contain the name of the table so guess it must be sql..
Code:
Private Sub Command91_Click()
Dim table_to_update, sql_string As String
table_to_update = Me.Combo49
Debug.Print table_to_update
sql_string = "UPDATE [" & table_to_update & "] SET [" & table_to_update & "].[Project] = """ & Text89.Value & """ WHERE [" & table_to_update & "].[ID] = " & Forms![T_entity]![" & table_to_update & "]![ID] & ""
db.Execute sql_string
End Sub
I have two tables, Table Products & Table Sold and I'm trying to aggregate the products table to reduce the total number of products and I want this update to happen with all tables that share a 1 to many relationship with the product table. (table sold is one of those).I have products apple, pear, bananna. I am now aggregating them to all just be called fruit. Problem is when I make this change in the product table I get this error:
"The changes you requested to the table were no successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."how to go about aggregating data in a table that has 1 to many relationships with many other tables. A
I've got my data organized like this:
[date 1] [goal 1] [percentage for goal 1]
[date 1] [goal 2] [percentage for goal 2]
[date 1] [goal 3] [percentage for goal 3]
(one record per separate goal)
But I really need to be able to change the structure of the table/query so that there is one record per date, like this:
[date 1] [goal 1] [percentage for goal 1] [goal 2] [percentage for goal 2] [goal 3] [percentage for goal 3]
Is there any way to run a query or some other function that will reorganize the data in this way? or would I have to redesign the whole database?
Thanks
Shan
I am try to build a small invoicing interface in access 2007. I have designed a purchase invoice form with a table purchase_detail. When I receive products from supplier, products have a specific batch # on its cover.
I entered products in purchase invoice with these batch #. Now when I sale these items through sale invoice form, I need to get batch # automatically in batch # box from purchase_detail.