Hi, I know MS Access quite but not much. I am using Access 2000.
What I want to do is to use DLOOKUP Function to find a value stored in a recordset defined in VBA. I successfully did this task by creating a query.
You will say if I have done this then what is the problem. Dear I have to create as many queries as NO of forms as this query is required on every form on a controll when it is double clicked. Thus I want to create a function with my parameter which is "Date" of that form.
For testing I created this sub on my form but got an error .
Error message says
****Run Time Error '3061'
****Too Few parameters. Expected 1
I really am not able to get why this error is coming as I copied and pasted this SqlString created by MSAccess from my successful query .
==================================
Dim rstTemp As Recordset
Dim sqlStr As String
sqlStr = "SELECT tblJournal.Account AS AccID, Sum(tblJournal.amtDebit) AS Dr, Sum(tblJournal.amtCredit) AS Cr, tblCOA.OpBalDr, tblCOA.OpBalCr, [OpBalDr]+[Dr] AS Debit, [Cr]+[OpBalCr] AS Credit, [Debit]-[Credit] AS Bal FROM tblCOA INNER JOIN tblJournal ON tblCOA.accNum = tblJournal.Account WHERE (((tblJournal.Date) <= " & Me.Date & " )) GROUP BY tblJournal.Account, tblCOA.OpBalDr, tblCOA.OpBalCr;"
Set rstTemp = CurrentDb.OpenRecordset(sqlStr)
xx = DLookup("[Bal]", "rstTemp", "AccID=1010")
MsgBox xx
rstTemp.Close
================================
Please tell where I am wrong and how can I perform this task in a rather easier way.
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
Hi...hope someone can help. I'm a bit new to the programming scene and need some help with a recordset.
The current code that I am using is:
Private Sub Command126_Click() 'September - 2006 - Create Application Form. Dim appWord As Word.Application Dim NoPrompt Dim rstContacts As Recordset Set rstContacts = Me.Recordset
Set appWord = GetObject(, "Word.Application")
With appWord .Documents.Add "C:Documents and SettingsAdministratorMy Documents est.doc" .ActiveDocument.ShowSpellingErrors = False .Selection.Goto wdGoToBookmark, Name:="test" End With
Do Until rstContacts.EOF
appWord.Selection.TypeText rstContacts!ContactName & " " rstContacts.MoveNext Loop End Sub
I want a situation where recordset information can be 'fed' to a word document depending on what is written in control boxes on the form. So, for example if there are three options in a Combo Box, namely:
Option 1, Option 2 and Option 3
I want information from the 'ContactName' control box to be inserted in the 'test' document at specific 'bookmarks' for all forms where 'Option 1' is selected in the Combo Box.
Hope this makes sense....I think I'm getting close but still no joy
Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?
Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?
For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
I have a database in which the tables are located in one MS Access file, and the queries, forms, reports, etc. are all located in a seperate MS Access file (MS Access 2003). The MS Access file that stores the tables, I call the "back end file".
I got a call regarding the database, and found that some how the back-end file got corrupted. When I want to open the file, I got the error...
"Database is not in a recognized format.... You must repair the file".
So I clicked "Yes" to compact and repair the back-end file, and the database worked correctly for me.
All except one query. I have a query that pulls in the main table of the database and then one of the subform queries. Linked on the main table primary key, it is a pretty simple query. This query has always been used to update data for the sub-form.
Today, after the compact and repair job, this query became a locked record set. I cannot add, edit or delete from the records in this query.
I can add, edit and delete from both the tables in the query, just not from the query itself. To make things even more strange, when I try to edit, I don't get the message at the bottom of the query stating "This recordset is locked".
I am just wondering if anyone has experienced this, and if there is something more trouble some brewing. If so, is there anyway to fix my query? Is my database back end file still corrupt (I have compact and repaired it since then). I even copied every table from the back-end file into a new MS Access database file, and I still get the same error with the query.
I've been banging my head against this problem for a while now and making no progress and am hoping someone cleverer than me can provide some assistance in solving it, or point me in the right direction.
Here goes....
I am working solely in the VBA environment and would like to perform an automated process that returns a recordset based on the results of a previously created recordset (assuming of course it is possible in the first instance.)
The catch is I would like to perform an INNER JOIN using the results held within a recordset. I have already achieved this by creating a temporary table (using a SELECT INTO statement) and then removing the temporary table when I am finished but find this is a slower process than I would like.
I have three tables: Table A - Fields: GROUP, NAME, DATA1, DATA2 Table B - Fields: GROUP, NAME, , CITY, DATA3 Table C - Fields: CITY, DATA3
I have the following declarations:
Dim db As Database Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset Dim strSql As String
Set db = CurrentDb
Next, I select a specific range I am interested in. For the purpose of this example, I am only selecting GROUP values of 1,2 and 3.
strSql = "SELECT * FROM A WHERE A.GROUP IN (1,2,3)" Set rs1 = db.OpenRecordset(strSql) rs1.MoveFirst
Now, for each record returned in the query above, I would like to process as follows:
The rs2 query returns the results of that query into what I though was a memory resident table referred to as "jc". (This is most likely where I am going wrong!) Using this reference, I perform another query...
And from this point I would be able to process the results (if any) that I need to....
...if it wasn't for the error: "The Microsoft Jet database engine cannot find the input table or query 'jc'. Make sure it exists and that its name is spelled correctly."
I know the table doesn't 'truly' exist as a physical object or entity within the database, so how do I reference this 'phantom' table in a query?
I am happy to provide more details if it would help solve this problem and would really appreciate any feedback that anyone has to offer.
i want to open a recordset, in its 'where' clause i want to pass the value which i entered in Form textbox? is this possible ?
COde: rs.open "select * from table1 where id=form.textbox",con
i have written general code above. when i run my application ..it gives error "Control which is focus can only be reference" how can i pass my value to select query ?
I am in the middle of creating a function that populates two unbound text boxes on a form and then uses those unbound textboxes in a query (Total of 3 unbound text boxes - 2 are populated from this function). The saved query object is working fine when I manually execute it (after the unbound text boxes have been populated). However, when I go to set the same query to a recordset I am getting the "Too Few Parameters. Expected 3." error message.
In the saved query I used the build function to use the unbound text boxes as part of the where clause. Below is the code I am trying to execute:
PHP Code:
Public Function Test()Dim db As DAO.DatabaseSet db = CurrentDbDim rst As DAO.Recordset Dim DtBegin As String''FInd the Begin dtstBegin = DateAdd("q", -1, DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3 + 1, 1)) Dim DtEnd As String''find the end dateDtEnd = DateAdd("Q", DateDiff("Q", 0, Date) - 1, 0)
[Code] ......
The unbound text boxes are populated before the query is set to the recordset.
I'm trying to assign the result of an SQL query to a variable using VBA in Access. The query returns a value but the variable which it is assigned to has a value of Nothing. Here is the code snippet:
Dim queryReturnID As String queryReturnID = "select dbo_tbl_SupplierReturn.ReturnID from dbo_tbl_SupplierReturn" & _ " where SupplierID = " & lstPOHdr.Column(1) Debug.Print queryReturnID Dim RecordSet1 As DAO.RecordSet Set RecordSet1 = CurrentDb.OpenRecordset(queryReturnID)
I am trying to run this recordset with parameter query and export to excel. Problem I am getting is it is sending the ID of employee name and exporting row wise instead of column wise.
Code: Dim dbMyDatabase As DAO.Database Dim rsMyRecords1 As DAO.Recordset Dim qdMyParameters1 As DAO.QueryDef Dim iIndex As Integer Dim iFound As Integer Dim oExcel As New Excel.Application
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1 Private Sub Save_Record_Click() 'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information") If messageusr = vbYes Then
Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
'Clear fields on form to indicate write has occurred txtserver = "" cmblocation = "" cmbtype = "" cmbsize = "" cmbassign = "" txtrequiredby = "" txtrequestedby = "" txtrequest = "" txtnotes = ""
'Close recordset and database saverecord.Close db.Close MsgBox "This information has been succesfully saved" End 'return user back to form End If
End Sub
and I have even looked into an insert sql statement [B]Method 3 Dim SQL_Text As String SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &" INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;" Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message Run time error '3061' Too few parameters. Expected 3. :confused:
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset Dim strReport As String Set db = CurrentDb() Set qdf = db.QueryDefs("7-ErrorsReport") qdf.Parameters(0) = Forms!frmmain!dt1.Value qdf.Parameters(1) = Forms!frmmain!dt2.Value qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.
Code:
Sub TestIt2() Dim strFileName As String Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("QryUpdateRevisionHistory")
I have a Product form that shows the details of each of the Products in my Product table. I also have a "Pick a product" combobox (who's rowsource is being fed by the same Products Query that feeds the form). This allows you to go straight to a particular product by selecting its name. So far, so simple.
I've since added a couple of combobox filters which use different columns ("Product Type" and "Collection") to allow the user to get down to a more manageable recordset to then work with.
Of course, because the "Pick a Product" combobox is being fed by the Products Query, the combobox can end up showing products that are not contained in a filtered recordset. This is... sub-optimal.
Is there a way of telling the "Pick a Product" combobox to use the products listed in the recordset as its rowsource rather than the product query? So that it only lists the products the user has filtered down to?
I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection Dim recordst As ADODB.Recordset Dim strSQL As String Dim strPath As String Dim appXL As Excel.Application Dim wb As Excel.Workbook
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records: userID / Date u1 / d1 u1 / d2 u2 / d1 u2 / d2 u3 / d2 ... 1st Select Query (where Date = d1): u1 / d1 u2 / d1
Desired 2nd Query based off 1st Query (where ?): u1 / d1 u1 / d2 u2 / d1 u2 / d2
I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.
sql example below. Which is the Access Sql in the Query
