cbo box SQL

J

Junior

Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in txtbox -
txtHybPos but i can't seem to get my syntax correct - because Access doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string values and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY [TlkpNurType].[NurType]
;
 
G

Guest

Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
 
J

Junior

Ofer - thanks that almost works
the cbo works properly when i try the cbo from the subform - sfrmTypeHybMult
but i get a pop up box when i try using the subform cbo form the main
form - frmHybMultiple
So - i think the answer must be very close - and my SQL is still wrong
main form is frmHybMultiple
subform is sfrmTypeHybMult
control on the subform is txtHybPos

this SQL works (i.e. displays the proper list in the cbo)from the subform
but not from the main form..thanks for any help

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=[Forms]![sfrmTypeHybMult]![txtHybPos] ORDER BY
[TlkpNurType].[NurType] ;



Ofer said:
Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos] ORDER
BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
--
I hope that helped
Good luck


Junior said:
Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in txtbox -
txtHybPos but i can't seem to get my syntax correct - because Access
doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string values
and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it
works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]
;
Click to expand...
Click to expand...
 
R

Ron Weiner

Try

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.[NameOfTheSubformCONTROL].Form.txtHybPos
ORDER BY NurType

The NameOfTheSubformCONTROL parameter is the name of the subform control in
the main form (frmHybMultiple) NOT the name of the Form that the subform
control hosts.
--
Ron W
www.WorksRite.com
Junior said:
Ofer - thanks that almost works
the cbo works properly when i try the cbo from the subform - sfrmTypeHybMult
but i get a pop up box when i try using the subform cbo form the main
form - frmHybMultiple
So - i think the answer must be very close - and my SQL is still wrong
main form is frmHybMultiple
subform is sfrmTypeHybMult
control on the subform is txtHybPos

this SQL works (i.e. displays the proper list in the cbo)from the subform
but not from the main form..thanks for any help

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=[Forms]![sfrmTypeHybMult]![txtHybPos] ORDER BY
[TlkpNurType].[NurType] ;



Ofer said:
Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos] ORDER
BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
--
I hope that helped
Good luck


Junior said:
Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in txtbox -
txtHybPos but i can't seem to get my syntax correct - because Access
doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string values
and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it
works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]
;
Click to expand...
Click to expand...
Click to expand...
 
J

Junior

Ron - i feel like a complete nerd but can't seem to get this to work -here
are the variables - what did i do wrong?
still get the parameter message, and thanks for your response

frmHybMultiple = main form
sfrmTypHyb = name of subform control on frmHybMultiple
sfrmTypeHybMult = name of subform
txtHybPos] = txtbox on sfrmTypeHybMult
cboNurTypID = cbobox on the subform wher ethe below SQL is located

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE
[TlkpNurType].[Pos]=[Forms]![frmHybMultiple].[sfrmTypHyb].[sfrmTypeHybMult].[txtHybPos]
ORDER BY [TlkpNurType].[NurType] ;


Ron Weiner said:
Try

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.[NameOfTheSubformCONTROL].Form.txtHybPos
ORDER BY NurType

The NameOfTheSubformCONTROL parameter is the name of the subform control
in
the main form (frmHybMultiple) NOT the name of the Form that the subform
control hosts.
--
Ron W
www.WorksRite.com
Junior said:
Ofer - thanks that almost works
the cbo works properly when i try the cbo from the subform - sfrmTypeHybMult
but i get a pop up box when i try using the subform cbo form the main
form - frmHybMultiple
So - i think the answer must be very close - and my SQL is still wrong
main form is frmHybMultiple
subform is sfrmTypeHybMult
control on the subform is txtHybPos

this SQL works (i.e. displays the proper list in the cbo)from the subform
but not from the main form..thanks for any help

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Forms]![sfrmTypeHybMult]![txtHybPos] ORDER BY
[TlkpNurType].[NurType] ;



Ofer said:
Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos] ORDER
BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
--
I hope that helped
Good luck


:

Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in txtbox -
txtHybPos but i can't seem to get my syntax correct - because Access
doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string values
and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it
works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]
;
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weiner

My understanding is that you have a combobox that needs to refer to the
value in a text box that resides in a subform of a form. My understanding
is that the Form name is frmHybMultiple which contains a subform control
whose name is sfrmTypHyb and the text box name is txtHybPos. That being the
case you access that text box by referring to it thusly:

Forms!frmHybMultiple.sfrmTypHyb.form.txtHybPos

Basically what the above statement is saying is get me the value of the text
box "txtHybPos" which resides on the Form of the subform control sfrmTypHyb
which resides on the form frmHybMultiple which is part of the Access Forms
collection.

That being the case you should be able to paste in the following sql as the
row source of the combo box cboNurTypID

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.sfrmTypHyb.form.txtHybPos
ORDER BY NurType

That ought to get you going
--
Ron W
www.WorksRite.com
Junior said:
Ron - i feel like a complete nerd but can't seem to get this to work -here
are the variables - what did i do wrong?
still get the parameter message, and thanks for your response

frmHybMultiple = main form
sfrmTypHyb = name of subform control on frmHybMultiple
sfrmTypeHybMult = name of subform
txtHybPos] = txtbox on sfrmTypeHybMult
cboNurTypID = cbobox on the subform wher ethe below SQL is located

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE
[TlkpNurType].[Pos]=[Forms]![frmHybMultiple].[sfrmTypHyb].[sfrmTypeHybMult].
[txtHybPos]
ORDER BY [TlkpNurType].[NurType] ;


