save record to a different table

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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)

Answer 5

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

Answer 6

>>>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.

Answer 7

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   

Answer 8

tank you

pls read what i just reply to Hans Vogelaar

Answer 9

How else can we identify the student?

---

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

Answer 10

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  

Answer 11

You will have to tell us how to identify the students.

---

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

Answer 12

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

Answer 13

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)

Answer 14

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"

Answer 15

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)

Answer 16

cant the db is not in english