+ Reply to Thread
Results 1 to 3 of 3

Inserting String into a table field with VBA

  1. #1
    Forum Contributor
    Join Date
    04-16-2020
    Location
    Alberta, Canada
    MS-Off Ver
    365
    Posts
    100

    Inserting String into a table field with VBA

    I'm trying to insert a string into a field in a table using vba but I can't seem to get it. I'm not use to Access VBA but have some experience with Excel.


    I'm using cr to find my current record of my form, and em to find the e-mail addresses I want to find.

    the debug gets exactly what I want, but I can't get it to put it into the table ( cr.Fields("ToAddress") = AllAddress )


    HTML Code: 
    Dim ToAdd           As String
    Dim em              As DAO.Recordset
    Dim CurRecord       As String
    Dim cr              As DAO.Recordset
    Dim RecSet          As Integer
    Dim AllAddress      As String
    
    ToAdd = "tblContacts"
    CurRecord = "tblEmail"
    
    
    Set em = CurrentDb.OpenRecordset(ToAdd)
    Set cr = CurrentDb.OpenRecordset(CurRecord)
    
    With cr
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
                If cr.Fields("EmailID") = Forms!frmEmail!EmailID Then
    '                Debug.Print cr.Fields("EmailID")
    '--------- Add E-Mails to the current record.-----------------------------------------------------------------------------------------------------------------
                            With em
                            If Not .BOF And Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                While (Not .EOF)
                                    If em.Fields("MailingList") = -1 Then
    '                                    Debug.Print em.Fields("Email") & ";"
                                        AllAddress = AllAddress & em.Fields("Email") & ";"
                                    End If
                                    .MoveNext
                                Wend
                            End If
                        End With
                        Debug.Print AllAddress
                        cr.Fields("ToAddress") = AllAddress
    '--------- Add E-Mails to the current record.-----------------------------------------------------------------------------------------------------------------
                End If
                .MoveNext
            Wend
        End If
        .Close
    End With

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Inserting String into a table field with VBA

    if ur talking about this line:
    Please Login or Register  to view this content.
    or this line:
    Please Login or Register  to view this content.
    then ur missing 2 crucial methods access needs to allow you to do it. these:
    Please Login or Register  to view this content.
    thus, do this:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    04-16-2020
    Location
    Alberta, Canada
    MS-Off Ver
    365
    Posts
    100

    Re: Inserting String into a table field with VBA

    ok... that helped. I ended up with

    HTML Code: 
                      With cr
                            .Edit
                            ![ToAddress] = AllAddress
                            .Update
                        End With

    thanks...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find short string in reference table within a larger string in lookup table
    By alertall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-16-2019, 10:10 AM
  2. Replies: 7
    Last Post: 06-30-2017, 09:23 AM
  3. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  4. [SOLVED] Inserting Calculated field
    By jacob@thepenpoint in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-25-2015, 08:05 AM
  5. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  6. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  7. Inserting space in a field
    By smselvi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2010, 02:53 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1