I'm looking for solution to this. I've already searched in this forum a lot of healines for this problem but none of them really helps me.
I know that We're not suposed to change static Querys and bla bla bla because they static and bla bla bla bla BUT.... the thing is...
I have this form with a lot of filters for a report. I Want to change the join fields and parameters according to what the user chose in the form and then run the report.
The Report Record source property needs to be populated with a query object. to do that I need to some how load the query and change its sql.
Dim Q as QueryDef set q = CurrentDB.QueryDef("MYQUERY")
I don't know what property I can use, MS HELP doesn't help actually when I type "Q." after the dot the list of properties doesn't show.
I have a query that I referenced it to a combo box in a form. In my form I created a combo box which has a list of VendorName and I want my query to opens a VendorName report based on whatever chooses in the combo box so that I use the combo box name in the criteria of the VendorName in my query. Somehow I get an error which says "The Microsoft Office Access database does not organize "[Forms]![Form1]![Vendor_Name]" as a valid field name or expression".
I created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error
SELECT [CashValue Link Query1].Facility, [CashValue Link Query1].[Financial Class], [CashValue Link Query1].Date, [CashValue Link Query1].Date, [CashValue Link Query1].[Total A/R], [CashValue Link Query1].Current,
Code: TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs FROM Q_ALL_qa GROUP BY Month(qaDate) PIVOT month([qaDate]) IN (1,2,3,4,5,6,7,8,9,10,11,12);
This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.
All those queries calculate data for all departments.
In the form, I have placed a combobox.
What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.
Code: WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats
However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.
Where do I place the vba statement? Is it under combobox AfterUpdate event?
I am planning to use this: (As I never done it before) [URL] .... Modifying SQL On-The-Fly section
So I had this code working and then I cleaned it up a little and it no longer works. It should export data from a created query using criteria selected by the user on a form and put it into an excel file that exists. I get no errors but it does not export anymore. After pouring over it for a while checking for mistakes with my form control references and variables I have yet to find anything. I did change my form name and edited the code accordingly after I already had it working, and changed a few form settings but changing them back did not fix the issue. I am not very experienced and stumped since I am not getting error messages.
Code:
'First set variables for the SQL string and CreateQueryDef command Dim strExport As String Dim qdf As dao.QueryDef 'Then define the SQL to be exported (Static Response Info by ItemID)
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
All I want to do is run a make table query (or append) and show the user how many records were processed. If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".
I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.
strquery = "qryEmailGenerate" Set db = CurrentDb Set qdf = db.QueryDefs(strquery) Set rs = qdf.Execute txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf
I have a table I'm trying to query information out of. Key fields are below:
RecNo (Key) ParentRecNo Description
I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.
I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.
Code: Private Sub cmdModifyRecords_Click() On Error GoTo Err_cmdModifyRecords_Click Dim stDocName As String Dim strFilter As String Dim stLinkCriteria As String stDocName = "Modify_OpenItems"
My question is which method of finding the last record is best, QueryDef or Recordset? Here is my data:
Table: tbl_module_repairs
Field: aps_rma
Textbox to insert last record RMA into: txt_test
Here is some code I tried but get an invalid argument msgbox:
''''''''''''''''''''''''''''''''' 'Opens last RMA into textbox (For opening tag sheet) Dim dbs As DAO.Database Dim rst As DAO.Recordset 'Get the database and Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tbl_module_repairs")
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:
Code:
Dim Db As DAO.Database Set Db = CurrentDb Dim QDef As DAO.QueryDef Dim rst As DAO.Recordset Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")
[Code] ....
Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/
After this I'm going to assign the recordset to a matrix but that's a different story!
Please help, how can I refer to the field of a subform in Visual Basic when the (main) form is open?
A) Private Sub supplierID_AfterUpdate() Forms![invoices]![KombináltLista4].Requery End Sub
B) Private Sub supplierID_AfterUpdate() Forms![details1 Segédűrlap]![KombináltLista4].Requery End Sub
These don't work. (The main form: INVOICES; the subform:DETAILS1 Segédűrlap; KombináltLista4 is the name of a combo box)
The version B work, when I open the form and the subform together, but of course I'd like to work only on the form (where the subform is displayed as well, but I can't refer to it...)
I need to change the reference "Microsoft Access 9.0 Object Library" in Access 2000 with the "Microsoft Access 11.0 Object Library" but I can't de-select it 'cause it's used by the program. Another problem is that I tried to import the 11.0 copying the file and when I select it from the panel control it seems to import without problems, but when I search it in the list, the 11.0 doesn't exist. How can I import it?
Hi, I have been designing a database on my computer which seems to be working fine; 2 more users in the company should be able to use the database.In order to see if everything was working properly on other computers, I have put a copy of my db on the server and tried to open it on the other users computer station. Unfortunately, the reference library that I have been updating during the design of the database doesn't seem to apply to the other computers. I have been trying to update it on the other machine but the references on the tool menu in open module is not available. I didn't expect this to hapened and I am not sure what to do, what would be the apropriate method to make the library available to the other computers?
Is there a way to insert a calendar on a form from which a user can select a Month year and day similar to using the calendar on Outlook or Lotus Notes to set up or view appointments or meetings?