[AccessD] Need some Simple SQL help (Solved)

Ok, Figured it out.. Apparently, while the forms .Filter function works when the form is set to Data Entry = true, the .RecordSource = strSQL does not.

All that to find out that the forms .Filter function is much faster.. :-(

WBR
Robert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert
Sent: Wednesday, March 17, 2010 5:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need some Simple SQL help

Ok,
  Removed the vbCrLf's, but as suspected made no difference.. So strange....

WBR
Robert


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Wednesday, March 17, 2010 8:32 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need some Simple SQL help

Don't see the purpose of the vbCrLf's in a SQL - maybe removing them will do?

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Robert
Sendt: 17. marts 2010 12:57
Til: 'Access Developers discussion and problem solving'
Emne: [AccessD] Need some Simple SQL help

Goal: Try to speed up the filter of a form with 2 subforms

I'm trying to move to a specific record on the main form (and another
un-linked subform) from a record on a subform. The main form is based on a
saved query, and has it's "Data Entry" property set as YES.

I can use this and it works:

    .Filter = "[Customer ID]=" & lCID
    .FilterOn = True

But If I try to use the SQL below and RecordSource it fails.

strSQL = "SELECT Customer.[Customer ID], Customer.[First Name],
Customer.[Mid Init], Customer.[Last Name], " & vbCrLf & _
                "Customer.Address1, Customer.Address2, Customer.City,
Customer.Zip, Customer.HomePhone, " & vbCrLf & _
                "Customer.WorkPhone1, Customer.Ext1, Customer.WorkPhone2,
Customer.Ext2, Customer.CellPhone, " & vbCrLf & _
                "Customer.Ext3, Customer.Phone_Other, Customer.[Customer
Type], Customer.Comment, Customer.Balance, " & vbCrLf & _
                "Customer.[Type of Terms], Customer.[Term Day],
Customer.[Contact person], Customer.[Tax Exempt Status], " & vbCrLf & _
                "Customer.[Tax Number], Customer.[Ship To], Customer.[Uses
PO], Customer.Active, Customer.State, " & vbCrLf & _
                "Customer.Advertizements, Customer.SpecNeeds,
Customer.LocationType, Customer.[Service ID], Customer.MDRID, " & vbCrLf & _
                "Customer.TentPerm, Customer.Landlord, Customer.Directions,
Customer.ExtStaticNotes, Customer.IntNotes, " & vbCrLf & _
                "Customer.Field1, Customer.Field4, Customer.Ext,
Customer.CreatedBy, Customer.CreateDate, Customer.Phone1Type, " & vbCrLf & _
                "Customer.Phone2Type, Customer.Phone3Type,
Customer.Phone4Type " & vbCrLf & _
        "FROM Customer " & vbCrLf & _
        "WHERE Customer.[Customer ID] =" & lCID
        .RecordSource = strSQL

Can you see what I'm doing wrong, and is this faster?

WBR
Robert


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com