473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Quick Form Recordsource question

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
3 1770
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
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the...
2
by: Almir | last post by:
I have a simple problem, i just can get a grasp on it. I designed a database for inventory of computer equipment. Now i created forms for each table. Each piece of equipment is going to come in an...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
4
by: Paul | last post by:
I have a basic form. On it are two sub forms. The top sub form has a list of records. When a user clicks on a new row within the top sub form. I want the RecordSource of the bottom sub form to...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
7
by: Stu | last post by:
Hi, I have a combobox who's values change the recordsource of the form. Within this form, there is a subform, whos records also need to change pending the value in the combobox. I am able to get...
15
by: kokostik | last post by:
I did a few searches, but couldn't come up with a clear answer to my question. So here it is: If I am in an open form (MyForm1), is it possible to set the RecordSource of an un-opened form...
2
by: Robert | last post by:
I am trying to give the user dynamic search capabilities to select almost any record in the database from criteria they select. Everything seems to work except when I open the display form to...
8
Megalog
by: Megalog | last post by:
Hey guys.. my turn to ask a question: I'm having a weird issue with a form I've reworked. This form has a combo box, which when used is changing the recordsource of a subform. This subform has...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.