none
save record to a different table RRS feed

  • Question

  • hi:

    i have 2 tables that have the same fields. one called "students" one called "studentsinhold" for Each table i have a form. now i have 2 buttons for the form "studentinhold"  What I want to do is that when i press the Accepted button it well save the record in "students" table and delete the record from "studentsinhold" table. and the second button(called not Accepted) well do the same but save it to 3rd table.  i well use the same code. this db have 4 end user and Linked tables

    Saturday, September 3, 2016 7:18 PM

Answers

  • In the following, I'll assume that the tables have a primary key StudentID of type Number.

    The code for the Accepted button could look like this:

    Private Sub Accepted_Click()
        Dim strSQL As String
        On Error GoTo ErrHandler
        strSQL = "INSERT INTO students SELECT * " & _
            "FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        strSQL = "DELETE * FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub

    The code for NotAccepted would look similar, but with a different table instead of students.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eshay1 Sunday, September 11, 2016 12:25 PM
    Saturday, September 3, 2016 9:20 PM

All replies

  • i have 2 tables that have the same fields. one called "students" one called "studentsinhold" for Each table i have a form. now i have 2 buttons for the form "studentinhold"  What I want to do is that when i press the Accepted button it well save the record in "students" table and delete the record from "studentsinhold" table. and the second button(called not Accepted) well do the same but save it to 3rd table.  i well use the same code. this db have 4 end user and Linked tables

    Hi eshay1,

    Instead of having two tables, I would use one table with an additional field to distinguish the "students" and "studentsinhold". For this field I would take a Date-field. An empty date would mean "studentinhold", a filled date would mean when the student was accepted.

    Imb.

    Saturday, September 3, 2016 7:24 PM
  • can't do that like i said there are 4 end users and on the "students" table there a fields that have Sensitive Data that you can't see in the "studentsinhold" table
    Saturday, September 3, 2016 9:03 PM
  • can't do that like i said there are 4 end users and on the "students" table there a fields that have Sensitive Data that you can't see in the "studentsinhold" table

    Hi eshay1,

    End users never should "see" the tables. They interact with the tables through Forms. In the Forms you can make a distinction between the different end users what they can see, depending on some kind of authorization.

    Eventually, you could place the sensitive data in a separate table, with a 1:1 relations to the "total" table of all students.

    Imb.

    Saturday, September 3, 2016 9:13 PM
  • In the following, I'll assume that the tables have a primary key StudentID of type Number.

    The code for the Accepted button could look like this:

    Private Sub Accepted_Click()
        Dim strSQL As String
        On Error GoTo ErrHandler
        strSQL = "INSERT INTO students SELECT * " & _
            "FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        strSQL = "DELETE * FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub

    The code for NotAccepted would look similar, but with a different table instead of students.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eshay1 Sunday, September 11, 2016 12:25 PM
    Saturday, September 3, 2016 9:20 PM
  • tank you

    i need vba code. i got this db to add and fix things i can't start make changes  i don't want to get into it

    Saturday, September 3, 2016 9:26 PM
  • >>>i need vba code. i got this db to add and fix things i can't start make changes  i don't want to get into it

    According to your description, I think that Imb-hb's suggestion is feasible, but it seems that you do not want to change table structure. If you follow Hans's sample code, do you resolve your issue?
    If not, you could provide more information about your issue, for example table structure, screenshot etc., that will help us resolve it.

    Thanks for your understanding.
    Monday, September 5, 2016 1:44 AM
  • In the following, I'll assume that the tables have a primary key StudentID of type Number.

    The code for the Accepted button could look like this:

    Private Sub Accepted_Click()
        Dim strSQL As String
        On Error GoTo ErrHandler
        strSQL = "INSERT INTO students SELECT * " & _
            "FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        strSQL = "DELETE * FROM studentsinhold " & _
            "WHERE StudentID=" & Me.StudentID
        CurrentDb.Execute strSQL, dbFailOnError
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
    End Sub

    The code for NotAccepted would look similar, but with a different table instead of students.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    tank you

    the code is working but its make conflict with the studentID

    the studentID does not allow duplicates. it have a lot of relationships   

    Monday, September 5, 2016 5:41 PM
  • tank you

    pls read what i just reply to Hans Vogelaar

    Monday, September 5, 2016 5:43 PM
  • How else can we identify the student?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 5, 2016 6:54 PM
  • How else can we identify the student?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    so what do i have to do to make the students table to give the record from studentsinhold table a new id  
    Tuesday, September 6, 2016 1:01 AM
  • You will have to tell us how to identify the students.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 6, 2016 5:47 AM
  • You will have to tell us how to identify the students.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    by identity card the all have identity number 

    Regards eshai

    Tuesday, September 6, 2016 6:17 PM
  • What happens if you replace StudentID in the code that I posted with the name of the identity number field?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 6, 2016 6:58 PM
  • What happens if you replace StudentID in the code that I posted with the name of the identity number field?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    i get an error "Syntax error (missing operator) query expression"
    Tuesday, September 6, 2016 11:13 PM
  • Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive, FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, September 7, 2016 5:31 AM
  • cant the db is not in english 
    Wednesday, September 7, 2016 9:58 PM