update table Syntax problem

Dear Experts

I'm using Access 2010 & I'm trying to update a table "Permanent" with details from the current record in a sub form.  but I am getting an error message ... Compile error:method or data member not found & it highlights me.contact_fax despite the fact that I chose the field from the pop up field box you get when  you're writing the code.

if I remove that particular piece in the update statement then it merely selects another piece to bring up as an error.

The code I am using is below, can anybody help?

 
Private Sub AddAsInvoicecontact_Click()
CurrentProject.Connection.Execute "UPDATE [Permanent] SET [Permanent].[ Invoice Contact Name] ='" & Me.contact_name & "', [Invoice Contact Title]='" & Me.contact_jobtitle & "', [Invoice Contact Email]='" & Me.contact_fax & "', [Invoice Contact DL]='" & Me.Mobile_Phone & "', [Invoice Contact  Switchboard]='" & Me.contact_switchboard & "', [Invoice Contact Address]'" & Me.contact_address & "' WHERE [Permanent].[assignments_guid] = Me.assignments_guid"
End Sub

Open in new window

correlateAsked:
Who is Participating?

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

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

danishaniCommented:
You might try this, difference is the WHERE statement:

Private Sub AddAsInvoicecontact_Click()
CurrentProject.Connection.Execute _
"UPDATE [Permanent] SET [Permanent].[ Invoice Contact Name] ='" & Me.contact_name & "', [Invoice Contact Title]='" & Me.contact_jobtitle & "', [Invoice Contact Email]='" & Me.contact_fax & "', [Invoice Contact DL]='" & Me.Mobile_Phone & "', [Invoice Contact  Switchboard]='" & Me.contact_switchboard & "', [Invoice Contact Address]'" & Me.contact_address & "' WHERE [Permanent].[assignments_guid] =" & Me.assignments_guid

End Sub

Open in new window


Assuming that guid is a Numeric value.

Hope this helps,
Daniel
0
correlateAuthor Commented:
Hi Daniel,

Many thanks for this, unfortunately I'm still getting the same error
0
correlateAuthor Commented:
Just saw your last bit - the GUID is text
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

danishaniCommented:
Are you running the Update from the Subform or from the Mainform?

The syntax will be different referring from Mainform to Subform fields.
0
correlateAuthor Commented:
Its running from the subform, the sub & mainform are linked (child / master) if that makes a difference
0
danishaniCommented:
oh ok if guid is Text then the syntax will be:
Private Sub AddAsInvoicecontact_Click()
CurrentProject.Connection.Execute _
"UPDATE [Permanent] SET [Permanent].[ Invoice Contact Name] ='" & Me.contact_name & "', [Invoice Contact Title]='" & Me.contact_jobtitle & "', [Invoice Contact Email]='" & Me.contact_fax & "', [Invoice Contact DL]='" & Me.Mobile_Phone & "', [Invoice Contact  Switchboard]='" & Me.contact_switchboard & "', [Invoice Contact Address]'" & Me.contact_address & "' WHERE [Permanent].[assignments_guid] ='" & Me.assignments_guid & "'"


End Sub

Open in new window


... WHERE [Permanent].[assignments_guid] ='" & Me.assignments_guid & "'"

with single apostrophe.


0
danishaniCommented:
Note: when there is a Numeric value, don't use the the single apostrophe, only with Text values.

0
Arthur_WoodCommented:
try this change:

Private Sub AddAsInvoicecontact_Click()
Dim contactname as String
Dim jobtitle as String
Dim fax as String
Dim mobilePhone as String
Dim switchboard as String
Dim address as String
contactname  = Me.contact_name.Text
jobtitle = Me.contact_jobtitle.Text
fax = Me.contact_fax.Text
mobilePhone = Me.Mobile_Phone.Text
switchboard = Me.contact_switchboard.Text
address = Me.contact_address  
CurrentProject.Connection.Execute "UPDATE [Permanent] SET [Permanent].[ Invoice Contact Name] ='" & contactname& "', [Invoice Contact Title]='" & jobtitle & "', [Invoice Contact Email]='" & Me.fax & "', [Invoice Contact DL]='" & mobilePhone & "', [Invoice Contact  Switchboard]='" & switchboard & "', [Invoice Contact Address]'" & Me.address & "' WHERE [Permanent].[assignments_guid] = Me.assignments_guid"
End Sub


you can then set a Breakpont on the assignment to jobtitle, and single step through each line, to make sure that each value is being properly set.

AW
0
correlateAuthor Commented:
Hi,

Many thanks for this, for some reason Access still cant find the fields in the sub form.  The way I created the sub form was to create a form and then drag it across to the main form & add the master / child links - would effect access's inability to find the fields?

The code is run from the sub form

0
Arthur_WoodCommented:
if the code is running in the SubForm, then Me refers to the SubForm, and not to the Parent (the form on which the sub-form is placed)

