Can a record be turned into an object? 1

[DaveMac]

Hello there everyone who has the pleasure of knowing how to make Access do all the neat things you can think of. I as you can tell by my cynicism have about had it.

The idea:

Turn records into an object that can be dragged and dropped into a container. Another post I have seen recently with very little response called it a shopping cart. I want it to do the same basic thing just not for shopping. How about a list of records that shows order qty and order # that can be dragged into a box then dropped and upon being dropped shows a cumulative totals for that shipment. I would think that is handy only problem is a can’t write code and I even have problems with inventory ( but that’s another post if you would like to help). Any thoughts would help. I can provide more information or even a sample structure minus the dragging thing and the part that makes any kind of math work. Sorry just kinda frustrated.
[sig][/sig]

 

[DougP]

I created a list box that has items to be selected. and made the multi-sleection so several items could be selected.
And since the listbox had 100's of items. Each item clicked stayed highlighted so the user could go back and double check them.

Then in the list box on_click event I got the item and created a new record in a subform and added the list.column(x) to the appropiate field in the subform.
So as the user clicked an item in the listbox it showed in the subform below. And then I created a report that looked at the table where the subform was attached.
When the user clicked print it printed the records out then put a check in a Printed field so it was ready for next time. the subform showed no records. In other words all of the items ever choosen where there with the date and did not show on the subform becuse it had a check in a field I was monitoring.

So you can't actually drag records but you can click on it and have it go to another control.

here is the code for the list box
----------------------------------
Private Sub List0_Click()
Dim ctlList As Control, varItem As Variant
Dim Counter1, retval As Variant
GoTo skip
retval = InputBox("Please enter Qty", "Qty to do", Me![List0].Column(5))
If retval = "" Then
' remove selection from list cause it was canceled
Me!List0.Selected(List0.ListIndex + 1) = False
Exit Sub
End If

skip:
Set ctlList = Me!List0
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Counter1 = Counter1 + 1
Next varItem

Text2 = Counter1

Me![Just Print These subform].SetFocus
DoCmd.GoToRecord , , acNewRec

Me![Just Print These subform].Form![STOCK_CODE] = Me![List0].Column(0)
Me![Just Print These subform].Form![Description] = Me![List0].Column(1)
Me![Just Print These subform].Form![ON HAND QTY] = Me![List0].Column(2)
Me![Just Print These subform].Form![SAFETY STOCK LVL] = Me![List0].Column(3)
Me![Just Print These subform].Form![ON ORD QTY] = Me![List0].Column(4)
'Me![Just Print These subform].Form![Qty to Make] = retval
Me![Just Print These subform].Form![Qty to Make] = Me![List0].Column(5)
Me![Just Print These subform].Form![DateAdded] = Format(Now, "mm/dd/yy&quot
Me![Just Print These subform].Form![LastCost] = Me![List0].Column(6)

End Sub
---------------------------
Code for print button
---------------------------
Private Sub cmd_Print_Manufactured_Click()
On Error GoTo Err_cmd_Print_Manufactured_Click

Dim stDocName As String

stDocName = "Safety Stock Level Manufactured"
DoCmd.OpenReport stDocName, acNormal

'Archive items to History Table
ArchiveItems

' Remove Highlited items from top list
Dim ctlList As Control, varItem As Variant
Set ctlList = Me!List0
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
ctlList.Selected(varItem) = False
Next varItem

Exit_cmd_Print_Manufactured_Click:
Exit Sub

Err_cmd_Print_Manufactured_Click:
MsgBox Err.Description
Resume Exit_cmd_Print_Manufactured_Click

End Sub

-------------------------
Archive code
--------------------------
Public Sub ArchiveItems()
On Error GoTo Err_ArchiveItems
' Archive to History folder
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String
'delete items from table
DoCmd.DeleteObject acQuery, "UpdateTitles"

' Return reference to current database.
Set dbs = CurrentDb
strSQL = "INSERT INTO [History Just Print These] (STOCK_CODE, DESCRIPTION, QTY_ON_HAND, SAFETY_STOCK_QTY, QTY_ON_ORDER, [Qty to Make], DateAdded) SELECT [Just Print These].STOCK_CODE, [Just Print These].DESCRIPTION, [Just Print These].QTY_ON_HAND, [Just Print These].SAFETY_STOCK_QTY, [Just Print These].QTY_ON_ORDER, [Just Print These].[Qty to Make], [Just Print These].[DateAdded] FROM [Just Print These];"
' Create new QueryDef.
Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)
' Execute QueryDef.
qdf.Execute
qdf.Close
dbs.Close

