Query by form

[nhtraven]

Hi,

This is what i want to do. I want to allow users to set up any query on all fields in two tables. I have never tried the QBF before. I have the sql code needed but am wondering if it will work for a total of 15 fields. Not all fields will be queried at once, maybe 3 criterias at a time.
Is this doable. I have started on it, it is a BUNCH of code. I dont want to invest a lot of time in this if it is not doable. The client needs this asap.


also, in this code that i have seen for QBF they dont have the table name before the field name. I am wondering if this code would be correct:

where = where & " and [tblInput].[parcelno] = ' " + Me![parcelno]

I need to specify which table the field is coming from.


Thanks = )
Raven

 

[Dustman]

I think I've done something like this before. The way I had it set up, there was a subform in datasheet view on a form with unbound text boxes. In the KeyUp method of each of them, I passed the current values to a procedure that updated the query for the datasheet. It takes a lot of coding but it is great if you want to search and narrow on the fly.

Private Sub txtLName_KeyUp(KeyCode As Integer, Shift As Integer)
    'Update the search list.
    RefreshResults txtLName.Text, Nz(txtFName)
End Sub

Each of the text boxes are set up this way.. you have to grab the .text property on the current textbox and then use the Nz() function to grab the rest of the criteria. The procedure is:

Private Sub RefreshResults(LastName As String, FirstName As String)
    'Update the search list.
    subSearch.Form.RecordSource = "SELECT * FROM donorQuery WHERE ([d].[dLName] LIKE '" & LastName & "*' XOR [d].[dLName] IS NULL) AND ([d].[dFName] LIKE '" & FirstName & "*' XOR [d].[dFName] IS NULL);"
End Sub

The XOR is very key in this process.. the more fields you add the more XOR's you will have to add. It gets messy but the final product works great. If something like this will work for you, I can email you a copy to play with.

I also made another form once, it used the same type of procedure but I only had 3 text boxes and each of them had a combo box beside them. The user could select the field to search in the combo box and then type in the text box. It was another mess of code but again.. the final product worked great.

I'll be happy to help ya if this is what you are looking for. -Dustin
Rom 8:28

 

[Dustman]

Another possible solution is to create a form based on your query. Then the user can use the filter by form. This method is a whole lot simpler than the previous.. it isn't always as convenient for the end-user but it sure saves time coding.

I think you can do the same on a query if you view it in datasheet view. -Dustin
Rom 8:28

 

[nhtraven]

Yes you can email me and this helps alot. I have the form and textboxes all done and the code done. THis is what i did, not sure if this is correct. I made a form, with unbound textboxes, and a command button to activate the query.

The problem now is that i cannot get the last three lines of code to work correctly.

Is it better to use a subform for results? i was going to use a popup form?

Thanks = )
nhtraven@topseven.com

 

[nhtraven]

This is what i am trying to do. I have a form that i am setting up to run a query by command button, depending on the fields chosen in the form.

i need to use fields from two tables in this form. In the sql code, I used a string variable to hold the table name then insert it in select statement. I may be way off on this i am still a newbie in sql.

the code reads:

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim tblname1 As String
Dim tblname2 As String

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query&quot

On Error GoTo 0

where = Null

'InputData Table info

where = where & " and [tblInputData].[ParcelNo] = ' " + Me![ParcelNo] + "'"
tblname1 = tblInputData

' many more fields, each with the tblname1 / tblname2 stating which table is used

If Left(Me![ParcelNo], 1) = "*" Or Right(Me![ParcelNo], 1) = "*" Then

where = where & " AND [tblInputData].[ParcelNo] like ' " + Me![ParcelNo] + "'"

Else
where = where & " AND [tblInputData].[Parcelno] = ' " + Me![ParcelNo] + "'"
End If
tblname1 = tblInputData


' many more numeric ranges with same set up


' I cannot get this statement correct >> i need to add the tblname variables and am not sure how to go about it????
MsgBox "Select * from" tblname1 & ("where" + Mid(where, 6) & ";&quot

'Set QD = db.CreateQueryDef ("Dynamic_Query&quot, select * from" tblname1 & (" where " + Mid(where, 6) & ";&quot)


DoCmd.OpenQuery "Dynamic_Query"


End Sub


WIth the last bit of code from the Message box on is not compiling correctly

the error is expected end of statement



ANy ideas????
THanks in advance