Using a combobox to limit another combobox

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

Using a combobox to limit another combobox

Author

Topic

davers
Starting Member

14 Posts

Posted - 2005-04-28 : 13:59:09

Hi everyone. I'll be buying a few books soon, but until I do, I was wondering if I could get a help with my criteria in a view. I'm trying to limit what shows in a combobox based on whats selected in another combobox on the same form. How it looks and works form me in Access is like this:


SELECT tblSRReasonCodes.SRRCID, tblSRReasonCodes.SRReasonCode, tblSRReasonCodes.CategoryCode
FROM tblSRReasonCodes
WHERE (((tblSRReasonCodes.CategoryCode)=[Forms]![tblServiceRequest]![SRReasonCatID]));

However, MSDE has a problem with my Where clause. Can anyone tell me the syntax of using a control on a form as criteria in MSDE?

Thanks for any help and have a good day,

Dave

Windows XP, Office XP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-28 : 14:23:47

Make sure you understand how MSDE works -- it has no relation or knowledge of the MS Access application that it is returning data to or accepting requests from. When MS Access sends a query to MSDE, the context switches from the world of MS Access to the world of MSDE/SQL Server, the SQL is evaluated, and then the results are returned. Thus, if Access sends the expression [Forms]![tblServiceRequest]![SRReasonCatID] to MSDE, MSDE has no idea what [Forms!] means or how to evaluate that expression.

Thus, you must dereference all parts of your query that refer to objects or controls within MS Access and pass the *values* to the remote server. This means dynamically altering the RecordSource property of your combobox doing something like this:


private sub Combo1_OnChange()
  combo2.recordsource = "select .. from ... where ... = '" & combo1.value & "'"
  combo2.requery
end sub

I hope this helps ... really make sure you understand WHY you need to do this and it will save you many headaches.

- Jeff

davers
Starting Member

14 Posts

Posted - 2005-04-29 : 08:07:21

Hi Jeff, thanks for the reply! You got me pointed in the right direction. I wound up using the below code to populate my combo box:


Private Sub Combo40_GotFocus()

Dim strSQL As String

strSQL = "SELECT [SRReasonCode] FROM [tblSRReasonCodes] WHERE (CategoryCode = '" & SRReasonCatID.Value & "');"

Combo40.RowSource = strSQL
Combo40.Requery

End Sub

I've done things like this before...but it's hard to change my whole "Access" way of thinking...I was just beginning to get the hang of Access, and now I have to change my way of thinking... :-/ Actually, I think this is a lot of fun! I've started taking work home with me again!!! :-)

Thanks again and have a good day,

Dave

Windows XP, Office XP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources