Search a form and use a button to add a new record from that searc

Z

Zack

I have a database that is used to create quotes for equipment. In order to
keep track of each quote I have created a table called "T_Quotes" which has
"Quote_No" as the Primary Key. Each quote can have many parts that make it
up. Parts are under four different categories, based on their type.

I created a Union query that combines all 4 of the parts tables, and I use
that Union query in a form that users can search. I would like users to be
able to search for parts and add them to a quote. On the top of the form the
user selects their quote number, and then a text box is used to enter a part
number or part of the part number. After they enter their search criteria,
the users hits a search button that populates all parts that contain those
numbers somewhere in the Part_No.

Once a user has found the part number they are looking for I would like for
them to have the ability to add that part to the quote. I have tried adding
a button to the side of each search result, but I am unsure of the coding
that would be need behind the scenes in order to make it work.

I am sorry if this is confusing, but any help would appreciated.
Thank you in advance
 
C

Carl Rapson

Zack said:
I have a database that is used to create quotes for equipment. In order to
keep track of each quote I have created a table called "T_Quotes" which
has
"Quote_No" as the Primary Key. Each quote can have many parts that make
it
up. Parts are under four different categories, based on their type.

I created a Union query that combines all 4 of the parts tables, and I use
that Union query in a form that users can search. I would like users to
be
able to search for parts and add them to a quote. On the top of the form
the
user selects their quote number, and then a text box is used to enter a
part
number or part of the part number. After they enter their search
criteria,
the users hits a search button that populates all parts that contain those
numbers somewhere in the Part_No.

Once a user has found the part number they are looking for I would like
for
them to have the ability to add that part to the quote. I have tried
adding
a button to the side of each search result, but I am unsure of the coding
that would be need behind the scenes in order to make it work.

I am sorry if this is confusing, but any help would appreciated.
Thank you in advance
Click to expand...

If I understand what you're describing, the way I've done this is to display
the list of parts that match the search in a listbox. Then, in the Click
event of the listbox (after one item is selected), I add that selected part
to the quote.

Carl Rapson
 
Z

Zack

Thanks for your quick response Carl. I'm sorry, but I think I wasn't clear
enough. I have four search fields on the form header, and anyone of these
search fields can be used to search for a part. The four fields are Type,
Part No, Catalog No and Description. I am not sure if I will be able to use
a list box that will be populated based on a user's search criteria.
 
C

Carl Rapson

It sounds like you have a continuous form that you populate based on the
search criteria from the fields in the form header. On each line in the
continuous form, you also have a button that the user can click to add that
part to the quote. Is all of this correct? Is the continuous form currently
populating correctly?

If so, just put your code in the button's Click event to add the current
part to the quote. Access is smart enough to know inside the Click event
which record is the current record, so you can just refer to whichever field
you want to add to the quote. Something like this:

AddToQuote(Me.txtQuoteNumber, Me.txtPartID)

AddToQuote is whatever method you are using to add the part to the quote.
txtQuoteNumber would be the name of the control containing the quote number
(from the form header), and txtPartID would be the name of the control (from
the Detail section of the form) containing whatever value you want to add to
the quote.

Carl Rapson

Zack said:
Thanks for your quick response Carl. I'm sorry, but I think I wasn't
clear
enough. I have four search fields on the form header, and anyone of these
search fields can be used to search for a part. The four fields are Type,
Part No, Catalog No and Description. I am not sure if I will be able to
use
a list box that will be populated based on a user's search criteria.
Click to expand...
<snip>
 
Z

Zack

Yea, that's correct, I have a continuous form that populates based on the
search criteria in the form header. I understand all of what you are
suggestion except the "method":
AddToQuote(Me.txtQuoteNumber, Me.txtPartID)

The data that is generated in the continuous form comes from a UNION query
named "Q_All_Prices". This union query is made up of four tables that
contain part numbers, prices and descriptions for the four types of parts.
Will I be able to use a union query to add a part to my Quote Information
table, or will I have to combine the four part tables and then use that data
to populate my continuous form and use a click button to add a new record to
that table?

Thank you for your help so far, I am almost there.
 
C

Carl Rapson

You won't need to use the UNION query to add the part. In your continuous
form, each record is already populated by the UNION query. Just add a new
record to the quote table and set the part number to the part number on the
current record in the continuous form.

As for the "method", I was using that in a generic sense. You could be
adding a new part to the quote by using a Recordset, or by using
DoCmd.RunSQL or CurrentDB.Execute to insert a record. Since I don't know how
you are adding the record, I didn't want to provide a specific example. In
any case, use the part number of the current record in the continuous form,
as I indicated.

Carl Rapson
 
Top