I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View RepliesI'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
I have some shapes to be automated in powerpoint (Tables, Pictures and Charts). Each and every shape has to be created on basis of some reports (all excel files with a Dyno-Static Filenames: "Some Bond TE 1-29-2015.xlsx", "Scenario Analysis GSB 28th Feb 2015.xlsx"). Dyno-Static Filename: Filename having some part static and some part variable in it.
So I created a table with a list of file names (report names) with wild cards on it. Like "Some Bond TE*.xlsx", "Scenario Analysis GSB*.xlsx". I am opening these files using the following code from Access:
Code:
If Not IsWorkbookOpened(Dir(CurrentProject.Path & "Received FilesFiles" & SlideNshapes!Shape_File)) Then Set SourceFile = Excel.Workbooks.Open
(CurrentProject.Path & "Received FilesFiles" & Dir(CurrentProject.Path & "Received FilesFiles" & SlideNshapes!Shape_File), ReadOnly:=True)
There is no problem in Access Part. Now I come to my current problem. I am planning to have an attachment field (excel file) to store a table layout model identical to the powerpoint table (shape) to be automated.
Suppose:
The powerpoint table is like the following:
Rates Wtd Dur
US2Y 0.34
The Attachment Layout:
Rates Wtd Dur
US2Y =FilepathfromAccess!Sheet1!M24
There are so many cells to lookup on different excel files. The Following are the questions:
1. how to passs an access variable to a formula in a excel sheet?
2. How to refer only the opened sheets in an excel formula?
Hey folks,
Not sure if this is the best forum but decided to put it here anyway.
I have a Access query that reads:
SELECT bleh
FROM blah
WHERE something > 10
Then in Excel I pull the data across using:
Set qdf = db.QueryDefs(qryName)
Set rs = qdf.OpenRecordset
and paste the data using
ws1.Range("IV1").End(xlToLeft).Offset(0, 2).CopyFromRecordset rs
I now want to change the query so the where statement reads
WHERE something > [amt]
So the question is how do I pass the value for the parameter value for [amt] from excel to access?
I need to pass a variable from one Access Database to another
The scenario is I have a item number in one database that I need to use to open a form in another database,
I can open the database using vba, currently launching a BAT file that copies the latest version database to a local drive and then opens it,
Is there an easy way to pass the variable after this has completed?
I am trying to pass a boolean variable to a class module
Code:
Set rps.ViewS = View
the code in the module that this in theory is calling reads as
Code:
Private ViewC As Boolean
Public Property Set ViewS(ByRef ViewA As Boolean)
Set ViewC = ViewA
End Property
Public Property Get ViewS() As Boolean
Set ViewS = ViewC
End Property
However I am getting the error message
Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
My access is 2013.
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
I am trying to import data from an Excel file to MS Access (2013).
The Excel sheet consists of 700 columns. A group of 7 columns (always same header) are to be implement in Access one among the other.
That means:
At first columns A-G, than columns H-N, than columns O-U
I am trying to solve this with a loop (as you can see in the code)
But, if I start the modul I get this error:
"Runtime error 1004. The method Worksheets for _Global object failed"
But the module works, of I start it a second time. The error message appears only at the first start.
Apart from that, the code works. Sometimes, the module imported empty rows into the Access table.
Code:
Sub ExcelImport()
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Workbooks.Open FileName:="C:Users...DesktopBeispiel.xlsx"
[Code] ......
Ihave implemented custom events to sync between open forms. I noticed that if the code is engaging in middle of the way referncing a form with form constant (Form_formName) then the listeners stops listening so the event isn't firing, without getting the reason of this i just avoid using it Now, i have a problem that i want to open multiple instances of the same form and i didn't find another way than set frm = new form_formName as this is the only way i seem to be able to reference a closed form, so my question as followes
1. whether the "breaking" issue can be solved without avoiding "Form_FormName"
2. or a way to open
eference a form without using "form_FormName"
I am trying to set up something to be able to take me to a folder that holds various forms for personnel. I have a query that generates the link for each person. I have tried to set it up as a hyperlink in ACCESS 2013 and it displays as one but doesn't act like one.
I want to be able to click the link and have it open up a personnel folder for that individual. I can't find a MACRO that I can create to do it. Example of my query is that it creates a link K:Main BreakdownSection BreakdownPersonnel FoldersName and the name is the variable part.
My next problem will be to have it create that folder when we have new personnel arriving.
I would also note that I have not worked with ACCESS in many years and much of what I was able to do with 2002 and 2003 doesn't work with the newer versions of ACCESS.
I have about 4 combo boxes with a list value of dates in them, combo1 is the 'main control'. When a date from combo1 is chosen and a button is clicked, the other 3 combo boxes receive a default value that closely matches combo1. In fact the default value each of the 3 combo boxes receive exists in that combo box. For example, if I gave combo2 default value "2013/01/01", it actually has that date in its list. What I want to do is trigger a OnChange even after combo2,3,4 receive their values. Other things happen after the OnChage trigger is fired.
View 2 Replies View RelatedExcuse me!
i would like to know how to use browse function (like in windowXP) to select the excel file i want instead of using transferspreadsheet code.
thx!
I want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.
This is a much simplified example of what I am trying to do:
Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...
Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."
for i = 1 to 10
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
how can i pass a variable from one form to another
this works fine
Tracking_Number = Forms("Main").Control("mytext")
but if "mytext" was in vb (rather than from a text box as shown above) how can i do this
Hi Everyone,
I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.
The original code in my first form is:
Private Sub Command65_Click()
Dim MyFilter As String
If Me.Filter = "" Then
MsgBox "Please apply a filter to the form first."
ElseIf Me.Dirty Then
' Make sure the record is saved
RunCommand acCmdSaveRecord
Else
MyFilter = Me.Filter
DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If
End Sub
How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?
Thanks,
I have an event on a form kicked off by On Delete and I have an event on the same Form kicked off by After Delete Confirm. I need to pass a variable from the On Delete Event to the After Delete Confirm Event. I have set up the variable as public, but it keeps getting reset inbetween the two events. Any ideas?
View 1 Replies View RelatedHi all!
I have a college project were we have had to code a database for a fictional hospital to hold patient, doctor, consultant and appointment information. I am struggling with one particular problem.
I am trying to pass a variable from a combobox of results to another form to display a certain record.
I am trying to send:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=" & "'" & AppointmentID & "'"
to the form but it fails, yet if I "hard code" the variable it works:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=23"
Can anybody shed any light upon this please?
Hi
I have a matrix variable (ex: test(7,3)) define as private in a form's code vba. In this form, I open a report in which I would like to show the values of my matrix variable. How can I do this efficienly ... what i use now is a public buffer variable in a module(it eats memory for nothing)
can someone tell me a trick ...
tanks a lot
I'm attempting to pass a variable from form to form...I'm having trouble doing it...I checked the forum and read a little that the form I'm passing it from must stay open...is this correct? I have my variable as: Public strUserName as String. It does work fine if I leave the form open then I can pass it...
But I want to be able to close the form and still pass the variable...How can this be acheived?
Thanks in advance.
Kacy
In form1 I've a command button to open form2.
Code in form1:
Private Sub button_Click()
On Error GoTo Err_button_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form2"
stLinkCriteria = button.Caption
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_button_Click:
Exit Sub
Err_button_Click:
MsgBox Err.Description
Resume Exit_button_Click
End Sub
button.Caption contains a valid date value
In form2 I want to display the date in a label.
Code in form2:
Private Sub Form_Load()
Dim DatePassed As Date
DatePassed = CDate(Me.OpenArgs)
label.Caption = DatePassed
End Sub
On executing I get an error like "Invalid use of null value."
Who helps?
I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.
To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.
My code thus far is:
Private Sub Command5_Click()
Dim mySQL As String
Dim WHSE As String
mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
[Code] ....
When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct -
SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click()
If Right(strsql, 1) <> "'" Then 'check if statement was built
Else
strsql = strsql & ";" 'add trailing ; to statement
MsgBox strsql
DoCmd.OpenReport "tblincidents", acViewNormal, , strsql
End If
End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
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:
Group1 - clients 1 to 300.xlsx
Group1 - clients 301 to 600.xlsx
Group2 - clients 1 to 300.xlsx
etc.
I have an access program that is stored on a SharePoint site. I want to be able to export report to the computer even if I just open the access program as read only. Here's my code:
Code:
Private Sub btnExport_Click()
On Error GoTo btnExport_Click_Err
Dim db As DAO.Database
Dim strSQL As String
Dim qdfTemp As DAO.QueryDef
Dim qryFilter As DAO.QueryDef
[code]...
I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
I know Access can setup an Outlook Task to auto-export query to Excel, but it requires the Outlook to be always open on the user's computer.
Is there an easy way to setup a schedule that can automatically export a query to Excel and this schedule will then auto-email the exported Excel file to an email address every Monday at 5AM for example?
If this can only be done in VBA, any reference I may start with?