Making an Append Query move specific record(s)

[PPat]

Hi everyone,

I am a college student working on an Access database for my job. To give a quick overview, I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.

I understand this does not sound like a traditional, normalized database. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.

Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.

However, I cannot get figure out how to make the append query move only one student's record at a time. I am competent in VBA, but not as much in SQL, so a simplified explanation would really appreciated. If you need other pieces of information, please feel free to ask and I will provide. Also, any other tips you can give me would be appreciated since I'm still new to DBs and would like to learn more

Thank you for your time
Pete

[June7]

What are you using now to append the record?

You can use an INSERT SELECT with criteria in the SELECT part.

INSERT INTO tablename SELECT field1, field2, field3, FROM tablename WHERE studentID = [enter student id];

The [enter student id] parameter can be a reference to a control on form.

[PPat]

I'm still in the process of creating the macro/module, but everything i have is in the picture below. And I'll try that now, but I think that would work perfect, thank you!! But how do I reference the control to the form (sorry, I'm new at this haha)? Because ideally I would like it so the user doesn't have to enter the number manually, but instead have the query take the student ID number in the textbox and move the associated records to the next table all within a button click event.

[June7]

Try like:

Forms!formnamehere.textboxnamehere

[PPat]

Almost, here is my SQL WHERE Statement that i have:

WHERE [Student ID]=[90-Hr Sheet Request Form].StudentIDTextBox;

It will append the row of whatever ID number I put it, but I'd like for it to just append the one on the screen/form automatically. Did I write the SQL wrong? (PS: Thank you for your help June7!)

[June7]

Try:

WHERE [Student ID]=Forms![90-Hr Sheet Request Form].StudentIDTextBox;

[PPat]

That's perfect, thank you so much! Sorry I took out the Forms! without realizing it. Much appreciated June7. I'll make this post "solved".