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 Replies


ADVERTISEMENT

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 6 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

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Letter Storage & Retrieval

Nov 6, 2006

I am not sure whether this is a problem with MS Access, Visual Basic or Windows.

I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.

Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.

The VB code to store the letters (Save As) is

Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName])
objWord.Application.Activate
SendKeys MessageC

The VB code to retrieve the letters is

Dim MessageE As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
WordApp.Application.Activate
MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc"
SendKeys MessageE
Set WordApp = Nothing
GoTo Exit_Command53_Click

The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.

In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!

Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.

View 2 Replies View Related

Image Storage And Retrieval

Feb 5, 2008

I am trying to build a database for my digital photo collection.
(having pretty much gven up on the packages I see in the market)

Since most of the images reside on CD's and DVD's, I want to create
and store thumbnails and pointers (ie. location fully qualified name),
rather than a copy of the full image.

1. How do I define the fields in the table for the thumbnail, and the pointer?
2. How can I use the pointer value to retrieve the full image?

Any ideas and tips on where to start?

Thanks,

Roy

View 2 Replies View Related

Random Data Retrieval

Jan 30, 2006

Hi,

Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.

Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.

:confused: .

View 1 Replies View Related

Combining 2 Columns For Retrieval

Jan 11, 2008

Hi,

I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?

Thanks

View 1 Replies View Related

How Do I Specify Retrieval Order Of Records

Apr 19, 2005

I'm very confused. I have two tables in my db that should be the same except for columns(I copied one table from the other). In each I have a "default" entry as the first record in the table just to prevent my select queries from erroring out if they don't find a match for the criteria. When I use the following statement,
Code:SQL="SELECT ID, Name, Picture FROM background WHERE Name ='" & Fname & "' OR Name='Default'"
it returns the default value as the record, not the match for "Name", even though there is one(when I remove the OR Name='Default' it pulls the record).

When I use this query for the other table from the same .asp page,
Code:SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'"
it returns the record where "Technician" matches even though it is below "default" in the table.

I want the query to return the "default" record only if there is NO match for the first criteria, which it does in the second case, but not the first. Is there some way to force this? Thanks in advance for any assistance provided! -Chris Gordon

View 4 Replies View Related

Latest Date Record Retrieval

Apr 17, 2008

I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:

SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));


But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:

View 4 Replies View Related

Looking For Advice About Using Access As A Document Retrieval Tool

Mar 1, 2006

Our organization (a University) has been engaged in becoming "paperless."

As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.

The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.

At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).

My question is this: has anyone in this forum ever tried to implement such a solution with Access?

Any advice or insight would be very much appreciated.

Thanks in advance.

View 1 Replies View Related

New Form Instance - Suppress Record Retrieval?

Mar 11, 2005

Hi,

I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:

Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True

The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.

I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.

Any suggestions would be much appreciated.

Thanks,
Keith.

View 2 Replies View Related

Forms :: Unexpected Data Retrieval In Form

Aug 31, 2014

I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.

On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.

However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.

In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...

View 5 Replies View Related

Queries :: Using Recordset In VBA Code With Yes / No Datatype

Apr 18, 2013

I'm a having a problem with a Yes/No datatype. Fundamentally, my code looks like this:

strSQL = "SELECT * FROM tblAddresses " & _
" WHERE YPID = " & Me.Parent.IDNUMBER & " AND CurrentAddress = yes"

Set rs = db.OpenRecordset(strSQL)

Now this statement returns 3 records even though only 1 Current Address exists.

false also returns 3 records
true also returns 3 records
no also returns 3 records
-1 also returns 3 records
0 also returns 3 records
1 RETURNS 0 records!!

If I look at the table in Access, I only have 1 CurrentAddress record for my IDNumber.

View 1 Replies View Related

Queries :: SQL Code - Recordset Is Not Updateable?

Oct 4, 2013

I'm fairly new to SQL and I'm trying to input data into this form I've created but it tells me that "this Recordset is not updateable". Here's my SQL code, I'm not certain this is the problem though.

Code:
SELECT [Annual Instructor Report County Summary].*, [Camps, SS Events, Events, Total Members].*,
[Archery Members].[numArchClubs]+[Archery Members].[numArchNon4H]+[Archery Members].[numArchCamp]+
[Archery Members].[numArchSSEvent]+[Archery Members].[numArchEvent] AS totArchMem, [Rifle Members].

[Code] ....

View 6 Replies View Related

