I have a main switchboard but because the limit on it is 8 objects I decided to have different switchboards (Main data-entry switchboard, Query switchboard, and Report/Printing switchboard). I can easily add an object on the MAIN switchboard to open the Query switchboard and then on that Query switchboard have a button to go back to the MAIN switchboard but what I would like to do is have each switchboard open in different tabs instead, maybe a macro or "onclick" event needed? Would it be easy to build and customize my own switchboard?
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection
I have a tab controled form and when it opens in form view the tabs are to high to be seen on the page and you have to use the scroll bars to have access to them.
What in the world am I doing wrong. I cannot get this to open correctly and my form fits just fine on one screen in design view.
I set up my tabs at the very top of the Detail section. In design view I can see the tabs but when I open the form I can't see the tabs. (Within the Tabs I have placed sub Forms, and when the Tabbed Parent form opens it just displays the contents of the child form... NOT the tabs from the parent Form)
Infact I can't even see what I put in the Header section of the Parent form.
How can I make the Tabs appear? How can I get my stuff in the Header section to appear?
Ok, here I go. I have seen several examples on here, but still can't seem to figure how to get this to work for me. Currently the code below exports qry1 to excel with no problem. I have 4 other queries "qry2, qry3,qry4,qry5" that I need to export to the same excel workbook but in different tabs for each. How do I change the below code for this work? Can someone shed some light on this?
Option Compare Database Option Explicit
Private Sub cmdExportAutomation_Click() On Error GoTo err_Handler
exit_Here: Exit Sub err_Handler: MsgBox Err.Description, vbCritical, "Error" Resume exit_Here End Sub
Public Function ExportRequest() As String On Error GoTo err_Handler
' Excel object variables Dim appExcel As Excel.Application Dim wbk As Excel.Workbook Dim wks As Excel.Worksheet
Dim sTemplate As String Dim sTempFile As String Dim sOutput As String
Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim sSql As String Dim lRecords As Long Dim iRow As Integer Dim iCol As Integer Dim iFld As Integer
Const cTabOne As Byte = 1 Const cStartRow As Byte = 2 Const cStartColumn As Byte = 1
DoCmd.Hourglass True
' set to break on all errors Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file sTemplate = CurrentProject.Path & "AOTemplate.xls" sOutput = CurrentProject.Path & "AoOutput.xls" If Dir(sOutput) <> "" Then Kill sOutput FileCopy sTemplate, sOutput
' Create the Excel Applicaiton, Workbook and Worksheet and Database object Set appExcel = Excel.Application Set wbk = appExcel.Workbooks.Open(sOutput) Set wks = appExcel.Worksheets(cTabOne)
sSql = "select * from qry1" Set dbs = CurrentDb Set rst = dbs.OpenRecordset(sSql, dbOpenSnapshot) If Not rst.BOF Then rst.MoveFirst
' For this template, the data must be placed on the 4th row, third column. ' (these values are set to constants for easy future modifications) iCol = cStartColumn iRow = cStartRow
Do Until rst.EOF iFld = 0 lRecords = lRecords + 1 Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AoOutput.xls" Me.Repaint
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1) wks.Cells(iRow, iCol) = rst.Fields(iFld)
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy" End If
wks.Cells(iRow, iCol).WrapText = False iFld = iFld + 1 Next
exit_Here: ' Cleanup all objects (resume next on errors) On Error Resume Next Set wks = Nothing Set wbk = Nothing Set appExcel = Nothing Set rst = Nothing Set dbs = Nothing DoCmd.Hourglass False Exit Function
I have a form with mandatory fields highlighted a different colour (yellow or blue). On the form are 3 tab pages with subforms which also have these fields.
I have added a checkbox named incomplete to each tab page and to the form. My intention is to try to write some code to look at each field on the page and if the non-white (ie mandatory) fields all have a value, then the incomplete on that page is changed to no. The code then checks the incomplete value for all 3 pages - if these are all no and the form's mandatory fields are also filled in, then this also becomes no.
When creating reports, I can then find out which records have not been completed and notify the relevant staff. Also if the data is incomplete, they don't want those records appearing in reports - so I can use the incomplete value from the form.
I have a main form with two subforms. I'm trying to get my code so that it allows me to put 1 subform on one tab and the other spreadsheet on the other tab.Heres my code:
Code:
Option Compare Database Public Function Send2Excel(frm As Form, Optional strSheetName As String) ' frm is the name of the form you want to send to Excel ' strSheetName is the name of the sheet you want to name it to
[code]...
It won't let me pass more than one subform when I call Send2Excel, so I have to list it twice, which opens two excel files.
I have information held in 3 different tables and I would like to extract this information to three different tabs in a singe Excel workbook - preferably in one step.
My access knowledge is fairly basic but I have been looking online and I can only find out how to do it using a VBA script - which is quite terrifying! Is there a simple way to do this?
Need to get these into Access from an excel spreadsheet (located on sharepoint). I'm using the spreadsheet fields to create the table fields in Access.
I have a single form with multiple tabs. At the top of the form appears the name of an individual and below the name are multiple tabs containing information specific to that individual. Each tab has a separate underlying table, which is the data source for the information contained on that tab.
As best as I can tell, I can only use one single data source (a query at the moment) to populate all the data that appears on all the tabs. Is there a way that I can have a separate data source (namely, a table) for each tab?
I'm trying to clean up a form a bit and have it only show certain subforms/graphs if the data exists. I already have columns in a combobox query to show an "X" for if certain data appears:
Now, I know I could build another query and have some system go through and identify these things, but the easiest thing would be to reference the "X" in the columns of the combobox. Is there an easy way to reference values in the other (non-primary) columns? Or can you think of an easy way to make these subforms only be visible when the data exists? Maybe have an on load event for the subform?
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.
is there any way to add a button to a switchboard but not have it appearing on every s/b because in design view, it just shows a generic board, how do you edit specific boards?
Hi all. I am having trouble creating a switchboard. Im new at this, and get so far, yet the board wont work. I can get the board up, the buttons are there, when I click on them, nothing happenes.
What is the purpose of a switchboard. I set one up that has options to either go to a data entry form or go to a list of reports. My understanding of a switchboard is that its a screen that pops up with options to go other places in the database. But how? If this is correct than how do I get to a place where I would design this screen(?) and associate the right commands to the buttons. What am I missing here?
I want to create a switchboard with a field - that I enter a record # in, and it will bounce me to a (already created) report, and then find the specific record so I can print it. Is this possible? I am a beginner with almost not programming experience. Actually I am a finance manager by trade. I have been teaching myself Access so please..please..make your suggestions simple and clear enough that a turnip would get it...Thank you
I have created a switchboard to view a hours worked report. what I would like to do is be able to have dirrerent buttons on the switchboard to view different hours worked from the same report. As an example one would bring up the report showing only last weeks hours, and another for this weeks hours only, or by person. Is this possible and what are the conditions that I should use
This might be a question that isn't possible, but I have three html pages made in my database, and i was wondering if there is a way to link the pages into the switchboard somehow because then everything would be accessible from one central location?? if you have any answers or sugestions they are greatly appreciated! thanks
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export Spreadsheet Type Excel 8-10 Table Name (query Name) FIle Name (FIle location) Has field names No Range Blank ---- This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I have my db set up so that when you load all you see is the switchboard...no menus or tool bars. I have done this by going into startup and disabling them..no real trick there..
I have an option on my main switchboard to exit to design...from there it opens a form for a password, if the password is correct, it closes the password form, closes the switchboard and uses the sendkey {F11} macro function to open my design view for the db....the problem is that my menus and tool bars are not around either...
is there anyway to do a vb command to enable these
When I added some Access 2003 (Winows XP) machines to our Access 2000 (on Windows 2k)-based database, many of them would not operate some of the Switchboard "buttons" (especially for reports that contained macros). The animation of "click" would operate, but nothing would happen, no form or report would occur like it should.
After several rounds with Microsoft's Access Help guys, here's how we "fixed" it: 1. I deleted all printers from the affected user's computer. 2. I booted into SAFE MODE with NETWORKING, then installed the Windows-based GENERIC TEXT printer. 3. I rebooted into XP and installed the GENERIC TEXT printer there also, for the affected users. 4. I re-installed the usual office printers around our office.
This completely fixed the problem on all XP-based computers. The technician at Microsoft says he cannot explain WHY this works, but I have experienced that it DOES work, so try it if you're having troubles, too. It's some kind of mysterious workaround that "fixes" Windows XP somehow.
This is the first time ive used the switchboard so hopefully this will be just a quicky!
Bascially i have a main switchboard which has 3 buttons. These buttons all link to different switchboard pages.
THE PROBLEM :- I have labels on the main switchboard and these are all appearing on the consequent switchboard pages. Is there a way of assigning these just to the main switchboard and not the other pages?
Hello- I’ve searched the forums and was not able to find the answer, so I was hoping someone would be able to help with this.
I would like to open multiple forms (cascade) in the same .mdb file when the first form is opened. There are a total of 4 forms. This is probably a very simple thing to do. I'm not much of an Access person to simple things like these throw me for a loop. Cheers to anyone who can help. -Tom