what is the Name of the 'master form' (the form on which the subForm is placed?

you can then use that name to access the fields on the master form:

Forms!nameofMasterForm.contact_name.Text for example  replace nameofMasterForm with the actual name of the Master Form for your application

AW

0
danishaniCommented:
You might try explicitly use the SubForm name in Update statement, replacing:

Me.YourFieldName

into

Forms![frmYourSubformName]![YourFieldName]

If this not work, then you might consider to attach a stripped version of your database containing the Mainform, subform and some test data.

Thanks.

0
correlateAuthor Commented:
Both the data I want to copy & the code is on the sub form, so Me. should work, The data on the main form comes from a seperate table (incidentally the one I want to update - so maybe I try & update the main form with the data rather than its table?

The master form name is Perm Booking
the sub form is Permanent
0
correlateAuthor Commented:
I'll have to have a further bash at this later on - thanks for your help so far
0
correlateAuthor Commented:
Hi,

I had a further play around last night & worked out that for some reason I couldn't get the focus to work, so by the looks of things I have to set the focus on each field and build the SQL string piece by piece.  This I have done but I'm getting a run time error (see picture below) Error Screen Shot

The code I am using is this (I cut out a number of the fields & will build them back in once I have this bit working)
 
Dim contactname As String
Dim jobtitle As String
Dim GUID As String
Dim StartString As String
StartString = "CurrentProject.Connection.Execute ''UPDATE [Permanent] SET [Permanent].[Invoice Contact Name] = '"
'------------------------------------------
Me.contact_name.SetFocus
contactname = Me.contact_name.Text
SQLString = StartString & contactname
'------------------------------------------
Me.contact_jobtitle.SetFocus
jobtitle = Me.contact_jobtitle.Text
SQLString = SQLString & "', [Invoice Contact Title] ='" & jobtitle
'------------------------------------------
Me.assignments_guid1.SetFocus
GUID = Me.assignments_guid1.Text
SQLString = SQLString & "' WHERE [Permanent].[assignments_guid] = '" & GUID
'------------------------------------------
SQLString = SQLString & "'''"
Run SQLString

Open in new window


Can anybody help?

Many thanks
0
Arthur_WoodCommented:
check your references.  Are any marked as MISSING ?
0
correlateAuthor Commented:
Nothing missing, I'm afraid

 I've also just tried putting taking out the first bit of the SQL statement (CurrentProject.Connection.Execute) and using that as the command (i.e. CurrentProject.Connection.Execute SQLString - that throws up no errors but doesnt do anything
0
correlateAuthor Commented:
Hi

I have attached a cut down version of the Db, the form is called "Perm Booking" and the page is Client Information.  There are then 2 main command buttons "Export Text Box method" & "First Method" - its the first method one

Many thanks
Invoicing---Copy.accdb
0
Arthur_WoodCommented:
try it this way:

Dim contactname As String
Dim jobtitle As String
Dim GUID As String
Dim StartString As String
StartString = "''UPDATE [Permanent] SET [Permanent].[Invoice Contact Name] = '"
'------------------------------------------
Me.contact_name.SetFocus
contactname = Me.contact_name.Text
SQLString = StartString & contactname
'------------------------------------------
Me.contact_jobtitle.SetFocus
jobtitle = Me.contact_jobtitle.Text
SQLString = SQLString & "', [Invoice Contact Title] ='" & jobtitle
'------------------------------------------
Me.assignments_guid1.SetFocus
GUID = Me.assignments_guid1.Text
SQLString = SQLString & "' WHERE [Permanent].[assignments_guid] = '" & GUID
'------------------------------------------
SQLString = SQLString & "'''"

CurrentProject.Connection.Execute SQLString


AW
0

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
correlateAuthor Commented:
Hi AW,

Unfortunately the same error number, with a different message Invalid SQL Statement; Expected 'DELETE', 'INSERT' etc etc
0
correlateAuthor Commented:
Hi AW,

Thanks for all your help - finally got it sorted & I have successfully built it up - your method was spot on - I think was missing a couple of spaces!

I'll paste the finished code in the next post
0
correlateAuthor Commented:
The Finished Code ...
Private Sub AddAsInvoicecontact_Click()
Dim contactname As String
Dim Company As String
Dim jobtitle As String
Dim Email As String
Dim Direct As String
Dim Switchboard As String
Dim Address As String
Dim Fax As String
Dim GUID As String
Dim StartString As String
Dim SQLString As String
'------------------------------------------
StartString = "UPDATE [Permanent] SET [Permanent].[Invoice Contact Name] = '"
'------------------------------------------
Me.contact_name.SetFocus
contactname = Me.contact_name.Text
SQLString = StartString & contactname
'------------------------------------------
Me.contact_company_name.SetFocus
Company = Me.contact_company_name.Text
SQLString = SQLString & "', [Invoice Contact Company] = '" & Company
'------------------------------------------
Me.contact_jobtitle.SetFocus
jobtitle = Me.contact_jobtitle.Text
SQLString = SQLString & "', [Invoice Contact Title] = '" & jobtitle
'------------------------------------------
Me.role.SetFocus
Email = Me.role.Text
SQLString = SQLString & "', [Invoice Contact Email] = '" & Email
'------------------------------------------
Me.contact_phone.SetFocus
Direct = Me.contact_phone.Text
SQLString = SQLString & "', [Invoice Contact DL] = '" & Direct
'------------------------------------------
Me.contact_switchboard.SetFocus
Switchboard = Me.contact_switchboard.Text
SQLString = SQLString & "', [Invoice Contact Switchboard] = '" & Switchboard
'------------------------------------------
Me.contact_address.SetFocus
Address = Me.contact_address.Text
SQLString = SQLString & "', [Invoice Contact Address] = '" & Address
'------------------------------------------
Me.contact_fax.SetFocus
Fax = Me.contact_fax.Text
SQLString = SQLString & "', [Invoice Contact Fax] = '" & Fax
'------------------------------------------
Me.assignments_guid.SetFocus
GUID = Me.assignments_guid.Text
SQLString = SQLString & "' WHERE [Permanent].[assignments_guid] = '" & GUID
'------------------------------------------
SQLString = SQLString & "'"
'------------------------------------------
CurrentProject.Connection.Execute SQLString
Forms![Perm Booking].Form.Requery
End Sub

Open in new window

0
Arthur_WoodCommented:
Glad to be of assistance.

AW
0
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.