Queries :: Find Value In Recordset Then Update?

Apr 4, 2014

I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.

What I did was:

1. Read tbCOMPANY each row using for loop
2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT
3. If tbCOMPANY.noMatch, it will insert into tbRESULT
4. else it will update the matched row in the tbRESULT

Its been taking like over 30 minutes to process this and still ongoing and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this?

View 3 Replies View Related

Queries :: Recordset Shows Nothing In The Code?

Aug 21, 2013

i have a combo box ,and when i should select "all",this button after update code should show me all the column of table test ,but looks like for no reason the record set shows nothing and its not working.

Code:

Private Sub cboTaskListName_AfterUpdate()
'On Error GoTo cboTaskListName_AfterUpdate_Err
Me.Refresh
Dim db As DAO.Database
Dim SQL As String
Dim rs As DAO.Recordset
If Me.cboTaskListName = "111111" Then
Set db = CurrentDb()
SQL = "SELECT no1 from test"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
End If
End Sub

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 :: Using Query As DAO Recordset Showing Incorrect Results

Apr 26, 2013

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.

View 3 Replies View Related

Queries :: Export Filtered Datasheet Recordset To Excel

Oct 9, 2013

how to grab the recordset after a user has filtered a datasheet and export it to EXCEL with VBA from a Button on a form.

I've figured out how to export a pre-defined query to excel with all the fields I need. I was hoping to be able to grab the filters from the datasheet form and pass them to the query.

I have a main form with a bound Datasheet subform. The export button is on the main form and I need to grab the filtered data from the datahsheet subform.

View 1 Replies View Related

Queries :: Recordset Is Showing As Empty But Query Wizard Returns Records

May 12, 2014

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

[Code] ....

View 5 Replies View Related

Queries :: Why Is SQL Add In AS Statement

Nov 14, 2013

I have an UPDATE query that I would think to be adequately written as follows:

UPDATE (SELECT num FROM DataSrc ORDER BY group, zone)
SET NUM = getnextcounter([num]);

The purpose of the query is to update the value in field [num], by incrementing it sequentially according to the ORDER BY sort order, using get nextcounter(), which simply adds 1 to a counter variable that is first set to 0 (before running the query).When I save the query and re-open it in design view, Access has changed it by adding an AS statement:

UPDATE (SELECT num FROM DataSrc ORDER BY group, zone, name, date, time, datasrc, id) AS [%$##@_Alias] SET NUM = getnextcounter([num]);

It does what I want, but I don't understand why the AS statement is being generated and put into the SQL statement

View 2 Replies View Related

Modules & VBA :: Datasheet Mode Form For Both Data Entry And Data Retrieval

Feb 9, 2015

I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.

<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden

Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False

[code]...

View 3 Replies View Related

Multiple Queries, One Statement?

Aug 15, 2005

Hi all,

I thought the whole point of the ; sign at the end of a sql statement in access was so that you can
run more than one query at a time?
if this is the case, could you tell me why this doesn't work?
Code: sqlStr = "UPDATE tabControlFeeType SET FeeAmount = " & txtFirst.Text & " WHERE KeyFeeType = 1;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtSecond.Text & " WHERE KeyFeeType = 2;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtThird.Text & " WHERE KeyFeeType = 3;" Dim comm As New OleDbCommand(sqlStr, conn) comm.ExecuteNonQuery()
the query doesn't work if I put it straight into access either?

do I just have this wrong?

Thanx

View 2 Replies View Related

Queries :: Using The Value Of Table Row In A Like Statement?

Oct 22, 2014

Is it possible to use a value of a table row in a "Like" statement of a query ? Below is query code that I want to modify to accomplish what I need. I want to search the "BudgetIn (Description) table for records that are like "ExpensesIn (Accounts) - table. I thought that if I entered the value in the "EXpensesIn" (Accounts) table, surrounding by asterisks, would give me what I needed. Unfortunately, the query criteria placed quotes around the critrea and the query looked for text (i.e. "*[ExpensesIn]Accounts*") rather than the value in the table.

SELECT BudgetIn.Description, ExpensesIn.Expenses, BudgetIn.Previous_Budget, BudgetIn.Budget_Year, ExpensesIn.Accounts
FROM BudgetIn LEFT JOIN ExpensesIn ON BudgetIn.Description = ExpensesIn.Accounts
WHERE BudgetIn.Description Like *[ExpensesIn]Accounts*;

View 5 Replies View Related







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