Passing a variable from a function

  • Thread starter Thread starter Ronny
  • Start date Start date
R

Ronny

Hi.

I am trying to insert a variable (apGetUserName)from a
function into the database; the WordDoc and Now variable
goes well. I am able to put the variable on a MsgBox and
forms, but not in the table. I have also tried to put
apGetUserName in the SQL and several "" '' && types.

Does anybody know why or has anybody a solution?


---------------------------------
Option Compare Database
Option Explicit


'-- API Calls for getting the current user and computer
names
Declare Function wu_GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As
Long) _
As Long

------------------------------------------

Function ap_GetUserName() As Variant

Dim strUserName As String
Dim lngLength As Long
Dim lngResult As Long

'-- Set up the buffer
strUserName = String$(255, 0)
lngLength = 255

'-- Make the call
lngResult = wu_GetUserName(strUserName, lngLength)

'-- Assign the value
ap_GetUserName = strUserName

End Function

-------------------------------------------
Dim sqlPers As String
Dim dbs As ADODB.Connection

Dim user As String
user = ap_GetUserName

Set dbs = Application.CurrentProject.Connection

sqlPers = "INSERT INTO Historikk VALUES ('" & WordDoc
& "', Now(), '" & user & "')"

dbs.Execute sqlPers
dbs.Close
 
Hi,

I think you missed a paren in the SQL. Try this:

(' " & WordDoc & " '," & Now() & ",' " & user & " ')"
 
Your column size in your destination table is probably not large enough to
hold the data that you're passing considering you're not trimming your
strUserName results. I do this slightly differently which can be seen on Tip
#34 of my site, if interested.
 
sqlPers = "INSERT INTO Historikk VALUES ('" & WordDoc
& "', Now(), '" & user & "')"

dbs.Execute sqlPers
Click to expand...

? sqlPers
INSERT INTO Historikk VALUES ('Eric The WordDoc', Now(), 'Samantha')



Does ADO know about the Now() function -- should it not be something
generic like @@SYSTEMTIME or something? I would normally pass the actual
value, not the function just to be on the safe side.

Incidentally, it's good form always to include the field list in case
someone changes the order or number of fields in the table:

? sqlPers
INSERT INTO Historikk (SomeWordDocument, TimeOfUpdate, UserName)
VALUES ('Eric The WordDoc', #2004-02-18 20:56:01#, 'Samantha')


Hope that helps


Tim F
 
it is only the last one that causes problem, worddoc and
now() works well....
Click to expand...

I should have noticed. User is a reserved word in VBA (it's part of the DAO
library). Changing the name of the variable to something like
strTemporaryUserName might help.

Tim F
 
Back
Top