Modules & VBA :: Using Recordset Inside SQL Statement?

Jun 19, 2015

Is it possible to use recordsets inside an sql-statement how described in following example. the error message: access can't find the table or querydef.

Code:

public function useRS (RS_ext_1 as DAO.Recordset, RS_ext_2 as DAO.Recordset) as DAO.Recordset
dim sql_RS_int as string
dim RS_int as DAO.Recordset

sql_RS_int = "SELECT * FROM RS_ext_1, RS_ext_2 WHERE col1_ext1 = 1 and col1_ext2 = 5"
set RS_int = CurrentDB().OpenRecordset(sql_RS_int)
set useRS = RS_int.Clone

end function

View Replies


ADVERTISEMENT

Forms :: Recordset Of Subform Inside Another Subform Which Is Inside A Mainform

Dec 16, 2013

I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.

Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList

Here is my code:

Code:

Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If

I am getting this error: Object doesn't support this property or method

View 1 Replies View Related

For Statement With Space Inside

Jul 27, 2007

My following sql statement is wrong. "Scenairovalues Step02" is the table name. How should I write this one with the empty space in the name?

vtSql = "Select * from Scenairovalues Step02"

Thanks a lot in advance!

View 2 Replies View Related

Forms :: Object Missing Inside IF Statement

Mar 28, 2015

Code:
Sub ClearDeck()
Dim i As Integer
Dim ToStay As Variant

'Because Access will not allow a Frame and all it's contents to set Visible = False
'Remove all Frame Controls except those to keep outside our Frame

[Code] ...

I'm getting RunTime Error 424 Object Missing on the line inside the if statement. Although it is getting .Controls(i).Name correctly and I would have thought Me is an Object? I also tried the complete Form name but still got the error.

View 1 Replies View Related

Modules & VBA :: How To Extract Recordset From Subform Into Recordset Object

Aug 14, 2015

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

View 4 Replies View Related

Queries :: Recordset Retrieval Using If Statement (With And)

Feb 24, 2014

Why I getting an error when trying to run the below code ? If I take out

Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .

add "And" to

If Me.ClientNameList = rs.Fields("[Client Name]") . . .

I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.

The code follows:

Private Sub ClientNameList_Click()
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Progress Tracking")

[Code] .....

View 14 Replies View Related

SQL Statement For ADODB Recordset.filter Method

Feb 8, 2007

I want to use the NOT IN operator for the filter method of ADODB.recordset in VBA. Do anybody in the forum know the syntax.

View 3 Replies View Related

Modules & VBA :: Can Use Result Of One Recordset For Other Recordset

Jul 7, 2013

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?

View 1 Replies View Related

Modules & VBA :: Searching For Text Or Comment Inside PDF Files

Jun 21, 2014

I just want to know if i can create a vba code or a module that will search text or comments in specific table that has attached pdf file.

View 1 Replies View Related

Modules & VBA :: Display Particular Record Inside Of A Tab Located On Popup

Jan 7, 2014

What is the syntax to display a particular record inside of a tab located on a pop-up?

In other words,

I have a pop-up called People_Enter.

On People_Enter is a tabbed control called TabCtl89

On tab (2) is a subform called company_contacts_attachcompany that displays a Single Form record.

I want to open that particular Single Form where EntryId = value

I know how to assign the Where but not how to call the form.

View 4 Replies View Related

Modules & VBA :: How To Access Description Inside Form Error Event

Jul 31, 2013

I'm trying to extract information from Err.Description within a form's Error event. Alas, Err.Description does not seem to be available from there.I have a form that normally displays in DataSheet view. If a trigger on the Oracle back-end raises an error, I want my Access app to be able to parse out Err.Description and deliver a more user-friendly message than ODBC's message. I want to get the info about the message from the error coming back from Oracle, not by matching up error codes.

If I change the form to Single-Form view and put a Save button on it, I can capture Err.Description in the command button's Clicked() event, and then parse out what I need.So if I must display this form in Datasheet view, where can I trap the ODBC error and display my user-friendly message?

View 5 Replies View Related

Modules & VBA :: Access Textbox And Button Inside Form Of A Frame Of A Webpage

Oct 9, 2013

I am writing a macro to automate the filling up some data from excel to company website. I have changed the website name in this post for data protection purpose. The excel will login for different clients by using the combination of username and password for respective clients and then some data are required to be inserted in a text box on a web page, I think the text box is on a form and form is within an iframe, within the web page. Once the data is inserted into text box, one button (Submit), which is also on the same form, is to be clicked.

On the click of a button, the updated data appears on another section, I could not make out if it is an form or frame, which is under the abovementioned form. Once we are happy with the way data appears on the web page, we have to click another button (Update), which is on the same section, to finally updating the data on website.

I wrote the following code to login to the website and then to navigate to the web page where I have to fill up the performance numbers in a text box. The first problem is how to access the text box inside the form from VBA so that the macro can write a number in that text box and how to access the button to submit the data. The HTML code, which can be seen on click of F12, is attached below. The second problem is how to access the Update button inside the other section, so that the data will be finally uploaded.

