What is missing?

R

RoMi

Hi everyone,



On a form frmProba there are two unbounded combos: cboSifOO and cboSifOsoba
(just for this example here).



cboSifOO selects one of the departments from a table tblSifOO (text like
1.3.). Depending on this value the second combo selects the people from this
department only (this is a text, too). This new value would be transferred
into one field in a table tblTrening which is opened on a subform. This
means that the cboSifOsoba must be related to the tblTrening later.





Unfortunately I do not see what is missing in these few lines of code and I
can't continue further more.



The problem is in my improper syntax in WHERE clause. If I write down
something like .WHERE tblOsKarton.SifOO LIKE "1.3."; then cboSifOsoba shows
just what I need (selected persons).



Problematical code is .



Private Sub cboSifOO_AfterUpdate()

..

Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba,
tblOsKarton.Prezime FROM tblOsKarton WHERE (((tblOsKarton.SifOO) =
Forms!frmProba!cboSifOO));"

..

End Sub



Do you see a catch?



Thank you.
 
R

RoMi

Sorry, but the suggested article does not give the answer to my question!

I need a value (text like "0.2.") of SifOO (Field Name) from the first
combobox cboSifOO to filter all people related to "0.2." and not related to
"SifOO".

Probably is the next SQL all right but if I try to select a person (among
filtered people) by the second combobox (cboSifOsoba) I get a parameter
request for [Forms!frmProba!cboSifOO]. After entering the right value, the
second combobox works correctly. That means that I still don't know how to
use the value of the first combobox as a parameter for the query in a
RowSource of the second combobox.



Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba,
tblOsKarton.Prezime FROM tblOsKarton WHERE (((tblOsKarton.SifOO) =
Forms!frmProba!cboSifOO));"

In the meantime I shall try to rearrange the strSQL from the frm0028.htm
but, please, stop me making wrong steps.

Thank you.
 
N

Nunya

You intend to pass the value of Forms!frmProba!cboSifOO into the SQL string,
but you are currently including the text "Forms!frmProba!cboSifOO" in the
string -- you need to concatenate it with the string as follows:
Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba, tblOsKarton.Prezime
FROM tblOsKarton WHERE tblOsKarton.SifOO) = """ _
& Forms!frmProba!cboSifOO & ""
 
R

RoMi

Well, you have understood my first question - how to write down correct code
in WHERE clause. I have been trying it in so many combinations - with the
single and or double quotation marks but I still can not get anything what
would Access accept. I always get compile or syntax error, 'Expected end of
statement ' or Access just transforms it into quite different value which
produces additional errors.



I am so confused with the use of a single and or double quotation marks that
I can' help myself. There are so many already finished and approved examples
that do not work on my computer without additional corrections. Why? I work
on MS Office XP (Access 2000 & 2002). No one book or Access Help does really
help me.



How to concatenate strings in SQL?



Please, show me on this example again:



Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba, tblOsKarton.Prezime
FROM tblOsKarton WHERE tblOsKarton.SifOO) = """ & Forms!frmProba!cboSifOO &
"";" (your solution)



And why is this not correct?



Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba,
tblOsKarton.Prezime FROM tblOsKarton WHERE (((tblOsKarton.SifOO) = '" &
Forms!frmProba!cboSifOO & "'));" (my solution)





Thank you to all who might advice me.
 
R

RoMi

I did it! I did it my way!

Private Sub cboSifOO_AfterUpdate()
Dim strOO As String


strSQL = "SELECT tblOsKarton.SifOsoba, tblOsKarton.Prezime FROM
tblOsKarton "
strSQL = strSQL & " WHERE tblOsKarton.SifOO = '" &
Forms!frmProba!cboSifOO & "'"
strSQL = strSQL & " ORDER BY tblOsKarton.Prezime;"

Me.cboSifOsoba.RowSource = strSQL
Me.cboSifOsoba.Requery


End Sub

Well, I found out that 'WHERE clause' was taking a value from the wrong
column. A combobox was requested to show the 2nd and the 3rd column only
while the second one was bounded. The first column was set to be hidden and
its width was set to be zero. For some reason, Forms!frmProba!cboSifOO was
sending a value from the first column (autonumbers like 1, 2, 3 etc.)
instead of a value from the second and bounded column with the text like
0.2., 1.0., 1.1., ... These values were shown in the combobox after the
selection and I have expected them as a filter.

OK Thank you both.
 
N

Nunya

You're trying to use single quotes to denote string values in SQL statements
(which is correct in most RDBMS like SQL Server or Oracle), but Access
requires double quotes around strings. You're probably looking at examples
for SQL Server rather than for Access
Another quirk that will cause you trouble if you haven't already encountered
it is dates. Access requires you to surround dates in SQL with the pound
sign (# -- not the British pound), while RDBMS use single quotes.

You already seem to understand string concatenation, so you probably already
know this, but if there's ANY chance the string you are embedding in SQL
contains double quotes (single quotes in SQL Server or Oracle), then you
need to use REPLACE function to replace double up single instances:
replace " with "";
e.g., "He said "I told you so""
becomes
"He said ""I told you so """.


Well, you have understood my first question - how to write down correct
code
in WHERE clause. I have been trying it in so many combinations - with the
single and or double quotation marks but I still can not get anything what
would Access accept. I always get compile or syntax error, 'Expected end of
statement ' or Access just transforms it into quite different value which
produces additional errors.

I am so confused with the use of a single and or double quotation marks
that
I can' help myself. There are so many already finished and approved
examples
that do not work on my computer without additional corrections. Why? I work
on MS Office XP (Access 2000 & 2002). No one book or Access Help does
really
help me.

How to concatenate strings in SQL?

Please, show me on this example again:

Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba,
tblOsKarton.Prezime
FROM tblOsKarton WHERE tblOsKarton.SifOO) = """ & Forms!frmProba!cboSifOO
&
"";" (your solution)

And why is this not correct?

Me.cboSifOsoba.RowSource = "SELECT tblOsKarton.SifOsoba,
tblOsKarton.Prezime FROM tblOsKarton WHERE (((tblOsKarton.SifOO) = '" &
Forms!frmProba!cboSifOO & "'));" (my solution)

Thank you to all who might advice me.
 
R

RoMi

Correction again: it must be written - Dim strSQL As String. This is due to
many variations of tests and lot of e-mails.

Private Sub cboSifOO_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT tblOsKarton.SifOsoba, tblOsKarton.Prezime FROM
tblOsKarton "
strSQL = strSQL & " WHERE tblOsKarton.SifOO = '" &
Forms!frmProba!cboSifOO & "'"
strSQL = strSQL & " ORDER BY tblOsKarton.Prezime;"

Me.cboSifOsoba.RowSource = strSQL
Me.cboSifOsoba.Requery


End Sub

Please, note the difference between the single and the double quotation
marks:


.... = '" & Forms!frmProba!cboSifOO & "'" ....
.... = single double & Forms!frmProba!cboSifOO & double single double ...
This combination works at my PC but there are probably some other
possibilities.(?)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

positional data in WHERE claus 4
Limitations on WHERE Clause in lookup query for a table column 1
How to have Word create a new or re-ordered document 0
creating a table for accessing data via Access forms 5
Data Entry Form. 3
two-tiered selection 0
Union and group, then count 1
What is the best way to set-up my Database? 1

Top