Updating QueryDefs to a table

[mhubbard]

I am currently am using the 'QueryDefs' command to create a query and gather the desired results. It is working fine. What I do want to do is every time I establish the 'QueryDefs' not only populate the query to be created (which it is) but insert the criteria into a table so I can later keep track of the criteria used.
Any suggestions would be a huge help
Thanks!

 

[Fornatian]

How do you build the querydef - using a dialog box with options for the various possible criteria that may be used?

My first thoughts on this were to interrogate the SQL for the query using string functions extrapolating the appropriate fields and criteria. However, it may get mighty complicated when dealing with multiple criteria. This all depends on how you want to store the criteria. If you just want all the criteria in a textfield on bulk you could use a recordset method to insert the appropriate text(maybe everything after the "WHERE" word as the field value.

Alternatively, my second thought is to split the querydef creation and storing of criteria into two separate sub procedures.

One which does your querydef thing(which you already have working) and the other which pushes field names and values, from your criteria form into table. If you wanted to get really complicated you could use a two level hierachical structure using a 1-many relationship to join the query header details and query criteria table as follows:


table1(header)

QryName(PK):
QryCreationDate:

table 2(criteria)

CritID(PK)
QryName(Long)
CritField(Text)
Criteria


is this along the right lines?

 

[mhubbard]

To answer your first question, that is correct, I am using a dialog box with options. I like your idea of the two level approach.
This is how I am setting my querydefs.

Dim ArgCount As Integer
Dim MyDB As Database, qdf As QueryDef
Dim strSQL As String, MyCriteria As String, flgAll As Boolean
ArgCount = 0
Set MyDB = CurrentDb()
Dim strIn As String, i As Integer



strSQL = "SELECT Distinct Novowels(Faxnumber), Name, Company, ID FROM [SelectAllInfo] WHERE"

SelectionCriteria List1, IIf(IsNull(Field1.Column(1)), "", Field1.Column(1)), MyCriteria, ArgCount, Me

If Not flgAll Then
strSQL = strSQL & MyCriteria '
End If

Set qdf = MyDB.QueryDefs("SelectInfo")
qdf.SQL = strSQL

I like the two tables you have create, but unsure how I would populate them?


Thanks Fornatian!

 

[Fornatian]

What does your SelectionCriteria sub procedure do?

 

[mhubbard]

Sub SelectionCriteria (ListName As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer, frm As Form)
Dim strIn As String, i As Integer, strJoinType As String
strJoinType = ""
''ArgCount = 0
If ListName.ItemsSelected.Count > 0 Then
For i = 0 To ListName.ListCount - 1
If ListName.Selected(i) Then
strIn = strIn & "'" & ListName.Column(0, i) & "',"
End If
Next i
ArgCount = ArgCount + 1

If ArgCount > 1 Then
If frm("opgClauseType" & Right(ListName.Tag, 1)) = 1 Then
strJoinType = " AND "
Else
strJoinType = " OR "
End If

MyCriteria = MyCriteria & strJoinType
End If
MyCriteria = MyCriteria & " " & FieldName & " in (" & Left(strIn, Len(strIn) - 1) & ")"
End If
End Sub

 

[Fornatian]

Looking at this sub procedure which builds the MyCriteria variable and passes it back this is the best place to put your table recordset update procedure, really you have all the bits you need in there.

Do some reading up on the AddNew method of the recordset object which has all the ingredients you need to add rows to your table.

If you are still stuck give me a shout.