In my access database I have a "products" table which contains three concerning columns (pID, price, listprice) This table contains products from hundreds of different suppliers. Periodically, each supplier will send an updated price list containing the three pieces of information listed above.
My question is, is there any way to automate the following three processes:
1) if the product ID already exists. update to the new price
2) if the product ID doesn't exist add the new product and price
3) if a product has been discontinued, delete the record (this part not a necessity, but would be nice)
Thank you in advance for any help, if you have any other questions please ask
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet. They have a common row ie say account number and other common fields that need to be updated.
I 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 have a table for storing details of share prices relating to specific certificate numbers, so only the £ value and the value date changes when we update (done manually at present).
The updates for different companies are done at different times, hence I cannot just delete and import new data, it needs to be an update to a value from an excel sheet (the excell sheet is downloaded from the web provider in question).
I had thought of using "get external data" to create a new or ad to a new table, then an update query to update the main table from the new one, but again cannot seem to get it to work on the specific certificate numbers.
As you can see I have little knowledge on code etc, and have so far only used macros to automate the application we use, can anyone please help???
I currently have a database that is missing various pieces of information, we are getting a temp in to complete this. I don't want the temp adding data directly into Access so I have exported the table that needs updating into Excel.
How can I then import the new data without altering current data.
I know this is no problem for new records, but if I have a current record with a few fields of information missing how can I just import information into the blank fields. This can't be a manual process as there are hundreds of records.
I have an excel spreadsheet that uses a data connection to a website to download text. I have the data connection properties to refresh upon opening the file. If i double click the file from windows, it updates. Although using the code below doesn't work. When i've added the code ObjXL.Save before closing it, it usually saves a file with no data and no cells. I'm eventually going to switch visible to false.
Private Sub btnDownLoadSettle_Click() Dim XLapp As New Excel.Application Dim ObjXL As Excel.Workbook Set ObjXL = XLapp.Workbooks.Open("C:...Settlements.xlsx") ObjXL.Application.Visible = True ObjXL.Windows(1).Visible = True Sleep (5000) ObjXL.Close XLapp.Quit End Sub
I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.
I also have code inside Excel that will update chart data, it works fine.
Then I have code in Access that calls the code in Excel to update the data.
The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.
Here is the code that will update the Excel Data
Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet) ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String
Access Database 2010 is used to capture progress on accounts. We are able to perform remedies on multiple accounts in the field and would like to update the records in Access all at once (by batch) rather than one-by-one.
I would like to export specific records from Access into Excel, make the updates to the records in Excel, then import the changes back into Access. I am looking for the updated Excel spreadsheet to overwrite the existing data in Access for that particular record.
Here'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.
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 have used a make table query to create a table with text values. I need to convert these values to numerical data, for instance "Male" should become 1 and "Female" should become 0. I tried doing this in an update query, but had to use 2 separate queries. Don't know if a macro would work to update a table (there are no forms in this DB, it's all data fields). Same with code, I usually do this with forms. How can I update the raw data, which in some cases has multiple ifs, into the correct format? Thanks!
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated. I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated. I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
I have a table (A) with three variables: ProjectID, SubprofileID, and Budget. A projectID may have several subprofileIDs. I need to update this table monthly. Each month, I will get a table (B) with exact format. I want to update table A using data from Table B. I used UPDATE..... Left JOIN. But there is a probelm. For example, if project XX is not in Table B, the budget of project XX will be empty in Table A after updating whcih originally has data. Also, if Table B has a new Project YY, it is not added into Table A.
I want to add a lot of e-mail addresses to a membership database. The database has 2 relevant tables
Home details with fields Group ID: various Address fields: E-mail
Personal Details with fields GroupID: FirstName: Surname: etc etc
These two are linked by the GroupID field
I have another table with fields FirstName: Surname: E-Mail
I would like to be able to design a query that checks in the Personal Details table for match with FirstName and Surname, and then updates the E-Mail field in the Home Details table.
Is this possible, and how should the query be designed?
I'm using MS Access 2007, and I have a lookup table that I use for a form dropdown. I decided I want to change the names to abbreviations. But I see this does not automatically refresh the coorisponding names in the rest of the database. It only changes the dropdown menu options in my form as I add new data. All the old data stays as full names rather than the new abbreviations.
I have two tables. One table is linked to a text file that is always update by the another program. I have another table the is same exact one but it does not get updated. I have access run these two tables to see the difference between. (Its basically and add/drop function) But after I run those two tables to see the difference, I want to update the nonlink file, so it is up to date with the "link" table. Thank you.
Code:Public Function FOB()Dim sku As StringDim invoice As StringDim strSQL As StringDim dbs As DatabaseDim sku_rst As DAO.RecordsetDim invoice_rst As DAO.RecordsetSet dbs = CurrentDbSet sku_rst = dbs.OpenRecordset("SKU_ TABLE", dbOpenDynaset)Set invoice_rst = dbs.OpenRecordset("INVOICE", dbOpenDynaset)strSQL = "UPDATE INVOICE RIGHT JOIN [SKU_ TABLE] ON (INVOICE.SKU = [SKU_ TABLE].SKU) AND (INVOICE.DIV = [SKU_ TABLE].DIV) SET [SKU_ TABLE].FOB = 'Fob' " & _ " WHERE ((([SKU_ TABLE].FOB) Is Null) AND ((INVOICE.ACCOUNT) Like " * 11010 * " Or (INVOICE.ACCOUNT) Like " * 11111 * "));" End Function
I receive an type mismatch error. Run time error 13.
I have a form with a subform (main form is "frm_Maintenance_Request_2" and sub form is "subfrm_Job_Costs". The main form has a field called "GrandTotal" which is draws from a calculation on the sub form.) What I want to be able to do is that once the field has the calculation it updates a field called "Estimate_Cost" in the table "tbl_Maintnenance_Request". Can I do this without running an update query? I would prefer to use an Event Procedure that says something like afterUpdate:
