Using combo box as input for field name

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

Other Forums

MS Access

Using combo box as input for field name

Author

Topic

bflemi3
Starting Member

5 Posts

Posted - 2005-04-07 : 16:46:00

HI guys:

I have a SQL statement for a multiple search criteria form that i created. Im having trouble with a portion of the code that takes a selection from one of the combo boxes as the field name of a table.

Here is the code:
tblCompany.[cboSearchDes]=True

this particular cbo is a value list that has only 5 selections to it.

Thanks for the help!!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-08 : 00:13:51

so cboSearchDes is the combo?

What's your SQL statement?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

bflemi3
Starting Member

5 Posts

Posted - 2005-04-08 : 08:45:29

It's awfully long...not sure if it is pertinent but here it is anyway:

SELECT tblCompany.CompanyName, tblCompany.[cboSearchDes], tblClients.ClientName, tblCapabilities.Capabilities
FROM (tblCompany INNER JOIN (tblCapabilities INNER JOIN tblCompanyCapabilities ON tblCapabilities.CapID=tblCompanyCapabilities.CapID) ON tblCompany.CompanyID=tblCompanyCapabilities.CompanyID) INNER JOIN (tblClients INNER JOIN tblCompanyClients ON tblClients.ClientID=tblCompanyClients.ClientID) ON tblCompany.CompanyID=tblCompanyClients.CompanyID
WHERE (((tblCompany.[cboSearchDes])=True) And ((tblClients.ClientName)=cboSearchClient) And ((tblCapabilities.Capabilities)=cboSearchCap));

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 08:56:47

you cannot dereference a combobox's value into a SQL statement like that; that portion of a SQL statement does not allow for expressions.

One option is to use VB code to dynamically create a sql statement and concatenate the value of the combobox into the string, and then assign that sql statement to the object that needs it, whenever the combobox value changes.

For example, on the AfterUpdate() event of your combo box, you'd do something like this:


Private Sub YourCombo_AfterUpdate()
  Dim SQL as String 
  If Isnull(YourCombo) Then Exit Sub
  SQL = "Select tblCompany.CompanyName, tblCompany." & YourCombo.Value & ", tblClients.ClientName, " ' .... etc ......
  
  ' Assign the SQL string to whichever object needs it changed:
  SomeControl.RecordSource = SQL

  ' And be sure to requery that object:

  SomeControl.Requery
End Sub

Alternatively, if your combo has only 5 set values that cannot change, you can use an IIF() function in your query to dynamically choose the column to pull from, and this does not require building SQL strings or changing the properties of object through code, you can keep it static:


SELECT 
   tblCompany.CompanyName,
   IIF(CboSearchDesc = 'Column1', tblCompany.Column1,
       IIF(CboSearchDesc = 'Column2', tblCompany.Column2,
          IIF(CboSearchDesc = 'Column2', tblCompany.Column3,
             IIF(CboSearchDesc = 'Column2', tblCompany.Column4, Column5)
          )
       )
   ) as Result,
   tblClients.ClientName,
   ..etc...
FROM
   ...etc...

- Jeff

bflemi3
Starting Member

5 Posts

Posted - 2005-04-08 : 09:04:43

When you say tblCompany." & YourCombo.Value & "

what does value mean?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 09:16:00

That's the value property of your combobox. It is implied if you ommit it, but I often like the explicitly state things whenever I can to make things more clear.

Any combobox or any other control on your form has about 40 properties that you can reference and even alter using VB.

- Jeff

bflemi3
Starting Member

5 Posts

Posted - 2005-04-08 : 09:17:33

Nevermind

bflemi3
Starting Member

5 Posts

Posted - 2005-04-08 : 09:17:43

Nevermind..Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources