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
Solved

VBA Code not working need help as soon as possible

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

808 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