 |  |  |  |  |  |  |  |
transfering data between databases
Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved |
All,
I have an issues form in a database (MS Daily log) and when you fill in the form it save the data into an issues table. Everytime you do this it gives each issue an ID number (auto number).
Basically I want to fill in the form then before clicking next I would put another button on the form and when clicked it copied the data I just added to a table in another database (Helpdesk Log). The I can click next and goto the next record and carry on.
Any ideas please as its driving me mad.
Thanks
MArtin
I have an issues form in a database (MS Daily log) and when you fill in the form it save the data into an issues table. Everytime you do this it gives each issue an ID number (auto number).
Basically I want to fill in the form then before clicking next I would put another button on the form and when clicked it copied the data I just added to a table in another database (Helpdesk Log). The I can click next and goto the next record and carry on.
Any ideas please as its driving me mad.
Thanks
MArtin
I would recommend adding a button on the form and setting its visibility to false. Then on the AfterUpdate event of the form, make the button visible. Lastly, if someone clicks the button, have the database open a recordset that is the same as the Record Source of the form but where the ID is the current record and appends the data to the HelpDesk Log table.
Let me know if you need any assistance with what I described.
Let me know if you need any assistance with what I described.
thats sounds excatly like what i am after. can you give me anyideas on where to start with the code? I think can do the button. Thanks.
Are you familiar with both VBA and SQL? If so, then you can see how to run an append query from VBA at this site:
http://www.blueclaw-db.com/docmd_runsql_example.htm
Specifically, look at this section:
http://www.blueclaw-db.com/docmd_runsql_example.htm
Specifically, look at this section:
vb Syntax (Toggle Plain Text)
This seems fine but im am having problems with the criteria. I have edited the below. can you see where it is going wrong please.
"INSERT INTO I:\IT\Documentation\Helpdesk Log\helpdesk log.mdb issues ( title, id, time, status ) SELECT title, time, id, status " & _ "FROM issues where id=issues.id.text'" DoCmd.RunSQL SQLText
Last edited by peter_budo; Nov 18th, 2008 at 7:01 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
So I need to insert data from the issues table into the issues table of ms daily log.mdb. But I need to only transfer the data where is id is the same one that is on the form. (the id field auto up's). cheers
Two issues:
1) I don't think you can update a table that is not in the database. I think you need to create a linked table. To do so, click on File, Get External Data, Link tables. Then browse to the database with the table and then double-click on the table.
2) The problem is that the issues.id.text is within the quotes, so when the database tries to execute the line, it is searching for where the id = "issues.id.text" and not the value of issues.id.text.
Try this instead:
Replace helpdeskLogTable with the name of the table you want to append to
Oh, one last thing, the source fields and the destination fields have to be in the same order; I corrected this in the code already.
1) I don't think you can update a table that is not in the database. I think you need to create a linked table. To do so, click on File, Get External Data, Link tables. Then browse to the database with the table and then double-click on the table.
2) The problem is that the issues.id.text is within the quotes, so when the database tries to execute the line, it is searching for where the id = "issues.id.text" and not the value of issues.id.text.
Try this instead:
vb Syntax (Toggle Plain Text)
Replace helpdeskLogTable with the name of the table you want to append to
Oh, one last thing, the source fields and the destination fields have to be in the same order; I corrected this in the code already.
cheers for the reply. Your helping a lot.
You mention replace helpdesklogtable with name of table but in the sql statement it says helpdesklogtable issues, isn't issues the name of the table?
I have linked the 2 tables now. the one I wish to add the data to has come up called issues1.
Thanks for your help again
You mention replace helpdesklogtable with name of table but in the sql statement it says helpdesklogtable issues, isn't issues the name of the table?
I have linked the 2 tables now. the one I wish to add the data to has come up called issues1.
Thanks for your help again
You are correct... I overlooked the word 'issues' in the SQL statement.
You should be able to replace 'helpdeskLogTable issues' with isses1
You mentioned you linked the two tables. Wasn't there already an issues table in the MS Daily Log database? I had in mind that you would only link one additional table, which is from the Helpdesk log database.
You should be able to replace 'helpdeskLogTable issues' with isses1
You mentioned you linked the two tables. Wasn't there already an issues table in the MS Daily Log database? I had in mind that you would only link one additional table, which is from the Helpdesk log database.
I have edited it and I have:
it errors though. it wants end of statement after issues1.
I have only linked 1 table into this database.
there is an issues table in the daily log database and i have linked in issues from the helpdesk database. the helpdesk issues table is the one i wish to add the data to. when i linked in the helpdesk.issues it called it issues1.
cheers
INSERT INTO issues1 ( title, id, time, status ) SELECT title, id, time, status " & _"FROM issues where id=" & issues.id.text
it errors though. it wants end of statement after issues1.
I have only linked 1 table into this database.
there is an issues table in the daily log database and i have linked in issues from the helpdesk database. the helpdesk issues table is the one i wish to add the data to. when i linked in the helpdesk.issues it called it issues1.
cheers
Last edited by peter_budo; Nov 18th, 2008 at 7:01 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
 |
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: SubForm-2nd Subform Combo box Question
- Next Thread: unordered TAB movement in Front end
| Thread Tools | Search this Thread |
Latest MS Access and FileMaker Pro Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for MS Access and FileMaker Pro
