Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple insert into (A2k)

    Although this is relatively simple, it's stumped me, syntax error.

    Private Sub EmailAddress_AfterUpdate()
    Dim strSQL As String
    strSQL="Insert into tblClientCode "mailto:" & ([emailaddress])"
    DoCmd.RunCommand , strSQL
    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple insert into (A2k)

    The problem is you strSQL statement.

    It should probably be something like:

    strSQL = "Insert Into tblClientCode ""mailto:" & ([emailaddress]) & """"

    That will make your SQL statement

    Insert Into tblClientCode "mailto:email@server.tld"
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple insert into (A2k)

    Hello Dave,

    Do you want to update a field in an existing record, or do you want to append a new record? In both cases, you must specify a field name; this is lacking in your SQL Moreover, quotes aren't handled correctly.

    To update a field named Email, you will need to add a WHERE condition; the following is only a suggestion:<pre>strSQL = "UPDATE tblClientCode SET Email = 'mailto:" & [EmailAddress] & "'" & _
    " WHERE ClientID=" & [ClientID]</pre>

    To insert a record:<pre>strSQL = "INSERT INTO tblClientCode (Email) VALUES ('mailto:" & [EmailAddress] & "')"</pre>

    Note that this will insert a record with only the Email field filled in, all other fields will be empty.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple insert into (A2k)

    I'm getting type 13 mismatch and cannot append 0 rows etc.

    Let me try to be a little clearer.

    The form opens for entering names and address's.
    There is one bound field [EmailAddress] (text)]
    After the user has enterred this Email address without the "emailto:" prefix, I would like full "emailto:Whoever@wherever.com to be enterred into the table.

    I can't understand why it's not working because on hovering the cursor over the yellow highlited VBA error, the text looks correct.

    I'm wondering if it's becaus the field is bound ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple insert into (A2k)

    If the text box is bound, you shouldn't execute SQL, just set the value of the field. With a test to avoid adding the prefix if it is already there, it would be:

    Private Sub EmailAddress_AfterUpdate()
    If Left(EMailAddress, 7) <> "mailto:" Then
    EmailAddress = "mailto:" & EmailAddress
    End If
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple insert into (A2k)

    Thanks Hans.
    I understood your advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •