Recordset.addnew

[NigelShaw]

Hi,

i seem to be having trouble with adding a record to a recordset. i dont want to open a form so thought it would be just as good to opn a recordset and the info direct. here are my details-
FrmSpGood holds the following from currentloggedOnQry
Username
FirstName
lastName
UserID

and also unbound

DateLog
timeLog

i have placed this code in the onClose-

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = db.OpenRecordset("UserLogTbl", dbOpenTable)

rst.AddNew
rst!LoggedInAs = Me.UserName.Value
rst!DateLogged = Me.LoginDate.Value
rst!TimeLogged = Me.TimeStamp
rst!UserID = Me.UserID


rst.Update

Set rst = Nothing
Set db = Nothing

though i do not get any errors, i do not get any data entered into the table.

i tried putting this in OnLoad but i have a progress timer on the form which is started in the OnLoad. it seems that if i place anything else in the OnLoad, the progress doesnt work.

do i have my syntax and placement correct?



regards,

Nigel

 

[boblarson]

Try changing this:
dbOpenTable

to

dbOpenDynaset

 

[NigelShaw]

Hi,

i tried your change but still nothing. to make it clear, i have currently set up.

i have a log table UserLogTbl
LoggedInAs
DateLogged
TimeLogged
UserID

i have a query CurrentLoggedInQry
UserName
Password
firstName
LastName

i have set my code to run on the OnClose of my progressFrm which starts immediately after my loginFrm closes. ( when the loginFrm closes, the CurrentLoggedInQry is updated and requeried as the ProgressFrm get info from it so i do know that the data is available).

my aim is get the following info ready for the table entry-

LoggedInAs = [UserName] ( from query )
DateLogged = Date()
TimeLogged = Format(Now(), "hh:mm:ss")
UserID = [UserID] ( from query )

my code-

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset


'Set Variables to store data
Dim GetLoginName As String
Dim GetLoginDate As Date
Dim GetLoginTime As String
Dim GetUserID As String

'Open Query to get data
Set rst = db.OpenRecordset ("CurrentLoggedOnQry", dbOpenDynaSet)
Set db = CurrentDb

'Fill Variables with Data from Query
GetLoginName = rst!UserName
GetLoginDate = Date
GetLoginTime = Format(Now(), "hh:mm:ss")
GetUserID = rst!UserID

'Clear the Variables
Set rst = Nothing
Set db = Nothing

'Close current recordset
rst.Close
db.Close

'Open New Recordset
Set rs2 = db.OpenRecordset("UserLogTbl", dbOpenTable)
Set db2 = Currentdb

'Set Recordset for new record
rs2.AddNew

'Fill the fields with the stored data
rs2!LoggedInAs = GetLoginName
rs2!DateLogged = GetLoginDate
rs2!TimeLogged = GetLoginTime
rs2!UserID = GetUserID

'Save the record
rs2.Update

'clear the variables
Set rs2 = Nothing
Set db2 = Nothing

'close the Recordset
rs2.Close
db2.Close


is my code correct? i think it is correct working on the basis of other code i have read in my VB book but nothing happens. i dont get any errors when i compile nor any debug message when it runs but i dont get any data entered into the table.

could someone be so kind to advise please?


many thanks,


Nigel

 

[boblarson]

One thing jumps our immediately.

Set rst = db.OpenRecordset ("CurrentLoggedOnQry", dbOpenDynaSet)
Set db = CurrentDb


Needs to be

Set db = CurrentDb
Set rst = db.OpenRecordset ("CurrentLoggedOnQry", dbOpenDynaSet)

 

[NigelShaw]

Hi Bob,

i will try that now. i didnt realise that it was so picky like that. of course, i would understand if i had the 2 lines at the bottom of the code...

i will let you know in a minute or 2

regs,

Nigel

 

[NigelShaw]

Hi Bob,

still didnt work. i also changedit a little moving the second recordset to a new procedure and calling it from the end of the first. i made the variables public so to retain the data but absolutely nothing......

 

[NigelShaw]

Hi,

this seems to be a little peculiar!

so i set my Recordset as

Set rst = db.OpenRecordset("UserLogTbl", dbOpenTable)

UserLogTbl holds the fields
LoggedInAs
UserID
DateLogged
TimeLogged

for curiositys sake, i purposely didnt type the fields in the recordset as LoggedInAs but typed in loggedinas. this should update to the correct caps as usual but it doesnt. except UserID. if i type this as userid, it updates to UserID but the others dont!!!

does this mean that somewhere, there is a recordset still open?

the other recordset i created to get the data all updates correctly and holds the field UserID

im a little puzzled. is there any other way of doing this?


regs,

Nigel

 

[NigelShaw]

to update,

i have now tried the following-

