I've got such a simple SQL query, but it won't output the friggin results of the query!
I put the query in a "Query" in access, run it... and it outputs me a integer value. I'm running the following VBA code in "Form Load"
Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT Count(*) FROM ContactDetails;"
MsgBox strSQL
What it does is on form load instead of popping up a msgbox saying 41 or whatever the integer value of that count query should be.... it outputs the query itself:
SELECT Count(*) FROM ContactDetails
Any ideas on how I can get the SQL query to run as a query and output the value into the msgbox?
I have a query which returns a list of reports I have created this session.
I want a button that when I click it runs the query and then stores the results into a variable.
I looked online and found a pretty good connection code and loop code to get the data but I'm having a hard time getting it to store into a variable.
What I want to do next is using code print the reports that were listed in the query.
That Means I need to be able to take that variable and pop the information off it, storing it into another variable (or if i can use an array just use that) and use that to concatenate into a command to print that report.
I am having problems with the syntax of this though. I think I'm just missing some key elements.
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
I'm trying to determine if an employee changed departments in 2007.
We maintain a table that records every time an employee transfers, giving an effective date of the change. But, this table also records transferring to a different shift without a department change.
So, my logic was to look at what an employee's most recent assignment was as of 12/31/06 - this way, even if the employee's last transfer was June 3, 1827, it would still be picked up.
Then, I would see if there was any department change in 2007 by comparing any records recorded in this table in 2007 with the list from 2006 and pulling out those records that had different departments.
My first attempt was to use a query for 2006 and earlier with totals, grouping on the employee ID and grabbing the Max Effective date along with the department ID (org). Like so;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate, dbo_assignment_log.workingorg FROM dbo_assignment_log GROUP BY dbo_assignment_log.ppms_ID, dbo_assignment_log.workingorg HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#)) ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
All well and good. But, then I decided that I didn't want the org in the list since it was giving me extra records (because it was on Group By), so I took it out and ran the query again like this;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate FROM dbo_assignment_log GROUP BY dbo_assignment_log.ppms_ID HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#)) ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
I am trying to figure out if this line of code is usable, but I don't know: DoCmd.RunSQL ("SELECT Max([BSRangeTbl].[Date]) AS MAXofDate FROM [BSRangeTbl];")
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code: Option Compare Database ' Constant for maximum number of columns EmployeeSales query would ' create plus 1 for a Totals column. Here, you have 9 employees. Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I am using a query with a variable which select what records I want to use.
When I use the query from the query above it ask me for the variables again.
I do not want to input the variable again because I have the records I need but must use the 1st query for the secord query to build what I need.
How can I use the first query to build the 2nd query and not have to answer the variable input on the 2nd query? I already have the records needed.
Trust me, I need to build the 2nd query with the 1st query in order to do what I need to do. The first query select the dates I need, then the second query sums only the month and year and not the day of the month. However, I am asked for the dates again. I do not need for the user to put the dates in again. If you know how to do the operation all in one query, that would be great.
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.DistroÂ
Is where I have the report via client name linked the the distro list that should be pulled.
Hi, I am totally out of my depth on this one. I have managed to find ways to solve my other problems but this one won't seem to budge.
I have one large table ALLENGPUMP which has columns of data and other columns of lookups to associated tables. What I am trying to achieve is that by selecting a value from a combo box (which contains the results of: SELECT DISTINCT ALLENGPUMP.Coupling_Order_code FROM ALLENGPUMP; ) a list box will show all of the other columns from the table (and the associated tables).
In lstOther3 and lstOther4 the columns sought are in the same table and this will produce a result of multiple rows when the Coupling_Order_Code is apparent for more than one ID, as required.
lstOther5 contains the columns with lookups and only works when the Coupling_Order_Code returns only one ID. When more than one ID is returned it no longer functions and returns nothing. The best I have so far is shown below (this is from VBA) although lstOther5 only has one of the columns I am looking up whereas there are 5 in total for the data in 5 other tables.
So, to recap, the user chooses one of the list of Coupling_Order_Codes, this returns the ID for all rows that contains the Coupling_Order_Code. From these IDs I want a list box to show the data held in all of these rows, including the lookups to the associated tables.
Dim GvarCPLG_Ord As String
Private Sub cbCPLG_Ord_AfterUpdate() GvarCPLG_Ord = Me.cbCPLG_Ord
Me.lstOther3.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.FW_No, ALLENGPUMP.FLANGE_MOUNTING, ALLENGPUMP.SHAFT_DETAILS, ALLENGPUMP.SHAFT_LENGTH FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))" Me.lstOther3.Requery Me.lstOther4.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.COUPLING_REF, ALLENGPUMP.Coupling_Order_code, ALLENGPUMP.DRAWING_No FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))" Me.lstOther4.Requery Me.lstOther5.RowSource = "SELECT ALLENGPUMP.ENGMOD_ID, ENGMOD.ENGINE_Model FROM ENGMOD INNER JOIN ALLENGPUMP ON ENGMOD.ID = ALLENGPUMP.ENGMOD_ID WHERE (((ALLENGPUMP.ID) = (SELECT ALLENGPUMP.ID FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """)))))" Me.lstOther5.Requery End Sub
Any help at all would be very, very, very much appreciated. Thanks, Brodie
Is is possible to use the "LastOf" function to show the last (n) records in a query? Where (n) is asked for each time.
Example query show me the last 3 records...
SELECT tblEvent.eventID, tblEvent.event, Last(tblTransaction.transaction) AS LastOftransaction FROM tblEvent INNER JOIN tblTransaction ON tblEvent.eventID = tblTransaction.eventid GROUP BY tblEvent.eventID, tblEvent.event;
Basically I'm trying to filter records from a view in my listbox based on a value in a text box. As such...
Private Sub Form_Open(Cancel As Integer)
Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = " & Form_frmMain.txtName
End Sub
**Note that this the value I'm trying to pull is from a seperate form. My statement works fine as long as I don't use a variable ie. Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = 'Agnew' "
I'm sure it may be a simple syntax problem but I fairly new at this. Any help would be appreciated.
hello every one, first, im apologize for my English. I have form that runs until the user answer the all the questions. I place the last question Id in listbox. do I can place the result of the query (max of question id) in a variant in VB or I must place it in listbox/combobox/textbox?
I have a saved Query that returns a single line and a single field. I want the data in that field to be a VBA var, or straight into a field in a different table.
We're using MS Access 2003 on a MS Access 2000 database file. There are several queries, tables, forms within the database. When we dclick a form the "Enter Parameter Value" dialog box appears requesting input for "Enter Job ID#". The dialog box appears to be popping up due to settings within another query that is systematically called. We oblige and are taken to the results. When we click a dropdown box it is listing all the items in the database but it isnt filtering the contents based on the earlier provided parameter value as we'd hoped.
If I modify enter Design View and modify the dropdown box's RowSource SQL statement to include a WHERE clause all is fine but the provided value is fixed as opposed to a variable.
Is it possible to use the parameter value input from the dialog box as a variable for my dropdown box RowSource? I;ve already tried [MyExternalQuery].[Enter Job ID#] as a variable but no luck -- i just get another dialog box asking for input.
I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....
1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages
With that I am stuck in step 1
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.
Code: Dim WellID As String Dim strModel As String Dim strSQL As String Dim rst As DAO.Recordset WellID = Forms!f_Customer_Lookup.Well_ID MsgBox WellID ' testing to see if it picks up the correct box in form strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _ "FROM Customer_Call " & _ "WHERE Customer_Call.[Cus_Well_ID] = WellID;" Set rst = CurrentDb.OpenRecordset(strSQL) strModel = rst!Cus_Well_ID rst.Close MsgBox rst ' Testing to see if the strSQL captured the data Set rst = Nothing End Sub
I have a query that has three fields: 1) Days 2) Bit Size 3) Feet. The Days field has a date range specified in the Criteria that comes from two unbound fields on a form. I have a second query based on the first: Two fields are Days with the Total as Min and Max. Three fields are Feet with the Total as Min, Mac, Avg. One field is Bit Size with the Criteria as a specified bit size (i.e. 6.125). These queries are used on a report, and all data is inputted from forms prior to the report.
The queries work as expected with a specified bit size. My problem is that while Bit Size is normally chosen from a list of sizes, a custom size can be inputted. I am unsure how I can specify the Criteria in Bit Size to be a custom size..? To compound things further, more than one custom size can be used.
A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)...
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.* FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF")) ORDER BY STATUSHISTORIE.PROBLEM_ID, STATUSHISTORIE.STATUSDATUM;
I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:
SELECT STATUSHISTORIE.* FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR] WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF")) ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];
My two public functions that return the global variables look like this:
Public gstrE As String 'global variable: contains E used for query Public gdatKW As Date
Public Function ReturnE () ReturnE = gstrE End Function
Public Function ReturnKW () ReturnKW = gdatKW End Function
The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam? :)
