Addind a combobox column value to a sql statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm doing a program for a friend and for some reasons it had to be done in
Access, which I never used before, so I have this (entry level?) question:

A textbox has a row source sql statement that depends of the value from
another combobox. Here's what I have in the row source properties:

SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
(tblTURMA.ANO_LECTIVO_ID)=cboAnoLectivo.column(1)

Access refuses to accept the value of the column 1 from combobox
cboAnoLectivo, but if I remove the ".column(1)" it works fine, but the the
WHERE condition does not work because I link the ID with the combobox
description, not the other ID.

How do I make this work, that is my question.

Thanks in advance,

Pedro
 
The column should work, try this instead

SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
tblTURMA.ANO_LECTIVO_ID=Forms![FormName]![cboAnoLectivo].column(1)

Add the form name to the criteria
 
Unfortunately, SQL does not know about the Column property of the Combo Box
object. You can only use the bound column. I'm a little unclear why you
have to do this, but if you must, you can create an unbound Textbox and set
the visible property to No. Make the Control Source for your Textbox (lets
call it MyText for simplicity) to =Combo.Column(1). Then reference this
Textbox in your query:

SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
(tblTURMA.ANO_LECTIVO_ID)=MyText

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
My mistake it can't be done, Roger is right
Instead you can assign the row source on the after update event of the first
combo
Me.Combo2Name.RowSource = "SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID
FROM tblTURMA WHERE
tblTURMA.ANO_LECTIVO_ID= '" & me.[cboAnoLectivo].column(1) & "'"

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Ofer said:
The column should work, try this instead

SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
tblTURMA.ANO_LECTIVO_ID=Forms![FormName]![cboAnoLectivo].column(1)

Add the form name to the criteria

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Pedro Vaz said:
Hello,

I'm doing a program for a friend and for some reasons it had to be done in
Access, which I never used before, so I have this (entry level?) question:

A textbox has a row source sql statement that depends of the value from
another combobox. Here's what I have in the row source properties:

SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
(tblTURMA.ANO_LECTIVO_ID)=cboAnoLectivo.column(1)

Access refuses to accept the value of the column 1 from combobox
cboAnoLectivo, but if I remove the ".column(1)" it works fine, but the the
WHERE condition does not work because I link the ID with the combobox
description, not the other ID.

How do I make this work, that is my question.

Thanks in advance,

Pedro
Click to expand...
Click to expand...
 
Thank you both for your quick replies.

Roger, since you asked I’m going to explain my idea and maybe you (and
others) could tell me if this is the right approach or not:
This is supposed to be a mini student database, and in this form I want the
user to select from different combo boxes (cb) the year, the school, and the
subject so that another cb receives the students in that year, school and
subject. Each one has their own table, so I populate the first cb
(cboAnoLectivo) with:

Select year, year_id from tblYears order by …

Now, the data in the second cb (schools) depends on the years, so I wanted
it to get only the schools that belong to that year. This means (and you
already told me it cannot be done this way):


SELECT Grau & Letra, TURMA_ID, ANO_LECTIVO_ID FROM tblTURMA WHERE
(tblTURMA.ANO_LECTIVO_ID)=cboAnoLectivo.column(1)

And the subject depends on the year and school previously selected. Finally,
the student’s cb would be populated with the students that belong to that
year, that schools and that subject.
Since I display the names, I store the IDs in an invisible column in the
ucb's, and that's why I want to column(1) to link them (since column(0)
contains the description). This means that when the user selects a particular
year, all the other cb get new data automatically, and the form itself.

Does this makes sense or do you have better suggestions?

Thanks,

Pedro



"Roger Carlson" escreveu:
 
Back
Top