asked on

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 :

Can anybody help?

Badotz

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?

Tom Crowfoot

ASKER

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

ASKER CERTIFIED SOLUTION

Badotz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Crowfoot

ASKER

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'

Badotz

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).

Tom Crowfoot

ASKER

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

Tom Crowfoot

ASKER

Hi

Thanks for all your help on this one, whilst I used pure code rather than a text box in the end (see https://www.experts-exchange.com/questions/27480506/update-table-Syntax-problem.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

Badotz

No worries - glad to help.