|
 |
access thread: R: Re: R: Re: recipes treatment - SQL PROBLEM
Message #1 by Bohus Peter <peter.bohus@l...> on Tue, 18 Jun 2002 16:47:07 +0200
|
|
Dear Brian
Really I have made some confusion in alias names (since I'm beginner).
I
applied changes you suggested. But I get run-time error 3075: syntax
error
(missing operator) in query expression. But I cannot find this.
In any case I enclose code again. I'm afraid of that it is not correct
to
apply INNER JOIN two or three times in SQL.. (because when I choose
only 1
ingredient on frmCriteria or 1 ingredient + company, or 1 ingredient +
formulationtype etc. code is running well. The error is coming when
more
than 1 ingredients are being put in criteria.
(herein already 3 ingredients on frmCriteria), [ Recipe Detailed]
corresponds to tblRecipeIngredient
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT =3D "s.* "
strFROM =3D "Recipes s "
If chkIngredient1ID Then
strFROM =3D strFROM & "INNER JOIN [Recipe Detailed] i " & _
"ON s.RecipeID=3Di.RecipeID "
strWHERE =3D " AND [Recipe Detailed].IngredientID=3D " &
cboIngredient1ID
End If
If chkCompanyID Then
strWHERE =3D strWHERE & " AND r.CustomerID =3D " & cboCompanyID
End If
If chkFormulationTypeID Then
strWHERE =3D strWHERE & " AND r.FormulationTypeID =3D " &
cboFormulationTypeID
End If
strSQL =3D "SELECT " & strSELECT
strSQL =3D strSQL & "FROM " & strFROM
If strWHERE <> "" Then
strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString =3D True
End Function
Many thanks for your kind collboration and help, best regards
Peter
> -----Messaggio originale-----
> Da: braxis@b... [SMTP:braxis@b...]
> Inviato: marted=EC 18 giugno 2002 15.37
> A: Access
> Oggetto: [access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> Peter
>
> There seems to be some confusion with the table aliases the code is
using.
> This may be causing the problem.
>
> Try the changes below:
>
> > Function BuildSQLString(strSQL As String) As Boolean
> > Dim strSELECT As String
> > Dim strFROM As String
> > Dim strWHERE As String
> > strSELECT =3D "s.* "
> > strFROM =3D "tblRecipes s "
> > If chkIngredient1ID Then ' checkbox in frmCriteria when is
enabled I
> can
> > choose ingredient from combobox
> >
> > strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> > "ON s.RecipeID=3Di.RecipeID "
> > strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient1ID
> CHANGE TO
> strWHERE =3D " AND i.IngredientID=3D " & cboIngredient1ID
>
> >
> > End If
> >
> > If chkIngredient2ID Then ' checkbox in frmCriteria when is
enabled I
> can
> > choose ingredient from combobox
> >
> >
> >
> > strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> CHANGE TO
> strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i2 " &
_
>
> > "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
> CHANGE TO
> "ON s.RecipeID=3Di2.RecipeID "
>
> > strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient2ID
> CHANGE TO
> strWHERE =3D " AND i2.IngredientID=3D " & cboIngredient2ID
>
> >
> > End If
> >
> >
> >
> > If chkCompanyID Then
> > strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " &
cboCompanyID
> CHANGE TO
> strWHERE =3D strWHERE & " AND s.CustomerID =3D " & cboCompanyID
>
> > End If
> > If chkFormulationTypeID Then
> > strWHERE =3D strWHERE & " AND tblRecipe.FormulationTypeID =3D "
&
> > cboFormulationTypeID
> CHANGE TO
> strWHERE =3D strWHERE & " AND s.FormulationTypeID =3D " &
> cboFormulationTypeID
>
> > End If
> >
> > strSQL =3D "SELECT " & strSELECT
> > strSQL =3D strSQL & "FROM " & strFROM
> > If strWHERE <> "" Then
> > strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> > End If
> > BuildSQLString =3D True
> >
> > End Function
> >
> > The form frmCriteria contain command button cmdFind and its
clicking
> event
> > call BuildSQLString
> >
> > The frmCriteria contains different checkboxes and combobox.
> >
> >
> > Waiting news
> >
> >
> > many thanks in advance, best regards, ciao
> >
> > Peter
> >
>
>
>
Message #2 by "Amy Wyatt" <amyw@c...> on Tue, 18 Jun 2002 16:01:24
|
|
Check my response in the Second thread you started R: RE: recipe ... and
you will see that if you are using the same table twice (or more) in a
query, you have to use parenthasis () around parts of the from statement.
Thanks,
Amy
> Dear Brian
Really I have made some confusion in alias names (since I'm beginner).
I
applied changes you suggested. But I get run-time error 3075: syntax
error
(missing operator) in query expression. But I cannot find this.
In any case I enclose code again. I'm afraid of that it is not correct
to
apply INNER JOIN two or three times in SQL.. (because when I choose
only 1
ingredient on frmCriteria or 1 ingredient + company, or 1 ingredient +
formulationtype etc. code is running well. The error is coming when
more
than 1 ingredients are being put in criteria.
(herein already 3 ingredients on frmCriteria), [ Recipe Detailed]
corresponds to tblRecipeIngredient
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
strSELECT =3D "s.* "
strFROM =3D "Recipes s "
If chkIngredient1ID Then
strFROM =3D strFROM & "INNER JOIN [Recipe Detailed] i " & _
"ON s.RecipeID=3Di.RecipeID "
strWHERE =3D " AND [Recipe Detailed].IngredientID=3D " &
cboIngredient1ID
End If
If chkCompanyID Then
strWHERE =3D strWHERE & " AND r.CustomerID =3D " & cboCompanyID
End If
If chkFormulationTypeID Then
strWHERE =3D strWHERE & " AND r.FormulationTypeID =3D " &
cboFormulationTypeID
End If
strSQL =3D "SELECT " & strSELECT
strSQL =3D strSQL & "FROM " & strFROM
If strWHERE <> "" Then
strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString =3D True
End Function
Many thanks for your kind collboration and help, best regards
Peter
> -----Messaggio originale-----
> Da: braxis@b... [SMTP:braxis@b...]
> Inviato: marted=EC 18 giugno 2002 15.37
> A: Access
> Oggetto: [access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> Peter
>
> There seems to be some confusion with the table aliases the code is
using.
> This may be causing the problem.
>
> Try the changes below:
>
> > Function BuildSQLString(strSQL As String) As Boolean
> > Dim strSELECT As String
> > Dim strFROM As String
> > Dim strWHERE As String
> > strSELECT =3D "s.* "
> > strFROM =3D "tblRecipes s "
> > If chkIngredient1ID Then ' checkbox in frmCriteria when is
enabled I
> can
> > choose ingredient from combobox
> >
> > strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> > "ON s.RecipeID=3Di.RecipeID "
> > strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient1ID
> CHANGE TO
> strWHERE =3D " AND i.IngredientID=3D " & cboIngredient1ID
>
> >
> > End If
> >
> > If chkIngredient2ID Then ' checkbox in frmCriteria when is
enabled I
> can
> > choose ingredient from combobox
> >
> >
> >
> > strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> CHANGE TO
> strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i2 " &
_
>
> > "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
> CHANGE TO
> "ON s.RecipeID=3Di2.RecipeID "
>
> > strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient2ID
> CHANGE TO
> strWHERE =3D " AND i2.IngredientID=3D " & cboIngredient2ID
>
> >
> > End If
> >
> >
> >
> > If chkCompanyID Then
> > strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " &
cboCompanyID
> CHANGE TO
> strWHERE =3D strWHERE & " AND s.CustomerID =3D " & cboCompanyID
>
> > End If
> > If chkFormulationTypeID Then
> > strWHERE =3D strWHERE & " AND tblRecipe.FormulationTypeID =3D "
&
> > cboFormulationTypeID
> CHANGE TO
> strWHERE =3D strWHERE & " AND s.FormulationTypeID =3D " &
> cboFormulationTypeID
>
> > End If
> >
> > strSQL =3D "SELECT " & strSELECT
> > strSQL =3D strSQL & "FROM " & strFROM
> > If strWHERE <> "" Then
> > strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> > End If
> > BuildSQLString =3D True
> >
> > End Function
> >
> > The form frmCriteria contain command button cmdFind and its
clicking
> event
> > call BuildSQLString
> >
> > The frmCriteria contains different checkboxes and combobox.
> >
> >
> > Waiting news
> >
> >
> > many thanks in advance, best regards, ciao
> >
> > Peter
> >
>
>
>
Message #3 by Bohus Peter <peter.bohus@l...> on Tue, 18 Jun 2002 17:14:19 +0200
|
|
Dear Dave,
Many thanks for help. I will try it this evening
ciao
Peter
> -----Messaggio originale-----
> Da: Carnley, Dave [SMTP:dcarnley@a...]
> Inviato: marted=EC 18 giugno 2002 16.53
> A: Access
> Oggetto: [access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> There is no need to make multiple joins to the cross-reference table.
>
> You can retrieve the list of RecipeID's that have the correct set of
> ingredients witht he following query, then use that list and a second
> query
> to add further criteria (like company and method)
>
>
>
> SELECT Recipe.recipeID, COUNT(RecIng.IngredientID) AS CountIng
> FROM Recipe INNER JOIN
> RecipeIngredient ON Recipe.RecipeID =3D RecipeIngredient.RecipeID
> WHERE (RecipeIngredient.IngredientID IN (1, 2, 3))
> GROUP BY Recipe.recipeID
> HAVING (COUNT(RecipeIngredient.IngredientID) =3D 3)
>
> This will return the list of recipe IDs, you could then use that
recordset
> to build a query using the other criteria, and make na IN list of
recipe
> IDs
>
> Select * from Recipe where RecipeID in (...)
> and companyID =3D ...
> and FormulationTypeID =3D ...
>
>
>
>
>
> -----Original Message-----
> From: Amy Wyatt [mailto:amyw@c...]
> Sent: Tuesday, June 18, 2002 10:49 AM
> To: Access
> Subject: [access] Re: R: Re: recipes treatment - SQL PROBLEM
>
>
> In order for this to work you have to add the table
tblRecipeIngredient to
>
> the query twice which means you have to enclose the firs part of the
FROM
> statement in (). Also, you are not adding the strWhere to the second
> strWhere you are replacing it when they choose a second ingredient.
You
> have to concatinate the first where to the second.
>
> Try this:
> Function BuildSQLString() As String
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> dim strSQL as String
>
> Me.Refresh
> strSELECT =3D "s.* "
>
> strFROM =3D "tblRecipes s "
> If chkIngredient1ID Then ' true choose ingredient from combobox
1
> 'Add parentheses to from statement in case second ingredient
used
> strFROM =3D "(" & strFROM & "INNER JOIN tblRecipeIngredient
i ON "
> _
> & "s.RecipeID =3Di.RecipeID) "
> strWHERE =3D " AND i.IngredientID=3D" & cboIngredient1ID
> End If
>
> If chkIngredient2ID Then ' true choose ingredient from combobox
> strFROM =3D strFROM & "INNER JOIN tblRecipeIngredient z ON "
_
> & "s.RecipeID =3D z.RecipeID"
> strWHERE =3D strWHERE & " AND z.IngredientID=3D " &
cboIngredient2ID
> End If
>
>
>
> If chkCompanyID Then
> strWHERE =3D strWHERE & " AND s.CustomerID=3D" &
cboCompanyID
> End If
>
> If chkFormulationTypeID Then
> strWHERE =3D strWHERE & " AND s.FormulationTypeID=3D" _
> & cboFormulationTypeID
> End If
>
> strSQL =3D "SELECT " & strSELECT
> strSQL =3D strSQL & "FROM " & strFROM
> If strWHERE <> "" Then
> strSQL =3D strSQL & " WHERE " & Mid$(strWHERE, 6)
> End If
> BuildSQLString =3D strSQL
> End Function
>
> Hope this helps.
>
> Amy
>
> > Hi Brian,
>
> I send hereby non working SQL which was prepared according to =3D
> instuctions
> found in the book of Beginning Access 2000 VBA ( but I'm using MS
=3D
> Access
> 97)
> I hope that it is clear because I apply a function BuildSQLString in
=3D
> order
> to create SQL. This Function can be found in class modul of one form
> (frmCriteria) and I intend to find recipe
>
> Function BuildSQLString(strSQL As String) As Boolean
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> strSELECT =3D3D "s.* "
> strFROM =3D3D "tblRecipes s "
> If chkIngredient1ID Then ' checkbox in frmCriteria when is enabled
I =3D
> can
> choose ingredient from combobox
> =3D20
> strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> "ON s.RecipeID=3D3Di.RecipeID "
> strWHERE =3D3D " AND [tblRecipeIngredient].IngredientID=3D3D " &
> cboIngredient1ID
> =3D20
> End If
>
> If chkIngredient2ID Then ' checkbox in frmCriteria when is enabled
I =3D
> can
> choose ingredient from combobox
> =3D20
>
> =3D20
> strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> "ON tblRecipe.RecipeID=3D3DtblRecipeIngredient.RecipeID "
> strWHERE =3D3D " AND [tblRecipeIngredient].IngredientID=3D3D " &
> cboIngredient2ID
> =3D20
> End If
>
>
>
> If chkCompanyID Then
> strWHERE =3D3D strWHERE & " AND tblRecipe.CustomerID =3D3D " &
=3D
> cboCompanyID
> End If
> If chkFormulationTypeID Then
> strWHERE =3D3D strWHERE & " AND tblRecipe.FormulationTypeID =3D3D
" &
> cboFormulationTypeID
> End If
>
> strSQL =3D3D "SELECT " & strSELECT
> strSQL =3D3D strSQL & "FROM " & strFROM
> If strWHERE <> "" Then
> strSQL =3D3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> End If
> BuildSQLString =3D3D True
>
> End Function
>
> The form frmCriteria contain command button cmdFind and its clicking
=3D
> event
> call BuildSQLString
>
> The frmCriteria contains different checkboxes and combobox.
>
>
> Waiting news
>
>
> many thanks in advance, best regards, ciao
>
> Peter
>
> > -----Messaggio originale-----
> > Da: braxis@b... [SMTP:braxis@b...]
> > Inviato: marted=3DEC 18 giugno 2002 12.48
> > A: Access
> > Oggetto: [access] Re: recipes treatment - SQL PROBLEM
> >=3D20
> > Hi Peter
> >=3D20
> > Could you send the non-working SQL?
> >=3D20
> > Brian
> >=3D20
> > > from: Bohus Peter <peter.bohus@l...>
> > > date: Tue, 18 Jun 2002 11:05:13
> > > to: access@p...
> > > subject: Re: [access] recipes treatment - SQL PROBLEM
> > >=3D20
> > > Have a Nice Day for Everybody
> > >=3D20
> > > I hope I will have similarly nice day if somebody can give me
=3D
> solution
> > . I
> > > have several tables and among them there are 3 tables
(tblRecipe,
> > > tblIngredient and tblRecipeIngredient) which ought to be used
=3D
> for
> > SQL.
> > > The appropriate relationship is as follows:
> > > tblrecipe (1) -> (n) tblRecipeIngredient (n) <- (1)
tblIngredient,
> > since a
> > > recipe is composed of many ingredients and the same ingredient
can =3D
> be
> > used
> > > in many recipes. Therefore tblRecipeIngredient can be considered
=3D
> as
> > > intermediate table for crating many-to-many relationship.
> > >=3D20
> > >=3D20
> > > I would like to make a query (or SQL...) in order to choose out
all
> > recipes
> > > in database which contain certain ingredient (IngredientID)
> > > When I must choose only one ingredient the SQL, like next one ,
is
> > working
> > > well
> > >=3D20
> > > Select * FROM tblRecipe INNER JOIN tblRecipeIngredient ON
> > > tblRecipe.RecipeID=3D3DtblRecipeIngredient.RecipeID
> > > WHERE IngredientID=3D3Dx
> > >=3D20
> > > But I would like to find all recipes which, for example, contain
> > > simultaneously 2 or 3 pre-determined ingredient. Using AND =3D
> operator in
> > > WHERE clause I don't get required results.
> > >=3D20
> > > What can I do it?
> > >=3D20
> > >=3D20
> > > tblRecipe:
> > > RecipeID
> > > Recipename
> > > Ingredient
> > > ....
> > >=3D20
> > > tblIngredients
> > > IngredientID
> > > Name
> > > Description
> > > ..
> > > tblRecipesIngredients
> > > IngredientRecipeID
> > > RecipeID
> > > IngredientID
> > > Quantity
> > >=3D20
> > > Best regards
> > >=3D20
> > > ciao
> > >=3D20
> > >=3D20
> > > Peter
> > >=3D20
> > > ---
> > > Change your mail options at http://p2p.wrox.com/manager.asp
or=3D20
> >=3D20
> >=3D20
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or=3D20
> > to unsubscribe send a blank email to =3D
>
Message #4 by Bohus Peter <peter.bohus@l...> on Thu, 20 Jun 2002 15:00:37 +0200
|
|
Hi Amy,
Many thanks for comments. I have applied BuildSQLstring function with return
value declared as Boolean since in development stage I wanted to know
whether I was able to create SQL for query or not. It seems that your
solution is more flexible. I put in enclosed code of frmCriteria module.
Now I have problem only to display results. As said before I 'm beginner
but step by step - following all useful instructions available under this
forum(list) I will arrange each problem.
Option Compare Database
Option Explicit
' frmCriteria contains five checkboxes and five comboboxes
' this structure provides to choose field values to be found
' and finally to create SQL string
' but first of all it is necessary to know how the comboboxes
' are enabled and disabled. Eacf of the check boxes has an event
' procedure handling its CLICK event
Private Sub chkCompanyID_Click()
cboCompanyID.Enabled = chkCompanyID
End Sub
Private Sub chkFormulationTypeID_Click()
cboFormulationTypeID.Enabled = chkFormulationTypeID
End Sub
Private Sub chkIngredient1ID_Click()
cboIngredient1ID.Enabled = chkIngredient1ID
End Sub
Private Sub chkIngredient2ID_Click()
cboIngredient2ID.Enabled = chkIngredient2ID
End Sub
Private Sub chkIngredient3ID_Click()
cboIngredient3ID.Enabled = chkIngredient3ID
End Sub
' the frmCriteria contains two command button: cmClose and cmdFind, they
' have CLICK events too.
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
Private Sub cmdFind_Click()
' cmdFind button will take Find Matches process , its CLICK
' event calls 2 different functions: EntriesValid and BuildSQLString
' these callings are carried out in indirect way through If Not ..Then
' condition. The said functions return variable Boolean
'Dim strSQL As String
' as Amy suggested I can introduce other declaration like next one
Dim strReturnSQL As String 'supposing that function
' BuildStrSQL returns with string but in original version at
' function gives Boolean value
If Not EntriesValid Then Exit Sub
'If Not BuildSQLString(strSQL) Then
' MsgBox "There was a problem building the SQL string"
' Exit Sub
'End If
' in order to see strSQL string I can use Msgbox, but it will
' be cancelled from this development stage
strReturnSQL = BuildSQLString
MsgBox strReturnSQL
' using strReturnSQL a query is being built before displaying found
' and matched records
CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString
' as Amy suggested to display results
Me.Refresh
' Amy suggested to open form but I have already prepared report
' rptRecipesSelected
DoCmd.OpenReport "rptRecipesSelected", acViewPreview
Reports!rptRecipesSelected.RecordSource = strReturnSQL
Reports!rptRecipesSelected.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)
' when I open form frmCriteria I want that each combox is disabled
' just with checking on checkboxes I will be able to use comboboxes
cboCompanyID.Enabled = False
cboFormulationTypeID.Enabled = False
cboIngredient1ID.Enabled = False
cboIngredient2ID.Enabled = False
cboIngredient3ID.Enabled = False
End Sub
Function EntriesValid() As Boolean
'this function must control whether any checkbox is fired in appropriate
combobox
'any value is coming chosen or not. The return value is Boolean.
'ez a fuggveny azt ellenorzi, hogy amennyiben egy checkbox ki lett jelolve,
'ugy a megfelelo combolistben szinteen hozza lett e rendelve bemeneti
eerteek
Dim sMsg As String
Dim sBullet As String
sBullet = "*"
If chkCompanyID And IsNull(cboCompanyID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by company but " & _
"have not selected a company. Either select a company or " & _
"uncheck the ""Company is"" checkbox."
End If
If chkFormulationTypeID And IsNull(cboFormulationTypeID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by Formulation Type but " & _
"have not selected a Formulation Type. Either select a Formlation Type
or " & _
"uncheck the ""Formulation Type"" checkbox."
End If
If chkIngredient1ID And IsNull(cboIngredient1ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a first ingredient. Either select a first ingredient
or " & _
"uncheck the ""First component"" checkbox."
End If
If chkIngredient2ID And IsNull(cboIngredient2ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a second ingredient. Either select a second
ingredient or " & _
"uncheck the ""Second component"" checkbox."
End If
If chkIngredient3ID And IsNull(cboIngredient3ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a third ingredient. Either select a third ingredient
or " & _
"uncheck the ""Third component"" checkbox."
End If
If sMsg <> "" Then
MsgBox "The following errors are preventing you from " & _
"viewing the records that match the criteria " & _
"you have selected:" & sMsg & vbCrLf & vbCrLf & _
"Please correct these errors and try again.", _
vbExclamation + vbOKOnly
Else
EntriesValid = True
End If
End Function
Function BuildSQLString() As String
'Function BuildSQLString(strSQL As String) As Boolean
' alternatively strSQL declaration was given as parameter
' and function return value was declared as Boolean
Dim strSQL As String
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Me.Refresh ' as suggested Amy
strSELECT = "s.* "
strFROM = "Recipes s "
If chkIngredient1ID = True Then ' true choose ingredient from combobox
CboIngredient1ID
' Amy suggested to add parentheses to FROM statement
strFROM = "(" & strFROM & "INNER JOIN [Recipe Detailed] i " & _
"ON s.RecipeID=i.RecipeID)"
strWHERE = " AND i.IngredientID= " & cboIngredient1ID
End If
If chkIngredient2ID = True Then ' true choose ingredient from combobox
CboIngredient2ID
strFROM = strFROM & "INNER JOIN [Recipe Detailed] i2 " & _
"ON s.RecipeID=i2.RecipeID "
strWHERE = strWHERE & " AND i2.IngredientID= " & cboIngredient2ID
End If
If chkIngredient3ID = True Then 'true choose ingredient from combobox
CboIngredient2ID
strFROM = strFROM & "INNER JOIN [Recipe Detailed] i3 " & _
"ON s.RecipeID=i3.RecipeID "
strWHERE = strWHERE & " AND i3.IngredientID= " & cboIngredient3ID
End If
If chkCompanyID = True Then
strWHERE = strWHERE & " AND s.CustomerID = " & cboCompanyID
End If
If chkFormulationTypeID = True Then
strWHERE = strWHERE & " AND s.FormulationTypeID = " &
cboFormulationTypeID
End If
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then
strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString = strSQL
End Function
Best regards
Peter
> -----Messaggio originale-----
> Da: Amy Wyatt [SMTP:amyw@c...]
> Inviato: mercoledì 19 giugno 2002 15.56
> A: Access
> Oggetto: [access] Re: R: RE: recipes treatment - SQL PROBLEM
>
> I made the change of the declaration and actually to the function itself
> (I made it a string instead of a boolean) because from the code you sent I
>
> did not see where you passed any variable that the function used. The
> change I made would allow you to return the SQL string through the
> function so that it can be used at the Call location.
>
> For instance, if after the SQL is built you wanted to display the results
> in another form you could do that as follows from the OnClick event of you
>
> command button (the one that fires the BuildSQLString function):
>
> Private Sub cmdBSQL_Click()
> Dim strReturnSql As String
>
> Me.Refresh
> strReturnSql = BuildSQLString
> DoCmd.OpenForm "frmDisplayResult", acNormal
> Forms!frmDisplayResult.RecordSource = strReturnSql
> Forms!frmDisplayResult.Requery
> DoCmd.Close acForm, "frmCriteria", acSaveNo
>
> End Sub
>
> If you do not need to passing information to the function there is really
> no point including a parameter in the function.
>
> This, of course, is just the method I would use. Not knowing what you are
> doing with the rest of the code makes it difficult to determine the best
> method to use.
>
> Hope this helps,
>
> Amy
>
> > Hello " Boys" , Dear Dave, Leo, Amy and Brian
>
> First of all I have to say a lot thanks for your kind help since my
> problem
> could be quickly solved with success. Finally I have applied Amy's
> recommendations . Dave's solution is good too. As Leo supposed my
> IngredientID is keyfield having numeric value. In any case, as Dave
> says I
> need to find all recipes containing pre-determined ingredients
> simultaneously (AND relation and not OR)
> Brian gave good contribution to understand alias name application and
> to
> avoid confusion.
>
> Looking at Amy's answer I see that in function you have changed the
> place
> of declaration of variable strSQL as string. What advantages or
> disadvantages can be considered regarding this change.
>
> Thanks again and ciao
>
> Peter
>
Message #5 by "Amy Wyatt" <amyw@c...> on Thu, 20 Jun 2002 15:23:45
|
|
I am not exactly sure which things are working and which you need help on
but after examining the code there are a couple of things you may need to
change.
Before calling the EntriesValid function you should do a form refresh
because, combo boxes and check boxes sometimes do not update as expected.
Doing a refresh forces the data to be saved in the control after a change.
(I don't know why this is but it happens.)
Second, you can't apply a record source to a report in preview mode. You
will have to first open it in design mode, apply the recordsource and then
open it in preview mode.
DoCmd.OpenReport "rptRecipesSelected", acViewDesign
Reports!rptRecipesSelected.RecordSource = strReturnSQL
DoCmd.OpenReport "rptRecipesSelected", acViewPreview
I'm not sure why you are defining the query again as in
(CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString). I don't think
you need to do this unless you are using it somewhere else in the code.
You should also make sure the sql was built even if it is not returning
the boolean by
If strReturnSQL<>"" Then
DoCmd.OpenReport .....
Else
MsgBox "There were no matches for the criteria you entered."
End If
This way, you can prevent the report from opening with the dreaded #Error
message when no data is found. This also means you would need to check the
SQL statement in the BuildSQLString function to make sure it is returning
at least one record. To do this you would define a recordset using strSQL
and check to see if .EOF = True. Following is the code to add to the
BuildSQLStrig function to accomplish this:
Dim rstCheck as ADODB.Recordset
'put this before the line BuildSQLString = strSQL
Set rstCheck=New ADODB.Recordset
rstCheck.Open strSQL, CurrentProject.Connection, adOpenStatic, _
adLockReadOnly, adCmdText
If rstCheck.EOF Then strSQL=""
rstCheck.Close
set rstCheck=Nothing
This will determine if the SQL statement will return any rows and if it
does not then the function returns the "" string.
Hope these are the things you are looking for.
Amy
> Hi Amy,
Many thanks for comments. I have applied BuildSQLstring function with
return
value declared as Boolean since in development stage I wanted to know
whether I was able to create SQL for query or not. It seems that your
solution is more flexible. I put in enclosed code of frmCriteria module.
Now I have problem only to display results. As said before I 'm beginner
but step by step - following all useful instructions available under this
forum(list) I will arrange each problem.
Option Compare Database
Option Explicit
' frmCriteria contains five checkboxes and five comboboxes
' this structure provides to choose field values to be found
' and finally to create SQL string
' but first of all it is necessary to know how the comboboxes
' are enabled and disabled. Eacf of the check boxes has an event
' procedure handling its CLICK event
Private Sub chkCompanyID_Click()
cboCompanyID.Enabled = chkCompanyID
End Sub
Private Sub chkFormulationTypeID_Click()
cboFormulationTypeID.Enabled = chkFormulationTypeID
End Sub
Private Sub chkIngredient1ID_Click()
cboIngredient1ID.Enabled = chkIngredient1ID
End Sub
Private Sub chkIngredient2ID_Click()
cboIngredient2ID.Enabled = chkIngredient2ID
End Sub
Private Sub chkIngredient3ID_Click()
cboIngredient3ID.Enabled = chkIngredient3ID
End Sub
' the frmCriteria contains two command button: cmClose and cmdFind, they
' have CLICK events too.
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
Private Sub cmdFind_Click()
' cmdFind button will take Find Matches process , its CLICK
' event calls 2 different functions: EntriesValid and BuildSQLString
' these callings are carried out in indirect way through If Not ..Then
' condition. The said functions return variable Boolean
'Dim strSQL As String
' as Amy suggested I can introduce other declaration like next one
Dim strReturnSQL As String 'supposing that function
' BuildStrSQL returns with string but in original version at
' function gives Boolean value
If Not EntriesValid Then Exit Sub
'If Not BuildSQLString(strSQL) Then
' MsgBox "There was a problem building the SQL string"
' Exit Sub
'End If
' in order to see strSQL string I can use Msgbox, but it will
' be cancelled from this development stage
strReturnSQL = BuildSQLString
MsgBox strReturnSQL
' using strReturnSQL a query is being built before displaying found
' and matched records
CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString
' as Amy suggested to display results
Me.Refresh
' Amy suggested to open form but I have already prepared report
' rptRecipesSelected
DoCmd.OpenReport "rptRecipesSelected", acViewPreview
Reports!rptRecipesSelected.RecordSource = strReturnSQL
Reports!rptRecipesSelected.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)
' when I open form frmCriteria I want that each combox is disabled
' just with checking on checkboxes I will be able to use comboboxes
cboCompanyID.Enabled = False
cboFormulationTypeID.Enabled = False
cboIngredient1ID.Enabled = False
cboIngredient2ID.Enabled = False
cboIngredient3ID.Enabled = False
End Sub
Function EntriesValid() As Boolean
'this function must control whether any checkbox is fired in appropriate
combobox
'any value is coming chosen or not. The return value is Boolean.
'ez a fuggveny azt ellenorzi, hogy amennyiben egy checkbox ki lett jelolve,
'ugy a megfelelo combolistben szinteen hozza lett e rendelve bemeneti
eerteek
Dim sMsg As String
Dim sBullet As String
sBullet = "*"
If chkCompanyID And IsNull(cboCompanyID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by company but " & _
"have not selected a company. Either select a company or " & _
"uncheck the ""Company is"" checkbox."
End If
If chkFormulationTypeID And IsNull(cboFormulationTypeID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by Formulation Type but " & _
"have not selected a Formulation Type. Either select a Formlation Type
or " & _
"uncheck the ""Formulation Type"" checkbox."
End If
If chkIngredient1ID And IsNull(cboIngredient1ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a first ingredient. Either select a first ingredient
or " & _
"uncheck the ""First component"" checkbox."
End If
If chkIngredient2ID And IsNull(cboIngredient2ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a second ingredient. Either select a second
ingredient or " & _
"uncheck the ""Second component"" checkbox."
End If
If chkIngredient3ID And IsNull(cboIngredient3ID) Then
sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
"have not selected a third ingredient. Either select a third ingredient
or " & _
"uncheck the ""Third component"" checkbox."
End If
If sMsg <> "" Then
MsgBox "The following errors are preventing you from " & _
"viewing the records that match the criteria " & _
"you have selected:" & sMsg & vbCrLf & vbCrLf & _
"Please correct these errors and try again.", _
vbExclamation + vbOKOnly
Else
EntriesValid = True
End If
End Function
Function BuildSQLString() As String
'Function BuildSQLString(strSQL As String) As Boolean
' alternatively strSQL declaration was given as parameter
' and function return value was declared as Boolean
Dim strSQL As String
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Me.Refresh ' as suggested Amy
strSELECT = "s.* "
strFROM = "Recipes s "
If chkIngredient1ID = True Then ' true choose ingredient from combobox
CboIngredient1ID
' Amy suggested to add parentheses to FROM statement
strFROM = "(" & strFROM & "INNER JOIN [Recipe Detailed] i " & _
"ON s.RecipeID=i.RecipeID)"
strWHERE = " AND i.IngredientID= " & cboIngredient1ID
End If
If chkIngredient2ID = True Then ' true choose ingredient from combobox
CboIngredient2ID
strFROM = strFROM & "INNER JOIN [Recipe Detailed] i2 " & _
"ON s.RecipeID=i2.RecipeID "
strWHERE = strWHERE & " AND i2.IngredientID= " & cboIngredient2ID
End If
If chkIngredient3ID = True Then 'true choose ingredient from combobox
CboIngredient2ID
strFROM = strFROM & "INNER JOIN [Recipe Detailed] i3 " & _
"ON s.RecipeID=i3.RecipeID "
strWHERE = strWHERE & " AND i3.IngredientID= " & cboIngredient3ID
End If
If chkCompanyID = True Then
strWHERE = strWHERE & " AND s.CustomerID = " & cboCompanyID
End If
If chkFormulationTypeID = True Then
strWHERE = strWHERE & " AND s.FormulationTypeID = " &
cboFormulationTypeID
End If
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then
strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString = strSQL
End Function
Best regards
Peter
|
|
|
View Cart
