Solved

VBA Code not working need help as soon as possible

Posted on 2006-11-07
7
263 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now