insert records from one form to another form

Hello ,
I’m trying to make a form named OrderForm for data entry into a table .The table is named : tblOrder with some fields : OrderID(primary key ) , ServicesID, SupplierID, UnitPrice.
In OrderForm ‘s header I put some combo box for using the query by form (QBF) technique to open another form ( for example :QBF_Form) to display records that matched QBF filter. All are running well .
What I want is that QBF_Form would have a check box for each record and a command button (cboInsert).When I stick in the check box , meaning this record is selected and finallly , cboInsert button is clicked , QBF_form will close and selected records will be displayed in OrderForm and tobe ready for edit or dellete ,etc .
Any help would be greatly appreciated.
Thanks,
AT71

Hi AT71,

Just a quick response. Add a Yes/No field to your records as a checkbox. Place a subform on your order form with the record source set to a query with the records that will have a check box in the field, and set the criteria to either -1 or Yes. When a record has been selected on your QBF form and when the form is closed, requery the subform on your order form to display the records that have been selected.

This concept should get you started. You will need to add additional code to remove the check box value when you are finished with the records etc.

If this isn't what you were looking for, let me know and I will try to be more specific on how to do this.

lwells

Thank Iwells , for your quick response.
Let say I have 3 cars: BMW, BENZ, Lesux stored in a tblCars. There are several Providers who supply services onto my cars whenever needed and I put in tblProviders.
All providers ‘ services quotation put in table tblServices included unit price.
So my way goes like this : I made a table name tblOrder to store data for every time I order services from one or many providers at same time .It may like :
OrderID - Car - Provider - Services - UnitPrice
01 - BMW - A - x - 10
01 - BMW - A - y - 20
01 - BMW - B - z - 30

In a form frmOrder ( for data entry into tblOrder) i made some combo box for search or filter what services I need because there are a lot of services ,and each service will be depended from other conditions so I canot chose it one-by-one from a drop-down combo box built from tblServices source . I use QBF method and display a form ( frmQBF) for expected services.It ‘s OK but my way comes to end here. I need a check box for every record in this frmQBF because I need to reviews one more time by check box selected (Yes value ) before “Paste “ them by a command button into frmOrder .
I ‘m a beginner, so please provide me code step by step . Would you correct if I chose wrong way .
Thank a million .
AT71

Hi AT71,

Can I make a small suggestion here. Being that you are new to Access, I would set up your table design before going into form design and coding. You have got the right idea for your tables, however they need a little tweeking before moving on with forms. Writing code to overcome table design will cause more grief than you will ever want. (Voice of experience, as I learned the hard way)

Open the Northwinds sample database to get the idea on how to set up your tables. It also has some good examples of forms using the correct table relationships. What you are wanting to do, actually mimics what this database design has already done. Use it as a template and substitute your own field names.

If you still are having problems after that, then let me know.

lwells

Thank you , Iwells
Your suggestion is good . Before I build my database , I spent a lot of time to review Northwinds examples because I am new to access and I chose Northwind to be my background . Northwind is Trade company so every time Customer send an order to him , he can check his products available or not and then he can make an order to provide products to Customer via Order Form . My work is a little different . I am a customer who order services from providers. But let say I only have Northwind then I build my data base 80% same as Northwind ‘s.
I have tblServices same as Product table , tblSupplier as Supplier , tblOrder As Order , tblOrderDetails as OrderDetails . Then I make some more tble : tblObjiect – a specify service is effected for a specify objiect . It is same relationship as Customer in northwind.
I also have tblProjiect because I want to group same OrderID are for one Project.
My problem orcur when I open my Oder Form ( you can see it 90% similar as Order From in NorthWind sample ). I can entry record like Northwind do : chose one Service from dropdown combo box but Northwind have a product name before they chose ( they can see by customer send to him – actually )I don’t have it . I have to filter first Objiect then Providers-then time then Services .I chose using QBF to do with . Example : When I chose Objiect is BMW car from a combo box then next combo box have Provider : A , B and C ( mean only A,B,C can be availble for service on BMW) then I chose A and click a buton to display a Form included all services from A provider working for BMW.
At here , I don’t know how to transfer these record to Order form .
I think I can find a solution to check a check box for selected record , then when i click a button then selected records will appear on my Order form so I can edit them. This is my dream .
I greatly appreciate your kindness to help me pass this .
AT71

Hi AT71,

Okay, being that said, lets take a look at what you want. Bear with me, because I will now need to ask lots of questions to be able to give you the answer you want.

1) The form that you open to display the services that are available from a selected provider, how do those records appear for you to choose. Are they in a list box, a datasheet view, a continuous form view, or a single form view.
2) Are these services indexed and on which field, unless are you using a unique primary ID for each service.
3) You want the ability to select multiple services at one time before closing the form and to have those selected records added to the tblOrderDetails records, is this correct?

I will need to know which field(s) you want to get from the tblServices to add to which field(s) in the tblOrderDetails. I will need to know the data type for each selected field(s) in both the tblServices and the tblOrderDetails.

By the way, you do know that everything you want to accomplish here can be done with subforms using correct table relationships and not have to have multiple forms and code written...you are aware of that, aren't you. And by doing it this way, you are going against table normalization and most likely will have a database that will require alot of maintenance and be filled with bugs and errors. Like I said before, I am trying to give you a heads-up of what you are getting yourself into. So think this through carefully, and if you still want to go down this path, I will give you the code to start with.

lwells

Now i chose continuous form view , I think list box or datasheet view are also OK



The selected Services come from Services tbl where indexed by ServicesID using a unique primary ID ( Autonumber)


That is my wish . Beside , after I close this form and do another supplier select to open this form again , then I can add more records to the tblOerderDetails.



From tblServices : ServicesID-Number ,ServicesDetails – String , UnitPrice-Number
To tblOrderDetails : ServicesID-Number, ServicesDetails-String , UnitPrice-Number.
Now I have ServicesID is Auto Number , I can change it to String or Number if required.



I tried to control a mainform by data on subform . But it seem not be easy for me to entry a lot of records .

Thank you very much , Iwells

Okay, tell you what...send me your database in a zip file to my personal email address. I will add all the code and queries to make it work like you want...I will also make a copy of the database and show you the other method...then you can decide for yourself which one works best for you.

Send it to lwells1433@aol.com

lwells

I sent email to you by my mail : netanhtuan@yahoo.com
AT

For the benefit of all that will read this thread.

AT71 had a continueous form that listed all the services filtered by a selected supplier. To multi select these services and add them to the order details subform on the order form, two queries were created and a few lines of code were added.

1) In the table that contained the service details, a new field was added and had the properties set to a Yes/No check box.
2) An append query was built using the service details table to append records to the sub order details table, matching the related fields and ID. Criteria was set to show only those records where the Yes/No check box had been selected.
3) An update query was built to change the Yes/No check box back to a No after adding the new records to the sub order details table
4) The checkbox control was added to the continueous form that displayed the service details and placed in front of all the other controls. The user could select and deselect as many services as needed by simply checking or unchecking the check boxes.
5) In the close event for the services form, code was added to run the append query to add the records that were selected and then run the update query to uncheck the Yes/No checkbox. Then requery the sub order details subform on the order form to display the new records that were added.

In short, two queries, one to append and one to update, and some simple code to run the queries when the form is closed to add the selected records to the subOrder form.

The procedure is easy to build and maintain, rather than writing all of the above procedures directly into code and makes it much easier to understand how the process works for those new to designing databases. The entire procedure however, can be converted directly into code at a later time to remove the queries from the database window if necessary.

lwells

Thank Iwells ,
All run smothly .
AT71

some code here would be nice

thanks in advance