We help IT Professionals succeed at work.

Run-Time Error 2147217904

correlate asked
Dear Experts,

I have a form "Perm booking" & within it a sub form "PermanentBookingsContacts".  both have seperate datasources.

I want to be able to export certain fields from the sub form to the main form. I've tried a few different methods but am struggling (in particular with setting the focus)

The method I've got furthest with it to create a text box (DataToExport) within the subform which creates the SQL String.  I then have a simple piece of code to run the export ...

Private Sub ExportButton_Click()
Dim SQLExport As String
SQLExport = Me.DataToExport
CurrentProject.Connection.Execute SQLExport
Forms![Perm Booking].Form.Requery
End Sub

Open in new window

The text box (DataToExport) gives me this:

UPDATE [Permanent] SET [Permanent].[Invoice Contact Name] = 'Client1-First Client1-Last', [Invoice Contact Title] = 'Client1 job Title 123', [Invoice Contact Email] = 'Fax', [Invoice Contact DL] = 'Client 1 DL', [Invoice Contact Switchboard] = 'Switchboard', [Invoice Contact Address] = 'Example Address 1' WHERE [Permanent].[assignments_guid] = '3F19E113-C458-4890-AF79-CE9E073120E'

its design view is this:

="UPDATE [Permanent] SET [Permanent].[Invoice Contact Name] = '" & [contact name] & "', [Invoice Contact Title] = '" & [contact jobtitle] & "', [Invoice Contact Email] = '" & [contact fax] & "', [Invoice Contact DL] = '" & [contact phone] & "', [Invoice Contact Switchboard] = '" & [contact switchboard] & "', [Invoice Contact Address] = '" & [contact address] & "' WHERE [Permanent].[assignments_guid] = '" & [assignments_guid] & "'"

Unfortunately I keep getting this runtime error :

Can anybody help?
Watch Question

Top Expert 2007

It seems pretty clear.

Have you verified that all of your supplied values actually have a value?

Not sure why you add & "" to your parameters?


All the supplied values have a value, I need to add & "" to the parameters as otherwise the text box throws up an error and wont populate
Top Expert 2007
Which leads me to believe the either your parameters are miscast (numbers as strings, etc.) or NULL.

For each parameter, please show it's name and value from the DEBUG window.


I think I'm being thick here, but there is no debug window in the text box where the string is constructed.  The parameters & either values in this text box are:

[contact name] = 'Client1-First Client1-Last'
[contact jobtitle] = 'Client1 job Title 123'
[contact fax] = 'Client 1 DL'
[contact switchboard] = 'Switchboard'
[Invoice Contact Address]  = 'Example Address 1'
[assignments_guid] = '3F19E113-C458-4890-AF79-CE9E073120E'
Top Expert 2007

This is Access, right? You are doing this in code, right?

Then you can step through the code as it runs (often referred to as "debugging") and press CTRL-G to get the DEBUG console (not sure of the keystrokes to view locals, but that is a menu selection, so no biggie).


Yep this is access, the code is created in the text box and then run by picking up the contents of the text box.

I think I have sussed this one out, just need to run a couple more tests & will get back to you - thanks for your help so far



Thanks for all your help on this one, whilst I used pure code rather than a text box in the end (see  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_27480506.html ) It was yoru comments regarding the parameters being miscast that made me go through & realised that both methods were doomed to fail as in the destination table one of the fields had an extra space in it (more proof that spaces should never be used)

Many thanks
Top Expert 2007

No worries - glad to help.