Module Update - Selects multiple records to set onto one label (1 Viewer)

[Valery]

Hi all!

This is a module that creates a query (correct me if I am saying this wrong ).
I would like to modify it to include more fields with some criteria. I have no idea where that would go - obviously I did not write this wonderful module.

These are the fields I would like to add and their criteria. All the fields are in the tblTenant table.

MailList - a Yes/No field - CRITERIA =-1
Status - a text field - CRITERIA = "M" or "F"
LabelFlag - a Yes/No Field - CRITERIA = -1
Deactivate - a text field - CRITERIA = "N"

Thank you! Please don't hesitate if you need more info.


Module:

Public Function GetField(UnitNo As Long, TenantOrd As Long, FieldName As String, Optional QueryName As String = "qry_F_TenantList") As Variant

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim TenantCount As Long

TenantCount = DCount("*", QueryName, "UnitNum = " & UnitNo)
If TenantOrd > TenantCount Or TenantOrd < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE UnitNum = " & UnitNo & " " & _
    "ORDER BY Status ;"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
'qdf.Parameters("[forms]![frmLabels]![Gender]") = [Forms]![frmLabels]![Gender]

Set rs = qdf.OpenRecordset
rs.Move TenantOrd - 1
GetField = rs.Fields(FieldName)
qdf.Close
rs.Close

End Function

 

[arnelgp]

you should modify your qry_F_TenantList query and include those fields.

 

[Valery]

Arnel, I don't know why the programmer chose a query that was already in-use by a form - I cannot change it otherwise the form content will be wrong.

Can I create a new one? And just change the name where ever I see qry_F_TenantList?

 

[arnelgp]

you can create one ofcourse.
your function has GetField, has a 4th parameter, which is optional, meaning if you only call the function with 3 parameter it uses "qry_F_TenantList" as the default parameter.

you should modify your form that use this function + your new query by supplying the 4th parameter with your new query name (string).

/*/
im tied up right now, so cannot review the parking report.

 

[Valery]

Sorry Arnel - totally confused. Way above my level here!

The query qry_F_TenantList drives the main form of the entire database. It lists all the tenants that are active.

The query behind this module is to create a label report. It must be restricted as per the field I mentioned above. Again, no idea what it is doing in that module - maybe it was picked because I had sent it in my sample...

So I will create a new one and try it.

 

[arnelgp]

ok, just leave the query for a while, modify the function to include your additional criteria.

Public Function GetField(UnitNo As Long, TenantOrd As Long, FieldName As String, Optional QueryName As String = "qry_F_TenantList") As Variant

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim TenantCount As Long

TenantCount = DCount("*", QueryName, "UnitNum = " & UnitNo)
If TenantOrd > TenantCount Or TenantOrd < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If
'this is the original select string
'strSQL = "SELECT " & FieldName & "  " & _
'    "FROM [" & QueryName & "] " & _
'    " WHERE UnitNum = " & UnitNo & " " & _
'    "ORDER BY Status ;"
[COLOR=Blue]'new sql string
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE UnitNum = " & UnitNo & " " & _
    "And MailList = True " & _
    "And Status IN ('M','F') " & _
    "And LabelFlage = True " & _
    "DeActivate = 'N' " & _
    "ORDER BY Status ;"

[/COLOR]Set qdf = CurrentDb.CreateQueryDef("", strSQL)
'qdf.Parameters("[forms]![frmLabels]![Gender]") = [Forms]![frmLabels]![Gender]
Set rs = qdf.OpenRecordset
rs.Move TenantOrd - 1
GetField = rs.Fields(FieldName)
qdf.Close
rs.Close

End Function

 

[Valery]

working on it thanks!

 

[arnelgp]

i am wide awake, we have time difference, im in the middle east right now (10:30am).

 

[Valery]

Dear Arnel,

I must be missing something! I did create a query - it is called qry_R_Labels. And the label report is now based on that query. I am sure I messed up somewhere! It does not return an error message but opens the module and stops at : Set qdf = CurrentDb.CreateQueryDef("", strSQL).

Here is my updated module:

Option Compare Database
Option Explicit

Public Function GetField(UnitNo As Long, TenantOrd As Long, FieldName As String, Optional QueryName As String = "qry_R_Labels") As Variant

Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim TenantCount As Long

TenantCount = DCount("*", QueryName, "UnitNum = " & UnitNo)
If TenantOrd > TenantCount Or TenantOrd < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE UnitNum = " & UnitNo & " " & _
    "ORDER BY Status ;"
 'new sql string
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE UnitNum = " & UnitNo & " " & _
    "And MailList = True " & _
    "And Status IN ('M','F') " & _
    "And LabelFlage = True " & _
    "DeActivate = 'N' " & _
    "ORDER BY Status ;"
    
Set qdf = CurrentDb.CreateQueryDef("", strSQL)

Set rs = qdf.OpenRecordset
rs.Move TenantOrd - 1
GetField = rs.Fields(FieldName)
qdf.Close
rs.Close

End Function

Here is the new query:

SELECT DISTINCT tblOccupancy.UnitNum, tblOccupancy.TenantID
FROM tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID;