Post reply

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

  • 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

  • December 16, 2011 at 7:19 am

    #1422495

    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.

  • December 16, 2011 at 7:33 am

    #1422501

    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 ?

  • December 16, 2011 at 7:38 am

    #1422504

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

  • December 16, 2011 at 8:32 am

    #1422561

    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 ?

  • December 16, 2011 at 8:43 am

    #1422569

    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.

  • December 16, 2011 at 8:53 am

    #1422578

    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 ?

  • December 16, 2011 at 8:57 am

    #1422580

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

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

  • December 16, 2011 at 9:17 am

    #1422591

    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.

  • December 16, 2011 at 9:42 am

    #1422607

    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" ;-).

  • December 16, 2011 at 9:53 am

    #1422614

    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

  • December 16, 2011 at 10:09 am

    #1422622

    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.

  • December 16, 2011 at 10:09 am

    #1422623

    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.

  • December 19, 2011 at 3:27 am

    #1423016

    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 ?

  • December 19, 2011 at 4:37 am

    #1423030

    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.

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

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