get the varilbe " strinput" into a query (1 Viewer)

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
Code:
Private Sub Command370_Click()

Dim strInput As String
Dim strMsg As String

Beep
strMsg = "Please Enter  the password to allow access."
strInput = InputBox(Prompt:=strMsg, Title:="Admin Password")
If strInput = "CH1" Then




the code is used to allow someone * could be 5 people" from admin to enter a certain form to monitor the details from many records , I have a log to monitor if anyone changes the target date field , what I would like to do is also monitor who changed it therefore is it possible to get the password into the query ? thanks steve
 

isladogs

MVP / VIP
Local time
Today, 16:57
Joined
Jan 14, 2017
Messages
18,186
I'm not clear what your purpose is but it might help if you showed all your code

In answer to the title of the thread, variables cannot be used directly in queries.
You have two choices
1. Create a function that reads the variable then use the function in the query
2. Use a tempvar instead of a variable. Those can be used directly in queries
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
Code:
Private Sub Command370_Click()

Dim strInput As String
Dim strMsg As String

Beep
strMsg = "Please Enter  the password to allow access."
strInput = InputBox(Prompt:=strMsg, Title:="Admin Password")
If strInput = "CH1" Then

'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

'DoCmd.ApplyFilter , "company = 'PPI -ENGINEERING'"

DoCmd.ApplyFilter , "company = '1'"
DoCmd.Close acForm, Me.Name
'DoCmd.Close acForm, Me.Name

ElseIf strInput = "PW1" Then

'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

'DoCmd.ApplyFilter , "company = 'PPI -ENGINEERING'"

DoCmd.ApplyFilter , "company = '1'"
DoCmd.Close acForm, Me.Name


ElseIf strInput = "ew1" Then
'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

DoCmd.ApplyFilter , "company = '2'"

DoCmd.Close acForm, Me.Name


ElseIf strInput = "PW2" Then

'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

'DoCmd.ApplyFilter , "company = 'PPI -ENGINEERING'"

DoCmd.ApplyFilter , "company = '2'"
DoCmd.Close acForm, Me.Name

ElseIf strInput = "lm1" Then
'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

DoCmd.ApplyFilter , "company = '3'"

DoCmd.Close acForm, Me.Name



ElseIf strInput = "mrall" Then
'MsgBox "Password accepted! Welcome!     " & "Mike" 'password is correct
DoCmd.OpenForm "issue list"

'DoCmd.ApplyFilter , "company = '3'"

DoCmd.Close acForm, Me.Name





Else 'password is incorrect
MsgBox "Incorrect Password!" & vbCrLf & vbLf & "You are not allowed access to the ''issue list''.", vbCritical, "Invalid Password"
Exit Sub
End If

End Sub





1638870499875.png



above is the code and the query I have

steve
 

isladogs

