if statment in query 2

[mustangcoupe]

I have a table called lu_method that has 3 fields, ID(autonumber), method, Type(number bound to autonumber of lu_test_types)

I am filtering a combo box linked to this by

SELECT [lu_method].[methodID], [lu_method].[testmethod], [lu_method].[Type] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value)) ORDER BY [lu_method].[testmethod];

but if Forms]![job_form]![type].value = combined(ID# 6) then I dont want it filtered

how would I do this?

 

[PHV]

if Forms]![job_form]![type].value = combined(ID# 6)
???
Maybe an OR condition in the WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FoxProProgrammer]

I'd suggest something similar to PHV:

SELECT [lu_method].[methodID], [lu_method].[testmethod], [lu_method].[Type] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value) And ([Forms]![job_form]![type].Value <> 6)) ORDER BY [lu_method].[testmethod];

Best regards,


dz
dzaccess@yahoo.com

 

[mustangcoupe]

Im not sure If I explained myself correctly, (yes I tried both soloutions but neither work, I am a tech user attempting to program please be kind ).

On my form (job_form) I have a combo box(TYPE) that is bound to table (lu_test_type) othermethodID and othermethods are the two fields

On my subform (test_subform) I have a combo box(TestMethod) that is bound to table (lu_method) 3 fields methodID, Test method, and type (type being bound to (lu_test_type othermethodsID )

currently when I pick a type in the combobox on the job_form, then I can pick the corosponding method in the testmethod combo box test_subform. But if Combined is selected in the type field then I want to be able to pick from ALL the types not just those which have that ID# in the table.

Hope I am clear... (If I was before tell me so I dont type this much next time)

thanks agian

 

[PHV]

Something like this ?
SELECT methodID, testmethod, [Type] FROM lu_method WHERE ([Type])=[Forms]![job_form]![type] OR [Forms]![job_form]![type]=6) ORDER BY testmethod;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FoxProProgrammer]

mustang,

Thanks for the clarification. I think that I understand what you are trying to do now. Please let me know if I have it right:

If the user selects "Combined" from the Type combo box (ID = 6), then you want to list all types in the combo box on the subform.

If the user does not select "Combined" from the Type combo box, you want to list only the types that correspond to the type selected.

If I have it right, then you will have to do something like this:

In the OnClick event of the combo box on your main form (Type), you will need an If structure to check the value of ID. If Type = 6, you will need to set the RecordSource of the subform equal to a query that shows all types. If Type does not equal 6, you will need to set the RecordSource of the subform equal to the query that retrieves only the types that correspond to the type selected, which should look like the Query in your first post.

Please post back if I interpreted your issue correctly and whether you need help with the code. I'll give you something more specific.

Regards,

dz
dzaccess@yahoo.com

 

[mustangcoupe]

I need to write this in a way to put in the row source of a combo box...

if Forms!job_form!type.value=6 then
SELECT [lu_method].[methodID], [lu_method].[testmethod], [lu_method].[Type] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value)) ORDER BY [lu_method].[testmethod];
else
SELECT [lu_method].[methodID], [lu_method].[testmethod]
FROM lu_method
ORDER BY [lu_method].[testmethod];
end if

 

[PHV]

Have you tried my suggestion with the OR in the WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FoxProProgrammer]

Try this in the OnClick event of the "Type" combo box.

Private Sub TypeMeth_Click()

Dim strQuery As String

If Forms!job_form!Type.Value = 6 Then
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod], [lu_method].[Type] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value)) ORDER BY [lu_method].[testmethod];"
Else
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod] FROM lu_method ORDER BY [lu_method].[testmethod];"
End If

Forms!job_form.test_subform!TestMethod.RowSource = strQuery

End Sub

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

The SQL strings are backwards.

Private Sub TypeMeth_Click()

Dim strQuery As String

If Forms!job_form!TypeMeth.Value = 6 Then
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod] FROM lu_method ORDER BY [lu_method].[testmethod];"

Else
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value)) ORDER BY [lu_method].[testmethod];"
End If

Forms!job_form.test_subform!testmethod.RowSource = strQuery

End Sub


dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Sorry about the typos. By the way, you could also create queries and refer to them by name in the following code, if you prefer. If you want to do that, replace the string in quotes with the Query name.

[code]Private Sub Type_Click()

Dim strQuery As String

If Forms!job_form!Type.Value = 6 Then
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod] FROM lu_method ORDER BY [lu_method].[testmethod];"

Else
    strQuery = "SELECT [lu_method].[methodID], [lu_method].[testmethod] FROM lu_method WHERE ((([lu_method].[Type])=[Forms]![job_form]![type].value)) ORDER BY [lu_method].[testmethod];"
End If

Forms!job_form.test_subform!testmethod.RowSource = strQuery

End Sub

dz
dzaccess@yahoo.com

 

[mustangcoupe]

PHV,
Thanks for the responce, I copied your code but it gave me an error. I thought it was the same error as my code gave me. BUT I re-tried it. There is an extra ) in the code and that is what was hanging it up.


thanks agian...

 

[PHV]

Sorry for the typo:
SELECT methodID, testmethod, [Type] FROM lu_method WHERE ([Type]=[Forms]![job_form]![type] OR [Forms]![job_form]![type]=6) ORDER BY testmethod;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244