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 Replies


ADVERTISEMENT

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

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 :: 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 :: 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 5 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

Modules & VBA :: Importing Excel Data To Access Table

Mar 25, 2015

I have a VBA function to syncsuppliers as below

Function SyncSuppliers()
On Error GoTo errhandle
Filename = DLookup("SupplierPath", "Setup", "SetupActive = True")
If Filename = "" Then
Exit Function
End If
Set xlapp = CreateObject("Excel.Application")

[code]....

The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.

View 2 Replies View Related

Modules & VBA :: Entering Data Into A Table Using Record Field

Jan 19, 2015

I am creating a log in form, it checks the user name which is unique. if the user has entered a password, it shows only one password text box, they enter the password, if it is correct they enter the database, otherwise they return to the text box.

But if they have not entered a password before the form opens with two text boxes, one for the password and one to confirm the password is typed correct, if the are different a message box shows telling them that they are different, now is where i having problems, when they have typed the two passwords and they are correct i want them to save this password in the same record "Password" as the selected "username" record, I can find the "username" record by doing a Dlookup, easy, but i am stuck how to then save the password from the text box where the selected username record is.

My table "staff" has fields of "IDStaff", "FirstName", "Surname", "Password", "Username".

View 3 Replies View Related

Modules & VBA :: Import Info Path Data Into A Table?

Aug 20, 2014

I am trying to automate the function that imports Infopath (.xml) data into access tables. I can have a button run the MenuCommand, but then the users have to go through all of the prompts and I just don't trust them enough to do it properly.

All I want is the user to click a button, then it lets them browse for the desired file and then imports it.

View 1 Replies View Related

Modules & VBA :: Delete And Insert Data From A Table In One Routine

Oct 10, 2013

Access 2010. Can vba code be written to delete and insert data from a table in one routine.

Something like Delete * Insert into select from where order by....

I tried it with a query but have to write two.

View 1 Replies View Related

Modules & VBA :: Adding Data In Listbox From Access Table

Jun 5, 2014

The following code gives me runtime error message "couldn't set the list property , Type mismatch".

Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim j As Long
j = 0

[Code] ....

View 3 Replies View Related

Modules & VBA :: How To Arrange Table Data Into Date Order

Dec 31, 2013

I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:

strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)

The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.

what I am doing wrong?

View 4 Replies View Related

Modules & VBA :: Query Inserts Wrong Data In Table

Apr 30, 2015

I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.

Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;

View 5 Replies View Related

Modules & VBA :: Append Data From One Table To Another - Skipping Duplicates

Mar 12, 2015

I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.

I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.

Any way to do this by creating a recordset and looping through to check for dupes.

INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism )
SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism
FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN
WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));

View 2 Replies View Related







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