Public Sub FillUserLog()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rst = db.OpenRecordset("currentLoggedOnQry")
GetValue1 = rst!UserName
GetValue2 = rst!UserID
getvalue3 = Date
GetValue4 = Format(Now(), "hh:mm:ss")
strSQL = "INSERT INTO UserLogTbl ([loggedinas], [UserID], [DateLogged], [TimeLogged]) "
strSQL = strSQL & "Values ('" & rst!UserName & rst!UserID & Date & Format(Now(), "hh:mm:ss") & "');"
CurrentDb.Execute strSQL

End Sub

i think the syntax is correct.

all i want to do is get infor from a query, and place it in a table. soemthing that looks so simple yet seems impossible.

again,

when i set the DAO. recordset to the query, it is making the link because the text updates but i just cannot seem to link to the table. i have tried creating a query of the table but still nothing.

according to my books, it should work.

the table is not linked to anything.

does anyone have any suggestions?


thanks,



Nigel

 

[NigelShaw]

i seem to be the only one posting here.......

still not working.

i tested an existing table in the current recordset and that works. i copied the table ( structure ) only, re-named it and it still worked. i changed the names of the fields so not to creat confusion and now it doesnt work. i haveritted the code a few times now but it is not working.

has anyone else even experienced this? get data via DAO.Recordset works on other procedures. i have DAO.Recordset to get the info to be passed over, i have set public variables to hold the data but no-matter what i do, i cannot get the table required to get any data in. its as if the table doesnt exist in my database.

surely someone knows what is going on. i dont want anyone to do it, just advise where the problem is so i can fix it..


many thanks,


Nigel

 

[boblarson]

Sorry, I was very busy yesterday and couldn't get to this question. Is there any way you can post the db? It might get us there faster.

 

[WayneRyan]

Nigel,

strSQL = "INSERT INTO UserLogTbl ([loggedinas], [UserID], [DateLogged], [TimeLogged]) " & _
         "Values ('" & rst!UserName & "', " & _
                       rst!UserID & ", #" & _
                       Date & "#, #" & _
                       Format(Now(), "hh:mm:ss") & "#);"

Assumptions:

UserName is a string     <-- single-quotes
UserID is a number       <-- no "punctuation"
DateLogged is a DateTime <-- pound sign
TimeLogged is a DateTime <-- not sure of its punctuation (I don't use time fields)
                             but it's probably the #

hth,
Wayne

 

[dallr]

So much things to say here.....

1. You do not need to use DAO here to open a recordset and pass the value from your query (currentLoggedOnQry). I can fix your initial code but, as I said doing this is unnecessary.
2. You can simply use.

Dim strSQL As String

strSQL = "INSERT INTO UserLogTbl ([loggedinas], [UserID], [DateLogged], [TimeLogged]) " & _
         "SELECT UserName, " & _
                "UserID, " & _
                "Date() AS [DateLogged], " & _
                "Format(Now(), 'hh:mm:ss') AS TimeLogged " & _
         "FROM currentLoggedOnQry"
Debug.Print strSQL
CurrentDb.Execute strSQL

3. Why are you also logging the date and time when you can pass the date and time in one go?. Format(Now(), 'dd/mm/yyyy hh:mm:ss')

4. Why are your getting the username and ID which is already stored then passing the date and time to it. It seems like you are checking the time a user logs in. But somehow your logical flow (steps) seems off to me. Maybe a little clarification holistically what you are trying to achieve will help.


Dallr

 

[NigelShaw]

Sorry, I was very busy yesterday and couldn't get to this question. Is there any way you can post the db? It might get us there faster.

Hi Bob,

dont worry, i never expect anyone to respond as it is a forum afterall and everyone has a right if desired, not to reply if they dont want to. i was merely saying that i was the only one posting. i try and reply ( even if only to myself ) if i have figured out a solution to my problem for the benefit of other people with the same problem.

i will try and post a db, i will have to export the tables and form out because my db is quite large.


regs,

nigel

 

[NigelShaw]

Hi Dallr,

i will try your line of code. im not that familiar with SQL statements. i have various books that show you examples but not really explain the construction. i ve been using more box standard vb ( if there is a box standard ) to collect and distribute my data.

in regard to my data required,

i need to filter the log information by date to provide a list of times logged in / out. there are other fields in the table now that will be updated on different events like payments made, payments deleted etc to filter out problem users. the fact that i have added the userID and the UserName is for visual only. i was going to use the UserID as the unique ref for the login person and only wanted to show the username for reference. so in essence, i should be ably to pull a report for example-

User - Nigelshaw
Login Date - 03-07-08

Activities

12:53:41 - Logged On
12:54:25 - Added Payment to - Person 1
12:56:09 - Confirmed & Processed Payment
12:58:37 - Payment Printed
12:59:05 - Logged Off

or to this effect anyway.

every activity carried out and applied will write to this table.

regards,

Nigel