Query by Form

[Sean Tucker]

Has anyone any experience with using a query by form that displays a list of valid records of the query in a browse like format. That the user could choose, by clicking on, the recorded they wanted. Such as a query for last name field �Jo� could produce a list containing records of last names Jones, Johnson, etc. Then the user could select the appropriate record, and thus filtering the original form for the record selected.
Thanks for your help!

[JerryBrightbill]

RE: Query by Form

Sean,

I use query by form as a search method as you suggest and it works fine. In fact, it is the only method the user uses to find any record. Attached is a screen shot of the form. I open it as a dialog. There are some quirks using a query by form as a dialog. I will try to put together a sample form with the query and post it.

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

That's exactly what I'm looking for in a find form. I'll start playing with the idea of using a dialog for a query, but if you could put together a sample that would be great!

Thanks!

Sean

[JerryBrightbill]

RE: Query by Form

Sean

Here is a fully functional customer form that will open a query by form as a dialog. I populated it with customer data from the sample inventory app. Unzip this into its own directory. Open the app and open the "customers" form. On the menubar there is a option to "Find a Customer". This will open the dialog query form. There is a custom button on the toolbar that does the same thing.

The dialog form opens in query by form mode. Enter the query info you want and press "Run Search". Either highlight the record in the browse that you want and press "Select" or doubleclick on the record. When the dialog closes, it saves the record number of the record selected. Then the script (on a hidden button) finds that record and displays it.

Now for the "quirks" i.e. critters - bugs, etc. First, you must specify a menu for the dialog (I use the same one as the main calling form). Even though you can't access a menu from the dialog, if you don't specify one, you will lose the original menubar completely when the dialog opens. Very wierd. The second quick occurs when the dialog closes. Even if you specified a menubar for the original calling form and the dialog, when the main form receives focus, you will get the system menu, not the custom menu. So you have to reset the menu back to the original custom menu.

Hope this gives you some ideas. The customer form and the dialog are not password protected, so open them up and look at the scripts. A lot of work and experimentation went into this method, but the users love it. One place this really works great is in sets when you need to find a record based on information in a child record to find the parent record. Query by form works here very well.

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

Great! Thanks a ton!

Sean

[Sean Tucker]

RE: Query by Form

Jerry,

Have you had any problems with MSVCRT40.dll crashing when you select a record? It crashes for me every time. I looked thru the forum for topics about errors with that file and there doesn't seem to be one type of problem. Any ideas?

Thanks
Sean

[JerryBrightbill]

RE: Query by Form

Sean,

I once had a problem on a specific computer with video dll's crashing, but only one and then only when I tried print preview full screen. That computer used a s3 based video chip. The same app on another computer works fine. I have used the dialog query by form lookup on a number of computers with no problem and the sample I sent works fine on 2 different computers. I suspect the problem may be unique to your configuration. If you can, try the sample on another computer.

You may want to experiment a little and try a number of things. Since there already seems to be a bug with loading a form as a dialog and immediately running query_by_form, try commenting out the line

'p_frm.query_by_form()

as shown on the "button_findcust" OnPush script on the "Customers" form. This will open the dialog but not in query form. Select a record and close and see what happens. If it still crashes, comment out the line

'parentform.close()

on the RowDblClick event for the browse on the "findcust" form. Now doubleclick on a record and see what happens. If you want to see what the variable value is, just add the field to the form temporarily. You may have to keep commenting out actions one by one until you find the problem. The best solution is still to try another computer with a different configuration.

Computers are sometimes quirky things. Many of us blame our gray hair and lack of sleep on the infernal machines.

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

Thanks for all your help!

Removing the parentform.close() from the button that opens the find form dialog solved all the bugs. It works great!

Sean

[JerryBrightbill]

RE: Query by Form

Sean,

A little experimentation resulted in my creating a crash in a different scenario. If I opened the calling form maximized, as soon as I opened and closed the dialog I got a crash in "user.exe". Opening with calling form non-maximized never caused a crash. I tried a number of changes and found two problems. A corrected code segment for the find button on the main form is shown below.

