user and query criteria

  • Thread starter Thread starter shiro
  • Start date Start date
S

shiro

Hi All,
I want to be able to switch of the query criteria of my form record source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
 
In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause and
the following double quote character
Note: there is a single space between the end of strWhere and the following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
 
If this form is *not* [Find data_form], you could use the Open event
procedure of the form to assign the correct RecordSource. Example:

Private Sub Form_Open(Cancel As Integer)
Dim strWhere As String
Const strcStub = "SELECT [Table1].* FROM [Table1] "
Const strcTail = " ORDER BY SomeField;"

If User = "A" Then
strWhere = "WHERE (([Inspection result_tbl].Model = """ & _
[Forms]![Find data_frm]![list_Model] & _
""") AND ([Inspection result_tbl].[Lot no] = """ & _
[Forms]![Find data_frm]![list_Lotno] & """)"
Else
'whatever
End If

Me.RecordSource = strcStub & strWhere & strcTail
End Sub
 
Thank's All,
but one more question.If we totally put all the sql statement
in the vba code,does it mean I don't need the query psychally?
Thank's for reply


Allen Browne said:
If this form is *not* [Find data_form], you could use the Open event
procedure of the form to assign the correct RecordSource. Example:

Private Sub Form_Open(Cancel As Integer)
Dim strWhere As String
Const strcStub = "SELECT [Table1].* FROM [Table1] "
Const strcTail = " ORDER BY SomeField;"

If User = "A" Then
strWhere = "WHERE (([Inspection result_tbl].Model = """ & _
[Forms]![Find data_frm]![list_Model] & _
""") AND ([Inspection result_tbl].[Lot no] = """ & _
[Forms]![Find data_frm]![list_Lotno] & """)"
Else
'whatever
End If

Me.RecordSource = strcStub & strWhere & strcTail
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

shiro said:
Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
Click to expand...
Click to expand...
 
Since the controls are still bound to fields, Access expects those fields to
come from somewhere, so typically I still provide a query statement in the
form's RecordSource anyway -- even though it's used only in design view.
 
Dear Ms Jeanette and Mr Allen
I've got syntax error on below line

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "

and how to add one more line of below statement
to the vba code window;

FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON [Model
specification_tbl].Model = [Inspection result_tbl].Model

Sorry if my question disturbing you and Thank's for the help.



Jeanette Cunningham said:
In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause and
the following double quote character
Note: there is a single space between the end of strWhere and the following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
---------------------
replace txtUser, A, B , your select part of the query here, ORDER BY ...
with the appropriate things on your form.

Jeanette Cunningham

shiro said:
Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
Click to expand...
Click to expand...
 
You can't put double quotes inside of double quotes like that. You either
need to double up on the double quotes inside (that's a confusing sentence
isn't it! <g>):

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK"")) "

or use single quotes as the delimiter inside the string:

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)='OK')) "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


shiro said:
Dear Ms Jeanette and Mr Allen
I've got syntax error on below line

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "

and how to add one more line of below statement
to the vba code window;

FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model
specification_tbl].Model = [Inspection result_tbl].Model

Sorry if my question disturbing you and Thank's for the help.



Jeanette Cunningham said:
In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause
and
the following double quote character
Note: there is a single space between the end of strWhere and the following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK"))
"
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
---------------------
replace txtUser, A, B , your select part of the query here, ORDER BY ...
with the appropriate things on your form.

Jeanette Cunningham

shiro said:
Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry,Now the code return a error message..
Run Time error 2176 :
"The setting for this property is too long"
and highlight
Me.RecordSource = strSQL
I don't remove the criteria on the actual query.And it seem the criteria
don't
work at all,cause the form return all records,and it seem some another code
become not work properly.
Here is the excatly what I've wrote in my vba window code :

Dim strSQL As String
Dim strWhere As String

Const cstrStub = " SELECT [Inspection result_tbl].Model, [Model
specification_tbl].[Rotation speed No]," _
& "[Model specification_tbl].Customer, [Model specification_tbl].[Remark
free air current spec_1]," _
& "[Model specification_tbl].[Free air current lo limit_1], [Model
specification_tbl].[Free air current hi limit_1]," _
& "[Model specification_tbl].[Remark rotation speed spec_1], [Model
specification_tbl].[Rotation speed lo limit_1]," _
& "[Model specification_tbl].[Rotation speed hi limit_1], [Model
specification_tbl].[Remark lock current spec_1]," _
& "[Model specification_tbl].[Lock current lo limit_1], [Model
specification_tbl].[Lock current hi limit_1]," _
& "[Model specification_tbl].[Remark free air current spec_2], [Model
specification_tbl].[Free air current lo limit_2]," _
& "[Model specification_tbl].[Free air current hi limit_2], [Model
specification_tbl].[Remark rotation speed spec_2]," _
& "[Model specification_tbl].[Rotation speed lo limit_2], [Model
specification_tbl].[Rotation speed hi limit_2]," _
& "[Model specification_tbl].[Remark lock current spec_2], [Model
specification_tbl].[Lock current lo limit_2]," _
& "[Model specification_tbl].[Lock current hi limit_2], [Inspection
result_tbl].[Inspection date]," _
& "[Inspection result_tbl].[Input date], [Inspection result_tbl].[Lot no],
[Inspection result_tbl].[Input voltage]," _
& "([Input voltage]*[Current_1]) AS WattCurr1, [Inspection
result_tbl].Current_1, [Inspection result_tbl].RPM_1, " _
& "([Input voltage]*[Lock current_1]) AS WattLockCurr1, [Inspection
result_tbl].[Lock current_1]," _
& "([Input voltage]*[Current_2]) AS WattCurr2, [Inspection
result_tbl].Current_2, [Inspection result_tbl].RPM_2," _
& "([Input voltage]*[Lock current_2]) AS WattLockCurr2, [Inspection
result_tbl].[Lock current_2]," _
& "[Inspection result_tbl].Judge, [Inspection result_tbl].Inspector" _
& "FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model specification_tbl]." _
& "Model = [Inspection result_tbl].Model"

Const cstrTail = " ORDER BY [Inspection result_tbl].[Input date];"


strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Inspection date])=" _
& "[Forms]![Find data_frm]![list_InspectionDate]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

'add to strWhere
If Me.Password_txt.Value = "123" Then
Me.cmd_Edit.Visible = True
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK"")) "

Else
Me.cmd_Edit.Visible = False
End If

strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
'it's highlighted the below line
Me.RecordSource = strSQL



Douglas J. Steele said:
You can't put double quotes inside of double quotes like that. You either
need to double up on the double quotes inside (that's a confusing sentence
isn't it! <g>):

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK"")) "

or use single quotes as the delimiter inside the string:

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)='OK')) "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


shiro said:
Dear Ms Jeanette and Mr Allen
I've got syntax error on below line

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "

and how to add one more line of below statement
to the vba code window;

FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model
specification_tbl].Model = [Inspection result_tbl].Model

Sorry if my question disturbing you and Thank's for the help.



Jeanette Cunningham said:
In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause
and
the following double quote character
Note: there is a single space between the end of strWhere and the following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK"))
"
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
---------------------
replace txtUser, A, B , your select part of the query here, ORDER BY ....
with the appropriate things on your form.

Jeanette Cunningham

Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
Click to expand...
Click to expand...
Click to expand...
 
Shiro,
here is an alternative way to do this, easier for this form.
Create a query in the query design grid.
Select all the tables you need and all the fields you need.
---------------------------------------
--In the column for Inspection result_tbl.Model, in the criteria row type
[Forms]![Find data_frm]![list_Model])

--In the column for Inspection result_tbl.Inspection date, in the criteria
row type
[Forms]![Find data_frm]![list_InspectionDate])

--In the column for Inspection result_tbl.Lot no, in the criteria row type
[Forms]![Find data_frm]![list_Lotno]

--Save this as qryUserA
----------------------------------

Now make a copy of qryUserA and name it qryUserB
Make these changes to qryUserB
------------------------------------
--In the column for Inspection result_tbl.Model, in the criteria row type
[Forms]![Find data_frm]![list_Model])

--In the column for Inspection result_tbl.Inspection date, in the criteria
row type
[Forms]![Find data_frm]![list_InspectionDate])

--In the column for Inspection result_tbl.Lot no, in the criteria row type
[Forms]![Find data_frm]![list_Lotno]

--In the column for Inspection result_tbl.Judge, in the criteria row type
"OK"
--save the query
---------------------------------------

Comment out all the code you already have on the form's Load event
Put this code in the form's Load event

If Me.Password_txt.Value = "123" Then
Me.cmd_Edit.Visible = True
Me.RecordSource = "qryUserB"
Else
Me.cmd_Edit.Visible = False
Me.RecordSource = "qryUserA"
End If

Jeanette Cunningham



shiro said:
I'm sorry,Now the code return a error message..
Run Time error 2176 :
"The setting for this property is too long"
and highlight
Me.RecordSource = strSQL
I don't remove the criteria on the actual query.And it seem the criteria
don't
work at all,cause the form return all records,and it seem some another
code
become not work properly.
Here is the excatly what I've wrote in my vba window code :

Dim strSQL As String
Dim strWhere As String

Const cstrStub = " SELECT [Inspection result_tbl].Model, [Model
specification_tbl].[Rotation speed No]," _
& "[Model specification_tbl].Customer, [Model specification_tbl].[Remark
free air current spec_1]," _
& "[Model specification_tbl].[Free air current lo limit_1], [Model
specification_tbl].[Free air current hi limit_1]," _
& "[Model specification_tbl].[Remark rotation speed spec_1], [Model
specification_tbl].[Rotation speed lo limit_1]," _
& "[Model specification_tbl].[Rotation speed hi limit_1], [Model
specification_tbl].[Remark lock current spec_1]," _
& "[Model specification_tbl].[Lock current lo limit_1], [Model
specification_tbl].[Lock current hi limit_1]," _
& "[Model specification_tbl].[Remark free air current spec_2], [Model
specification_tbl].[Free air current lo limit_2]," _
& "[Model specification_tbl].[Free air current hi limit_2], [Model
specification_tbl].[Remark rotation speed spec_2]," _
& "[Model specification_tbl].[Rotation speed lo limit_2], [Model
specification_tbl].[Rotation speed hi limit_2]," _
& "[Model specification_tbl].[Remark lock current spec_2], [Model
specification_tbl].[Lock current lo limit_2]," _
& "[Model specification_tbl].[Lock current hi limit_2], [Inspection
result_tbl].[Inspection date]," _
& "[Inspection result_tbl].[Input date], [Inspection result_tbl].[Lot no],
[Inspection result_tbl].[Input voltage]," _
& "([Input voltage]*[Current_1]) AS WattCurr1, [Inspection
result_tbl].Current_1, [Inspection result_tbl].RPM_1, " _
& "([Input voltage]*[Lock current_1]) AS WattLockCurr1, [Inspection
result_tbl].[Lock current_1]," _
& "([Input voltage]*[Current_2]) AS WattCurr2, [Inspection
result_tbl].Current_2, [Inspection result_tbl].RPM_2," _
& "([Input voltage]*[Lock current_2]) AS WattLockCurr2, [Inspection
result_tbl].[Lock current_2]," _
& "[Inspection result_tbl].Judge, [Inspection result_tbl].Inspector" _
& "FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model specification_tbl]." _
& "Model = [Inspection result_tbl].Model"

Const cstrTail = " ORDER BY [Inspection result_tbl].[Input date];"


strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Inspection date])=" _
& "[Forms]![Find data_frm]![list_InspectionDate]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

'add to strWhere
If Me.Password_txt.Value = "123" Then
Me.cmd_Edit.Visible = True
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK""))
"

Else
Me.cmd_Edit.Visible = False
End If

strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
'it's highlighted the below line
Me.RecordSource = strSQL



Douglas J. Steele said:
You can't put double quotes inside of double quotes like that. You either
need to double up on the double quotes inside (that's a confusing
sentence
isn't it! <g>):

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK"")) "

or use single quotes as the delimiter inside the string:

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)='OK')) "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


shiro said:
Dear Ms Jeanette and Mr Allen
I've got syntax error on below line

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "

and how to add one more line of below statement
to the vba code window;

FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model
specification_tbl].Model = [Inspection result_tbl].Model

Sorry if my question disturbing you and Thank's for the help.



In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause
and
the following double quote character
Note: there is a single space between the end of strWhere and the
following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection
result_tbl].Judge)="OK"))
"
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
---------------------
replace txtUser, A, B , your select part of the query here, ORDER BY ...
with the appropriate things on your form.

Jeanette Cunningham

Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
Click to expand...
Click to expand...
Click to expand...
 
Ms Jeanette,
That is the way currently I walk on,
but sometimes we just need a more challenge
to make us satisfied.
Thank's


Jeanette Cunningham said:
Shiro,
here is an alternative way to do this, easier for this form.
Create a query in the query design grid.
Select all the tables you need and all the fields you need.
---------------------------------------
--In the column for Inspection result_tbl.Model, in the criteria row type
[Forms]![Find data_frm]![list_Model])

--In the column for Inspection result_tbl.Inspection date, in the criteria
row type
[Forms]![Find data_frm]![list_InspectionDate])

--In the column for Inspection result_tbl.Lot no, in the criteria row type
[Forms]![Find data_frm]![list_Lotno]

--Save this as qryUserA
----------------------------------

Now make a copy of qryUserA and name it qryUserB
Make these changes to qryUserB
------------------------------------
--In the column for Inspection result_tbl.Model, in the criteria row type
[Forms]![Find data_frm]![list_Model])

--In the column for Inspection result_tbl.Inspection date, in the criteria
row type
[Forms]![Find data_frm]![list_InspectionDate])

--In the column for Inspection result_tbl.Lot no, in the criteria row type
[Forms]![Find data_frm]![list_Lotno]

--In the column for Inspection result_tbl.Judge, in the criteria row type
"OK"
--save the query
---------------------------------------

Comment out all the code you already have on the form's Load event
Put this code in the form's Load event

If Me.Password_txt.Value = "123" Then
Me.cmd_Edit.Visible = True
Me.RecordSource = "qryUserB"
Else
Me.cmd_Edit.Visible = False
Me.RecordSource = "qryUserA"
End If

Jeanette Cunningham



shiro said:
I'm sorry,Now the code return a error message..
Run Time error 2176 :
"The setting for this property is too long"
and highlight
Me.RecordSource = strSQL
I don't remove the criteria on the actual query.And it seem the criteria
don't
work at all,cause the form return all records,and it seem some another
code
become not work properly.
Here is the excatly what I've wrote in my vba window code :

Dim strSQL As String
Dim strWhere As String

Const cstrStub = " SELECT [Inspection result_tbl].Model, [Model
specification_tbl].[Rotation speed No]," _
& "[Model specification_tbl].Customer, [Model specification_tbl].[Remark
free air current spec_1]," _
& "[Model specification_tbl].[Free air current lo limit_1], [Model
specification_tbl].[Free air current hi limit_1]," _
& "[Model specification_tbl].[Remark rotation speed spec_1], [Model
specification_tbl].[Rotation speed lo limit_1]," _
& "[Model specification_tbl].[Rotation speed hi limit_1], [Model
specification_tbl].[Remark lock current spec_1]," _
& "[Model specification_tbl].[Lock current lo limit_1], [Model
specification_tbl].[Lock current hi limit_1]," _
& "[Model specification_tbl].[Remark free air current spec_2], [Model
specification_tbl].[Free air current lo limit_2]," _
& "[Model specification_tbl].[Free air current hi limit_2], [Model
specification_tbl].[Remark rotation speed spec_2]," _
& "[Model specification_tbl].[Rotation speed lo limit_2], [Model
specification_tbl].[Rotation speed hi limit_2]," _
& "[Model specification_tbl].[Remark lock current spec_2], [Model
specification_tbl].[Lock current lo limit_2]," _
& "[Model specification_tbl].[Lock current hi limit_2], [Inspection
result_tbl].[Inspection date]," _
& "[Inspection result_tbl].[Input date], [Inspection result_tbl].[Lot no],
[Inspection result_tbl].[Input voltage]," _
& "([Input voltage]*[Current_1]) AS WattCurr1, [Inspection
result_tbl].Current_1, [Inspection result_tbl].RPM_1, " _
& "([Input voltage]*[Lock current_1]) AS WattLockCurr1, [Inspection
result_tbl].[Lock current_1]," _
& "([Input voltage]*[Current_2]) AS WattCurr2, [Inspection
result_tbl].Current_2, [Inspection result_tbl].RPM_2," _
& "([Input voltage]*[Lock current_2]) AS WattLockCurr2, [Inspection
result_tbl].[Lock current_2]," _
& "[Inspection result_tbl].Judge, [Inspection result_tbl].Inspector" _
& "FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model specification_tbl]." _
& "Model = [Inspection result_tbl].Model"

Const cstrTail = " ORDER BY [Inspection result_tbl].[Input date];"


strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Inspection date])=" _
& "[Forms]![Find data_frm]![list_InspectionDate]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

'add to strWhere
If Me.Password_txt.Value = "123" Then
Me.cmd_Edit.Visible = True
strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK""))
"

Else
Me.cmd_Edit.Visible = False
End If

strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
'it's highlighted the below line
Me.RecordSource = strSQL



Douglas J. Steele said:
You can't put double quotes inside of double quotes like that. You either
need to double up on the double quotes inside (that's a confusing
sentence
isn't it! <g>):

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)=""OK"")) "

or use single quotes as the delimiter inside the string:

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)='OK')) "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear Ms Jeanette and Mr Allen
I've got syntax error on below line

strWhere = strWhere & "AND (([Inspection result_tbl].Judge)="OK")) "

and how to add one more line of below statement
to the vba code window;

FROM [Model specification_tbl] RIGHT JOIN [Inspection result_tbl] ON
[Model
specification_tbl].Model = [Inspection result_tbl].Model

Sorry if my question disturbing you and Thank's for the help.



In the form's load event put code something like this:
make sure to leave a single space between the end of the select clause
and
the following double quote character
Note: there is a single space between the end of strWhere and the
following
double quote character
------------------
Dim strSQL as String
Dim strWhere as String

const cstrStub = "your select part of the query here"
const cstrTail = "ORDER BY ..."

strWhere = "WHERE ((([Inspection result_tbl].Model)=" _
& "[Forms]![Find data_frm]![list_Model]) " _
& "AND (([Inspection result_tbl].[Lot no])=" _
& "[Forms]![Find data_frm]![list_Lotno])) "

If Me.txtUser = "A" Then
'do nothing here, there is nothing to add to strWhere
ElseIf Me.txtUser = "B" Then
strWhere = strWhere & "AND (([Inspection
result_tbl].Judge)="OK"))
"
Else
'anything else you may need here
End If
strSQL = cstrStub & strWhere & cstrTail
Debug.Print strSQL
Me.RecordSource = strSQL
---------------------
replace txtUser, A, B , your select part of the query here, ORDER BY ...
with the appropriate things on your form.

Jeanette Cunningham

Hi All,
I want to be able to switch of the query criteria of my form record
source,
depending on the user name.May be On Load even of my form wee need
to write some code but I always fail.

Please help:
If the user is A then the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno]))

And if the user is B the query's criteria is :

WHERE ((([Inspection result_tbl].Model)=[Forms]![Find
data_frm]![list_Model])
AND (([Inspection result_tbl].[Lot no])=[Forms]![Find
data_frm]![list_Lotno])
AND (([Inspection result_tbl].Judge)="OK"))

Thank's
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

Repost : Mission to populate record through a make table query 2
Dcount return incorrect result for filtered record 4
union query 10
Shown all record in a list box from combobox. 1
Monthly Inspection Database 0
Error on command button 2
Data Type Mismatch in Criteria Expression 17
New Record Command Button 3

Back
Top