Variable to table 1

[UnsolvedCoding]

For some reason I am totally drawing a blank on something very basic.

I found code in Access Help to pull the users naame but am having a huge issue trying to get the value of the users name into the table in Access.

Can anyone post a code sample on how to do that?

 

[Andrzejek]

I am sure there are other ways, but one of them would be:

strUser = Environ("USERNAME")

strSQL = "INSERT into tblTable (User_Name) VALUES ('" & strUser & "')"

DoCmd.runsql strSQL

Have fun.

---- Andy

 

[UnsolvedCoding]

That worked great.

I have one more question about comparing dates. The main table is based on finding the current date and the employee id - if there is not id then the last name and first name have to match.

Getting the info to append isn't a problem, but getting it to append to the correct row is proving difficult.

Any ideas?

 

[Andrzejek]

You cannot 'append' info to any record, you can either INSERT a new record to your table, or UPDATE a field in your existing record.

You would have to provide some sample of your data, which record(s) you want to UPDATE (I guess) and why this record and not the other (criteria)


Have fun.

---- Andy

 

[UnsolvedCoding]

I apologize, I haven't used the Access in awhile and it shows...

One more question -

sSQL = "update Time_Table "
sSQL = sSQL & "Set Day_Start_Entered_By = '" & Logon_UserName & "' "
sSQL = sSQL & "Where date_stamp = " & Date & " and " & [Name_List_Table]![Last_Name] & "", "" & [Name_List_Table]![First_Name] ; "
DoCmd.RunSQL sSQL

If I run just the first two lines it updates but if I run it with the third line, regardless of whats included - it doesnt' update anything or it won't run.

What am I doing wrong?

 

[PHV]

Your WHERE clause is meaningless.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

sSQL = "update Time_Table "
sSQL = sSQL & "Set Day_Start_Entered_By = '" & Logon_UserName & "' "
sSQL = sSQL & "Where date_stamp = " & Date[red] & " and " & [Name_List_Table]![Last_Name] & "", "" & [Name_List_Table]![First_Name] ; "[/red]

Your Update statement is almost there, but - how is your date_stamp field declared in your Time_Table table? If it is a Date, you need # around the date value:
[tt]
... Where date_stamp = #" & Date & "# ...
[/tt]
and in the RED portion of your statement you use Name_List_Table, but it is not connected to your original Time_Table.

Also, do yourself a favor and do:
[tt][blue]
MsgBox sSQL[/blue]
DoCmd.RunSQL sSQL
[/tt]
to see your SQL, or in Immediate Window do:
[tt]
? sSQL
[/tt]

Have fun.

---- Andy

 

[UnsolvedCoding]

Let me explain a bit more.

I was given the project of creating a time sheet that includes reporting and tracking for holidays, Days off, Travelers etc, and allows for additon of new people while being protected so that only managment can change and see all the Access objects.

The main form has several options for the user and the command buttons on the form do things for the user and managment. Most of this has already been completed but some of the coding is figting back.

There are a few pieces of code that are messing with me - in part because I don't use Access 2007 often and in part because I haven't coded in Access or SQL for the better part of 4 years.

The three pieces that are giving me trouble are the combo box that has the last name and first name from the Name_List_Table, hiding and unhiding things for managment (with password protection) and updating specific fields as the command buttons on the main form are clicked.

The main form shows "last name, first name" for a combo box so the user can select their name. The names are from the Name_List_Table which holds things like first and last name, employee ID and so on. The Name_List_Table is created as employee information is entered by managment.

So here is an example -

A user opens the main form and the list of names for the combobox is populated from the Name_List_Table. Then they click on the start of day button on the main form which will put the name selected in the combo box into the Time Table, along with a date stamp for today, a time stamp showing when the button was clicked and the users system name.

So using our example of the start of day button, I am trying to get the computer users logon into the Time_Table where the first and last name, employee ID and the date stamp all match.

This is what I have so far however for each change there are a crapload of different probelms that come up.

For this code I either get a message that says Microsoft cant find the field "|" or if I take away [ ] it says the variable isn't defined.

sSQL = "update Time_Table "
sSQL = sSQL & "Set Day_Start_Entered_By = '" & Logon_UserName & "' "
sSQL = sSQL & "Where date_stamp = #" & Date & "# and " & [Name_List_Table]![Last_Name] & ", " & [Name_List_Table]![First_Name] & "= "
sSQL = sSQL & "[Time_Table]![Last_Name] + "", "" + [Time_Table]![First_Name] and [Name_List_Table]![Employee_ID] "
sSQL = sSQL & " = [Time_Table]![Employee_ID] ; "

 

[Andrzejek]

So you have Time_Table and Name_List_Table.
How are the 2 tables related?
What fields do you have in them and how are they declared?
Could you show a sample of your data from both tables?

Have fun.

---- Andy

 

[Andrzejek]

BTW, When you refer a field from the table in the data base in your SQL, you do not use !, use period .

Time_Table.First_Name and Name_List_Table.Employee_ID

If you refer to a field from the recordset, you may use !:

rstEmployee!LastName.Value
which is the same as saying:
rstEmployee.Fields("LastName").Value


Have fun.

---- Andy

 

[UnsolvedCoding]

Got it -

sSQL = "UPDATE Name_List_Table, Time_Table SET Time_Table.Day_Start_Entered_By = '" & Logon_UserName & "', "
sSQL = sSQL & "Time_Table.Logon_Name = '" & Logon_UserName & "', "
sSQL = sSQL & "Time_Table.Environ_User_Name = '" & Environ_Username & "' "
sSQL = sSQL & "WHERE ((([Name_List_Table]![Last_Name])=[Time_Table]![Last_Name]) AND "
sSQL = sSQL & "(([Name_List_Table]![First_Name])=[Time_Table]![First_Name]) AND "
sSQL = sSQL & "(([Time_Table]![Employee_ID])=[Name_List_Table]![Employee_ID]) AND "
sSQL = sSQL & "(([Time_Table]![Date_Stamp])=Date() ));

 

[Andrzejek]

Good for you.
Do you have 2 different values for Logon_UserName and Environ_Username? Or is that the same User?

Have fun.

---- Andy

 

[UnsolvedCoding]

They are pulled from different code so that if someone tries to screw with the system will will have better odds of knowing.

 

[Andrzejek]

This is just my $0.02 worth, but since you do know who is logged in, you can allow only this user's information to be changed. No chance of "someone tries to screw with the system". If Susie logs in, she can have access only to Susie's info, if Steve logs in, he can mess with Steve's info.

Have fun.

---- Andy

 

[PHV]

Another way:

sSQL = "UPDATE Name_List_Table N INNER JOIN Time_Table T "
sSQL = sSQL & "ON N.Last_Name=T.Last_Name AND N.First_Name=T.First_Name AND N.Employee_ID=T.Employee_ID "
sSQL = sSQL & "SET T.Day_Start_Entered_By = '" & Logon_UserName & "', "
sSQL = sSQL & "T.Logon_Name = '" & Logon_UserName & "', "
sSQL = sSQL & "T.Environ_User_Name = '" & Environ_Username & "' "
sSQL = sSQL & "WHERE T.Date_Stamp=Date()"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886