I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet. In simple terms the query looks like below:
Group | Client
Group1 Client1 Group1 Client2 Group1 Client3
Group2 Client4 Group2 Client5 Group2 Client6
etc.
As some of the groups contain large number of clients, I am trying to find the code to split groups by pre-specified number of clients and export to excel, as follows:
I am looking to check 3 sub forms for values and if they contain any then delete them. After a bit of googling I discovered that you should use recordset.recordcount - to which I tried but I get method or data not found - is this a valid method?
My code to which Im using is:
If Me.NoneChargeable_Admin_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Manufact_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Research_subform.Recordset.RecordCount >= 1 Then DoCmd.RunSQL "DELETE NoneChargeable_Admin.*, NoneChargeable_Manufact.*, NoneChargeable_Research.* " & vbCrLf & _ "FROM NoneChargeable_Admin, NoneChargeable_Manufact, NoneChargeable_Research;" End If DoCmd.Close acForm, "NoneChargeableHrs_frm", acSaveNo End Sub
I almost complete my mini project - Stock Inventory:
To track Items enter and exit from Videoshop or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the Items in the Videoshop, each Items details including category, transaction type description and most importantly quantity on hand.
As mentioned above, I almost complete the database except the most important Form - New Stock Transaction where User enter all the necessary details to issue an IN or OUT Transaction. To make things easy to understand, it share many similarity with Invoice and New Order.
Code: Main Form (Table: StockTrans) Trans_ID Trans_Type Trans_Date Trans_Cust Trans_Note
Code: SubForm (Table: StockTrans_Items) Trans_ID fk ItemID fk Trans_Item_Qty
When I finished records entry and enter Invoice completed button then it will auto update the Item Quantity on Hand.
Up until now I've put a tabbook on my main forms and subforms on each page of a tabbook when I want to display various tabledata related to the main form.
Now though I want to put the tabbook on a subform, so that I can include it from two different parent forms, but I thought perhaps I should base this subform on a query which draws everything together, and then just put these fields on the tabbook without having a seperate subform for each tab/related table.
However, I'm drawing and displaying seperate repeating groups in each tab, so I don't think this will work
Should I just have a hidden control on my subform to allow communication between the parent forms and the tabbook forms?
I have a subform and on that form i have a button, with that button i want to export the contents of the form to excel. It is in datasheet view and just want to export it to a file.
When i load the sub form. I put the query in through a macro (for various reasons) which i think what is making it a little more complicated. I have done a macro which is set to outputTo but because the is no record source until it is loaded within the sub form, it is not exporting anything apart form the titles and #name.
I'm new to programing so if this is not possible please tell me! Because this is going to be hard to explain I attached an example.
I have a Main form that a user will open, this is the only window that will ever pop up in front of them so it has a subform that will open differnt froms by picking buttons on the left.
One of the sub forms is the Member Information form, this form will show member information than at the bottom has another subform that will allow a user to pick a button above it to open another subform (this is so member info is always show and a member can be registered, pay...all different subforms)
on the Event Register subform the "Class" pulldown is based on the event that was pick just above it.
Issue: when I am in the main form, I can not get the class pulldown to work it always prompts me for "enter Parameter Value". I have tried the following SQL statements in the row source and noting work.. what am I missing???
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms].[MainFRM]![MemberInfoFRM].Form]![EventRegFRM].[Form]![EventName]));
and
SELECT SubCatList.SubCatName, SubCatList.EventName FROM SubCatList WHERE (((SubCatList.EventName)=[Forms]![MainFRM]![MainSubFRM]![Form]![memberinfoFRM]![MemberSubFRM].[Form].[EventName]));
I am trying to add multiple subforms (or subtables?) on an existing form. The underlying table is a very detailed descriptive table for art in a collection, I am already using one subtable to manage the movement in and out of inventory. When I look at the table in the datasheet view, I see the "+" sign next to each record, and when I click on that, I see the subtable I already have setup - looking at that, it seems impossible to have more than one subtable... is this true? This does not seem right.
My question is this: Can I have more than one subform in a given form? What I would like to have is a subform to keep track of provenance for any given piece (history of ownership), and another to track appraisals (values, dates, appraiser, etc) of any given piece in the collection.
how to add times together from multiple subforms and display the data on the main form. Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.
My most recent attempt was to simply make an expression in the control source as follows:
I'm v.new to Access so do excuse my ignorance. I have a form with a combo-box with the following values: Trust, Course, Overseas, GP When Trusts is selected I'ld like frmtrust_sub to appear. When anything else is selected I'ld like frmnontrust_sub to appear Once the data has been entered in the subform the user would continue back to the main form. I'ld like the subforms to remain hidden until needed, if possible so as not to confuse users.
I’ve copied this piece of code and have tried pasting it into the “after update” event procedure but I get this error message
“the link masterfields property section has produced this error “Ambiguous name detected: Type_afterupdate”
Private Sub Type_AfterUpdate()
Option Compare Database
Sub ShowSubform()
'Save unsaved changes to currently open subform DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Display appropriate subform based on Type chosen If Type = "Trust" Then frmtrust_sub.Visible = True frmnontrust_sub.Visible = False
ElseIf Type = "GP" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Course" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Overseas" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
ElseIf Type = "Other" Then frmnontrust_sub.Visible = True frmtrust_sub.Visible = False
End If
End Sub
Private Sub cmdClose_Click()
'Close form DoCmd.Close
End Sub
Private Sub Form_Current()
'Call subroutine to display appropriate subform based on template type ShowSubform
End Sub
Private Sub Type_AfterUpdate()
'Call subroutine to display appropriate subform based on template type ShowSubform
Combo boxes cboTN and cboSB are on my main form. cboTN is a search combo box that updates combo box cboSB with one of 3 values (ID, IDC, or MC).
I would like to do the following:
1: If the after-update value of cboSB is ID, subform sf1 will be visible. If the after-update value of cboSB is IDC, subform sf2 will be visible. If the after-update value of cboSB is MC, subform sf3 will be visible. (only one subform visible at a time since they are stacked in one place on my main form)
2: Once the appropriate subform is displayed, is it possible to use option buttons to set the property of the subform to either Add-only mode or Edit-only mode? If so, how? (perhaps some sort of Case A, Case B scenario with the option buttons?)
If you can help with either of the above; example codes would help my novice skills greatly!
I am trying to export a table from access excel spreadshhet.. but not in the simple traditional way.
I have 140 lines of data, I need a marco to take this single table and 140 lines and create 140 spreadsheets one each line of data appearing on one of the sheets. Id also need the title of the spreadsheet to be in one of the fields in the actual spreadsheet.
Hi Guys & Gals, I have an Access 2007 database that I am fighting with. I have a friend who needs a data entry database. So far I have created 4 tables: Cases, Customers, Data1, Data2. Cases_tbl has following fields:ID(autonumber), Customers_ID(number), Data1_ID(number), Data2_ID(number).
The Customers, Data1 and Data2 Tables are all similiar, they all have the ID(autonumber) Field followed be fields that need data inputed.
I would like to have the Cases table as the master table that is linked to the other 3 tables, so when I delete a customer, there case, and all of there data is deleted with it.
I have one form with 3 subforms. Each subform is used for inputing data into the customer, data1, and data2 tables. However, I cannot for the life of me link all these tables together. I have tried all different relationships and have search the web high and low and I cannot find an answer anywhere. So if somebody could help I would be much appreciative.
I have a form for timesheet entry that has 2 subforms.
The main form has a combo box for selecting staff name and another for WkEnding date. The form is based on table "SELECT StaffRef, WkEnding, Status FROM tTimesheets".
SubForm sfTimesheets is based on table tTimesheets. The subform is linked by StaffRef and WkEnding. (This has been working for ages.)
Recently added second subform sfOnCall based on table tOnCall. The subform is also linked by StaffRef and WkEnding.
If the user has a timesheet entry for the date, then sfOnCall displays and operates ok. But if there is no entry in the timesheet table, the sfOnCall form doesn't show the appropriate entry from the OnCall table. - I did try basing the main form on a UNION of the 2 tables, but whilst the query showed the correct data when I ran it - all the controls on the form disappeared!
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
Hi guys. I'm a bit of a noob with Access. I have a table which is 15 rows by 250,000 columns. So there are about 3.75m records.
What I need to do is to get access to export a number of csv files automatically from this table.
I need each csv file to contain all the data in the table for a certain range of rows. This is determined by a value in a particular column. This is a sample of the table.
http://i30.tinypic.com/aljf5s.jpg
Basically Store_Nbr represents a UID for a particular store. All the data is currently in one big table, and I need a single csv file for each different store_nbr (so it contains all the data in the table for each unique store). Also, for each store_nbr, there are different Year Month of Surveys. For each store_nbr, I need a different csv file for each Year Month of Survey. This will probably divide up the 250,000 long table into about 200 csv files, as there are about 50 different Store_nbrs, and there are 4 surveys for each individual store.
Sorry if I've not made myself perfectly clear.
I'm sure there's a simple way of doing it, but I really do suck with access and was wondering if you guys had any idea.
Work have asked for a lot of information to be run from Access and exported into Excel. The info they require will need to be exported into 4 Excel worksheets in the same workbook. Is it possible to tell Access that when they click on the report button on the form, it will automatically run the various queries and then put them into separate worksheets in the same book? I think this is perhaps too complex for Access to do?
I have a typical table regarding client details: tblClients
Another Table containing Investment orders: tblOrders , of course many orders can be made per client.
What I would like to achieve is some way of exporting this data in to excel with all data regarding each individual client stored in a single record. (This is needed for mail merge purposes)
What i have now:
Client : Bob Fossil Order: Pension Client : Bob Fossil Order: ISA
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer) Me.OrderByOn = True Me.OrderBy = [Contract No] End Sub
I have a main form with multpile tabs - each tab containing a different subform.
Link Master Field: ClientID (field in Master Form) Link Child Field: Client ID (field in all subforms)
There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......
Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.
When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.
If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.
exporting an Access query to Excel using VBA.When I run the code, the Excel workbook that is created defaults to the name of the query.I use naming conventions for my queries so the tab of the Excel spreadsheet is named "qryProviderAuditExport". I would like to name it "Provider Report". Is there a way to do this.It is one spreadsheet that is created when the code is run and there is only one tab to worry about. Here is my code so far:
Dim file_name As String file_name = CirrentProject.Path & "Submitter_Audit_Report.xls" DoCmd.OutputTo acOutputQuery, "qryProviderAuditExport", acFormatXLS, file_name, True
If possible, I would like to do this during the export without having the code open the Excel spreadsheet and doing it after the fact.
I have created 2 reports one that needs to be exported as an excel file and the other report as a PDF file. I have the exports working correctly for each on a button except this saves the files with the name of the report. The client wants the name of the file to appear as one of the fields on the report (the info that is in the description text box for example).
I have thought of two ways to do this and neither seems to be great - one is create a copy of the report with the new name and then export that saved file then delete the renamed report OR similar to that but instead rename the file save and rename it back (this causes all kinds of problems if the user cancels out that would need to be handled in the error handling - this is not a good way to go.
It works actually but when i go back to Access i get the following error message
runtime error 424, object is necessary.
Code: Private Sub Befehl1_Click() Dim xlApp As Object 'Excel.Application Dim xlBook As Object 'Excel.Workbook Dim xlSheet As Object 'Excel.Worksheet Dim rstID As DAO.Recordset, tmpStr As String
