Modules & VBA :: Amend Data In Different Table

Jan 23, 2015

I have a form that has four text boxes populated by a combo selection on a prior form; on the form in question, these four text boxes are locked, however there is a command button to allow users to amend the information in these boxes - all works fine, however the changes they make are only applied to the current record. On occasion, it would be useful if these changes were able to be sent back to the source table to amend it.

Giving the option would be easy enough with a Yes/No message box, but is there a way with some code to amend the source record of the combo?

View Replies


ADVERTISEMENT

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

Jun 18, 2013

I'm trying to create a function to update and amend records in a table.

The update part works and updates existing records with new data but I'm getting an error with the insert part.

Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.

Nothing called 'FALSE' so not sure what that means?

Code:

sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "

CurrentDb.Execute sSQL, dbFailOnError

View 5 Replies View Related

Modules & VBA :: Amend Coded Command Buttons Using Code?

Dec 10, 2013

I want to be able to change certain parts of a Query and Code based on the contets of a text box.

Example with Command Button Code:

Textbox contains the name Tom

This is a command button on a form that i have copied and renamed etc through VBA to Tom (contents in the textbox)

DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "Qry BM to Management", acViewNormal
DoCmd.SetWarnings (True)
DoCmd.GoToRecord , , acNext

I now want to change where it says "Qry BM to Management" to "Qry Tom to Management",

Example with Command Query:

Again Textbox Contains Tom

Query has been copied but now i want to change the following SQL string from where it says Bhavins Table to Toms Table

INSERT INTO [Management Table] (Postcode)
SELECT [Bhavins Table].Postcode
FROM [Bhavins Table];

Im not sure if this is possible but i would like to know as adding a new user to my Database takes me about 10-15 minutes but I know what im doing (copy paste change specific parts of Queires and points of forms etc ) but i need to make it easy for management to add new Users....

View 2 Replies View Related

Amend The TOP Value

Nov 6, 2006

Is it possible to set the "TOP" value of a query, via an unbound text box from a form ?

i.e. SELECT TOP "this number will be from a value in a text box" ...

Thanks

View 3 Replies View Related

Can You Amend MSysObjects

Feb 23, 2006

Well, the title says it all.

Is it possible to amend this hidden table? It is for my ODBC links, I would like to amend (enmass) where it is linked to and the table name. If I could amend this table, it would only take seconds.

View 1 Replies View Related

Amend Top Value Via Text Or Combo Box

Jan 23, 2007

Is there a way whereby I, or another user, can amend the ‘TOP’ value of a query via a Combo or Text box entry on a form? I have searched this Forum for a solution to this very problem but have had no success. I understand how to achieve this via the queries ‘SQL’ view but cant figure out a way to do it by the way that I have already described!!

Your advice or a ‘pointer’ in the right direction would be extremely well received.

Thanks for your time.

Have a nice day...

Kind Regards

CarolW

View 2 Replies View Related

Modules & VBA :: Check If Data In Table 1 Matches With Any Of Data In Table 2

Dec 4, 2014

There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.

If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.

As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".

View 14 Replies View Related

Need To Have Query Amend A Revised Calculation

May 30, 2006

I have a query which calculates and original amount, looks to see if there is an increase or a decrease in the amount, and if it's an increase, it adds the original amount to the amount of the increase, and gives a "revised" amount. If it's a decrease, it subracts the original amount from the amount of the decrease, and gives a "revised" amount as well. This works fine for the line item, as long as there's only one Revision to the line item.

What I need it to do, and I'm not sure if I should still be doing this at a query stage, or if it should be coded, is, if there is more than one revision to the line item, it needs to look at the new "revised" amount (from the first revision), and then add the increase or subtract the decrease from the revised amount, and give a new "revised" amount, instead of taking it from the original amount.

example of what it's doing now.

Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Original line item - $4,300 Increase - $50 New Revised amount - $4,350

What it should be doing.

Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Revised Line item - $4,800 Increase - $50 New Revised amount - $4,850

I need to have it look to see if the revision # is higher than 1, and then look at the revised amount, and do the increase or decrease at that time, and then give another "revised" amount.

Is this doable? And if so, any help would be greatly appreciated!

:confused:

