itchyII
MIS
- Apr 10, 2001
- 167
Hi All,
I am having a problem with a query that is created using VB. (I am using Access 2000) I have a function that runs through the fields of the created query and pulls out the field source table and field name to use in generating the ‘where’ portion of a new query. The problem I am having is that the wrong source table is being extracted for two of my fields and this is messing up the second query. I don’t know what I’m doing wrong! Here is the code for that the initial query is based on:
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef(qry, "SELECT tblSiteInfo.strSiteName, tblSiteInfo.strSiteManager, tblSiteInfo.strCourStNumberName, tblSiteInfo.strCourBuildBase, tblSiteInfo.strCourWingOther, tblSiteInfo.strCourCity, tblSiteInfo.strCourStateProvince, tblSiteInfo.strCourCountry, tblSiteInfo.strCourPostalCode, tblSiteInfo.strMailStNumberName, tblSiteInfo.strMailPOBox, tblSiteInfo.strMailBuildBase, tblSiteInfo.strMailWingOther, tblSiteInfo.strMailCity, tblSiteInfo.strMailStateProvince, tblSiteInfo.strMailCountry, tblSiteInfo.strMailPostalCode, tblSiteInfo.strPhoneNumber, tblSiteInfo.strFaxNumber, tblSiteInfo.strExtension, tblSiteInfo.strSiteType, qrySiteAddresses.CourAddress, qrySiteAddresses.MailAddress FROM tblSiteInfo LEFT JOIN qrySiteAddresses ON tblSiteInfo.strSiteName = qrySiteAddresses.strSiteName ORDER BY tblSiteInfo.strSiteName WITH OWNERACCESS OPTION;"
The last two fields in the query (qrySiteAddresses.CourAddress, qrySiteAddresses.MailAddress) are the two fields that are showing the wrong source table when I run the following code:
Dim db As Database
Dim fld As field
Dim rst As Recordset
Dim where As String
'set variables
Set db = CurrentDb()
Set rst = db.OpenRecordset(table)
'start 'where' with bracket
where = "("
'get field names and build 'where'
For Each fld In rst.Fields
If where = "(" Then 'if it is the first field
where = where + "((("
Else
where = where + " OR ((("
End If
If Not IsNull(fld.SourceTable) And fld.SourceTable <> "" Then
where = where + fld.SourceTable + "." + fld.Name
Else
where = where + fld.Name
End If
where = where + "" + text + ")"
Next
'end 'where with bracket
where = where + ""
When this code is run on the initial query, it finds tblSiteInfo as the source table for CourAddress and MailAddress. I have run this function on other queries based on multiple tables and queries and it seems to return a null for the source table of fields based on another query, which is fine because I check for this and compensate. But why for this particular query, does it not find a null source table for the CourAddress and MailAddress fields? Can anyone help?
Itchy
I am having a problem with a query that is created using VB. (I am using Access 2000) I have a function that runs through the fields of the created query and pulls out the field source table and field name to use in generating the ‘where’ portion of a new query. The problem I am having is that the wrong source table is being extracted for two of my fields and this is messing up the second query. I don’t know what I’m doing wrong! Here is the code for that the initial query is based on:
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef(qry, "SELECT tblSiteInfo.strSiteName, tblSiteInfo.strSiteManager, tblSiteInfo.strCourStNumberName, tblSiteInfo.strCourBuildBase, tblSiteInfo.strCourWingOther, tblSiteInfo.strCourCity, tblSiteInfo.strCourStateProvince, tblSiteInfo.strCourCountry, tblSiteInfo.strCourPostalCode, tblSiteInfo.strMailStNumberName, tblSiteInfo.strMailPOBox, tblSiteInfo.strMailBuildBase, tblSiteInfo.strMailWingOther, tblSiteInfo.strMailCity, tblSiteInfo.strMailStateProvince, tblSiteInfo.strMailCountry, tblSiteInfo.strMailPostalCode, tblSiteInfo.strPhoneNumber, tblSiteInfo.strFaxNumber, tblSiteInfo.strExtension, tblSiteInfo.strSiteType, qrySiteAddresses.CourAddress, qrySiteAddresses.MailAddress FROM tblSiteInfo LEFT JOIN qrySiteAddresses ON tblSiteInfo.strSiteName = qrySiteAddresses.strSiteName ORDER BY tblSiteInfo.strSiteName WITH OWNERACCESS OPTION;"
The last two fields in the query (qrySiteAddresses.CourAddress, qrySiteAddresses.MailAddress) are the two fields that are showing the wrong source table when I run the following code:
Dim db As Database
Dim fld As field
Dim rst As Recordset
Dim where As String
'set variables
Set db = CurrentDb()
Set rst = db.OpenRecordset(table)
'start 'where' with bracket
where = "("
'get field names and build 'where'
For Each fld In rst.Fields
If where = "(" Then 'if it is the first field
where = where + "((("
Else
where = where + " OR ((("
End If
If Not IsNull(fld.SourceTable) And fld.SourceTable <> "" Then
where = where + fld.SourceTable + "." + fld.Name
Else
where = where + fld.Name
End If
where = where + "
" + text + ")"Next
'end 'where with bracket
where = where + "
"When this code is run on the initial query, it finds tblSiteInfo as the source table for CourAddress and MailAddress. I have run this function on other queries based on multiple tables and queries and it seems to return a null for the source table of fields based on another query, which is fine because I check for this and compensate. But why for this particular query, does it not find a null source table for the CourAddress and MailAddress fields? Can anyone help?
Itchy
