How to have selected checkbox rows moved to another table (cutn paste)

[moto485]

How to have selected checkbox rows moved to another form (cutn paste so to speak)

Hi I have a question on how to go about something. I will quickly explain..

Looking at the image I attached, when first opened a Purchase Order # will be made then the supplier will be selected that will then populate the related ITEMS (items are previously added via a poForm along with QTY, invNo).

I want to be able to move the items to one of the selected forms and back if needed.

All these items, orders, etc are on the same table (orders) this table has a unique ID so I am guessing that could be used some how.

I don't know how to go about this so any ideas to point me in the right direction would be great, thanks

 

[GinaWhipp]

I'm not sure your tables are properly normalized... There should be no need to move them between tables. You should also not be storing them all in the same table. Do you have more than just the two tables?

 

[moto485]

Thanks for the reply. I actually only have the two Suppliers and the other one we will just call it Orders. Other than being easier to read the many columns in the Orders table I don't see a real need to split the Orders table up. The only relationship is the Supplier (one to many) I can't see any other relationships to be made as there are no customer details...

When I said move them I what I meant to say was I would like to move the "Item" from that subform to one of the other subforms. The subforms would all link to the same table "Orders" just that each subform would show different columns.

So it would just be the different subforms and they would each have different columns depending on their purpose of the same table. So my idea was to some how change a SQL select statement. If I selected a Item and clicked a button to move it across that would then add that items ID to the subforms select statement.

That's my idea anyway but I don't know enough VB just yet to no how to go about it exactly. I am happy to do some reading just need to know what to read and any other ideas on how to go about it.

Thanks

 

[GinaWhipp]

You would need some sort of *trigger* so that each subform knows what data to display. Is there a field like that?

 

[moto485]

Hi sorry I'm back on this now got a bit distracted with other work..
A trigger column I was thinking of would be the orderID as it is unique to each record......

 

[The John Rambo]

I think what Gina is trying to say is do you have a field in your table that can differentiate what is displayed in the three different sub forms. E.g. A field called "Allocation" which you include into each of your subforms as a combo box. You will then be able to select "Order Form", "Back Order Form" or "Items NA" in that combo box.

Change the WHERE statement of the Recordsources of forms to:
SubForm1 - WHERE (((TableName.Allocation)="Order Form"))
SubForm2 - WHERE (((TableName.Allocation)="Back Order Form"))
SubForm2 - WHERE (((TableName.Allocation)="Items NA"))
So Subform 1 will only show items that have "Order Form" in the "Allocation" field etc etc

Create an "Update" button on your Main form that requeries the three subforms and it will 'move' from the one form to the next.

Simple code for the Update Button

Private Sub Update_Click()       
    Me.Sub1.Requery
    Me.Sub2.Requery
    Me.Sub3.Requery
End Sub

Hope this helps

 

[moto485]

Thanks for that it did the trick seems so simple just came at it the wrong angle, thanks!