Advanced functionality with Access

[Guest]

Hello,

I know I am posing a lot of questions here at the moment, but I can see so
many possibilities in how access can be useful in my job, that I can hardly
constrain myself ;-) (What a geek, huh?)

This time, I am inquiring more in general terms than looking for a concrete
solution, although a kick in the right direction would be useful, as always.

I am working for a transport company, and what I would like to make is a
form (probably containing two forms that are not linked to each other. On the
left side, there would be a list of all my available trucks, and on the right
side, a list of all the orders that need to be collected. What I would like
to do, is to decide which orders go on what trucks by dragging the orders
onto the trucks.

Is this possible in access, and where should I look for articles describing
this functionality?

thx a lot

Baard

 

[Stefan Hoffmann]

hi Baard,

I am working for a transport company, and what I would like to make is a
form (probably containing two forms that are not linked to each other. On the
left side, there would be a list of all my available trucks, and on the right
side, a list of all the orders that need to be collected. What I would like
to do, is to decide which orders go on what trucks by dragging the orders
onto the trucks.

I solved a similar problem without drag'n'drop. I simply used a button
on the main form to assign to two selected records from to sub forms
with each other.

I placed a page control on my main form with two pages. Page 1 shows the
assigned datasets (sfOrderTruck), page 2 show the two sub forms
(sfOrder, sfTruck). On page 1 there is an "unassign" button and on page
2 there is the "assign" button.

My tables looked like that using your terms:

Table Truck: ID, License, Description
Table Order: ID, ...
Table Order_Truck: ID, Order_ID, Truck_ID

Id is an autonumber primary key field. Order_ID and Truck_ID need an
unique index.

The code behind the buttons:

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO Order_Truck (Order_ID, Truck_ID) " & _
"VALUES (" & sfOrder![ID] & ", " & _
sfOrder![ID] & ")"

sfOrderTruck.Requery
sfOrder.Requery

End Sub

Private Sub cmdUnassign()

CurrentDb.Execute "DELETE FROM Order_Truck " & _
"WHERE ID = " & sfOrderTruck![ID]

sfOrderTruck.Requery
sfOrder.Requery

End Sub

The record source of the sfOrder is

SELECT *
FROM Order
WHERE NOT ID IN (
SELECT Order_ID
FROM Order_Truck
)

 

[Stefan Hoffmann]

hi Baard,

I am working for a transport company, and what I would like to make is a
form (probably containing two forms that are not linked to each other. On the
left side, there would be a list of all my available trucks, and on the right
side, a list of all the orders that need to be collected. What I would like
to do, is to decide which orders go on what trucks by dragging the orders
onto the trucks.

I solved a similar problem without drag'n'drop. I simply used a button
on the main form to assign to two selected records from to sub forms
with each other.

I placed a page control on my main form with two pages. Page 1 shows the
assigned datasets (sfOrderTruck), page 2 show the two sub forms
(sfOrder, sfTruck). On page 1 there is an "unassign" button and on page
2 there is the "assign" button.

My tables looked like that using your terms:

Table Truck: ID, License, Description
Table Order: ID, ...
Table Order_Truck: ID, Order_ID, Truck_ID

Id is an autonumber primary key field. Order_ID and Truck_ID need an
unique index.

The code behind the buttons:

Private Sub cmdAssign_Click()

CurrentDb.Execute "INSERT INTO Order_Truck (Order_ID, Truck_ID) " & _
"VALUES (" & sfOrder![ID] & ", " & _
sfOrder![ID] & ")"

sfOrderTruck.Requery
sfOrder.Requery

End Sub

Private Sub cmdUnassign()

CurrentDb.Execute "DELETE FROM Order_Truck " & _
"WHERE ID = " & sfOrderTruck![ID]

sfOrderTruck.Requery
sfOrder.Requery

End Sub

The record source of the sfOrder is

SELECT *
FROM Order
WHERE NOT ID IN (
SELECT Order_ID
FROM Order_Truck
)

mfG
--> stefan <--