Solved

VBA Code not working need help as soon as possible

Posted on 2006-11-07
7
267 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 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