[AccessD] Need some Simple SQL help

Robert,

Try putting the query into the querybuilder and  see if it work.
As below.

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

And enter the LCID in manually ' WHERE Customer.[Customer ID] =" & lCID

If the query builder shows  it ok, then put the parameter in using, say
"Forms!MyForm!lCID" and test it again, if still ok, save it as as a querydef
and use that as the recordsource.


May work.

Max







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