Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unanswered: Add new record to a table in VBA

    I have a login system, and i want to show who is logged in when the user is logged on successfully.

    The way i have thought on doing this is to copy the users recordset to a table called "logged_in", and from there use a DMax statement to show the users name, as this'll double up as a log of users who have used the database from that specific client. when the recordset is copied over, i also need a date and time stamp added in to another column on the "logged_in" table, and another time stamp for when the user exits.

    What i need help with, is establishing the VB code to copy the recordset of the persons login details from the table "employees" to "logged_in". I know where in the login code i'll be putting this statement, but i just don't know how to do the above.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You can use a DoCmd.RunSQL statement and craft an SQL string that accomplishes your task. Access will write the SQL string for you - create an "Append Query" that does what you want and then preview the SQL. You simply replace the "filter" with the key value matching your desired record.

    Or, you can use recordsets to accomplish the same thing. Also, with recordsets, you can do it manually - finding fields and transfering data.

    Here's the basics of recordsets:
    Code:
    Dim rsDataSource As DAO.Recordset
    Dim rsDataDestination as DAO.Recordset
    Dim intKeyValue As Integer
    
    intKey = <insert your method of getting the key here>
    
    Set rsDataSource = CurrentDB.OpenRecordset("Select * From FirstTable Where KeyField = " & intKeyValue)
    Set rsDataDestination = CurrentDB.OpenRecordset("Select * From SecondTable")
    From here, use rsDataDestination.Edit and then rsDataDestination("FieldName") = rsDataSource("FieldName") for each record.

    End it with rsDataDestination.Update then set both = Nothing

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Ok, I have most of it done now, but im getting a syntax error on the following code:

    Code:
    Set rsDataSource = CurrentDb.OpenRecordset("Select * From Employees = " & intKeyValue)
    Can anyone help and tell me what i've done wrong here?

    Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You forgot the WHERE condition:

    Set rsDataSource = CurrentDb.OpenRecordset("Select * From Employees WHERE yourField =" & intKeyValue

  5. #5
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks for that Hammabakka, it worked. Now i have a small problem defining the inKeyValue.

    I want it to look up the value of the combo box, but i need it to look at the username column (column 2) and not the id column (column 1).

    I am using the following code to find the value, i think it's just the ID though:

    Code:
    intKeyValue = DLookup("Username", "Employees" & [Forms]![test_login]![login_select].Value)
    This doesn't get flagged up, but i think it's the cause of an error on the following line (i get a data mismatch error):

    Code:
    Set rsDataSource = CurrentDb.OpenRecordset("Select * From Employees WHERE Username = " & intKeyValue)
    Any ideas on how to sort this???

    Thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    username is a string, right?

    ("Select * From Employees WHERE Username = '" & comboName.column(n) & "');"

    replace comboname with whatever your combo is called

    replace n with the number of the column (counting 0, 1, 2, 3 - the column index is zero-based)

    you need the two ' if username is a string

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks izyrider, but i found out what the problem was, I used the line Dim intKeyValue As Integer, when it should have been string.

    New problem now. the following line is supposed to copy the relevant recordset into the array, using the intKeyValue variable as a filter. The problem here is that it isn't returning any values whatsoever.

    Code:
    Set rsDataSource = CurrentDb.OpenRecordset("Select * From Employees WHERE Username = " & intKeyValue)
    I can see a problem im going to run into when this works, but i'll tackle that one when i get this one sorted.

    Thanks!

  8. #8
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Here's a bit of an update:

    According to the error message i get that relates to the line of code above, it says that there are too few parameters, expected at least 1 parameter. As far as i can see, the "Select * From Employees WHERE Username = " & intKeyValue was the parameter.

    any ideas on this?

    ta.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my dear friend, the rules are very simple!

    if you want to feed a...

    ...date into an SQL string you use sqlsqlsql #" & theDate & "# sqlsqlsql
    ...string into an SQL string you use sqlsqlsql '" & theDate & "' sqlsqlsql
    ...number into an SQL string you use sqlsqlsql " & theDate & " sqlsqlsql (i.e. nothing)

    izy

    LATER: this is horribly confusing... i've tried to correct it a couple of posts down
    Last edited by izyrider; 10-28-04 at 10:16.
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The Too few parameters, expected at least one generally means it could not find a field you specified.

    Based on the previous posts and the expression you quoted:
    Code:
    Select * From Employees WHERE Username = " & intKeyValue
    I'll going to guess. Did you DIM intKeyValue as a String? If so, problem #1 is that your expression doesn't have the set of single quotes mentioned earlier by izyrider.
    Another problem may be: does Employees contain a field called Username and what data type is it?

    If intKeyValue is a string, please use find and replace and change it to strKeyValue.

    Here's what probably happened. Let's say the employee name is "Johnson". Your statement, without the single quotes, reads:
    Select all fields from Employees Where the field Username equals the field Johnson.
    Thus the problem - there is no field named Johnson.

    If you replace the above code with:
    Code:
    Select * From Employees WHERE Username = '" & intKeyValue & "'"
    as indicated earlier, the SQL reads:
    Select all fields from Employees Where the field Username equals the value 'Johnson'.

    Lastly, a question for you: if you are using a primary key (probably an autonumber) then why can't you search using the key value, which you know will be unique, whereas the name may not be?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my earlier should have read:

    if you want to feed a...

    ...date into an SQL string you use sqlsqlsql #" & theDate & "# sqlsqlsql
    ...string into an SQL string you use sqlsqlsql '" & theString & "' sqlsqlsql
    ...number into an SQL string you use sqlsqlsql " & theNumber & " sqlsqlsql (i.e. no SPECIAL # or ' around the value)

    sorry for the complete stupidity of my earlier post.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Quote Originally Posted by tcace
    The Too few parameters, expected at least one generally means it could not find a field you specified.

    Based on the previous posts and the expression you quoted:
    Code:
    Select * From Employees WHERE Username = " & intKeyValue
    I'll going to guess. Did you DIM intKeyValue as a String? If so, problem #1 is that your expression doesn't have the set of single quotes mentioned earlier by izyrider.
    Another problem may be: does Employees contain a field called Username and what data type is it?

    If intKeyValue is a string, please use find and replace and change it to strKeyValue.

    Here's what probably happened. Let's say the employee name is "Johnson". Your statement, without the single quotes, reads:
    Select all fields from Employees Where the field Username equals the field Johnson.
    Thus the problem - there is no field named Johnson.

    If you replace the above code with:
    Code:
    Select * From Employees WHERE Username = '" & intKeyValue & "'"
    as indicated earlier, the SQL reads:
    Select all fields from Employees Where the field Username equals the value 'Johnson'.

    Lastly, a question for you: if you are using a primary key (probably an autonumber) then why can't you search using the key value, which you know will be unique, whereas the name may not be?
    Ok, firstly yes the intKey value is as String, secondly, i didn't know those rules, and thanks to izyrider for pointing them out. It'll save me a heck of a lot of trouble later on.

    Searching by user id would be nice, just incase there are ever two people with the same name. It's not very likely at this company, but it's always a good idea to plan for that eventuality. the only other point i can ask is how do you do that?

    just for clarity, the fields on the Employees table are:

    EmployeeID (Autonumber)
    LastName (Text)
    FirstName (Text)
    Title (Text)
    TitleOfCourtesy (Text)
    Address (Text)
    City (Text)
    Region (Text)
    PostalCode (Text)
    Country (Text)
    Extension (Number)
    Notes (Text)
    ReportsTo (Text)
    Username (Text)
    Password (Text)

    Granted it's not the best table layout, but i was planning to use the one table for several different functions, just so i don't confuse myself with trying to remember what data is on what table.

    This is what i had in mind for this problem though:

    On successful login, the users details would be copied to another table (Username, FirstName, LastName, Extension) along with a date field that is automatically updated with the date/time that the user logs in at.

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    quick query why are you coping all that info accross to a new table when all you will need to put accross is the employeeID and you can just query for the rest of the information you need or do you want to havea denormalised form of yoour database for throwing queries at
    Just throwing a spanner into the works

    I think your table design is ok as every piece of information youhave there is individual to the employee
    there is not other information there that would help being in a seperate table possibly with the exception of city and region

    but remember to draw diagrams when producing databases, they don't need to be too detailed but can save you hours of work when you need to trace things,

    Dave

  14. #14
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    That sounds like a pretty good idea. It'll keep things relatively simple.

    Ok, now to try and figure out the coding to just copy the ID number over.

  15. #15
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    If youe already picked up the username you could write a query like this
    Code:
    Insert Into NewTable (field1,field2)
    SELECT oldtable.employeeid, Now()
    FROM Oldtable
    WHERE Oldtable.username = youruserid
    just run that at somepoint after you pick up the username

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •