Referring to a column of a combo in a form

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

Referring to a column of a combo in a form

Author

Topic

steved157
Starting Member

8 Posts

Posted - 2005-03-10 : 06:14:33

Hi

I've written this sub-routine to compare 2 columns in 2 different combo boxes. Both combo boxes store more than one table column. How do I reference a column? I want to filter 'combo58' on the 'district_id' entry.

Private Sub Combo58_Change()
If [Forms]![ADDR_PRIM_STREET_Form]![Combo58] = [Forms]![ADDR_PRIM_STREET_Form]![DISTRICT_ID] Then
GoTo Exit_combo58_Click
Else
MsgBox "Ward and district do not match", vbOKOnly, "Invalid Information"
[Forms]![ADDR_PRIM_STREET_Form]![Combo58].SetFocus
Exit Sub
End If

Exit_combo58_Click:
Exit Sub

End Sub

steved157
Starting Member

8 Posts

Posted - 2005-03-10 : 08:40:18

Also, can I refer to these columns in a 'Select... from... where...' statement?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-10 : 09:17:46

in a SELECT statement, you refer to them just as you wrote:

select .. from ... where [Forms]![ADDR_PRIM_STREET_Form]![Combo58] = 'some value'

in VB, you refer to them by their object names. In this case, if the code lives on that particular form, you can just refer to it as Combo58:


Private Sub Combo58_Change()
If Combo58.value  <>  DISTRICT_ID.value Then
  MsgBox "Ward and district do not match", vbOKOnly, "Invalid Information"
  Combo58.SetFocus
End if
Exit Sub

get a decent book on beginning VBA programming -- it can be tricky if you have never worked with VB before or if you are not a programmer. Once you get the hang of it, it's really easy. When typing code, once you press the "." after Combo58 you get a nice drop-down list of all different things you can access and/or do with the combobox, which is really helpful.

Also, combo boxes have a "columns()" property that returns the data from the columns. so if it's a particular column you want to check, you'd use:

if combo53.columns(3) = 'somevalue' then ....

Note that columns might start at 0, I don't recall specifically ... so if the combobox has 4 columns, they could be numbered 0-3 as opposed to 1-4. (listboxes work in the same way as you might expect)

read the MS Access VBA help as well; it explains comboboxes and what is stored in them with examples. NOte that VBA help is not installed by default last I checked, so you may need to add it to your current install.

to refer to a column in a SELECT statement, it is similiar:

select ... from .. where [forms]![YourForm]![Combo53].columns(2) = 'jeff'

- Jeff

steved157
Starting Member

8 Posts

Posted - 2005-03-10 : 09:43:02

Thank you, Jeff.

This has been plaguing me for weeks.

steved157
Starting Member

8 Posts

Posted - 2005-03-15 : 10:56:54

I have gotten some of my problem-code to work, I used the drop-down tip (it was combo58.column(0)) but I can't seem to write the SQL statement. This is what I have so far:

SELECT "PS_WARD"."CODE", "PS_WARD"."DESCRIPTION", "PS_WARD"."DISTRICT_ID", "view1"."DISTRICT_ID" FROM "PS_WARD", "view1" WHERE "view1"."DISTRICT_ID" = [Forms].[ADDR_PRIM_STREET_Form].[DEL_DIST]."Columns(0)" ORDER BY "PS_WARD"."DISTRICT_ID"

And this is the error message:
The column prefix 'Forms.ADDR_PRIM_STREET_Form.DEL_DIST' does not match with a table name or alias name used in the query.

It seems to refuse to accept a combo box from a form as part of the query.

steved157
Starting Member

8 Posts

Posted - 2005-03-16 : 06:58:33

The following code raises the same error. Changing anything seems to invalidate the syntax:

SELECT [PS_WARD].[CODE], [PS_WARD].[DESCRIPTION], [PS_WARD].[DISTRICT_ID], [view1].[DISTRICT_ID] FROM [PS_WARD], [view1] WHERE [view1].[DISTRICT_ID] = [Forms].[ADDR_PRIM_STREET_Form].[DEL_DIST].[Columns(0)] ORDER BY [PS_WARD].[DISTRICT_ID]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-16 : 08:59:25

your syntax is wrong:

[Forms].[ADDR_PRIM_STREET_Form].[DEL_DIST].[Columns](0)

EDIT: it appears Access doesn't let you access a particular column in a combo box from within a SQL statement. you will need to store this value somewhere else (using VB perhaps) and then retreive it from there ... or dynamically build your SQL string using VB.
- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources