transfering data between databases

[Swarv]

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

[timothybard]

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.

[Swarv]

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.

[timothybard]

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:

SQLText = "INSERT INTO T_Orders ( Order_Numb, ITEMDESC, XTNDPRCE, QUANTITY ) SELECT SOPNUMBE, ITEMDESC, XTNDPRCE, QUANTITY " & _
"FROM SOP30300 where SOPNumbe='" & Me.Previous_Order_ & "' or sopnumbe='" & Me.ReplOrder_ & "' or sopnumbe='" & Me.CR_ & "'"

DoCmd.RunSQL SQLText

[Swarv]

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

[Swarv]

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

[timothybard]

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:

"INSERT INTO helpdeskLogTable issues ( title, id, time, status ) SELECT title, id, time, status " & _
"FROM issues where id="  & issues.id.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.

[Swarv]

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

[timothybard]

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.

[Swarv]

I have edited it and I have:

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

[timothybard]

Do you have that code on a line all by itself? You need to add the 'SQLTEXT =' to the beginning of the line.

View the attached picture to see exactly what it should look like. The Me.Id refers to the ID field on the form the code is on.

[Swarv]

thanks for that _ believe were getting somewhere.

I have now:

Private Sub Command78_Click()

Dim SQLText As String

SQLText = "INSERT INTO issues1 ( [title], [time], [status] ) SELECT title, time, status " & _
"FROM issues where {issues.id=" & Me.ID & ")"

DoCmd.RunSQL SQLText

End Sub

I'm running it as a command button.
It is erroring out on the last line. Any ideas?

Thanks

[Swarv]

ignore that last one. what I have is:

Private Sub Command78_Click()

Dim SQLText As String

SQLText = "INSERT INTO issues1 ( [title], [time], [status] ) SELECT title, time, status " & _
"FROM issues where (issues.id=" & Me.ID & ")"

DoCmd.RunSQL SQLText

End Sub

it runs but comes up with error saying:

Helpdesk Log can't append all the records in the append query. Helpdesk log set 0 fields to Null due to a type conversion failure and it didn't add 0 records to the table due to key violations and 1 record due to validation rule violations.

I'll have a look at how the tables are set up.

Thanks

[Swarv]

I have it working, how going to add in more field headings.

Manythanks