I am trying to use the Environ Username function, which i have working! But i want to compare the result with a Username Table to determine if they have access etc...
View RepliesUsing Win 7 and Access 2007
db will be split and on a server
If it's possible: Would like to use the Environ username somewhere in the db so the user only sees their records.
Where would I place this? Or is their different code I should be using for this purpose???
How do I use the following as the default value of a textbox in Access2003?
Environ$("USERNAME")
Resolution:
Create Module and insert the following code:
Option Compare Database
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
On Error GoTo fOSUserName_Err
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
fOSUserName_Exit:
Exit Function
fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit
End Function
Use this function as default value
=fOSUserName()
Hi I am using the below code for getting Windows Username, this code is working fine when i am using this in lable, however if i use this in a query, it is not working, do it need some kind of autorun?
SELECT Associates.*
FROM Associates
WHERE (((Associates.CorpID)=Environ("UserName")));
Please help.
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code:
For x = 0 To 20
If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
i have a menu which recognises a user by using the environ function.
the problem i am getting is that with a small number of users the environ function isnt recognised by the system?
it gives them a #name# instead.
the crazy thing is that it also happens on simpler functions such as =Now() and =Date() which is really annoying, as these 3 things are a foundation on anything that is done within the system/!
the majority of users have no problems, which makes it even more annoying!
when i try to go into the properties of the label which has been assigned the function i get a message. (only on affected PC's)
see attached for pic
THANK YOU IN ADVANCE. HELP IS REALLY APPRECIATED!
I have noted a problem on 2 computers using this. There are around 30 or so computers on our network that work fine with this as a value. The majority of the users are at Access 2000 but some have Access XP. The offending machines have Access 2000
One day a persons computer crashed. DP imaged another persons machine and installed the image on the crashed machine as a solution. Since this the =Environ("username") returned #Name$ (not sure of the exact syntax on this). This same trouble surfaced on another machine recently. I unlocked the field so the users could enter their correct name but this can lead to errors and I would like to keep the username locked and read by code.
I checked the references on the offending machine and they were ok. Not sure what else there is to do to rectify this.
If anyone else has had a similar trouble and found the solution I would appreciate hearing about it.
Thanks a lot!!
-----------------------------
Edit:
I just checked the machine and corrected the syntax above. I also found that in a date search form where I have =now$() for a field, the same thing is displayed - #Name$ rather than the date.
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
i am using the following function in a query:
Code:
Public Function Progress(ByVal varCompany As Variant _
, ByVal varPercent As Variant) As Variant
Static varCompanyOld As Variant
Static varValueOld As Variant
Dim Value As Variant
If varCompany & vbNullChar = varCompanyOld Then
Value = (1 + varPercent) * varValueOld
varValueOld = Value
[code]....
This function is working if I use it with two or more companies.If I have only one company, it works at the first (query) run, too.But with the same company, a second (query) run calculates different results.
I think it is because of this condition:
Code:
If varCompany & vbNullChar = varCompanyOld Then ...
If the company name is e.g. Microsoft
At the first run of the query:
Microsoft & vbNullChar = "" -> wrong
The Else calculation will be carried out.
-> That is right for the first datapoint of a company
At a second run of the query:
varCompanyOld is saved as Microsoft & vbNullChar, so:
Microsoft & vbNullChar = Microsoft & vbNullChar -> True
The main calculation will be carried out.
-> That is wrong for the first datapoint of a company
I would like to ask how can i validate the username and password in a textbox? I have a frmLogin and frmMain.
I have also a table called 'tblUsers' with column fields 'username and password'.
If username is not found in database then a msgbox will prompt 'Username is not yet created'.
If username is okay and password is incorrect then msgbox will prompt 'Incorrect password'
If username and password is okay, then a msgbox will prompt 'Successfully login' and will continue to frmMain.
How can we store the first and last letter of the username using vba.
E.g. my name is "Daniel Rawlings" then it should display "DR".
I have a field that records comments call house comments i wonder is it possible to record which user leaves what comment?
Code:
=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([txtID],0))
I'm trying to find a code that will allow me to check a value of a yes/no checkbox based on a username.
What I want to happpen is:
1. The code looks to see if the username that is entered in "txtUsername" field on the form matches the "empUsername" value on the table "tblUsers."
2. If there is a match, I want it to look at the value for the field "Admin" on "tblUsers" to see if the value is checked as true.
3. If the value is true, I want it to open a specific form "frmAdmin." If it is false, I want it to open a form "frmMain."
VBA codes not recognizing the Admin field and instead taking all users into the frmMain.
I'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code:
If me.UsernameID = "KraussLJ" Or Environ$("Username") Then
DoCmd.OpenForm "frmShiftLog"
DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll
Else
MsgBox ("You are not authorized to edit this entry")
End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.
steps to perform:
1. Check each Reference in Table1 against all the values in "Reference" field of Table2.
2. if match occurs then store all the details of that Reference in Table3.
There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".
I have two access tables named as "DestructionTBL" and "AnnutiesTBL". Now I have to check each PolicyNumber of AnnutiesTBL with PolicyNumbers in "DestructionTBL". If match is found then add that PolicyNumber into "NODestructionTBL" and delete that PolicyNumber record from "DestructionTBL".
View 14 Replies View RelatedItem: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
In my database, I use TextStream.ReadLine to read a .txt file line by line and store pertinent parts of each line into specific fields in a table. One of these fields is called "Remarks", which is basically a descriptive paragraph of text explaining a task. Everything works great so far.
However, my leadership would like a condensed version of the "Remarks" field. The only way to really do this right now is for someone to manually read each "Remarks" field and create their own like condensed version of it. Let me give you an example...
Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."
Condensed Version: "Project Delta for IBS/SCADA and CIKR"
I've played with using Select Case to automatically create a condensed version of the Remarks field:
Code:
Select Case True
Case InStr(strRemarks, "SCADA") > 0 and _
InStr(strRemarks, "Project Delta") > 0:
!Condensed = "Project Delta for IBS/SCADA and CIKR"
End Select
However, this is too much VBA maintenance for each different thing that needs condensed. If a new tasking comes out, then I'll have to go into VBA and custom create a new Case for it. Multiply that by 10-20x each week.
Instead, I would like a form where my users can specify the criteria themselves. The Remarks field would be compared against the criteria to create a new condensed version of the Remarks field. I'm not sure how to go about this though. What I'm envisioning is this...
So each Remarks string would get compared against each criteria. If the Remarks string contains the words "SCADA" and "Project Delta", then the condensed version would be "Project Delta for IBS/SCADA and CIKR". If the Remarks string contains "OPSEC Assessment" then the condensed version would be "OPSEC Assessment". In the pic above, the form is based of another table that contains those fields in the form. I'm not sure if this is the most efficient method for my goal or not. Either way, I don't know how I would compare the Remarks string to records in this new table in order to create a condensed version.
I have two fields in a table that have multiple values. Example:
Field A:
CT, CA, PA
Field B:
CT, CA
I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.
i have 2 recordset and i need to compare the two. If a record doesnt existing i need it to be added I have VBA that works but it seems very slow. Is there a better way of doing it
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExorData")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM MainForm")
Dim TJb_Main, TJb_new
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
[Code]..
I have 2 tables and 1 query. Table dbo_RepOrderItem includes columns:
RepId
OrderNumber
Item
ShipDate
SerialCardID
Table tbl_LBP Sales Location Num includes columns:
Location ID
Rep Region Code
What I've been doing so far through my code is working through a query 'CalculateTotal' which looks up a structure number (Which I enter through an InputBox) from table dbo_RoicStructure, gets the SerialCardID from the same table and collects the associated RepId,OrderNumber,ShipDate from table dbo_RepOrderItem and checks that the OrderNumber(s) returned exist in column Location ID in table tbl_LBP Sales Location Num. It also checks that Rep Region Code is not equal to 'INT' nor 'inte'.
My code for all of this works just fine. What I am trying to do now is simplify my results. I want to only return rows that do not have the same Item, OrderNumber, and RepID.
Fore example:
OrderNumber: Item: RepID:
11 3 1
12 3 1
11 4 1
11 3 1
14 7 4
16 8 8
It would now count 5 existing RepID(s) rather than 6 because it would have deleted the duplicated row. This should only be deleted in the user's table not the actual table.
And speaking of the user's table. What I have been doing so far is only returning the results (right now it returns 6, which as I explained above is incorrect) but I also want to return a table or query that would should the user the work behind the returned number.
This is my code:
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
[Code] ....
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI am finishing up a database that logs basic activities/tasks for my coworkers. As they go in to the database and log an activity in it stays open until they end their day or they start a new task. All this works fine, but the problem comes when I try to determine the total time spent (in hours) on each activity/task. I have written similar queries before and they worked just fine, but for some reason I am having trouble getting this one to work. Here is the expression I'm using...
Time Diff (in hrs): DateDiff("h",[Sample_TM_Table_1]![Time_In],[Sample_TM_Table_1]![Time_Out])
Unfortunately, say Time_In = 8:32:38 am and Time_Out = 8:33:03 am
The expression says that is equal to 946632.
For another Time_In = 8:33:00 am and Time_Out = 3:18:19 PM.
The expression says this equals 946639.
I have tried changing the format of the date/time in both Time_In and Time_Out fields to ensure they were the same and cross checked the expression but still get the same answers each time. Even when I try to use "n" for the interval or "s" it is still highly incorrect. The only thing that makes me think I still have a formatting issue is that the Time_In shows a long date on the results table while Time_Out shows it in a general date format. However, when I go into both the table and the queries to check the formatting they both show general date. Any ideas? I'm all out! Thanks in advance.
I was given this piece of code the other day by somebody on the forum and it works really well. My question is:
Is there a way, using a similar function that I can retrive the IP address?
Environ("ComputerName")
Hello,
I use Environ("Username") all the time, most frequently for logging who has done something to a record, and have once used it to log people into the database (very basic, much the same as logging who did what to records).
But I was wondering, how feasible is it as a security measure?
I often use it on the main menu of my DBs to give me a different menu (with more advanced options on) than other users.
Is it really that safe to use it as a means to allow people in, or stopping them? I know that obviously you have to stop them getting in other ways (holding shift for example), but assuming all of that is done, in theory, they should only be able to get in if they can log in as an authorised user?
Don't know why it is just struck me - just curious I suppose!
Hi everyone
I have searched for help on this forum and all over for a while but cannot find the help i need.
I have a database stored in my user area on a schools network. Other users around the school access the database by clicking on a shortcut to it found on their start menu. The database is not split with a fe/be and usernames and passwords are not required to log on, they simply click the link.
I would like to know how I can find out which users have the database open at any given time? I have a piece of software that tells me the computer name but that is no use, I need the users name. Is there a way of pulling this from the network because each user must first log on to their computer using their name and password before they can access the database?
Any suggestions appreciated
James