p_frm = :Form.load("findcust","Dialog")
p_frm.index_set("")
p_frm:fname.activate()
p_frm.query_by_form()
p_frm.show()
form_mode = p_frm.mode_get()
if alltrim(upper(form_mode))=="QUERY"
p_frm.query_exit()
end if
p_frm.close()

The two problems were are follows.

If the dialog was closed before the query ran, and thus the dialog was left in query mode, this seemed to confuse Alpha. So I added the segment to check the mode and exit the query before closing the form.

The second problem was simpler, but is the one that always caused a crash. So some reason, the order in which things occur in opening a dialog is very critical. Moving the line to put the form in query mode from before the object activate() to after the object activate() cured the crashes.

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

Thanks for all your help with the query by form, but I�m having a little problem. It was working fine for the most part until users started searching multiple times during one A5 session, and then it started to cause crashes. After reading the post concerning closing a dialog form from memory I realized that was the problem. So, I replaced the line �p_frm.close()� on the bottom of the script that originally opens the dialog form, I had taken it out to eliminate errors. The form is no longer left in memory, but now it has become very unstable and I receive a �user.exe� error all the time. Below is the script that opens the form on a button from the original form. Any ideas would be greatly appreciated.

Thank in advance,
Sean

tbl = table.current()
recno = 0
parentform.disable()
p_frm = :Form.load("Phys Find Form","Dialog")
p_frm.index_set("")
p_frm:Phys_Numb.activate()
p_frm.query_by_form()
p_frm.show()
form_mode = p_frm.mode_get()
if alltrim(upper(form_mode))=="QUERY"
p_frm.query_exit()
end if
parentform.enable()
if recno 0
tbl.fetch_goto(recno)
parentform.resynch()
end if
p_frm.close()

[JerryBrightbill]

RE: Query by Form

Try enabling the parentform before you close the dialog. This could be resulting in no form being active.

p_frm.show()
parentform.enable()
form_mode = p_frm.mode_get()
if alltrim(upper(form_mode))=="QUERY"
p_frm.query_exit()
end if
if recno 0
tbl.fetch_goto(recno)
parentform.resynch()
end if
p_frm.close()

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

Unfortunately that didn�t solve the problem. Since the error mostly occurs when the record is selected here are the scripts I use for search and select. By the way I also get the same error using the example you posted above, but only when the window is maximized. I would like to be able to keep the parent form maximized due to the number of fields. I could try minimizing the parent form at the top of the script that calls the dialog form, I guess.

Let me know if something jumps out at you in the code.

Thanks,
Sean

Search:

form_mode = parentform.mode_get()
select
case alltrim(upper(form_mode))=="QUERY"
parentform.query_run()
end select
tbl = table.current()
idx = tbl.index_primary_get()
if alltrim(idx.name_get()) = ""
end
end if
parentform:browse1.activate()

Select:

tbl = table.current()
form_mode = parentform.mode_get()
select
case alltrim(upper(form_mode))=="QUERY"
end
end select
recno = tbl.recno()
parentform.close()

[JerryBrightbill]

RE: Query by Form

Sean

I had a lot of problems getting the query by form to work in a dialog. It never worked with the main form maximized. Check if any scripts are running on the dialog when it closes. If all else fails, post a copy of the tables on the forum with the forms and I will see if I can duplicate the problem.

Jerry

[Sean Tucker]

RE: Query by Form

Jerry,

Well it looks like I'll just have to find another way for conducting queries. Attached is a copy of a table and forms that use the dialog find form. I�ve striped most of the data but left what�s need for the dialog form. In a normal setting the entry form would be opened from the main menu. Also, the only global script is autoexec with one line hiding the control panel. It's unfortunate that this type of query has problems, it would be a great tool for the users here. Thanks again for your help in trying to find a solution.

Sean

[JerryBrightbill]

RE: Query by Form

Sean,

Attached is a modification of your forms showing how to simulate a maximized form. Since it appears you are using buttons to close your forms, this should work. Actually the form is running in restore size, but looks maximized. In this condition, the query by form works.

This query method is very quirky. The first time I tried your download, it crashed. Then I commented out the line in the calling script that checks for the object "querydescription" and it worked. Then I uncommented the lines and it still worked. Putting the main form maximized caused an immediate crash when the dialog was opened. However, this sample seems reliable.

Jerry