OpenRecordSet with Where 1

[WordTechinc]

I want to select record which FaceLetter = false only from table. FaceLetter is Yes/No field. When I run execute code I get all record from input table.

Here is code;

Private Sub CreateTaxCertification_Click()

Dim db As Database
Dim rs As Recordset ''' record write '''
Dim rs2009 As Recordset ''' record from here '''
Dim X As Variant

Set db = CurrentDb()

''' Select FaceLetter(Yes/No) false only '''
Set rs = db.OpenRecordset("TaxCertification")
Set rs2009 = db.OpenRecordset("Select * from RETX-2009 WHERE FaceLetter = 0")

''' function if there are FaceLetter = false records '''
X = rs2009.RecordCount
If X > 0 Then

''' it will go thru end of record and come back to first record '''

rs2009.MoveLast
rs2009.MoveFirst

Do While Not rs2009.EOF

rs.AddNew

rs.Fields("Parid") = rs2009.Fields("Parid").Value
rs.Fields("YearCur1FaceAmt").Value = rs2009.Fields("FaceAmount").Value
rs.Fields("YearCur1Status").Value = rs2009.Fields("FaceStatus").Value
rs.Fields("Legal 1") = rs2009.Fields("Legal 1").Value
rs.Fields("Legal 2") = rs2009.Fields("Legal 2").Value
rs.Fields("Own 1") = rs2009.Fields("Own 1").Value
rs.Fields("Own 2") = rs2009.Fields("Own 2").Value
rs.Fields("Address 1") = rs2009.Fields("Address 1").Value
rs.Fields("Address 2") = rs2009.Fields("Address 2").Value
rs.Fields("City") = rs2009.Fields("city").Value
rs.Fields("St") = rs2009.Fields("St").Value
rs.Fields("Zip") = rs2009.Fields("Zip").Value
rs.Fields("Apr Land") = rs2009.Fields("Apr Land").Value
rs.Fields("Apr Bldg") = rs2009.Fields("apr Bldg").Value
rs.Fields("Asmt Total") = rs2009.Fields("Asmt Total").Value


rs.Update ''' WRITE into new table '''
rs2009.MoveNext ''' read next input record '''

Loop

End If


Set db = Nothing
Set rs = Nothing
Set rs2009 = Nothing
Set rs2008 = Nothing

End Sub

 

[MazeWorX]

You need to include the field you are checking the criteria in the sql. you cant use a where clause on '*'

see below

Private Sub CreateTaxCertification_Click()

Dim db As Database
Dim rs As Recordset          ''' record write '''
Dim rs2009 As Recordset   ''' record from here  '''
Dim X As Variant
[red]Dim strSQL As String
[/red]
Set db = CurrentDb()

strSQL = "SELECT RETX-2009.*, RETX-2009.FaceLetter " _
       & "FROM RETX - 2009 " _
       & "WHERE (((RETX-2009.FaceLetter)=0));"

''' Select FaceLetter(Yes/No) false only  '''
Set rs = db.OpenRecordset("TaxCertification")
Set rs2009 = db.OpenRecordset([red]strSQL[/red], dbOpenDynaset)

notice the addition of strSQL we use this to contain our complete SQL string

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MazeWorX]

Also noted a couple of things here

If X > 0 Then

    ''' it will go thru end of record and come back to first record '''

    rs2009.MoveLast
    rs2009.MoveFirst

You should place the movelast - movefirst before you check the recordcount this forces access to return the correct count on the table.

Naming table fields with spaces is never a good idea try to avoid this you can use an underscore if you want a space

Use a With statement in your loop this will save repetative typing in your code

eg
With rs
.AddNew
.Fields("Parid") = rs2009!Parid
etc
If you have no spaces in your field names the above syntax could be used simplifing your code

Set rs2008 = Nothing << you can get rid of this line this rs is never declared or opened
I noticed upper/lower case differences when you are writing to your rs something to keep in mind it is case sensitive when you reference field names

Assuming the fields used underscores for spaces I rewrote the function below as an example

Private Sub CreateTaxCertification_Click()

    Dim db As Database
    Dim rs As Recordset
    Dim rs2009 As Recordset
    Dim X As Variant
    Dim strSQL As String

    Set db = CurrentDb()

    strSQL = "SELECT RETX-2009.*, RETX-2009.FaceLetter " _
             & "FROM RETX - 2009 " _
             & "WHERE (((RETX-2009.FaceLetter)=0));"

    Set rs = db.OpenRecordset("TaxCertification")
    Set rs2009 = db.OpenRecordset(strSQL, dbOpenDynaset)

    rs2009.MoveLast
    rs2009.MoveFirst

    X = rs2009.RecordCount
    If X > 0 Then
        Do While Not rs2009.EOF
            With rs
                .AddNew
                .Fields("Parid") = rs2009!Parid
                .Fields("YearCur1FaceAmt") = rs2009!FaceAmount
                .Fields("YearCur1Status") = rs2009!FaceStatus
                .Fields("Legal_1") = rs2009!Legal_1
                .Fields("Legal_2") = rs2009!Legal_2
                .Fields("Own_1") = rs2009!Own_1
                .Fields("Own_2") = rs2009!Own_2
                .Fields("Address_1") = rs2009!Address_1
                .Fields("Address_2") = rs2009!Address_2
                .Fields("City") = rs2009!City
                .Fields("St") = rs2009!st
                .Fields("Zip") = rs2009!Zip
                .Fields("Apr_Land") = rs2009!Apr_Land
                .Fields("Apr_Bldg") = rs2009!Apr_Bldg
                .Fields("Asmt_Total") = rs2009!Asmt_Total
                .Update
            End With
            rs2009.MoveNext
        Loop
    End If

    Set db = Nothing
    Set rs = Nothing
    Set rs2009 = Nothing

End Sub

HTH

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[WordTechinc]

Thank you MazeWorX. It worked beautifully. I have one more question. I want move the field I am checking the criteria in the sql. when I add the following line I get error;

Run-Time error '3265':
Item not found in this collection.

.Fields("FaceLetter") = rs2009!FaceLetter

 

[MajP]

in one of the recordsets you do not have a field "FaceLetter". Check your spelling.

 

[MazeWorX]

FaceLetter is in rs2009 and MajP is correct when he says it isnt in one of the recordsets so it has to be "TaxCertification"

.Fields("[red]FaceLetter[/red]") = rs2009!FaceLetter

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work