Code:

Sub LoginToCorpAccount()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

[code]...

<div id="ssc-consumers-holder"/> -

This the line which gets highlighted when I click on the section where the data appears after clicking the submit button, and from here Update button is to be clicked.

View 4 Replies View Related

Modules & VBA :: Insert HTML File Into Body (inside Loop Email)

Apr 30, 2015

What I want to do is inset a html file into the body of the email . I have created a publisher file and saved as html ..say

C: estpub1.html

How do I get this in to the body ?

Code to date below (not the tidiest - but it works)

Private Sub Command0_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim OutApp As Object
Dim OutMail As Object
Dim strAttach1 As String

[Code] ......

View 9 Replies View Related

Modules & VBA :: Open Folder Nested Inside Main Folder?

Mar 16, 2015

I'm trying to open a folder based on a BIN nr. This folder could be in a main folder that has diferent subfolder. As there where differnt naming used to create the folderes, one of the things they have always is the BIN nr. It alwasy start with a unique number and maybe I could use it to scan the subfolders and open that one that the BIN nr is equal as in the field BIN.

Now we have serveral 1000th of folders and finding them takes time.

View 3 Replies View Related

Modules & VBA :: EOF Is Not Being Recognized In Recordset?

Mar 18, 2014

I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.

I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.

Code:
Private Sub SelectTblMyMedButton_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)

[Code] .....

View 3 Replies View Related

Modules & VBA :: Looping Through A Recordset

Jul 8, 2014

I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...

My question is a followup to a thread that was opened on this forum: URL....I want to do the following:

1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)

Code:
sub loopandfix()
on error goto errorhandler:
strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name

[code]....

View 9 Replies View Related

Modules & VBA :: Cannot Instantiate Child Recordset

Jun 17, 2013

I am trying to open a child recordset but keep getting Error 424 "Object required".

Dim rstEmails As DAO.Recordset2, DBS As DAO.Database, rstFile As DAO.Recordset2
Set DBS = CurrentDb
Set rstEmails = DBS.OpenRecordset("Beldenemails")
Set rstFile = rstEmails.Fields("email").Value 'Error 424 here
End Sub

I've tried everything I can think of.

View 4 Replies View Related

Modules & VBA :: Loop Through SubForm RecordSet?

Jun 4, 2014

Is there a way of looping through a non update able continuous subform, and using information from each line.

Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?

View 3 Replies View Related

Modules & VBA :: Extracting A Field From Recordset?

Nov 11, 2014

Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.

Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = Nothing

View 4 Replies View Related

Modules & VBA :: How To Remove ADO RecordSet From ListBox

Aug 6, 2013

How does one go about removing a recordset from a ListBox?

I have a list box that I want to toggle between using a query and an ADO RecordSet to populate the values.

Once I set the listbox .RecordSet property to the ADO.Recordset, I can't remove the values displayed in the listbox when I assign a query to the .RowSource property.

I suppose I can turn the .RowSource query to an ADO Query but I am being lazy and don't want to rewrite the query as a T-SQL query.

I previously thought the list box was pulling data from the .RowSource query but I realize I was wrong.

View 1 Replies View Related

Modules & VBA :: Create Recordset From List Box

Sep 3, 2014

Is it possible to create a record set from a list box?

I have two list boxes list1 (customers) the can transfer records to list2. I want to take all records from list2 and use it to open a report, using customer id as where clause in my docmd.openreport statement.

View 4 Replies View Related

Modules & VBA :: Drop Down List From Recordset

Aug 21, 2013

As I am moving through my code, I'd like there to be a pop-up box which asks the user to choose from a list of dates. This list of dates only resides in a filtered recordset in the background.

Once the date is chosen, then my code continues onward..I know I could make some sort of pop-up form, but I'd rather not have to go in a design all that just for a list of dates...

1) Is there such a thing as a dropdown list on a Input box?

and even if there isn't...

2) Is there a way I can bind the column in a recordset to a dropdown list?

View 2 Replies View Related

Modules & VBA :: Looping Through Form Recordset And Looking Up Value

Jun 18, 2014

Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table

Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?

Code:
mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""

View 4 Replies View Related

Modules & VBA :: Query Object To Recordset

Oct 14, 2013

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.

View 5 Replies View Related

Modules & VBA :: RecordSet - Nothing After SQL Query Returns Value

Jun 11, 2014

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)

View 3 Replies View Related

Modules & VBA :: Why RecordSet Not Starting From Record 1

Sep 1, 2014

Why my Recordset start from record # 301 instead of # 1 Here's part of my code:

Set db2 = CurrentDb
Set rst2 = db2.OpenRecordset(strTable2)
If rst2.RecordCount = 0 Then
MsgBox "No records to process."

[Code] ....

Table has 12,000 records and the first record has an ID of 1. So why is it starting from the record 301? What am I doing wrong?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved