I cannot get my database to do what I want it to do. I have a form that has a combo box , two yes/no boxes and a text box on it. The combo box holds all the names of our subcontractors or project numbers. What I want to happen is when you click on the combo box and select a sub or project number, that it will retreive the information from the table/query. I dont know how to get it to do this. I already have it set up to go to the next record so you can add a new record in the table without replacing the first one. What can I do? Do I need to make two separate forms to get it to work or is there a way to delete, add and edit records all on one form?
I am using Access 2.0 so its a lil prehistoric but I will try anything!!! PLEASE HELP!!!!
The following sample database is more recent than Access 2 but the query by form concept is still the same. It would involve a search form and then the form or report to be opened up based on a query which has criteria based on the search form.
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:
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:
And the qryInv query's criteria for the CustNum field has:
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.