Set Recordset Using SQL

[schu7044]

Hi Everyone,

I am using a form and subform for user input. I have set the recordsource for the subform using VBA. This recordsource is based on user input entered via a combobox.

The following code works very nicely to set the recordsource:

Forms!frmGroupMain.frmGroupSub.Form.RecordSource = "SELECT * FROM qryGroup WHERE Left([TxGroup],3) = " & strGroup & " AND ActiveInGroup = True"

The code is selecting all records where the left 3 characters match the left 3 characters in the combobox (which is the group number, i.e. T02, W05, etc.) and those records that are identified as “ActiveInGroup.”

Now I want to create a recordset so I can loop through it and print letters for records identified as “NoShow.”

I am hoping someone can help me out with this.

Thank you, Don

 

[ajetrumpet]

Don,

This code:

"SELECT * FROM qryGroup _
   WHERE Left([TxGroup],3) = " & strGroup & " AND ActiveInGroup = True"

can be used with openrecordset so you can get in and manipulate the data. Type "recordset" into the help menu and see what pops up. You can probably learn a lot about data manipulation from that...

 

[schu7044]

Thank you! This worked beautifully!

I will move to the next phase and let you know when I'm stuck again.

Don

 

[schu7044]

Okay, it didn’t take me long to get stuck.

Here is the code to set the RecordsetClone—which works:

With Forms!frmGroupMain.frmGroupSub.Form.RecordsetClone

I am receiving a “variable not defined” error on line 3 below.

1. Do Until .EOF
2. 'Date the Appointment was Missed
3. If IsNull(RecordsetClone.CIALtrDate) Then
4. MsgBox "Call NoLtrApptDateEntered"
5. Exit Function
6. End If ' for LtrApptDate is empty
7. .MoveNext
8. Loop

End With

Help!

Don

 

[ajetrumpet]

The error occurs here:

3. If IsNull(RecordsetClone.CIALtrDate) Then

RecordsetClone is probably not the variable you declared. The correct syntax would be:

RecordSetNAME!CIALtrDate

But, if you're inside of a WITH RS block, it is this:

!CIALtrDate

Would you like some in-depth help with this? Or can you get through it?