Re: dublicate report



From your code.only 3 columns (itmx, itmx.subItems(5) and itmx.SubItems(6) are being populated with CustomerID, ItemNo and COReportNum. Therefore any cloning you do based on info loaded into listview(lsvCOReports) can only duplicate data in Itmx, subItems5 and SubItem6 (that is. CustomerID, ItemNo and COReportNum). My guess is you would most likely duplicate CustomerID and ItemNo and then create new COReportNum by incrementing the highest COReportNum in database by 1





Sub CloneSelectedRecord ()

Dim Itmx as listItem, CustomerID, ItemNo, COReportNum, NewRecNum

Dim sSQL as string, rs as Recordset

Set itmx lsvCOReports.selectedItem

CustomerID = Itmx

ItemNum = Itmx.subItems(5)

COReportNum = itmx.sub(6)

Set itmx = nothing

'Open Recordset

ssql = "Select Select top 1 CustomerID, ItemNum, COReportNum from " _

& "YourTableName Order By COReportNum Desc"

Set rs.Open = db.OpenRecordset(ssql, dbOpenDynaset)

If rs.reordcount = 0 then

'No record exist in yourtable

'So create new record number based on what ever number you want to start from

NewRecNum = 100 'Start from 100 (for example)

Else

NewRecNum = rs!COReportNum + 1

End if

Rs.addNew

Rs!CustomerID = CustomerID

Rs!ItemNum = ItemNum

Rs!COReportNum = NewRecNum

Rs.Update

Rs.close

End Sub

'Of course you will need to indicate your right table name

'Also the new COReportNum can be made module level variable so you can update 'other tables.in per your record add code

''''Warning.. Air code

'Code assumes COReportNum is your primary key or unique record number and 'increments by '1 each time you add anew record





"Tammie" <trojen4u@xxxxxxxxx> wrote in message news:OuLH9%23UHHHA.3616@xxxxxxxxxxxxxxxxxxxxxxx
sorry for the confusion. the code i had listed only creates new record. i have 6 tables and when i search and show the maching record in a listView and the user might close or open the record. you right i would like to clone any record selected by the user in the list view. Here's my search code and the code that populates the listView. Again thanks for your help.

--------------------------------------

Option Explicit
Public strFilterBy As String
Public intReportNum As Integer



Sub LoadListView()
Dim myRs As DAO.Recordset
Dim itmX As ListItem



On Error GoTo LoadError


lsvCOReports.ListItems.Clear

Set myRs = rsHDR
' While the record is not the last record,
' add a ListItem object.
While Not myRs.EOF



If Not IsNull(myRs!CustomerID) Then
Set itmX = lsvCOReports.ListItems. _
Add(, , CStr(myRs!CustomerID))
Else
Set itmX = lsvCOReports.ListItems. _
Add(, , CStr("lost item"))
End If

If Not IsNull(myRs!ItemNo) Then
itmX.SubItems(5) = myRs!ItemNo
End If
If Not IsNull(myRs!COReportNum) Then
itmX.SubItems(6) = myRs!COReportNum
End If

myRs.MoveNext ' Move to next record.
Wend



Dim strTemp As String
If lsvCOReports.ListItems.Count > 0 Then
lsvCOReports.SetFocus
Set itmX = lsvCOReports.ListItems(1)
lsvCOReports.SelectedItem = itmX
strTemp = lsvCOReports.SelectedItem.SubItems(6)
intReportNum = Val(strTemp)
cmdGo.Enabled = True
Else
intReportNum = 0
cmdGo.Enabled = False
MsgBox "No Record Found"
End If



Exit Sub
LoadError:
MsgBox Err.Description & " " & Err.Number
' MakeErrorLog "LoadListView", "frmLookup", Err.Description, Err.Number
End Sub



____________

Private Sub cmdFindIt_Click()
Dim strFilterString As String



On Error GoTo ErrorX



Me.MousePointer = vbHourglass

If txtFindBy.Text = "" Then
MsgBox "Enter something to search for!"
Me.MousePointer = vbDefault
Exit Sub
End If

MDIMain.StatusBar1.Panels(1).Text = "Searching for reports."

Select Case strFilterBy 'set filter string to the correct data type
Case "COReportNum", "CustomerID"
strFilterString = strFilterBy & " = " & Format$(txtFindBy.Text, "######") & ";"

Case Else
strFilterString = strFilterBy & " = '" & txtFindBy.Text & "';"
End Select

Set rsHDR = dbCODB.OpenRecordset("SELECT * from CO_HDR WHERE " & strFilterString, dbOpenDynaset)

LoadListView

Me.MousePointer = vbDefault
MDIMain.StatusBar1.Panels(1).Text = ""
cmdGo.Default = True

Exit Sub
ErrorX:
MsgBox "Error finding report. " & Err.Description
MakeErrorLog "frmSearch.cmdFindIt", " ", Err.Description, Err.Number
Exit Sub

End Sub



--------------------------------------

Private Sub cmdGo_Click()
Dim i As Integer



On Error GoTo ErrorIt

If lsvCOReports.SelectedItem Is Nothing Then
MsgBox "You must select a report first."
Exit Sub
Else
Me.MousePointer = vbHourglass
MDIMain.StatusBar1.Panels(1).Text = "Opening report " & intReportNum



OpenCORecordsets intReportNum
frmHDR.Show

Me.MousePointer = vbDefault
Unload Me
End If



Exit Sub



ErrorIt:
MsgBox Err.Description & " " & Err.Number
' MakeErrorLog "cmdGo", "frmLookup", Err.Description, Err.Number



End Sub




--------------------------------------

Private Sub Form_Unload(Cancel As Integer)



MDIMain.mnuEditDeleteReport.Enabled = False

End Sub



Private Sub lsvCOReports_Click()
Dim strTemp As String



If lsvCOReports.ListItems.Count > 0 Then
strTemp = lsvCOReports.SelectedItem.SubItems(6)
intReportNum = Val(strTemp)
MDIMain.mnuEditDeleteReport.Enabled = True
End If

End Sub



--------------------------------------

Private Sub lsvCOReports_ColumnClick(ByVal ColumnHDR As ComctlLib.ColumnHDR)
With lsvCOReports '// change to the name of the list view
Static iLast As Integer, iCur As Integer
.Sorted = True
iCur = ColumnHDR.Index - 1
If iCur = iLast Then .SortOrder = IIf(.SortOrder = 1, 0, 1)
.SortKey = iCur
iLast = iCur
End With
End Sub



--------------------------------------

Private Sub lsvCOReports_DblClick()



cmdGo_Click

End Sub



--------------------------------------

Private Sub optFindBy_Click(Index As Integer)



Select Case Index
Case 0
strFilterBy = "COReportNum"
lblSearch.Caption = "Enter Report Number:"
Case 1
strFilterBy = "CustomerID"
lblSearch.Caption = "Enter Customer Number"
Case 2
strFilterBy = "ItemNo"
lblSearch.Caption = "Enter Item Number"

End Select

txtFindBy.SetFocus

End Sub



Private Sub txtFindBy_KeyPress(KeyAscii As Integer)
If strFilterBy = "COReportNum" Then
If KeyAscii > 57 Or KeyAscii < 48 And KeyAscii <> 8 Then
MsgBox "You must enter Record Number!"
KeyAscii = 0
Exit Sub
End If
End If

cmdFindIt.Default = True

End Sub






"JP Bless" <jp3BlessNoSpam@xxxxxxxxxxx> wrote in message news:%2338s9ezGHHA.4112@xxxxxxxxxxxxxxxxxxxxxxx
Can only say this seems confusing. Either there is a bit of design problem else I am finding it hard to figure the need for six recordsets. If I understand you, when you search you show the matching record(s) in a listView and then close the (search recordset)...right? Now you want to clone any record selected by the user in the listview.

Can you show the search code and the code that populates the listview... and indicate the fields you want to clone (listview columns containing the data) . That might help. Other wise hard to figure from the info you provided.


"Tammie" <trojen4u@xxxxxxxxx> wrote in message news:e0pvEIvGHHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
Hi, Thanks for your help. Here's how i create new record, as you can see i have 6 tables and on new record from VB i increment the report number.



but i want to dublicate the record on my search form that displays the record in ListView.



thanks



Private Sub mnuFileNewReport_Click()
Dim intNewRecNum As Integer
Dim i As Integer



On Error GoTo ErrorX



Me.MousePointer = vbHourglass
MDIMain.StatusBar1.Panels(1).Text = "Creating new record"



Set rsHDR = dbCODB.OpenRecordset("CO_HDR", dbOpenDynaset)
Set rsOrder = dbCODB.OpenRecordset("CO_OrderMeasurments", dbOpenDynaset)
Set rsShip = dbCODB.OpenRecordset("CO_ShipMeasurements", dbOpenDynaset)
Set rsCusInfo = dbCODB.OpenRecordset("CO_CusInfo", dbOpenDynaset)
Set rsOtherCom = dbCODB.OpenRecordset("CO_OtherCom", dbOpenDynaset)
Set rsCusData = dbCODB.OpenRecordset("CO_CusData", dbOpenDynaset)

'create new record in each table
With rsHDR
.AddNew
'If this is the MSAccess built main DB then COReportNum is an Autonumber field and
'will be set automaticaly. If it is a new VB created database it will not autonumber.
If IsNull(.Fields("COReportNum")) Then
intNewRecNum = .RecordCount + 1
.Fields("COReportNum") = intNewRecNum
Else
intNewRecNum = .Fields("COReportNum")
End If
.Fields("Comments") = " "
.Update
End With

rsOrder.AddNew
rsOrder.Fields("COReportNum") = intNewRecNum
rsOrder.Update
rsShip.AddNew
rsShip.Fields("COReportNum") = intNewRecNum
rsShip.Update
rsCusInfo.AddNew
rsCusInfo.Fields("COReportNum") = intNewRecNum
rsCusInfo.Update
rsOtherCom.AddNew
rsOtherCom.Fields("COReportNum") = intNewRecNum
rsOtherCom.Update

rsCusData.AddNew
rsCusData.Fields("COReportNum") = intNewRecNum
rsCusData.Update

MDIMain.StatusBar1.Panels(1).Text = "Creating new CO Report"

OpenCORecordsets intNewRecNum

Me.MousePointer = vbDefault

frmHDR.Show



Exit Sub
ErrorX:
MsgBox "Error creating new report, " & Err.Description
MakeErrorLog "MDIMain.mnuFileNewReport", " ", Err.Description, Err.Number
Exit Sub

End Sub



"JP Bless" <jp3BlessNoSpam@xxxxxxxxxxx> wrote in message news:e6XFmxuGHHA.4904@xxxxxxxxxxxxxxxxxxxxxxx
> This is two part process...
> 1. Which record to clone and insert
> 2. Assign record number (in increment of last record number)
>
> Question is how do you assign record number (Auto Number or increment of top
> record number 1?)
>
> If AutoNumber... a simple insert query should do...
> Dim itmX as listItem, sSQL as string
> Set itmx = YourListView.SelectedItem
>
> ssql = "INSERT INTO YourTable( Field1, Field2, Field3 ) " _
> & "SELECT tbl_YourTable.Field1, YourTable.Field2, YourTable.Field3 FROM
> YourTable " _
> & "WHERE YourTable.RecordNumber =" & itmx.ColumnThatHoldsRecordNumber
>
> Call YourDbConnection.Execute(sSQL)
> set itmx = nothing
>
>
>
>
>
>
> "Tammie" <trojen4u@xxxxxxxxx> wrote in message
> news:eYrAdQmGHHA.1080@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi.
>>
>> I have a VB 6.0 small application. First the user can create new record
>> or open existing record. I have a search form that asks the user for the
>> report number or title of the record. then the record is listed on
>> ListView which then can be selected to open report or record with command
>> button.
>>
>> I would like to add one command button that can be able to duplicate the
>> record and yet assign new record number.
>>
>> can anyone help me accomplish this? please?
>>
>> I am using access 2000 with dao recordset.
>>
>> thanks
>>
>>
>>
>
>
Loading