Here is the Expression that I have in my query to calculate my "revised" amount. It looks at the original amount, and looks to see if it's an increase, and if it is, it adds it to the revision amount, and gives a new total for the line item, otherwise it sees that it's a decrease, and it subtracts the revision amount from the original, and gives an new total.

New Amount: IIf(tblRevisions![Increase?]=True,[revision Amount]+[Line Item Amount],[Line Item Amount]-[Revision Amount])

View 4 Replies View Related

Update/amend Querie Woes!

Mar 23, 2007

Hello,

I have a table exported from excel. I intially imported this in to access to form a list of due dates for services.

Every week I get and e mail with the updated version.

I am trying to figure out how to update the dates in the 1st table with the new weekly ones.

I can't get it to update. Also there may be the addition of new services as well.

Any tips would be grealty recieved.

Many thanks in advance.

View 2 Replies View Related

Queries :: Amend SQL For Find Duplicates Query

Oct 23, 2014

I have a find duplicates query with the following SQL:

Code:
SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;

This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match

So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.

View 3 Replies View Related

Modules & VBA :: Copy Data In Sheet1 Using Sheet 2 And Access Table Data?

Aug 5, 2014

I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.

The following formulas will be used in the select statment:

Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected

View 14 Replies View Related

Modules & VBA :: Take Data From One Table And Insert Into Another Whilst Doing Calculations On Data

May 2, 2014

I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.

However I can't get past this:

Code:
Private Sub Test_Click()
Dim strSQL As String

strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL

End Sub

It keeps saying "Run-time error '3292': Syntax error in field definition.

View 4 Replies View Related

Modules & VBA :: Get Data From Table

Sep 17, 2013

I'm using IE automation to put data from an access db into a webform, submit some values then want to get some results back. The submittin part is fine, but I can't get the values out because they are in a table! The VBA is below, with the table code below that:

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True
Dim url As String

[code]....

View 2 Replies View Related

Modules & VBA :: Extract PDF Data To Table

Nov 21, 2013

Through searching, I have seen a lot of post regarding 3rd party downloads to extract pdf data to a table, any coding or a sample database that doesnt require 3rd software?

In the end, I want to import the pdf data to the table, then attach the pdf file automatically. URL....I have a pdf form that was developed in Adobe Live Cycle. For sake of example, lets say my form object names are:

TextField1
TextField2
DateField1
CheckBox1
CheckBox1[3]

View 13 Replies View Related

Modules & VBA :: How To Move Data From A Table To Another

Mar 30, 2015

My boss has assigned a task for me to move the details under a field name (eg:[Name]) in table 1 to table2 under another field name (eg [Insured Name]).

Previously, the software I planned to use is Excel, but my boss says Excel cannot handle large data as the computer will be hanging.So,he wants me to use Access to do the programming.I have some programming backgrounds (like C++) but it seems like I hardly understand how Access works! So can any Access expert here teach me how to do so that i won't get fired.

Firstly, We will have the data like this.

Table1

Code:
Name | PolicyNo | Age | Benefit
--------+----------------+----------+-----------
Chris | 123 | 21 | Death
Jane | 456 | 34 | TPD
Adam | 789 | 55 | DD

Secondly, we let the other staffs to key in which column to be mapped with the other. Manually Key in (both columns).Mapping Table:

Code:
Table1 | Table2
--------------+-------------------
Name | InsuredName
Policy No | Pol_No
Benefit | InsuredBenefit
Age | InsuredAge

Thirdly, the staff will run the VBA and result would be like this.

Table2

Code:
InsureName | Pol_No | InsuredBenefit | InsuredAge
--------------+--------------+----------------------+----------------
Chris | 123 | Death | 21
Jane | 456 | TPD | 34
Adam | 789 | DD | 55

As you can see the new table(table2) , field number 3 is the same with the field number 4 in table1. There is something like copy and paste job, but user can decide how the arrangement for the new table is.

View 14 Replies View Related

Modules & VBA :: Updating Table With New Data Value

Aug 11, 2015

I have a VBA script that looks at a date on a form, adds a certain number of months (selected by the user). at the moment i can output the new data to a message box. I want to update a table with the new data value.

This is the code i have so far.

mySQL = "UPDATE job master file " & _
"SET [BBDate] = " & newdate & _
"WHERE [job name] = " & strname

View 10 Replies View Related

