My group uses VB and ODBC to query and format data on a daily basis from a central server. We may choose to add new columns from the server, but to do this means changing the code and having 40+ users of various technical skills copy and paste a new version of the database to their local machines.
What I want to do is have the Access DB call a central file that contains the VB that will extract and format the data.
I can do something similar in Excel by using Application.Run and pointing to a different Excel file with the Macro, but I haven't foudn a solution in Access. Any ideas?
Hi everyone. I have the following code attached to a text box.
Private Sub txtsearch_AfterUpdate() Me.lstsearch.RowSource = "Select [R&D ID#], [SKU#], [Project Name], [Construction level], [Manufacturer], [Hobbico Status], [R&D Work By], [Product Manager], [Desktopper]" & _ "From [Project Main]" & _ "Where [Project Name] like '*" & Me.txtsearch & "*'" & _ "OR [SKU#] like '*" & Me.txtsearch & "*'" & _ "OR [R&D Work By] like '*" & Me.txtsearch & "*'" & _ "OR [Product Manager] like '*" & Me.txtsearch & "*'" & _ "OR [Desktopper] like '*" & Me.txtsearch & "*'" & _ "OR [R&D ID#] like '*" & Me.txtsearch & "*'" Me.lstsearch.Requery End Sub
This code searched for matching text strings in a few different filed. It is working really well, but it would become much more helpful if instead of searching one single text string, I could enter two in the same box and it would perform the search based on matching both strings, that is, list all the records that have xxxx and xxxx in any of the fields checked.
I am looking to add multiple lines of record to a sub-form via a module activated from another linked sub-form. As an example of what I want to do: On identifying a particular type of vehicle servicing (e.g. 6000 mile service, 12000 mile service) on a service event sub-form of a vehicle form, I want to enter a list of required parts (e.g. oil filter, oil, etc) on another sub-form called "parts", that I can then edit before recording in an underlying table. Can anyone help. Thanks Steve :confused:
How do I add multiple recipients to the '.To' line in the code below?
Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem Set appOutLook = CreateObject("Outlook.Application") Set MailOutLook = appOutLook.CreateItem(olMailItem) With MailOutLook .BodyFormat = olFormatRichText If Me.txtDepartment = "IT" Then .To = "my email" End If .Subject = "hi" .HTMLBody = "hi" .send End With
URL...Essentially it seems like the format event fires only once with a docmd that has a where clause. If I print a single report it is perfect! Example one in the above post has a subreport, example 2 its very simply incorporated into the main report with no sub report.
Staff are monitored to make sure they are keeping up to date with our customers. A customer can have multiple projects going through the factory at any one time. Each customer has a record per project and a 'general' record. Ideally we would like our staff to be able to move the 'general' record when they update a project record as opposed to either having to find and then update the general record after, or forgetting and calling the customer again 2 days later!
Including a msgbox for the EnqNum seems to show the general record correctly, however being new to access I am unsure if I have the update part correct.
Code: If Me.chkMoveGen.Value = "-1" Then Dim EnqNum As Integer EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13") DoCmd.RunSQL "UPDATE tblEnquiries " & _ " SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _ " WHERE e_id= EnqNum"
Body (pre filled with "Dear ([Contact]if possible) Please find enclosed your documentation for your recent telephone order bla bla bla and our logo.jpg if possible)
then i would like to attach to the email the cover letter/invoice(report), book page(report) and the book (pdf) from the hard drive (r.eports changed to pdf)
if i can press a button on the form to send it that would be good.
if i could link it to a query to send a batch of them that would excellent.
The Database is Named: b4s.mdb The table is called: Main Table The input form is called: Client The letter/invoice report is called: b4sletinv The Book page report: b4sbook The pdf book is located at c:Documents and SettingsAdministrator4sfinal.pdf The query for the batch is called: coverlet&proforma
I've been trying to figure a way to minimize my code in a module. I currently have three function built excalty the same way to work with three different forms. Each textbox in all the forms are named the same now what I'm trying to do is have the function identify which form called the function and where should the data be.
Code: Dim frmCurrentForm As Form Set frmCurrentForm = Screen.ActiveFormMsgBox "Current form is " & frmCurrentForm.Name
Once that is found. have something like this.
Code: Select Case frmCurrentForm.Name Case 1 WorkForm = Form1
Case 2 WorkForm = Form2
End Code: WorkForm.NameField = "John Smith"
No matter if all three forms are open the data should only go to the one that declared the function.
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load() Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close If InputBox("What is the password?", "Password") = "2" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close End If End If
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
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 want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.
Also, it appears in the old Database Table once imported as:
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
I have 3 tables made up of ID and NAME and other parameters. NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.
Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount FROM [JE 06 Log] WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
Does anyone out there know how I might do the following:
I have a main folder which contains many sub-folders. In turen, each subfolder contains several files. I am interested in creating a text file that within each sub-folder will list certain files in that sub-folder. Example:
Read Main Folder Do While Sub-folders exist Do while selected sub-folder contains files If selected sub-folder contians FileZ then Write selected sub-folder name to text file Write time/date stamp to text file else loop to next ub-folder end if end do while end do while Close input and output
