[AccessD] Need some Simple SQL help

Hi Robert:

Just a note; why are you using a filter when you are supplying the record
source? 

I would use something like:

' This can be in the Form Load section
Me.recordsource = GetTheSQLFile([CustometrID]) 

Where a Public function like the following would be in a module.

Public Function GetTheSQLFile(strCustomerID as string) as string
  Dim strSQL as String

  strSQL = "SELECT Customer.[Customer ID], Customer.[First Name], " & _
	      ...
	     "WHERE Customer.[Customer ID] = " & strCustomerID & ";"

  GetTheSQLFile = strSQL

End Function

You will have to put a dummy query place holder in the Form recordsource
properties so the Form doesn't get confused... of course you will just be
over-writing it.

HTH
Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert
Sent: Wednesday, March 17, 2010 4:57 AM
To: 'Access Developers discussion and problem solving'
Subject: [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