Results 1 to 10 of 10
  1. #1
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11

    autofill form not saving data in table

    I wrote the following code to save auto populated data in form to a table but it is not working. Please assist.



    Private Sub Command19_Click()
    CurrentDb.Execute "INSERT INTO [student attendance_log]([Student Name] ,[Student Surname],Class,[Enrolled Campus])Values('" & Me.Student_Name & "','" & Me.Student_Surname & "','" & Me.Class & "','" & Me.Enrolled_Campus & "');"
    MsgBox "Record Saved !!!", vbInformation, "Success"
    End Sub

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    but it is not working
    "Not working" doesn't give a lot of information.
    What is not working?
    Code doesn't execute?
    Any error messages?

    The fields referenced are text type fields?
    Field names correct?
    Control names correct?

    I did notice there is a missing space before the "VALUES" keyword.
    Code:
    Class,[Enrolled Campus])Values('" & Me.Student_Name

    Try executing the following
    Code:
    Private Sub Command19_Click()
        Dim sSQL As String
    
        sSQL = "INSERT INTO [student attendance_log]([Student Name] ,[Student Surname],Class,[Enrolled Campus])"
        sSQL = sSQL & " Values('" & Me.Student_Name & "','" & Me.Student_Surname & "','" & Me.Class & "','" & Me.Enrolled_Campus & "');"
    
        '-------
        ' comment out following line when debugging is complete
        Debug.Print sSQL   '<<-- check the immediate window to see if the SQL string is correct
        ' -------
    
        CurrentDb.Execute sSQL, dbFailOnError
    
        MsgBox "Record Saved !!!", vbInformation, "Success"
    End Sub
    What is in the immediate window?


    Note: shouldn't have spaces in object names.......

  3. #3
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    Hi Steve ,

    Apologies for the bad description of my problem been up for 12hrs , my code was running and but it would not save anything to the table.Let me try your solution then i will revert back to you.

  4. #4
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    All field and control names are correct and they are all text fields . I neglected to mention that the table was created from a query that auto populates the form .

  5. #5
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    When i try using your code i get a Runtime error '3314' that says i need to enter a student ID_Number field which is the primary key for the linked tables in the query that auto populates the form.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Can I ask why you are using VBA to add data back in to the tables that underlie the form? It doesn't seem to make sense, but maybe I'm missing something.

    Another thought - do you want to use that VBA to ADD NEW records to the [Student Attendance_Log] table, or do you want to UPDATE a record that is already there with new information? Insert Into .... adds new records.

  7. #7
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    What i am trying to do is to auto populate a form with data from the Students table and then save it into a student attendance table which will update an archive table called Attendance. Basically what i am trying to do is to create a student register that i can capture attendance dates on and then do a count to see the days a student was absent. I wanted to make it easier for the user by auto populating the form using the Campus as the Criteria as there are more than 5 campuses. If there is another way to add the data into the table without using VBA i would be really grateful.

  8. #8
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    Yes i would like to add new records to the student attendance_log table

  9. #9
    lmahere is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    Thank you Steve , it worked like a charm i just needed to add 2 fields.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2015, 09:27 AM
  2. Struggling with saving autofill
    By Surreall in forum Forms
    Replies: 4
    Last Post: 02-07-2013, 11:04 AM
  3. Saving data back into a table from a form
    By skyview chick in forum Forms
    Replies: 22
    Last Post: 08-15-2012, 05:43 PM
  4. Replies: 4
    Last Post: 12-22-2011, 03:04 AM
  5. Replies: 4
    Last Post: 01-05-2011, 07:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums