NEED HELP! User-level security/SQL question

M

Matt Mcd

I am trying to apply user level security to my database. After defining the
user group, I found that a control button does not run properly for the
"User" level security login. The button runs a search by creating tables for
data that fit a specified criteria and then runs a query to select those
names that appear in each table. For "Users", the command button runs the
query but does not run any of the SQL commands so that the query pulls from
data tables that were not updated.

Any help on getting this thing to work for "Users" would be greatly
appreciated. Below is the SQL language in case it helps illustrate the
situation.

Private Sub Command29_Click()
On Error Resume Next

DoCmd.SetWarnings (0)

Dim stDocName As String

stDocName = "QrySearch2"

'Delete search table if it already exists
Call dropTables
'Create new table to find data
Call createTables

'Search
If (IsNull(PE_Descr.Value) = False) And (IsNull(PE_Descr2.Value) = False)
And (IsNull(PE_Descr3.Value) = False) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into TblSearchRange FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Descr like '*" & PE_Descr.Value & "*' And
[Tbl_PE_Fund].PE_Descr like '*" & PE_Descr2.Value & "*' And
[Tbl_PE_Fund].PE_Descr like '*" & PE_Descr3.Value & "*'"

DoCmd.RunSQL (qstr)
GoTo CitySearch

ElseIf (IsNull(PE_Descr.Value) = False) And (IsNull(PE_Descr2.Value) =
False) And (IsNull(PE_Descr3.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into TblSearchRange FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Descr like '*" & PE_Descr.Value & "*' And
[Tbl_PE_Fund].PE_Descr like '*" & PE_Descr2.Value & "*'"

DoCmd.RunSQL (qstr)
GoTo CitySearch

ElseIf (IsNull(PE_Descr.Value) = False) And (IsNull(PE_Descr2.Value) = True)
And (IsNull(PE_Descr3.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into TblSearchRange FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Descr like '*" & PE_Descr.Value & "*'"

DoCmd.RunSQL (qstr)
GoTo CitySearch

ElseIf (IsNull(PE_Descr.Value) = True) And (IsNull(PE_Descr2.Value) = True)
And (IsNull(PE_Descr3.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into TblSearchRange FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo CitySearch

End If

CitySearch:

If (IsNull(PE_City.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchCity FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo StateSearch

ElseIf (IsNull(PE_City.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchCity FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_City like '*" & PE_City.Value & "*' "

DoCmd.RunSQL (qstr)
GoTo StateSearch

End If

StateSearch:

If (IsNull(PE_State.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchState FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo EquitySearch

ElseIf (IsNull(PE_State.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchCity FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_State = PE_State.Value"

DoCmd.RunSQL (qstr)
GoTo EquitySearch

End If

EquitySearch:

If (IsNull(PE_Equity_Min.Value) = True) And (IsNull(PE_Equity_Max.Value) =
True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEquity FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo EVSearch

ElseIf (IsNull(PE_Equity_Min.Value) = False) And
(IsNull(PE_Equity_Max.Value) = True) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEquity FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_Equity_Min > PE_Equity_Min.Value"

DoCmd.RunSQL (qstr)
GoTo EVSearch

ElseIf (IsNull(PE_Equity_Min.Value) = True) And (IsNull(PE_Equity_Max.Value)
= False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEquity FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_Equity_Max < PE_Equity_Max.Value + 1"

DoCmd.RunSQL (qstr)
GoTo EVSearch

ElseIf (IsNull(PE_Equity_Min.Value) = False) And
(IsNull(PE_Equity_Max.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEquity FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_Equity_Max < PE_Equity_Max.Value + 1 And
[Tbl_PE_Fund].PE_Equity_Min > PE_Equity_Min.Value - 1"

DoCmd.RunSQL (qstr)
GoTo EVSearch

End If

EVSearch:

If (IsNull(PE_EV_Min.Value) = True) And (IsNull(PE_EV_Max.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEV FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo SalesSearch

ElseIf (IsNull(PE_EV_Min.Value) = False) And (IsNull(PE_EV_Max.Value) =
True) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEV FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_EV_Min > PE_EV_Min.Value"

DoCmd.RunSQL (qstr)
GoTo SalesSearch

ElseIf (IsNull(PE_EV_Min.Value) = True) And (IsNull(PE_EV_Max.Value) =
False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEV FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_EV_Max < PE_EV_Max.Value + 1"

DoCmd.RunSQL (qstr)
GoTo SalesSearch

ElseIf (IsNull(PE_EV_Min.Value) = False) And (IsNull(PE_EV_Max.Value) =
False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEV FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_EV_Max < PE_EV_Max.Value + 1 And
[Tbl_PE_Fund].PE_EV_Min > PE_EV_Min.Value - 1"

DoCmd.RunSQL (qstr)
GoTo SalesSearch

End If

SalesSearch:

If (IsNull(PE_Sales_Min.Value) = True) And (IsNull(PE_Sales_Max.Value) =
True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchSales FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo EBITDASearch

ElseIf (IsNull(PE_Sales_Min.Value) = False) And (IsNull(PE_Sales_Max.Value)
= True) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchSales FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Sales_Min > PE_Sales_Min.Value"

DoCmd.RunSQL (qstr)
GoTo EBITDASearch

ElseIf (IsNull(PE_Sales_Min.Value) = True) And (IsNull(PE_Sales_Max.Value) =
False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchSales FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Sales_Max < PE_Sales_Max.Value + 1"

DoCmd.RunSQL (qstr)
GoTo EBITDASearch

ElseIf (IsNull(PE_Sales_Min.Value) = False) And (IsNull(PE_Sales_Max.Value)
= False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchSales FROM Tbl_PE_Fund " & _
"WHERE [Tbl_PE_Fund].PE_Sales_Max < PE_Sales_Max.Value + 1 And
[Tbl_PE_Fund].PE_Sales_Min > PE_Sales_Min.Value - 1"

DoCmd.RunSQL (qstr)
GoTo EBITDASearch

End If

EBITDASearch:

If (IsNull(PE_EBITDA_Min.Value) = True) And (IsNull(PE_EBITDA_Max.Value) =
True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEBITDA FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo FundSizeSearch

ElseIf (IsNull(PE_EBITDA_Min.Value) = False) And
(IsNull(PE_EBITDA_Max.Value) = True) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEBITDA FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_EBITDA_Min > PE_EBITDA_Min.Value"

DoCmd.RunSQL (qstr)
GoTo FundSizeSearch

ElseIf (IsNull(PE_EBITDA_Min.Value) = True) And (IsNull(PE_EBITDA_Max.Value)
= False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEBITDA FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_EBITDA_Max < PE_EBITDA_Max.Value + 1"

DoCmd.RunSQL (qstr)
GoTo FundSizeSearch

ElseIf (IsNull(PE_EBITDA_Min.Value) = False) And
(IsNull(PE_EBITDA_Max.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchEBITDA FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_EBITDA_Max < PE_EBITDA_Max.Value + 1 And
[Tbl_PE_Fund].PE_EBITDA_Min > PE_EBITDA_Min.Value - 1"

DoCmd.RunSQL (qstr)
GoTo FundSizeSearch

End If

FundSizeSearch:

If (IsNull(PE_Fund_Size_Min.Value) = True) And
(IsNull(PE_Fund_Size_Max.Value) = True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchFundSize FROM Tbl_PE_Fund
" & _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo KeywordSearch

ElseIf (IsNull(PE_Fund_Size_Min.Value) = False) And
(IsNull(PE_Fund_Size_Max.Value) = True) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchFundSize FROM Tbl_PE_Fund
" & _
"WHERE [Tbl_PE_Fund].PE_Fund_Size > PE_Fund_Size_Min.Value"

DoCmd.RunSQL (qstr)
GoTo KeywordSearch

ElseIf (IsNull(PE_Fund_Size_Min.Value) = True) And
(IsNull(PE_Fund_Size_Max.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchFundSize FROM Tbl_PE_Fund
" & _
"WHERE [Tbl_PE_Fund].PE_Fund_Size < PE_Fund_Size_Max.Value + 1"

DoCmd.RunSQL (qstr)
GoTo KeywordSearch

ElseIf (IsNull(PE_Fund_Size_Min.Value) = False) And
(IsNull(PE_Fund_Size_Max.Value) = False) Then

qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchFundSize FROM Tbl_PE_Fund
" & _
"WHERE [Tbl_PE_Fund].PE_Fund_Size < PE_Fund_Size_Max.Value + 1 And
[Tbl_PE_Fund].PE_Fund_Size > PE_Fund_Size_Min.Value - 1"

DoCmd.RunSQL (qstr)
GoTo KeywordSearch

End If

KeywordSearch:

If (IsNull(PE_Keyword1.Value) = True) And (IsNull(PE_Keyword2.Value) = True)
Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchKeyword FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_ID > 0"

DoCmd.RunSQL (qstr)
GoTo IndustrySearch

ElseIf (IsNull(PE_Keyword1.Value) = False) And (IsNull(PE_Keyword2.Value) =
False) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchKeyword FROM Tbl_PE_Fund "
& _
"WHERE ([Tbl_PE_Fund].PE_CP_1 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_2 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_3 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_4 like '*" & PE_Keyword1.Value & "*') And
([Tbl_PE_Fund].PE_CP_1 like '*" & PE_Keyword2.Value & "*' Or
[Tbl_PE_Fund].PE_CP_2 like '*" & PE_Keyword2.Value & "*' Or
[Tbl_PE_Fund].PE_CP_3 like '*" & PE_Keyword2.Value & "*' Or
[Tbl_PE_Fund].PE_CP_4 like '*" & PE_Keyword2.Value & "*')"

DoCmd.RunSQL (qstr)
GoTo IndustrySearch

ElseIf (IsNull(PE_Keyword1.Value) = False) And (IsNull(PE_Keyword2.Value) =
True) Then
qstr = "SELECT [Tbl_PE_Fund].PE_ID into Tbl_SearchKeyword FROM Tbl_PE_Fund "
& _
"WHERE [Tbl_PE_Fund].PE_CP_1 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_2 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_3 like '*" & PE_Keyword1.Value & "*' Or
[Tbl_PE_Fund].PE_CP_4 like '*" & PE_Keyword1.Value & "*'"

DoCmd.RunSQL (qstr)
GoTo Finish:
End If

Finish:

stDocName = "QrySearch2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click

End Sub
 

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

Word macro that can call cells out of Excel for a find and replace 0
Day/Time Format 5
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Convert old wordbasic code into VBA that will run now 0
Error Message - Please Help 2
Calculation Problem 1
open a table to update or add record 7
Making a macro only work on certain files 2

Top