I'm using VB6 as front end and access2k as back end. I'm using two different tables linked in 1 to many format. Th format of teh table is as follows:

main table(detail)

|-------------------+---------+-----------+------------|
| id(primary key) | name | TL | prospec |
|-------------------+---------+-----------+------------|
| 123 | george | peter | sam |
|-------------------|---------|-----------|------------|
| 333 | jose | peter | sam |
|-------------------+---------+-----------+------------|

child table(data)

|-------------------+-----------+-----------+------------|
| id | date | issue | status |
|-------------------+-----------+-----------+------------|
| 123 |31/06/2004 | sale | complete |
|-------------------|-----------|-----------|------------|
| 123 |30/06/2004 | sale | complete |
|-------------------|-----------|-----------|------------|
| 123 |29/06/2004 | sale | complete |
|-------------------|-----------|-----------|------------|
| 333 |31/06/2004 | shipment | incomplete |
|-------------------|-----------|-----------|------------|

Both the tables are related through the id field.

I want to delete the record from the "data" table for the id 123 and from the table "detail" also on the click opf the delete button.

I also want to update the "detail" table for the records in case the detail of teh employee has changed.


Please help ASAP.

:(

Add reference to Microsoft ActiveX Data oBject 2.6

Public Sub DeleteRec()
  Dim Rs As new ADODB.RecordSet
  With RS
    .ActiveConnectIon = Conn '''Hope u have an Open COnnection Conn
    .CursorType = AdOpenDynamic
    .CursorLocation = AdUseServer
    .LockType = AdLockOptimistic
    .Source = "Select * From Data Where Id= " & Val(TxtID) & ""
    .Open
    If .Eof()=False then
       .Delete
       .Update
       .Close
    Endif

    .Source = "Select * From Detail Where Id= " & Val(TxtID) & ""
    .Open
    If .Eof()=False then
       .Delete
       .Update
       .Close
    Endif

  End With
  MsgBox "Deleted from data and details tables"
End Sub


The quickest way to do it is here:-

Public Sub Delrec()
  ''I expect that u have an open connection Conn here
  Conn.Execute ("Delete from data where Id=" & Val(txtId) & "")
  Conn.Execute ("Delete from Details where Id=" & Val(txtId) & "")
  MsgBox "Deleted from data and details tables"
End

B. Anant

thankx Anant

I tried dat and got it to work. I was not declaring the cursor type as dynamic so it was not allowing me to do the same. Thanx alot

dear anant

hi

I'm trying to generate the report using the Crystal Report.
I want the report to be generated based on some query. Below is teh code I'm using. I'm not able to generate the report. When I' Checking the error log it is giving me the error that file cannot be found. I want to generate the report in ".rpt" format.

subcmdreport_click()
    Dim objConn As New ADODB.Connection
    Dim objRS As New ADODB.Recordset
    Dim strlogin, strcrn, strdate, strduration, strdescription, _ strstatus, strspace As String

    'Create Connection
    objConn.Open "dsn=Call_Tracker"

       'Create RecordSet
        objRS.Open "Select * From tracker", objConn, adOpenDynamic,_ adLockOptimistic
        objRS.MoveFirst
        Do Until objRS.EOF
            If (objRS("Login_ID") = txtID.Text And objRS("txtDATE") _ = txtDate.Text) Then
                'strlogin = objRS("Login_ID")
                'strcrn = objRS("CRN")
                'strdate = objRS("txtDATE")
                'strduration = objRS("Duration")
                'strdescription = objRS("Description")
                'strstatus = objRS("Status")
                'strspace = " "
                'strconcat = CStr(intlogin + intcrn + intdate + intduration + intdescription + intstatus)
                'lstReport.AddItem (strspace & strlogin & strspace & strcrn & strspace & strdate & strspace & strduration & strspace & strdescription & strspace & strstatus)
                rpttracker.ReportFileName = App.Path & "\tracker.rpt"
                rpttracker.PrintReport
                'strlogin & txtReport.Text = strcrn & txtReport.Text = strdate & txtReport.Text = strduration & txtReport.Text = strdescription & txtReport.Text = strstatus
                objRS.MoveNext
            Else
                objRS.MoveNext
            End If
        Loop
    objRS.Close
    objConn.Close
    'Load frmReportAgent
    'frmReportAgent.Visible = True

end sub

Use CrystalReport ActiveX component and use its "SqlQuery" propery to fire a query.

CrystalReport1.SqlQuery = "Select ........."

Hope this helps

B. Anant

it is not generating the report file. in the event log of my project it stating the following:

09/30/2004, 12:02:15 PM, 4, 0, :Invalid file handle.
09/30/2004, 12:02:19 PM, 4, 0, :Invalid file handle.

I'm trying to use the following code:

Private Sub cmdReport_Click()
    'rpttracker is the name of the crystal report
    rpttracker.ReportFileName = App.Path & "\tracker.rpt"
    rpttracker.SQLQuery = "Select * from tracker where Login_ID = '" & txtID.Text & "'"
    rpttracker.PrintReport
End Sub

Please help.