I currently have a database which collects details of event effecting our business and am using a simple query that displays events within a defined date range.
I have also set an excel sheet which on the main worksheet appears as a calender format for a particular month which is updated by data in the access query. At the moment I tranfer the query to excel and it appears as a new sheet which I then have to link to the master calender.
Is there any way when exporting to excel to tell access to send the data to a particular sheet and cell range and even more advanced tell it were to put certain dates information? i.e send 1st March info to cells A1 to A4, 2nd to A5 to A8 etc.
Hope someone has some ideas on this one - even being able to send all data a specific cell range would be useful
I am setting up a database using Access 2013, and am trying to test downloading files from Excel. For some reason it is not allowing me to have more then 4 columns in the excel file, it gives the error "Subscript out of range". I thought it was the type of variable at first, but when I tried deleting different columns on both files, the transfer always worked with 4 columns. How do I allow more columns to be transferred?Also, is there a way to not include the row numbers of the excel file as an ID column and only include the ID given in the first column?
This works fine as long as column names in Excel do not have periods. (.) Other then asking whoever is sending this not to put periods, is there painless way ignore that fact that excel has it, un just do it without it ?
Hi, I'm running 2 append queries to my existing db to update fields in one table with data from 2 sources - basically plucking some fields from one and some from the other (using a client ID number as the common identifier - the data is fed into the parent table). However, it would appear that some of the data is not transferring into the parent table even though it is visible in the source file (.xls). I know that this seems like a broad request but I'm just wondering if there may be a potential simple error. Any ideas?
Hi, I'm running 2 append queries to my existing db to update fields in one table with data from 2 sources - basically plucking some fields from one and some from the other (using a client ID number as the common identifier - the data is fed into the parent table). However, it would appear that some of the data is not transferring into the parent table even though it is visible in the source file (.xls). I know that this seems like a broad request but I'm just wondering if there may be a potential simple error. Any ideas?
Hi all. Hopefully this will be a simple thing...What I have is one main table (RFA Main) and two related tables (S/R Disposition and Suppliers). RFA Main has a two check boxes (ScrapRewrk and SupActs) that I am using to trigger append queries that append the following SQL statement: Suppliers table INSERT INTO Suppliers ( LogNum, Originator, ReportDateOp, PartNum, PartNumDesc, ProbDesc, CustRtn, RmaNum ) SELECT [RFA Main].LogNum, [RFA Main].Originator, [RFA Main].ReportDateOp, [RFA Main].PartNum, [RFA Main].PartNumDesc, [RFA Main].ProbDesc, [RFA Main].CustRtn, [RFA Main].RmaNum FROM [RFA Main] WHERE ((([RFA Main].SupAct)=True));
S/R Disposition table SQL the same except for the WHERE statement reads ((([RFA Main}.ScrapRewrk)=True));
First, what kind of statement would I use to trigger the append query when the boxes are checked? Currently, the users are doing these append queries manually by clicking command buttons.
I have created a database that deals with inventory control. My next step that I have been approaching is to click a command button that opens up a separate from but takes specific information from FORM 1 into the new FORM that I just created.
So if a specific Employee takes out 4 aprons from a total of 50 and there are now 46 left, I would like the submit button to transfer that data over accordingly to Inventory control form in the correct text boxes.
Specific Example: If employee John Doe takes 4 aprons from a total of 50 which now leaves 46 how can I transfer that data into the other form showing specific information like 'Product ID' taken, 'Date' it was taken, 'Product Description', 'Employee' who took it, 'Amount Taken' and, 'total' left. I need the extended information to show up in the text boxes on form 2.
Is there a certain code that does this? Here are screen shots so you can get a visual....
I have one form that is serving as a popup while the parent form remains open... in my code for the popup I'm trying to transfer the value of a textbox to the actual textbox on the parent form... seems easy, but I get this message: "You can't reference a property or method for a control unless the control has the focus" with this code:
Private Sub btnExportData_Click() [Form]![Worksheet]![txtBookedNotBanked] = Me.txtTotal1.Text End Sub
... also is there a way to code this so the popup closes when the transfer is made?
I have a form (frmJob) that has a combobox (cmbCompany). If a new company is not in the table the NotInList code opens another form (frmCompany) and a new company is added into the textbox (txtCoName).
The code below is the code that closes the frmCompany after entering new company.
The first part of the code (Add new company into table) works and adds the new company into table but the second part (Add new company into frmJob) does not, and the new company does not appear in the cmbCompany combobox. Note: frmJob remains open while data is entered into frmCompany.
How can I get the second portion of the code to work?
Code: Public RemCompany As String Private Sub cmdCloseCompany_Click() "FIRST PORTION - Add new company into table Dim dbCom As Database Dim recCom As Recordset Set dbCom = CurrentDb
table 2 is bonus table id empno bonus amount iddate
table 3 is deduction tableid empno deduction amount iddate
Now i need is to transfer the data from bonus table and from deduction table to the table 1 if the empno and iddate are equal on the both tables
What I mean for ex if i create query and when i enter the employee number directly it will give me his bonus if he has and his deduction also if he has, so how can i do it?
I have a database with a main database that has 2 related tables...my problem is that i only keep data that i really need, in the main table. My problem is that i sometimes need to go back to old data that i erased. so i have to keep old data too. but i can;t on the same database. so i need to move the old records from the main table and all the related data to another database that will keep all the erased data, so if i need to find something i will search on the "deleted records" database.
I understand that any data that is inputted into a form is always transferred back to the table in question.
In the form I created, I had
4 subheading scores added together to form another subheading named function =([1]+[2]+[3]+[4])/4 was the formula that I put into the control source. The calculation worked fine on the form giving me the correct name. However, this number did not transfer back to the table simple being labelled as zero. Can someone please advice!
I have one table of records that I would like to query and display the results. I have created a form that asks the user for criteria for the query (search). Then I want to display the results from that query in another form (results). The problem is that I cannot figure out how to do it.
The results form is using the query as its source. I have tried running the search and then opening the results form, but I get a message saying that I tried to lock the table but it was in use.
Is there a way to link these forms so that i can bring up the search form, perform the search, and have the results brought up in the results form?
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
OK so my situation is a simple 1 to many relationship where the main form has the primary key and the secondary input form needs the foreign key to be passed to it to link the 2 tables.
Say for example 1 Site has many sections (Plants). The main menu form can therefore select the Site and filter to another combo box containing the plants that are attached to that site. The user can then either edit the plant or add a new plant record linked to the site.
I have figured out how to edit the plant attached to the site using a macro that opens the form Plants with the site ID passed from the main form however when I want to enter a new Plant I still need the site ID passed to the Plants form however using the following code
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
In Excel I have a sheet with about 3000 rows. Each row has an id. I need to retrieve a field from an Access database for each id in the Excel file.
I know how to query the Access database from Excel, but I don't know how to set the query to use the id's in the Excel sheet instead of an Access table.
I have always been wondering why this happens, and now I have a problem with it.
When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this? :confused:
Current situation is that I have this form with a chart.
Row Source :
Code: SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP] FROM PolyWrongRegInsCount;
PolyWrongRegInsCount; <- This table is one of the queries that I created.However, the data is manually added. I want to get the data from excel spreadsheet. And load into my queries
