Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Code not working need help as soon as possible

Posted on 2006-11-07
7
Medium Priority
?
270 Views
Last Modified: 2008-02-01
I am working with an access database with over 200000 records. I have been breaking it down into smaller databases, mailmerging it into another document, now i would like to import the information back into the database with a field changed to "yes" as you will see from this code, does any 1 know what is wrong with this?



Private Sub cmdrun_Click()
    Dim dbdatabase As Object
    Dim rstVideos As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object
    Dim sqlstr As String

 

    Set bdatabase = CurrentD
    Set rstVideos = bdatabase.OpenRecordset("tbltest")
    Set fldColumns = rstVideos.Fields

sqlstr = "select * from GLEN2"
With Me.lsttest
    .RowSource = sqlstr
    .ColumnCount = 11
    .ColumnHeads = True
End With

MsgBox sqlstr

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = postcode Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = sqlstr Then
                    ' then change its value
                    rstVideos.Edit
                    rstVideos("Mailed").Value = "Yes"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
End Sub



with this code currently nothign is being changed and no error messages are being reported.
0
Comment
Question by:CaptainGiblets
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 17888602
Also - the way i want to do it is i want to check every field in the record apart from 1 field (there are 11 fields), if they all match then i want to add a "yes" into a field called "Mailed",   there are 2 records that if used together can create a distinct value, these are contact number and postcode.

any problems please let me know.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 17889565
Scratching the surface here I'm sure. As I understand it, you want to get THIS code working, then expand it to compare 10 of the 11 fields before you update your mailed field to yes?


Private Sub cmdrun_Click()
    Dim dbdatabase As Object
    Dim rstVideos As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object
    Dim sqlstr As String

 

    Set bdatabase = CurrentD             <------------------------------This needs to be CurrentDB
    Set rstVideos = bdatabase.OpenRecordset("tbltest")
    Set fldColumns = rstVideos.Fields

sqlstr = "select * from GLEN2"
With Me.lsttest
    .RowSource = sqlstr
    .ColumnCount = 11
    .ColumnHeads = True
End With

MsgBox sqlstr

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = postcode Then              <---------What is postcode?
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = sqlstr Then
                    ' then change its value
                    rstVideos.Edit
                    rstVideos("Mailed").Value = "Yes"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
End Sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17889590
Why are you bothering with the listbox? seems this would be easier using a find duplicates method...two recordsets looping and comparing. Is there a reason you're doing this with the listbox?
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 17889901
the list box is only there for testing purposes, once its working it will be deleted.
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 17889909
oh and the postcode was the name of one of the fields in the databases, i was trying to see if that would work.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question