Include condition in the combo box in a form (MS Access 2007)

Question

Post reply

Include condition in the combo box in a form (MS Access 2007)

pwalter83

SSChampion

Points: 14575
More actions
December 16, 2011 at 7:14 am

#247827

Hi,
I have a requirement to include a condition in the combo box of the form which is as follows(SQL):
[MG_SERVICE].[DELETED_FLG] = 'N'
What I have done is right clicked on the combo box and chose - 'Build Event' and then in the Expression Builder added the following code:
=[dbo_MG_SERVICE]![DELETED_FLG]='N'
But it doesn't work and the combo still lists all the values. I only need to include the values where
[MG_SERVICE].[DELETED_FLG] = 'N'
Does someone know how to go about this ? Any suggestions would be appreciated.
Thanks,
Vishal

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Just add it in the query (datasource).

I've had issues in the past (2000, 2003). Not sure if it was me or access but that solved it.

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Ninja's_RGR'us (12/16/2011)
Just add it in the query (datasource).
I've had issues in the past (2000, 2003). Not sure if it was me or access but that solved it.

Thanks for your reply Ninja, however, this is the first time I have touched access and dont know where to go to access query(datasource) as you mentioned.

Is it supposed to be in the Properties section of the combo box ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This is for 2007, but it hasn't really changed since 97.

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Ninja's_RGR'us (12/16/2011)
This is for 2007, but it hasn't really changed since 97.

I did what you told me and created a query and when I run it int he query builder it shows the correct values, but when I view the form it shows a blank. Do I need to do something else ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Is the data on access or sql server?

If the latter you can run a trace to see what access is sending to sql server.

If the former, go step by step in your code. Something is messing what you did.

Also you might want to check the columns count & width in the combo.

pwalter83 SSChampion Points: 14575 More actions · Answer 6

Ninja's_RGR'us (12/16/2011)
Is the data on access or sql server?
If the latter you can run a trace to see what access is sending to sql server.
If the former, go step by step in your code. Something is messing what you did.
Also you might want to check the columns count & width in the combo.

Thanks Ninja, this is really getting frustrating now...I am using a form in MS Access 2007, this is the code I have created:

SELECT dbo_MG_SERVICE.SERVICE_DSC FROM dbo_MG_SERVICE WHERE (((dbo_MG_SERVICE.DELETED_FLG)='N')) ORDER BY dbo_MG_SERVICE.SERVICE_DSC;

and this should bring all the values in SERVICE_DSC column where DELETED_FLG)='N' but all I am getting is a blank...I have checked the combo box width and it is okay to hold the values and also it displays all the values without the condition.

Do I need to mention anything in the Control Source ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

try redoing the combo alone on a brand new blank form.

So is it access or sql server (as the datastore)?

pwalter83 SSChampion Points: 14575 More actions · Answer 8

Ninja's_RGR'us (12/16/2011)
try redoing the combo alone on a brand new blank form.
So is it access or sql server (as the datastore)?

I am using sql as the data source. The values actually appear in the combo box when no condition is specified. Its only when I restrict the values through the condition that the combo box shows up as blank.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Time to run profiler and see what access sends to sql server. Debug that in SSMS then bring it back to access once it works in SSMS.

Keep in mind that access converts stuff differently. 0, N, false al convert to ". I don't think this is the issue here, but "that exists" ;-).

pwalter83 SSChampion Points: 14575 More actions · Answer 10

Ninja's_RGR'us (12/16/2011)
Time to run profiler and see what access sends to sql server. Debug that in SSMS then bring it back to access once it works in SSMS.
Keep in mind that access converts stuff differently. 0, N, false al convert to ". I don't think this is the issue here, but "that exists" ;-).

I dont know how to run a profiler from access now..i have been told before but I am not sure how to do this thing...really frustrating

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

pwalter83 (12/16/2011)
Ninja's_RGR'us (12/16/2011)
Time to run profiler and see what access sends to sql server. Debug that in SSMS then bring it back to access once it works in SSMS.
Keep in mind that access converts stuff differently. 0, N, false al convert to ". I don't think this is the issue here, but "that exists" ;-).
I dont know how to run a profiler from access now..i have been told before but I am not sure how to do this thing...really frustrating

Profiler can be compared to a network sniffer. You point it at the machine you need to see the activity on. In this case your sql server.

Start a default trace and start playing with the GUI. There's nothing I can do for you to make you learn this. It's an invaluable tool you need to master to do your job well.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

P.S. Just make sure to not leave it on once you're done, it requires a lot of ressources and in a very worst case scenario it can slow down (halt) the server.

pwalter83 SSChampion Points: 14575 More actions · Answer 13

Ninja's_RGR'us (12/16/2011)
P.S. Just make sure to not leave it on once you're done, it requires a lot of ressources and in a very worst case scenario it can slow down (halt) the server.

Thanks Ninja, I was able to use the query to list the values in the combo box. However, now I am stuck on another issue.

Based on the values displayed in the combo box by the user, I need to autopopulate 2 different text boxes. I cant seem to find any place to write a code in the text box to achieve the same.

Would you have any idea on how to go about this ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Add the 2 columns in the select that fills the combo, change the # of columns property in the combo. Set the witdh for those colums to 0;

So it might look like this

0;3;0;0 (assuming column1 is a pk, 2 is description/name and the last 2 need to be hidden).

Then check the combo's event. I think it's in the afterupdate that you need to do this but I'm not 100% certain.