'Delete Items in list
DoEvents
Set dbs = CurrentDb
Dim rst As Recordset
Set rst = dbs.OpenRecordset("Just Print These&quot
rst.MoveLast
For a = rst.RecordCount To 1 Step -1
rst.Delete
rst.MovePrevious
Next
rst.Close
dbs.Close

Forms![frm-Safety Stock level]![Just Print These subform].Requery

Exit_ArchiveItems:
Exit Sub

Err_ArchiveItems:
Select Case Err.Number
Case 3011
' No update query to delete
Resume Next
Case Else
MsgBox "Error # " & Err.Number & " " & Err.Description, vbInformation, "In sub ArchiveItems"
Resume Exit_ArchiveItems
End Select

End Sub

This works very well and is used alot at our company....




[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[WP]

David,

This kind of coding can't be done with the standard Access controls. However, there may be third party controls that feature drag and drop functionality.







[sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[MichaelRed]

Doug P.

[red]WOW!!!!!!!!!!!!!!!!![/red] [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[Brambojr]

I just read this post and it looks like it is the answer to my dilemma as well. Unfortunately, I am still learning a bit about Access. I have the list box. I have the subform, and I have modified the code as best I can figure. Can someone help me?? This will serve as a means of selecting which records will be printed (an HR type thing) Also I am confused by the first section of code, as you may likely see.

The code I have is:

Private Sub List0_Click()
Dim ctlList As Control, varItem As Variant
Dim Counter1, retval As Variant
GoTo skip
retval = InputBox(&quot;Please enter Qty&quot;, &quot;Qty to do&quot;, Me![List0].Column(5))
If retval = &quot;&quot; Then
' remove selection from list cause it was canceled
Me!List0.Selected(List0.ListIndex + 1) = False
Exit Sub
End If

skip:
Set ctlList = Me!List0
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Counter1 = Counter1 + 1
Next varItem
Text2 = Counter1

Me![PrintLicListSubform].SetFocus
DoCmd.GoToRecord , , acNewRec
Me![PrintLicListSubform].Form![Full Name] = Me![List0].Column(0)
Me![PrintLicListSubform].Form![Dept] = Me![List0].Column(1)
Me![PrintLicListSubform].Form![Stpk] = Me![List0].Column(2)
Me![PrintLicListSubform].Form![Turt] = Me![List0].Column(3)
Me![PrintLicListSubform].Form![Ridr] = Me![List0].Column(4)
Me![PrintLicListSubform].Form![Rech] = Me![List0].Column(5)
Me![PrintLicListSubform].Form![SRec] = Me![List0].Column(6)
Me![PrintLicListSubform].Form![Prop] = Me![List0].Column(7)
Me![PrintLicListSubform].Form![Zamb] = Me![List0].Column(8)
Me![PrintLicListSubform].Form![First Licensed] = Me![List0].Column(10)
Me![PrintLicListSubform].Form![Last Certified] = Me![List0].Column(11)
End Sub


Can anyone help??

 

[raskew]

Whoa--that's some heavy-duty coding!!

Could you wait about a week for a response while I try to decipher it?

 

[Brambojr]

Anyone willing to help is the boss on this. I can't do it, don't have time to learn it all, so any help I get in whatever time frame is just fine with me. thanks for responding.

 

[DaveMac]

I found a sample DB on the Access web ring and it works pretty good. Unfortunately I can't really tell what the code is doing but the sample works great. What you have is container 1 and container 2. When you dbl click or drag a record it moves from container 1 to 2. It then updates the tbl with whether or not the record is selected in the container 2. Pretty good eh? Too bad I don’t understand what it is doing. If you want the whole thing send me an e-mail and I will send you the Zip.

Option Compare Database
Option Explicit


‘does this say when I press the button determine where the mouse is and pickup whatever is there
‘and place it in the shift ?

Private Sub List1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
DragStart Me
End Sub

Private Sub List1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
DropDetect Me, Me![List1], Button, Shift, X, Y
End Sub

Private Sub List1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
DragStop
End Sub

Private Sub List2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
DragStart Me
End Sub

Private Sub List2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
DropDetect Me, Me![List2], Button, Shift, X, Y
End Sub

Private Sub List2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
DragStop
End Sub

 

[Brambojr]

Dave,

Yes I would LOVE to have that sample Db with the two containers. It soulnds like what I want. Could you email it to me?? Hate to post email address here. . .Brambojr@mindspring.com.

Thanks