Roy wrote:
Hello,
Sorry for a lengthy post.
I develop a access 2000 application.As a part of the daily download,I
import data from a excel sheet into a access table.
The data is as follows:
Task Comments
11801 08/02/2004 07:15:43 CET: Submitted
08/02/2004 06:35:39 CET: Please provide Post Mortem
11804 08/18/2004 09:33:46 CET: Sent mail to Carey Walker chasing
...
I want to construct a table duplicating the task number for the
different comments associated with a task number.How to do this in a
query or a module,because this will be a part of a daily routine?
Currently,I am doing this copy and paste manually since the volume is
low but which is expected to go five fold soon.
Thanks,
Roy
I couldn't understand your question so I'll just write something :-).
I have a main form with a subform containing line items in datasheet
view. The subform contains a combobox with some common things that
people can choose. In addition to the selections from the combobox
there is a set of standard notes that can be selected. The standard
notes have to be marked with a standard note identifier (not shown) and
must be selected from a list of literal industry standard descriptions.
tblStandardNote s is a local table.
tblStandardNote s
NoteID Auto
PreClickThisNot e Y/N
Include Y/N
StandardNote Text
qryStandardNote s
SELECT Include, StandardNote FROM tblStandardNote s
frmSubStandardN otes
RecordSource: qryStandardNote s
frmSubLineItems
RecordSource: SELECT Remarks FROM tblLineItemsLoc al ORDER BY
LineItemID;
When the user clicks the option button for general notes, the standard
remarks are shown (in datasheet view also) with some Include boxes
checked. The user can check or uncheck the Include box in the subform.
When they are done selecting standard notes they click the option
button for line items. They can then choose to append all the standard
notes they selected as line items.
'Begin Code behind form-------------------
Private Sub optStandardNote s_Click()
Dim strSQL As String
Dim lngHold As Long
optLineItems.Va lue = 0
optStandardNote s.Value = -1
'First, set the prechecked items
strSQL = "UPDATE tblStandardNote s SET tblStandardNote s.Include =
[tblStandardNote s].[PreClickThisNot e];"
DoCmd.SetWarnin gs False
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True
'Wait a moment
For lngHold = 1 To 1000
DoEvents
Next lngHold
'Now, swap subforms
SubformMain.Sou rceObject = "frmSubStandard Notes"
End Sub
Private Sub optLineItems_Cl ick()
Dim Response As Variant
Dim strTitle As String
Dim strMessage As String
Dim MyDB As Database
Dim NoteRS As Recordset
Dim strSQL As String
Dim lngI As Long
Dim lngCount As Long
optLineItems.Va lue = -1
optStandardNote s.Value = 0
strTitle = "Action Confirmation"
strMessage = "Click OK to append the selected notes."
Response = MsgBox(strMessa ge, vbOKCancel, strTitle)
If Response = vbOK Then
'Add the selected notes
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblStandardNote s WHERE [Include] = -1"
Set NoteRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM tblLineItemsLoc al"
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
If NoteRS.RecordCo unt > 0 Then
NoteRS.MoveLast
lngCount = NoteRS.RecordCo unt
NoteRS.MoveFirs t
For lngI = 1 To lngCount
MyRS.AddNew
MyRS("Remarks") = NoteRS("Standar dNote")
MyRS.Update
If lngI <> lngCount Then NoteRS.MoveNext
Next lngI
End If
NoteRS.Close
Set NoteRS = Nothing
Set MyDB = Nothing
End If
'Now, swap subforms
SubformMain.Sou rceObject = "frmSubLineItem s"
End Sub
'End Code behind form-------------------
I realize that it would have easier to use an Append Query in
optLineItems_Cl ick() but this code is old, it wasn't intended to be
seen by anyone else and I haven't gotten around to changing it yet.
Perhaps you are trying to create a table of distinct tasknumber,
comment pairs that grow daily yet without adding duplicates. Maybe
these ramblings will help you clarify what you are trying to do.
James A. Fortune
