Menu and Query Questions

[Rev. Michael L. Burns]

Can someone tell me ow to add a "select all" function to my menu so that I can select everything in the RichtextBox?

Also, is there a way to implement a "query by form" function to a vb form so that the user can input query text in the appropriate textbox to query for?

Thanks Rev. Michael L. Burns

[HunterMcCray]

Originally posted by Rev. Michael L. Burns
Can someone tell me ow to add a "select all" function to my menu so that I can select everything in the RichtextBox?

Also, is there a way to implement a "query by form" function to a vb form so that the user can input query text in the appropriate textbox to query for?

Thanks Rev. Michael L. Burns

Select all is very easy. Place a command button or a menu option on the form and when the event is triggered have it run this code:

rtfTextBox.SelStart = 0
rtfTextBox.SelLength = len(rtfTextBox.Text)


The second question is more complex. Of course you can use data from objects on a form to create a query, but you have to write a lot of code to test the conditions of the data before you execute the query. Start simply with a single text box that represents a string that you want to sort for in a given table:

Private Sub Command1_Click()
Dim rs as recordset
Dim txtSQL as String

if Len(TextBox.Text) then
txtSQL="SELECT TableName.* FROM TableName WHERE TableName.FieldName Like " & Chr(34) & "*" & TextBox.Text & "*" & Chr(34) & ";"

Else
txtSQL = "SELECT TableName.* FROM TableName;"
End If

set rs=db.openrecordset(txtSQL, dbOpenDynaset)

'or if it is a data object:

Data1.RecordSource=txtSQL
Data1.Refresh

end sub

Of course in the real world we may want to build a query that uses multiple options of WHERE and SORT fields, as well as different SELECT fields. The process is the same; however, testing for valid conditions and then building syntactically correct SQL from the user selected data can become very trying. In a sales App that I use in my business the user can select items from a list that is generated by inputting any number of categories to include or exclude, an infinite number of character sorts in either "AND" Mode or "OR" Mode, the Mfg of the product, the vendor the product was last purchased from and the list can be sorted in a vast array of permutations. The code that produces this SQL statement took about a week to write and debug. In the particular application it was the only viable solution. When I rework that segment of code I will encapsulate it into a Class because it uses ten sub-routines
to build and assemble the SQL. The debugging is greatly simplified if you place a temporary label on your form that displays the SQL statement before it executes it. Many times you will think that you have it right because your syntax is correct and you get some recordset, but later when you are using the recordset you realize that it is not the group of items you were targeting. The "parenthetical trap" is what I call it. You can prevent this by carefully reviewing the SQL that you are generating.

Hope That This Helps, If you have any questions e-mail me and I will try to solve them for you. ([email protected]).

Hunter

[Rev. Michael L. Burns]

Hunter,

Thanks for the detailed reply. This should give me something to start with.

Thanks for your help.

Pastor Mike