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.
tblStandardNotes is a local table.
tblStandardNotes
NoteID Auto
PreClickThisNote Y/N
Include Y/N
StandardNote Text
qryStandardNotes
SELECT Include, StandardNote FROM tblStandardNotes
frmSubStandardNotes
RecordSource: qryStandardNotes
frmSubLineItems
RecordSource: SELECT Remarks FROM tblLineItemsLocal 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 optStandardNotes_Click()
Dim strSQL As String
Dim lngHold As Long
optLineItems.Value = 0
optStandardNotes.Value = -1
'First, set the prechecked items
strSQL = "UPDATE tblStandardNotes SET tblStandardNotes.Include =
[tblStandardNotes].[PreClickThisNote];"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'Wait a moment
For lngHold = 1 To 1000
DoEvents
Next lngHold
'Now, swap subforms
SubformMain.SourceObject = "frmSubStandardNotes"
End Sub
Private Sub optLineItems_Click()
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.Value = -1
optStandardNotes.Value = 0
strTitle = "Action Confirmation"
strMessage = "Click OK to append the selected notes."
Response = MsgBox(strMessage, vbOKCancel, strTitle)
If Response = vbOK Then
'Add the selected notes
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblStandardNotes WHERE [Include] = -1"
Set NoteRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "SELECT * FROM tblLineItemsLocal"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If NoteRS.RecordCount > 0 Then
NoteRS.MoveLast
lngCount = NoteRS.RecordCount
NoteRS.MoveFirst
For lngI = 1 To lngCount
MyRS.AddNew
MyRS("Remarks") = NoteRS("StandardNote")
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.SourceObject = "frmSubLineItems"
End Sub
'End Code behind form-------------------
I realize that it would have easier to use an Append Query in
optLineItems_Click() 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
