Adding Windows username in a new record?

[contractor]

Cronk, The only code that i have provided was to get network username, the other part belongs to himself...

 

[pr2-eugin]

funwithaccess, it would be so much easier if you could just upload the stripped DB.. This is one long thread..

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[funwithaccess]

Gee, this has been a labored thread.

Contractor, be aware that the code you supplied last neither declared tmpDB, nor set it as a database object.

Rookie, dates are stored as double precision numbers but displayed in date format with the integer part representing the number of days since 30/12/1899 and the fraction being the part of 24 hours eg 0.25 is 6:00:00[FONT=&quot][/FONT]

Cronk, Contractor is right, the code that he had posted is the code that I provided, he was just modifying it.

I want to thank you all for your interest in helping me learn and succeed. This certainly has been a labor-intensive, long thread.

Paul,

I will attempt to get a stripped version posted on here asap.

Nate

 

[funwithaccess]

Unfortunately, it would not translate to .mdb so it was saved at .accdb. I cut all of the fat out and left only the necessary code. It is pretty self explanatory. The form is "Start Here" and the necessary tables are there. I also took out all of the butchered code that I had and provided you with the working version.

Correction: The working version prior to attempting to pull the username, date, and time into the Usage Table.

Thanks again,
Nate

 

[RainLover]

Did someone say this is going on too long?

What an understatement.

If you want an answer, create a new Database is Access 2003 and let someone from the old school show you what to do.

All this requires only a few lines of code plus a Function to get the user name.
It goes into a separate Module.

Option Compare Database
Option Explicit


Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function GetUserName() As String
' Returns the network login name
    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
        GetUserName = Left$(strUserName, lngLen - 1)
    Else
        GetUserName = ""
    End If
End Function

Use GetUserName as the Default Value in the Control on the Form.

Code Tested

 

[pr2-eugin]

funwithaccess.. Here you go.. I am still using the old code I posted in Post#7

You can simply use Environ("username") to get the windows username.. List of Environ variables available @UtterAccessWiki.. Although I have seen various posts describing that usage of an API is better.. So I have used Rain's code to get the Username..

I was not sure why you had defined the loginDate and loginTime as String in the table..

I quote this again..

A very important advice, do not use On Error Resume Next until you exactly know why you would avoid the error.. With that statement you will fail to understand the actual error.. In this case the Execute statement is never run..

Use a proper Error handler..

Here is the updated version..

 

[RainLover]

This is required at the top of every Module.

Option Compare Database
Option Explicit

 

[funwithaccess]

funwithaccess.. Here you go.. I am still using the old code I posted in Post#7

You can simply use Environ("username") to get the windows username.. List of Environ variables available @UtterAccessWiki.. Although I have seen various posts describing that usage of an API is better.. So I have used Rain's code to get the Username..

I was not sure why you had defined the loginDate and loginTime as String in the table..

I quote this again..


Here is the updated version..

This worked!! There was a reason for the date and time listed as a string but now I can't remember. It had something to do with the function. Regardless, this is much better! Thank you for all of your help!

I'm attempting to do something similar in another form. I'll probably have questions.

Btw, could you explain to me what exactly this does?

    tmpDB.Execute "INSERT INTO [Usage Log](NTID, loginDate, loginTime) VALUES(" & Chr(34) & GetUserName() & Chr(34) & ", " & CDbl(Date) & ", " & CDbl(TimeValue(Now)) & ")"

 

[RainLover]

tmpDB.Execute "INSERT INTO [Usage Log](NTID, loginDate, loginTime) VALUES(" & Chr(34) & GetUserName() & Chr(34) & ", " & CDbl(Date) & ", " & CDbl(TimeValue(Now)) & ")"

This code executes an SQL Statement. It is not written very well but it should work.

If you open a query in design view you can change the view to SQL View. This can be executed in a Module.

A Insert Into is an Append Query which adds more records. An Update Query changes the existing data.

 

[pr2-eugin]

The line will Execute the CurrentDB (declared as tmpDB) to INSERT INTO the table (Usage Log), the VALUES GetUserName(), Date and Time into the fields NTID, loginDate, loginTime.

That is all that one line does..

This code executes an SQL Statement. It is not written very well but it should work.

@Rain : I am intrigued to know a better way of writing the INSERT statement..

 

[funwithaccess]

The line will Execute the CurrentDB (declared as tmpDB) to INSERT INTO the table (Usage Log), the VALUES GetUserName(), Date and Time into the fields NTID, loginDate, loginTime.

That is all that one line does..

@Rain : I am intrigued to know a better way of writing the INSERT statement..

That was the explanation that I was looking for, thank you! It helped me to get another form & table working.

For some reason, now that the Usage Log is being updated, the intcount is causing one of the queries to always pop up regardless of whether or not the text in the search field is in the table or not. Any ideas?

 

