By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Quick Form Recordsource question

P: n/a
I'm having brain fade today...

Can the recordsource in a Form's properties do more than one select
statement based on criteria?

On a popup subform I'm trying to display matching records from a table if
MarketPlaceID on the parent form (frmCustomerOrders) matches MarketPlaceID
from this table (tempOrders). If there are no matches I want it to display
data from this table where there is nothing in the OrderID field.

I can't seem to combine these two statements in the recordsource, separately
they would look like this....

SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
));

or else:

SELECT [tempOrders].* FROM [tempOrders] WHERE ((([tempOrders].OrderID) Is
Null));

I'm thinking that maybe the recordsource properties isn't the correct way to
achieve this kind of filtering?

Thanks

Alan
Oct 19 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Ben
Why don't you just set the recordsource with the onload event of the
popup form?

if isnull(forms!frmcustomerorders!marketplaceid) = true then
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].OrderID) Is Null))"
else
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE >
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
))"
end if

Or something like that...

Alan wrote:
I'm having brain fade today...

Can the recordsource in a Form's properties do more than one select
statement based on criteria?

On a popup subform I'm trying to display matching records from a table if
MarketPlaceID on the parent form (frmCustomerOrders) matches MarketPlaceID
from this table (tempOrders). If there are no matches I want it to display
data from this table where there is nothing in the OrderID field.

I can't seem to combine these two statements in the recordsource, separately
they would look like this....

SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
));

or else:

SELECT [tempOrders].* FROM [tempOrders] WHERE ((([tempOrders].OrderID) Is
Null));

I'm thinking that maybe the recordsource properties isn't the correct way to
achieve this kind of filtering?

Thanks

Alan
Oct 19 '06 #2

P: n/a
Ben
I had another thought...if you don't want to deal with actually making
any code you could possibly make something like this query work:

select *
from temporders
where orderid = iif(isnull(forms!frmcustomerorders!marketplaceid) , "",
forms!frmcustomerorders!marketplaceid)

The only thing is the "", I'm not sure if that is the same as null or
not.

Ben wrote:
Why don't you just set the recordsource with the onload event of the
popup form?

if isnull(forms!frmcustomerorders!marketplaceid) = true then
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].OrderID) Is Null))"
else
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE >
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
))"
end if

Or something like that...

Alan wrote:
I'm having brain fade today...

Can the recordsource in a Form's properties do more than one select
statement based on criteria?

On a popup subform I'm trying to display matching records from a table if
MarketPlaceID on the parent form (frmCustomerOrders) matches MarketPlaceID
from this table (tempOrders). If there are no matches I want it to display
data from this table where there is nothing in the OrderID field.

I can't seem to combine these two statements in the recordsource, separately
they would look like this....

SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
));

or else:

SELECT [tempOrders].* FROM [tempOrders] WHERE ((([tempOrders].OrderID) Is
Null));

I'm thinking that maybe the recordsource properties isn't the correct way to
achieve this kind of filtering?

Thanks

Alan
Oct 19 '06 #3

P: n/a

"Ben" <bm******@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...
Why don't you just set the recordsource with the onload event of the
popup form?

if isnull(forms!frmcustomerorders!marketplaceid) = true then
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE
((([tempOrders].OrderID) Is Null))"
else
me.recordsource = "SELECT [tempOrders].* FROM [tempOrders] WHERE >
((([tempOrders].[MarketPlaceID])=[Forms]![frmCustomerOrders]![MarketPlaceID]
))"
end if
Ben, you are a gentleman and a scholar...thanks! I've got it to work with
tweaking the code you posted. Haven't previously done much work with
recordsources in form code but I'm understanding it a bit better now, thanks
again.

Alan
Oct 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.