If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Can I Query a Form?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-03-04, 11:17
Superman07 Superman07 is offline
Registered User
 
Join Date: Jan 2004
Posts: 44
Can I Query a Form?

I currently have a form setup to read a table display information from the table depending of what primary key is being chosen on the form.
When the primary key is picked, the rest of fields on the form are auto populated.
What I want to do is have a query run to obtain the information off of the form, so that a report can be run off of the query. However, maybe I am going about this the long way? The reason is because when I attempt to run the report directly off of the form, none of the fields are entered, I simply get blanks in the form fields.

Is it possible, and do I need to go with option 1? Or is there an easier way to accomplish this?
Reply With Quote
  #2 (permalink)  
Old 02-18-04, 21:02
Bullschmidt Bullschmidt is offline
Guru
 
Join Date: Jun 2003
Location: USA
Posts: 1,032
This might be a little more than you asked for but the concept still works for just one key field instead of many fields to limit the search.

The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown. And to counter that you might consider creating the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.
__________________
J. Paul Schmidt, Freelance Web and Database Developer
www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On