Solved

VBA Code not working need help as soon as possible

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

803 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