Using only one record

[J.Bennett]

I have a database that the user enters in information related to customer
name, address, etc., along with selecting the item to purchase (a storage
building in this case). Additional combo boxes allow the user to select the
options and the number of each option, new or used, discounts, colors, full
purchase or rent, etc. Based on the information selected, I have a query set
up that uses this information, calculates the price associated with the
building, the monthly payments, etc. This information is displayed on
another form named "outputform." I have error messages that appear on this
form to ensure all the necessary information has been entered properly. Once
all errors have been corrected, the next step is to simply print a report
that uses the information from the query.

As of now, it is very cumbersome in selecting the one record to use for the
query and I am seeking a better way to handle this portion. I currently have
a check box on the initial form that the user checks to "use this record".
The query simply uses the records that have the check. The problem is that
the user must be sure that all other records have be de-selected. The
resultes of the query is printed out on a report that looks like the
manufacturers "Dealer order sheet". If other records were selected, then
multiple sets of the "Dealer order sheets" are printed.

I would like to find a way to eliminate the need for the user to have to
select the specific record to use. The best case senerio is that the
specific record that is used for entering the information is caried forward
to the query so that it is the only one that is displayed on the "outputform"
and the only one that prints the "Dealer Order Sheets" report.

Can anyone offer a way of doing this? Any help would be appreciated. I
currenlty switch from one form to another using macros that close the
specific forms and opens up the next form and searches to find the "Use this
record" that is checked. I plan to change this to a TAB form so that these
are no longer needed. However, I don't know how to limit the records in the
query without requiring the user input.

I look forward to someone's response. Thanks.
J.Bennett

 

[Allen Browne]

A fairly simple way to do this might be to create a search form in
Continuous Form view. The user enters whatever criteria they want to narrow
the search, and you filter your form to show the matches. The user then
clicks anywhere in the row they want (i.e. making it the current record in
the form), and clicks a command button on the Form Footer. The button runs
an OpenReport, filtering the report to the primary key of the current
record.

Here's an example of how to make a search form:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It involves writing some VBA code to create the Filter string. It's worth
spending half an hour to download the example and see how it works, as you
will use this approach in many other contexts as well.

Once you have that, you can use the code in this link to print just the
current record:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

 

[J.Bennett]

Thanks. I'll try that to see if I can figure out how to implement. I am
certainly not a guru at Access, but the references seem to be straight
forward. Thanks for the information.

 

[J.Bennett]

Allen,
Thanks. I have set up my database to print the current record. That works
GREAT. I have reviewed the information related to a continuous Form view and
setting up a search form. I really can't see how this serves what I am
trying to accomplish. It would be very rare that I would actually need to
search for a previous entered record. I would be entering a new customer
almost every time, or use a generic customer named "customer quote", which is
the first record in my table. I already have a simply "Find" button to find
a record for the last name should this be needed.

What would be very helpful if possible is to implement an "Open Form"
command that opens another form and keeps the same record very similar to the
commad for printing the specific ID (primary Key field). The code for
printing the report for my specific database is as follows:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[CUSTID] = " & Me.[CUSTID]
DoCmd.OpenReport "OrderPrintForm", acViewPreview, , strWhere
End If
End Sub

Is there any way to use a very similar approach to opening a form where it
too looks to find the same [CUSTID] record? I tried to change the command
above to the following (just trying, not sure if this is even possible):

DoCmd.OpenForm "CustomerInfoForm", strWhere

I have a total of three forms in my database. The first two enter
information into the CustomerInfoTable, the third form pulls data from a
query (same query as for printing). While I recognize this might not be the
most efficient approach, it would sure make things much easier on my end and
meet all of my needs.

Any help with this would be appreciated.