[RainLover]

Paul

Change tmpDB.Execute to CurrentDB.Execute

I can't see the need for this declaration. "declared as tmpDB" It is just unwanted surplus code.

Let me know if you disagree.

 

[pr2-eugin]

Let me know if you disagree.

It's not about disagreeing Rain. The comment, "not written well" regarding the INSERT query made me think twice..

Well I understand what you mean by surplus usage of declaration of the tmpDB. I consider me a miser when it comes to declaring objects, in general terms, declaring an object would create a local object, so in case you need to perform multiple executions the same one object referring to the DB can be used efficiently, instead of creating and emptying everytime CurrentDB is used.. I am not sure if I am explaining it properly here..

However for this particular operation, CurrentDB would suffice..

 

[funwithaccess]

Semantics aside Why doesn't the intCount function the same way as it used to? It no longer provides a message box if the queries return 0. Instead one query, the same query every time, opens completely empty. This same query opens empty after every query even if another query returns 1. Not sure how the tmpDB could have changed how that works. I apologize if I am beating a dead horse over and over here.

Private Sub cmdFind_Click()

    Dim tmpDB As DAO.Database
    Dim qry As QueryDef
    Dim intCount As Integer
    
    Set tmpDB = CurrentDB
    
    For Each qry In tmpDB.QueryDefs
        DoCmd.Close acQuery, qry.Name, acSaveYes
    Next
    
    Combo99.Value = ""
    Combo89.Value = ""
    Combo71.Value = ""
    Combo55.Value = ""
    Combo85.Value = ""
    Combo212.Value = ""

    intCount = 0
    
    If DCount("Alias", "SD Documentation Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If
    
    If DCount("SearchableAlias", "Info Gathering Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Info Gathering Query", acViewNormal, acReadOnly
    End If

    If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
    End If
    
    If DCount("AssetNumber", "Xerox Assets Query - CHP") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
    End If

    If DCount("[IP Address]", "Xerox IP Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
    End If
    
    If DCount("[IP Address]", "Xerox IP Query - CHP") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
    End If

    If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
    End If
    
    If DCount("SerialNumber", "Xerox Serial Query - CHP") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
    End If

    If intCount = 0 Then MsgBox "No results found in ServiceBase." & vbCrLf & "Please provide a specific word, phrase, or alias.", vbExclamation + vbOKOnly, "ServiceBase Search Results"
        
    tmpDB.Execute "INSERT INTO [Usage Log](NTID, loginDate, loginTime) VALUES(" & Chr(34) & GetUserName() & Chr(34) & ", " & CDbl(Date) & ", " & CDbl(TimeValue(Now)) & ")"
                          
cmdFind_Click_Exit:
    Exit Sub

End Sub

Thanks again!!!
Nate

 

[pr2-eugin]

Now don't you start blaming the tmpDB.. Step through the code.. See which Query is being opened.. Set the Breakpoint at intCount = 0, and execute the code one line at a time using F8.. You will find the culprit..

 

[RainLover]

It's not about disagreeing Rain. The comment, "not written well" regarding the INSERT query made me think twice..

Well I understand what you mean by surplus usage of declaration of the tmpDB. I consider me a miser when it comes to declaring objects, in general terms, declaring an object would create a local object, so in case you need to perform multiple executions the same one object referring to the DB can be used efficiently, instead of creating and emptying everytime CurrentDB is used.. I am not sure if I am explaining it properly here..

However for this particular operation, CurrentDB would suffice..

However for this particular operation, CurrentDB would suffice..

If you apply this attitude all the time you will be surprised how much cleaner your code will be. Easier to follow and understand. Far less likelihood of the code failing.

The expert in this as far as I am concerned is ChrisO. He has a link with his signature. Download something simple of his and see how tidy the code is.

BTW A lot of this setting things to nothing, closing Databases etc is simply not necessary. So that is another way to reduce code.

 

[RainLover]

funwithaccess

I would think NOW is as good a Time as any to learn some naming conventions.

See attached.

 

[funwithaccess]

funwithaccess

I would think NOW is as good a Time as any to learn some naming conventions.

See attached.

Rainlover, awesome! Thank you for the document!

I wanted to mention that one of the reasons for the unconventional names is that non-Access/SQL users will be using this as simply a knowledgebase. At the request of the management I had to keep the names on a familiar basis. However, you are correct, traditionally, the proper naming convention is a much better way to do it.

Btw, instead of Chr(34) what could I use for a longer string?

 

[pr2-eugin]

Chr(34) is Double Quotes mark.. What Longer Strings are you dealing with?

 

[funwithaccess]

Chr(34) is Double Quotes mark.. What Longer Strings are you dealing with?

I need to have a text box on another form that will accept more characters, at least a large paragraph size.