Open query recordset (1 Viewer)

[cah1982]

Hi,

Ive been using the following line that works fine when the query isnt linked to the front page

Set rs = CurrentDb.OpenRecordset("UkMailDataNotDelivered")

but now i need it to use 6 details from the home form.

I think the 2 options are to load the query with the 6 variables or add them to the query first.

i couldnt get this to work thou, so any advice is appreciated

Set rs = CurrentDb.OpenRecordset("SELECT UkMailData.ID, UkMailData.Status, UkMailData.Deleted, UkMailData.Status2, " & _
"UkMailData.AccNumber, UkMailData.Customer, UkMailData.Address1, UkMailData.Address2, " & _
"UkMailData.[Town/City], UkMailData.County, UkMailData.PostCode, UkMailData.ParcelRef, " & _
"UkMailData.CustomerRef, UkMailData.DateSent, UkMailData.FileLocation, UkMailData.Reason " & _
"FROM UkMailData " & _
"WHERE (((UkMailData.Status) Is Null) AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') AND ((UkMailData.Customer) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text127] & '"*"') AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] " & _
"And (UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128])) OR (((UkMailData.Status) " & _
"Not Like '"Delivered"' And (UkMailData.Status) Not Like '"Delivery Rearranged*"') " & _
"AND ((UkMailData.Deleted)='"No"') AND ((UkMailData.AccNumber) Like '"*"' & [Forms]![MAIN MENU]![Text124] & '"*"') " & _
"AND ((UkMailData.Customer) Like '"*"' & [Forms]![MAIN MENU]![Text126] & '"*"') AND ((UkMailData.PostCode) " & _
"Like '"*"' & [Forms]![MAIN MENU]![Text125] & '"*"') AND ((UkMailData.ParcelRef) Like '"*"' & [Forms]![MAIN MENU]![Text127] " & _
"& "*") AND ((UkMailData.DateSent)>=[Forms]![MAIN MENU]![Text129] And " & _
"(UkMailData.DateSent)<=[Forms]![MAIN MENU]![Text128]));")

 

[mh123]

try opening a query in design view, making the query in there and testing it to make sure it works then when it does go to SQL view and grab that and move it across VBA.

Alternatively paste your vba into the immediate window like sql = "Select UKmai.... etc" then ?ssql to get the query info and try paste that into a query and it should throw some useful errors at you.

I'd like to be of more help but staring at your filters at this hour I can't make sense of them!