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()
DoCmd.Save
Dim SQLExport As String
SQLExport = Me.DataToExport
CurrentProject.Connection.Execute SQLExport
Me.Requery
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 :

 error
Can anybody help?
correlateAsked:
Who is Participating?
 
BadotzConnect With a Mentor Commented:
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.
0
 
BadotzCommented:
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?
0
 
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

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
0
 
BadotzCommented:
No worries - glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.