Double click listbox item to copy record to a table in another database (1 Viewer)

[kate10123]

Hi there
I have a search form which works as follows:
User types in a studentid and a list box refreshes and displays any matches
What I need to do now is on double-clicking a match, I want that record to be copied to table tbl_student in a database called ‘Ask_dbase’. The student table fields are identical in both databases so it should be quite a clean procedure...
An example of the form is attached.
How would I go about doing this?
Thanks for all the advice

 

[ezfriend]

The easiest way I can think of right away is
1. Link the tbl_student table from the Ask_dbase to the current database where you do the search and double click. The link table in this case should be tbl_student1

2. Since both tables are identical, you can just use a simple SQL statement

ssql = ""
ssql = ssql & "insert into tbl_student1 "
ssql = ssql & "select * from tbl_student "
ssql = ssql & "where student_ID = " & [the ID from the selected record that you double click on]

currentdb.execute ssql

 

[kate10123]

Thanks for replying.

How do I do the link part?

Do I open the ASK_DATABASE right click tbl_student - import and try to link to the student table in my search database?

Or should I be using the linked manager? This is a greyed out option you see.

 

[kate10123]

I have also tried the following code:

Conn.Execute ("INSERT INTO tbl_Student SELECT * FROM tbl_Student_Test IN 'F:\ASK_Database_v7_be.accdb' ")

But this brings up the error message 'object required'

 

[ezfriend]

File => Get External Data => Link tables

Browse to the ASK_dbase then select the table that you want to link to.

Once that is done, the table should be more like a local table. You, then, can use the query I provided earlier to insert records. You should have both tbl_student and tbl_student1 tables at this point.


ezfriend.