Append Querry

[sbaud2003]

I have a Global Variable StrUser (Name of the User Login)
Cam I use this Global Variable to append a Table though Append querry

 

[jdraw]

What have you tried? What result?

 

[sbaud2003]

I have not done the coding... but thinking of using the variable to make a Login Tracking table through append querry,.. I am not sure Please suggest

 

[cheekybuddha]

>> Cam I use this Global Variable to append a Table though Append querry <<

Yes, but not directly.

You must create a function to return the value.

Eg

Public g_strUser As String

Public Function GetUser() As String

  GetUser = g_strUser

End Function

Then, in your query:

INSERT INTO YourTable (
  Fld1,
  Fld2,
  User
) VALUES (
  12345,
  'SomeValue',
  GetUser()
);

hth,

d

 

[sbaud2003]

Its working... But every time i runs the query it doubling the records. That means running first time id append one record, on second time it append same record twice and on third time it append four times and so on.. I am confused.

 

[The_Doc_Man]

I think we would need to see the actual SQL of the query you are using to understand that doubling effect. It would also help if you could describe the tables that are involved here.

 

[sbaud2003]

My Append Query SQL is
INSERT INTO tblLoginTime ( UserName, LoginTime, LogOutTime )
SELECT GetLoginUser() AS Expr1, Now() AS LogIn, Null AS LogOut
FROM tblLoginTime;
and I am calling a Temp variable from login Form, syntex is
TempVars!TempUserLogin = Me.Txtuser.value

 

[The_Doc_Man]

You are inserting into a table from itself.

INSERT INTO tblLoginTime ( UserName, LoginTime, LogOutTime )
SELECT GetLoginUser() AS Expr1, Now() AS LogIn, Null AS LogOut
FROM tblLoginTime;

This means that every time you run this, you double the size of tblLoginTime. I think you should look up the VALUES keyword because none of the things you are inserting have anything to do with previous entries. Also, you don't need the AS clauses because the names are supplied from the INSERT INTO sub-clause. Finally, use a date of zero rather than a null because you cannot compare to a null. As it happens, even if you had a null field, you cannot write this: ... WHERE Null = Null ... because Null is NEVER equal to anything - including itself. You can EASILY test for a date field of 0 because that is a valid but unlikely date.

What you want MIGHT look like this.

INSERT INTO tblLoginTime ( UserName, LoginTime, LogOutTime )
VALUES (GetLoginUser(), Now(), 0 ) ;

 

[sbaud2003]

You are inserting into a table from itself.

INSERT INTO tblLoginTime ( UserName, LoginTime, LogOutTime )
SELECT GetLoginUser() AS Expr1, Now() AS LogIn, Null AS LogOut
FROM tblLoginTime;

This means that every time you run this, you double the size of tblLoginTime. I think you should look up the VALUES keyword because none of the things you are inserting have anything to do with previous entries. Also, you don't need the AS clauses because the names are supplied from the INSERT INTO sub-clause. Finally, use a date of zero rather than a null because you cannot compare to a null. As it happens, even if you had a null field, you cannot write this: ... WHERE Null = Null ... because Null is NEVER equal to anything - including itself. You can EASILY test for a date field of 0 because that is a valid but unlikely date.

What you want MIGHT look like this.

INSERT INTO tblLoginTime ( UserName, LoginTime, LogOutTime )
VALUES (GetLoginUser(), Now(), 0 ) ;

 

[sbaud2003]

Thank you sir.... Its working fine....
Have a safe stay...