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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
correlateAuthor Commented:
Just saw your last bit - the GUID is text
0
 
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
 
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
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.