Run-Time Error 2147217904

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
correlateAuthor Commented:
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

correlateAuthor Commented:
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'
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).
correlateAuthor Commented:
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
correlateAuthor Commented:

Thanks for all your help on this one, whilst I used pure code rather than a text box in the end (see ) 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
No worries - glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.