MVP / VIP
Local time
Today, 16:57
Joined
Jan 14, 2017
Messages
18,186
Please explain what the code is meant to do and provide the SQL for that query.
Have you tried doing either of the things I suggested?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:57
Joined
Sep 21, 2011
Messages
14,044
If you indented your code, it would probably help you ? :(
 

oleronesoftwares

Passionate Learner
Local time
Today, 09:57
Joined
Sep 22, 2014
Messages
1,159
the code is used to allow someone * could be 5 people" from admin to enter a certain form to monitor the details from many records , I have a log to monitor if anyone changes the target date field , what I would like to do is also monitor who changed it therefore is it possible to get the password into the query ? thanks steve
What you need inside a query is the user name of the person, not password, other users passwords should not be made available available even to admin
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
this database is for 3 different companies . all owned by one person , each company will have an admin person to oversea reports, on time , late , and monitor the performance etc etc. when admin logs in it know to only bring up that companies information is company 1 ,2, or 3 the owner and the quality supervisor who will conduct the meeting to review all issues raised for each company also has access to admin. the admin people for each company could change details in each form I want to record who did it, was it for example in company 1 ch1, pw1, or mrall . so company 1 is ppi , lets say the target date gets changed I want to know who changed it



sql is

INSERT INTO logs ( ARNO1, TIMECHANGE, TARGETDATE, COMPANY, DATECHANGE1, name )
SELECT Issues.ARNO, Time() AS Expr1, [Forms]![Issue List]![TargetDate] AS Expr2, Issues.company, Date() AS Expr3, [cboCurrentEmployee] AS Expr4
FROM Issues
WHERE (((Issues.ARNO)=[Forms]![Issue List]![ARNO]));



thanks steve


1638880320590.png







1638880225545.png
 

isladogs

MVP / VIP
Local time
Today, 16:57
Joined
Jan 14, 2017
Messages
18,186
Your query doesn't contain a variable so it doesn't seem relevant to your original post.
Where EXACTLY are you trying to insert a variable?
And I'll ask again....have you tried doing either of the things I mentioned in post #2? If not, please do so
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
1 created at text box in my login form then put this code into my other code . put a text box into my issues for called up text34 and it was working ok .

Me.Text34.Value = strInput


however I found a error if someone is already in the form "issues list " as you will see ADMIN pw1 was signed in but when I tried to see what happened if ew1 treid to sign in this error came up . could we change the code so a message came up telling ew1 that someone is using the form

thanks steve







1638887826206.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:57
Joined
Feb 19, 2002
Messages
42,971
Does each user have his own personal copy of the FE? If not, you need to do that. The FE should NEVER, EVER be shared by multiple users. Only the BE, where the data is, should ever be shared. If you need help with the distribution of the FE, we can offer several solutions.

You should NOT hard code the passwords or userIDs. Define a user table with passwords and have them log in to the database. Include CompanyID in the user table so you can use that to filter the data.

And finally, do NOT provide dropdowns for logins. That makes it too easy for unauthorized users to log in. If you want to use Windows authentication, that's fine but you'll still need a table to associate users with companies for filtering.
 

isladogs

MVP / VIP
Local time
Today, 16:57
Joined
Jan 14, 2017
Messages
18,186
I'm sorry but you still haven't answered my questions so I'm going to drop out of this thread now
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
I was asked to create something if possible to record all our action requests for each company . The users have a company next to their name and a id number so when they select the company only the people in that company can log in . at this time the admin people are testing what I have done and give me feedback as to what is wrong and needs doing . although I am very limited to what I am doing.

when everyone is ok with what I have done . I was going to split the backend away. I thought then everyone would then be able to log nto the front end when they wanted to . but I think you are telling me this cannot or is not the correct way.. do I need to take copies of the front end and give them al a copies . or just create a shortcut on each desktop for them to access as and when required


[U]isladogs[/U] I have tried to do the tempvar and have seen some of your other threads covering this. but I could not get it to work but I used an example from one of them . of which I thought did and it seemed to work

thanks
steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:57
Joined
Feb 19, 2002
Messages
42,971
The FE should NEVER, EVER be shared. Only the BE, which holds the data, is shared. Each user must have his own personal copy of the FE. This is pretty easy to do. I use a shortcut that points to a batch file. Here's a sample:
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
The error may be caused because more than one person is opening the FE at a time.

Keep in mind that when the FE opens, Access loads what it needs into memory on the computer that opened the FE. That means that when multiple people have the same physical file open at one time, parts of that file are in memory on different computers. Some actions cause Access to save changes and that means that they have to write back to the shared file and current versions of Access do not support multiple users updating objects. Hence, you CANNOT share the FE. Older versions of Access did support this but it has always been unwise.

Even when you don't actually update anything, Access changes stuff in the FE. To prove this to yourself. Open the FE. Work with it for a while and close it. You will see that the update date has been changed.
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
The FE should NEVER, EVER be shared. Only the BE, which holds the data, is shared. Each user must have his own personal copy of the FE. This is pretty easy to do. I use a shortcut that points to a batch file. Here's a sample:
Code:
md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb
The error may be caused because more than one person is opening the FE at a time.

Keep in mind that when the FE opens, Access loads what it needs into memory on the computer that opened the FE. That means that when multiple people have the same physical file open at one time, parts of that file are in memory on different computers. Some actions cause Access to save changes and that means that they have to write back to the shared file and current versions of Access do not support multiple users updating objects. Hence, you CANNOT share the FE. Older versions of Access did support this but it has always been unwise.

Even when you don't actually update anything, Access changes stuff in the FE. To prove this to yourself. Open the FE. Work with it for a while and close it. You will see that the update date has been changed.
so just to confirm if I have 50 people who can input data , I will need 50 copies of the FE (ie one on each computer ) I did split a copy of the database and took 2 copies and could open the same form and work on both at the same time and it did update

thanks
steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:57
Joined
Feb 19, 2002
Messages
42,971
I didn't say you couldn't share the FE if nothing gets updated.

No expert will offer a different opinion than mine regarding the wisdom of sharing a FE. If you don't want to use the batch file to distribute new copies of the FE, then you can use one of the specialized databases that others have created for this purpose. The difference in the two solutions is that the batch file is extremely simple and every time the shortcut runs, a new copy of the FE is downloaded from the master folder on the server. The distribution databases usually use local and linked tables with version numbers in them and only download a new copy of the FE if the version actually changes. The time to download the FE is miniscule and it means that I never have to worry about compacting the FE so that Is why I always download a replacement FE each time. Depending on who wrote the download app, they might have chosen to do the same thing.

The reason I do it this way is efficiency. I distribute the FE in an uncompiled state. That means that the first time a user runs a query an execution plan is created and saved. This ensures that the queries are always up to date with the indexes and size of the tables they access so that the execution plan is as efficient as it can be. If you keep reusing the same FE, the saved execution plans can get stale and therefore inefficient.
 

rainbows

Registered User.
Local time
Today, 09:57
Joined
Apr 21, 2017
Messages
425
Pat , I was not /am not sure how to do the batch file and code etc . as I have never done anything lie that before .

so do I need to put the master FE into folder say " C:\Users\steve\Documents\Action Requests\FE" create a shortcut to that FE
then I am not sure where to put the code etc

thanks you for your help and patience

steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:57
Joined
Feb 19, 2002
Messages
42,971
1. Create a folder on the server. Make it hidden from browsing for safety (talk to your administrator)
2. Copy the "master copy of the FE into that folder (I also keep my master zipped copy there also as well as a zipped copy of the .bat file)
3. Create a batch file based on my sample and also place it into that folder. Having the .bat file on the server makes it easier for you if you ever have to change it.
4. Create a shortcut to run this batch file. Use the UNC name rather than a mapped drove. That eliminates issues in companies where there isn't consistency on drive mapping names. Notice how my .bat file refers to the master copy on the server with the name \\BSCCTIMBERLINE1...
5. Distribute the Shortcut to ALL users of your app. Again, get your network admin to help with this. He can do it easily. You will have to visit 50 desks to do it.

The batch file creates the local folder if it doesn't exist so the batch file doesn't have to be different for new or existing users. Then it deletes the existing file if it is there. No error is raised if the file is not there so again, the batch file works for both new and existing users. Then the file is copied from the server drive to the local folder and the final step runs the app from the local folder.

One thing to be wary of - .bat files are executable so if you double click on it , it runs. So, if you want to edit it, right click and click edit.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:57
Joined
Sep 21, 2011
Messages
14,044
Why delete the file and not just overwrite it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:57
Joined
Sep 21, 2011
Messages
14,044
I wanted to avoid the error message.
Hmm, TBH I though you could just use the /Y parameter, but having created
copy c:\temp\capture.png capturecopy.png as a batch file and executed it twice, it never complained about overwriting? :unsure:

1639084228112.png
 

Users who are viewing this thread

Top Bottom