Modules & VBA :: Append Data From Several Tables To One Table

Feb 14, 2014

I have the following code, the purpose of the code is that to take all rows from each table to append them into one table. However, I am testing this code with 2 tables (Table2 and Table3) each table has 2 records, when I run the code, it keeps adding records to table 1 that exceeds one million. what is wrong with my code?

Dim tblString, I As Integer
Dim rstFrom As Recordset, rst2 As Recordset
Dim db As Database
Set db = CurrentDb
Set rst2 = db.OpenRecordset("Table1", dbOpenDynaset)

[Code] .....

View 3 Replies View Related

Modules & VBA :: Distinguish If Data Already Exists In A Table

Aug 22, 2013

I currently need a way to check to see if a a field already exists. To be exact I have an access application that imports data from multiple sources and saves the file name of these imports into an import tracker table. The table has four fields, file name, Date Imported, Import Type, and Number of Records. I am writing some code that checks a Boolean given by a form. If the Boolean is true It skips over checking.

If it is false It needs to check my import tracker file names to see if the file was already imported. If it was then it will skip over the file and the algorithm will check the next file given by an array. So I was wondering..How would I go about making it check if the file already exists in the table. I was thinking of turning the File Name Field into a primary key, since there will only ever be one File Name of every type.

View 4 Replies View Related

Modules & VBA :: Import All The Data In PDF Forms To The Table

Jul 11, 2013

I have a fill-in pdf form, with fields that match database table fields. My database is in SQL, but I use Access for the forms and reports.

The users will go on-site (and be offline), fill out the pdf forms a bunch of times and come back with a folder of them.

I am looking for a way to have a form where a user can choose a folder (maybe on an access form) and say "import PDFs" and then have all the data in the PDF forms import to the table. A lot of other stuff has to happen in that process (checking the project number on the form, assigning an ID tag for that item, etc).

View 3 Replies View Related

Modules & VBA :: Save Data From Form To Table

Oct 24, 2013

i got a form with three normal fields where i add data i then have two auto number fields i.e. SupplierID and PersonID the supplierID works fine, i can add a new record and click save and it will save the data in the suppliers table.The problem is with my PersonID field, i need it to retrieve the data from my subform and firstly display in the field on my main form and secondly, when i click save it should save save the number that is displayed into my Suppliers table.

View 2 Replies View Related

Modules & VBA :: Append Data To Another Table With A Filter

Dec 11, 2013

I want to append data to the FC_TEMP table with the condition that the time_period is same on FC_TEMP and Scrap_Sales_Forecast table.Somehow it asks me to type in a parameter for Time_Period on FC_TEMP.

Code:
'Append Scrap Sales forecast to actual FC_TEMP table
Public Sub Append_Scrap()
DoCmd.RunSQL "INSERT INTO [FC_TEMP] SELECT Scrap_Sales_Forecast.* FROM Scrap_Sales_Forecast " & _
" WHERE FC_TEMP.[Time_Period] = Scrap_Sales_Forecast.[Time_Period]"
End Sub

View 3 Replies View Related

Modules & VBA :: ADO - How To Delete Data From Only One Table From A Join

Dec 17, 2013

How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?

On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:

Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection

[Code] ....

Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.

After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():

Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"

But only generates this: Run Time Error 3265 - Item not found in this collection...

I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?

View 3 Replies View Related

Modules & VBA :: How To Insert Data Recordset Into Table

Jun 4, 2013

I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.

Code:
Function fDAOServerRecordset()
Dim db As DAO.Database
Dim dblcl As DAO.Database
Dim rssql As DAO.Recordset

[code]....

View 2 Replies View Related

Modules & VBA :: Keyword Search In Table Of Data

Apr 29, 2014

I have a table of data lots of it (Table A)!!!

In another table I have a list of key words (Table B).

I need to somehow show all the records in Table A that contain (full words only (ie not part of a word) in Table B.

View 3 Replies View Related

Modules & VBA :: Clear Data From Table In Another Database

May 13, 2015

I have an append query that adds data to a table in another database, but I want it to replace the data every time it's run. How do I clear the data before running the append query? The databases are not linked, and I don't want them linked.

View 9 Replies View Related

Modules & VBA :: Pull Data From Query (or Table)

May 4, 2015

I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.

I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.

Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.

How do I do this?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved