Ill try to make this as simple as possible. I have a couple different ways I'm going about doing a concatenate query, in order to display the data cleanly for the end user. We have Job #'s here that can contain many Part #'s, among other things. I have two (Many - Many) and one (One - Many) relationship tables setup to access the Part #'s and the others. The issue comes into play when I run any of the concatenate queries on them.
The form that displays the data, is a continuous form made to look like a datasheet. The form has several criteria for sorting through the listing, such as things like Job #, Part #, Date Built, etc. Its a dynamic search form.
I've tried to use Form.Filter, in a couple different ways, as well as setting the record source dynamically with the parameters. It all comes back to the concatenate part of the query though, as the main source of the slowdown.
I've tried creating a temporary table created from the results of the concatenate query, although that is also rather slow and I have heard this is not optimal for a variety of reasons. I honestly am not sure how I should go about this. Multivalued fields are also not ideal, as I need to update parts of one of the many sides fairly frequently, and those also pose an issue with queries. If anyone could help point me in the right direction, I would definitely we willing to listen, as this is by far the main issue I am having at the moment
***Omitted other controls for shorter code. There is more than just a textbox to filter by
Code for Dynamic Record Source
Code:
Public Sub Filter()
Dim sql As String
sql = "SELECT * FROM AllInfoQuery"
SearchTxt.SetFocus
If Len(SearchTxt.Text) > 0 Then
sql = sql & " WHERE ((PartNo) LIKE '*" & SearchTxt.Text & "*') OR ((JobNo) LIKE '*" & SearchTxt.Text & "*')"
End If
UU_AdvancedSearchSubform.Form.RecordSource = sql
End Sub
Code for Form.Filter
Code:
Public Sub Filter()
Dim strCriteria As String
Dim var As Variant
Dim i As Integer
SearchTxt.SetFocus
If Len(SearchTxt.Text) > 0 Then
strCriteria = "PartNo & '|' & JobNo like '*" & SearchTxt.Text & "*'"
End If
UU_AdvancedSearchSubform.Form.Filter = strCriteria
UU_AdvancedSearchSubform.Form.FilterOn = True
End Sub
Concatenate Query for a One to Many relationship using DJoin from this gentleman here:
Code:
SELECT AllJobInfo.AutoNumber, AllJobInfo.PartNo, AllJobInfo.Location, AllJobInfo.JobReleaseDate, AllJobInfo.BuiltDate, AllJobInfo.MoreInfo, AllJobInfo.JobStatus, AllJobInfo.Working, AllJobInfo.Plant, AllJobInfo.Description, AllJobInfo.ImageField, DJoin("JobTable.JobNo","JobTable", "[JobTable].[JobID]= " & [AllJobInfo].[AutoNumber] & "") AS JobNo
FROM AllJobInfo;
Concatenate Query for a Many to Many relationship using ConcatRelated from Allen Browne
Code:
SELECT AllJobInfo.AutoNumber, ConcatRelated("Designer.TestFullName","Designer INNER JOIN DesignerJUNCTION ON Designer.NameID = DesignerJUNCTION.NameID","DesignerJUNCTION.JobID=" & [AllJobInfo].[AutoNumber],"Designer.TestFullName",", ") AS DesignersFROM AllJobInfo;