Problem with the query

[arunakumari02]

I have to populate txtfields on the form from the table.

I have table and want to select rows for which the check box (i.e. optimizer) is checked

Here is the code:

'Populating the fields 

strSQL = "SELECT ProjectID, Title, Owner, Optimizer From tblProjects where [Optimizer]=True"
Set rstProjects = CurrentDb.OpenRecordset(strSQL)
 
Debug.Print rstProjects.RecordCount
 
rstProjects.MoveFirst
   Debug.Print rstProjects!ProjectID & " " & rstProjects!Titl
   Debug.Print rstProjects.RecordCount
 
 
For intRow = 1 To rstProjects.RecordCount
   Forms!frmPipeline!("chkOptimizer" & intRow) = True
   Forms!frmPipeline!("txtProjectID" & intRow) = rstProjects!ProjectID
   Forms!frmPipeline!("txtTitle" & intRow) = rstProjects!Title
   Forms!frmPipeline!("txtOwner" & intRow) = rstProjects!Owner
      Debug.Print rstProjects!ProjectID & " " & rstProjects!Title
   rstProjects.MoveNext
      Debug.Print rstProjects!ProjectID & " " & rstProjects!Title
Next 
rstProjects.Close

When Debugging it shows:
1
109 Mav 2 PPQ Nonvalidated PPQ test
1
109 Mav 2 PPQ Nonvalidated PPQ test
120 Mavk India Recertification Support


Fact:

I have 4 records in the table which are checked but in the record count it shows only one and do not loop.

Any help is appreciated

 

[boblarson]

In order to get a record count with this method you need to move last first and then back:

rstProjects.MoveLast
rstProjects.MoveFirst

 

[arunakumari02]

Got it.

Thank you for the help

 

[boblarson]

Glad we could assist.

 

[arunakumari02]

From the previous code example, if I uncheck the checkbox the row should be deleted.

Any help of how to code it or any hint.