Ron Weiner said:
Try

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.[NameOfTheSubformCONTROL].Form.txtHybPos
ORDER BY NurType

The NameOfTheSubformCONTROL parameter is the name of the subform control
in
the main form (frmHybMultiple) NOT the name of the Form that the subform
control hosts.
--
Ron W
www.WorksRite.com
Junior said:
Ofer - thanks that almost works
the cbo works properly when i try the cbo from the subform - sfrmTypeHybMult
but i get a pop up box when i try using the subform cbo form the main
form - frmHybMultiple
So - i think the answer must be very close - and my SQL is still wrong
main form is frmHybMultiple
subform is sfrmTypeHybMult
control on the subform is txtHybPos

this SQL works (i.e. displays the proper list in the cbo)from the subform
but not from the main form..thanks for any help

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Forms]![sfrmTypeHybMult]![txtHybPos] ORDER BY
[TlkpNurType].[NurType] ;



Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos] ORDER
BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
--
I hope that helped
Good luck


:

Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in txtbox -
txtHybPos but i can't seem to get my syntax correct - because Access
doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string values
and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it
works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]
;
Click to expand...
Click to expand...
Click to expand...
 
J

Junior

Ron - thanks for your help and pointing me in the general direction
HERE is the SQL Where clause that finally worked - note your example was not
quite correct

WHERE
[TlkpNurType].[Pos]=[Forms]![frmHybMultiple]![sfrmTypHyb].[Form]![txtHybPos]

Junior

Ron Weiner said:
My understanding is that you have a combobox that needs to refer to the
value in a text box that resides in a subform of a form. My understanding
is that the Form name is frmHybMultiple which contains a subform control
whose name is sfrmTypHyb and the text box name is txtHybPos. That being
the
case you access that text box by referring to it thusly:

Forms!frmHybMultiple.sfrmTypHyb.form.txtHybPos

Basically what the above statement is saying is get me the value of the
text
box "txtHybPos" which resides on the Form of the subform control
sfrmTypHyb
which resides on the form frmHybMultiple which is part of the Access Forms
collection.

That being the case you should be able to paste in the following sql as
the
row source of the combo box cboNurTypID

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.sfrmTypHyb.form.txtHybPos
ORDER BY NurType

That ought to get you going
--
Ron W
www.WorksRite.com
Junior said:
Ron - i feel like a complete nerd but can't seem to get this to work -here
are the variables - what did i do wrong?
still get the parameter message, and thanks for your response

frmHybMultiple = main form
sfrmTypHyb = name of subform control on frmHybMultiple
sfrmTypeHybMult = name of subform
txtHybPos] = txtbox on sfrmTypeHybMult
cboNurTypID = cbobox on the subform wher ethe below SQL is located

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE
[TlkpNurType].[Pos]=[Forms]![frmHybMultiple].[sfrmTypHyb].[sfrmTypeHybMult].
[txtHybPos]
ORDER BY [TlkpNurType].[NurType] ;


Ron Weiner said:
Try

SELECT NurTypeID, NurType FROM TlkpNurType
WHERE Pos=Forms!frmHybMultiple.[NameOfTheSubformCONTROL].Form.txtHybPos
ORDER BY NurType

The NameOfTheSubformCONTROL parameter is the name of the subform
control
in
the main form (frmHybMultiple) NOT the name of the Form that the
subform
control hosts.
--
Ron W
www.WorksRite.com
Ofer - thanks that almost works
the cbo works properly when i try the cbo from the subform -
sfrmTypeHybMult
but i get a pop up box when i try using the subform cbo form the main
form - frmHybMultiple
So - i think the answer must be very close - and my SQL is still wrong
main form is frmHybMultiple
subform is sfrmTypeHybMult
control on the subform is txtHybPos

this SQL works (i.e. displays the proper list in the cbo)from the subform
but not from the main form..thanks for any help

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Forms]![sfrmTypeHybMult]![txtHybPos] ORDER BY
[TlkpNurType].[NurType] ;



Try this
SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=Forms![EnterHereTheFormName]![txtHybPos]
ORDER
BY
[TlkpNurType].[NurType]

On the after update event of the Text box, txtHybPos, write the code
Me.ComboBoxName.Requery

To refresh the list
--
I hope that helped
Good luck


:

Have a subform with a cbo box with following SQL in row source -
I want to change the available selections based on the value in
txtbox -
txtHybPos but i can't seem to get my syntax correct - because
Access
doesn't
recognize
my reference to txtHybPos - txtHybPos contains the correct string
values
and
if i substitute the actual string such as [TlkpNurType].[Pos]="RN" it
works
what am i doing wrong?

SELECT [TlkpNurType].[NurTypeID], [TlkpNurType].[NurType] FROM
TlkpNurType
WHERE [TlkpNurType].[Pos]=[Me]![txtHybPos] ORDER BY
[TlkpNurType].[NurType]
;
Click to expand...
Click to expand...
Click to expand...
 

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

cbo box sql help req'd 8
cbo box Catch 22 situation 9
Need combo box to refresh when moving to new record 2017 1
Default value of combo box based on another combo box 6
How to refresh data in a subform 1
Data not staying in form cbo 4
Row Source of cbo 13
Cbo box Limit to list = No write question 2

Top