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.
Author |
Topic |
steved157
Starting Member
8 Posts |
Posted - 2005-03-10 : 06:14:33
|
HiI'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.SetFocusEnd ifExit 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 |
 |
|
|
|
|
|
|