Hello All,
I was wondering if someone could give me some advice to help me get started. I have a database that I am using to keep track of my printers. I have it set up as a one to many relationship(there is an intermediate table). I have a form set up that is using VBA code to connect to my database with a local connection. There is a delete button, update button & add button. I am working on one button at a time. The button I am working on now is the delete button. When I hit the delete button I want it to move the information displayed on the form into a deleted table then purge the information from the database. We need to do this so we can keep track of the printers we delete. The problem as I see it is deleting the related information from the intermediate table. Each printer can be configured on multiple systems. So if the record that shows up on the form is JD234TE(Printer ID) located on H010(System ID), and I hit delete, I only want it deleting JD234TE on H010. Not on H001, H008 and so on. This is probably gonna be tough but I have referential integrity set up to cascade delete. I have been studying SQL code for about a week and from what I see SQL only deletes data where you set the conditions. I just need someone to give me a little advice so I can get started. I am trying to learn how to do this myself, but I really could use some assistance with this. I am not sure if I should use SQL or VBA, but I have been studying both. I appreciate any help anyone is willing to offer. Thanks and have a good day.

Teqlump

Hi Teqlump,

You're still stuck on this one then. I don't really understand exactly what your problem is. You say you have both the PrinterID and the SystemID, so why is it a problem to delete only the record(s) matching those IDs?

Last week we went through the relevant SQL statements you need (INSERT INTO and DELETE) so I'm assuming you understand them now?

Why don't you give us the structure (Field Names and Data Types) of the relevant Printer, System and PrinterSystemHistory tables - plus any other tables that are relevant (you mention an intermediate table - what's this?) and we can take it from there.

You're using Access aren't you?

rgds
Phil

Yes, it is Microsoft Access. What makes it so hard is the intermediate table. If I have all of my printer information in one table and all of my system information in another table and they are connected with an intermediate table, then how can I delete JD234A on System H010 and that System only. First off, I am having trouble loading the information from two tables onto one form. My code is as follows:
Public Sub SetRecordset()

    Dim sql As String

    'On Error GoTo DbError

    sql = "select * from tblPrintersLumpkin"(I want to put code here to select information from tblPrintersLumpkin and tblIntermediateTablelumpkin and load it into controls I have set up on my form)

    rsPrinters.CursorType = adOpenKeyset
    rsPrinters.LockType = adLockPessimistic

    rsPrinters.Open sql, localConnection, , , adCmdText

    If rsPrinters.EOF = False Then
        Me.txtPrinterIDLumpkin = rsPrinters!PrinterID
        Me.txtBuildingNumberLumpkin = rsPrinters!BuildingNumber
        Me.txtModelLumpkin = rsPrinters!Model
        Me.txtTypeLumpkin = rsPrinters!Type
        Me.txtIPAddressLumpkin = rsPrinters!IPAddress
        Me.txtDateCreatedLumpkin = rsPrinters!DateCreated
        Me.txtSystemIDLumpkin = rsPrinters!SystemID
    End If

    Exit Sub

'DbError:

    'MsgBox "There was an error retrieving information from the database." _
        '& Err.Number & ", " & Err.Description

End Sub

Hi Teqlump,

So your sql statement for extracting rows from those two tables would look like... Hope that is what you are looking for, though not sure about your table structues.

Cheers!

_________________________
- Vijay G
Strive for Perfection

What you describe is a many-to-many relationship, not a one-to-many - that's why your post didn't quite make sense to me.

So you have tables like this?
1. Printer - having PrinterID (char) + some other fields
2. System - having SystemID (char) + some other fields
3. SystemPrinter - the intermediate table having PrinterID (char), SystemID (char)

If so you use a query like this to join the Printer and System tables via the intermediate table
SELECT <<Fields>>
FROM Printer
INNER JOIN SystemPrinter ON Printer.PrinterID = SystemPrinter.PrinterID
INNER JOIN System ON SystemPrinter.SystemID = System.SystemID
WHERE <<Conditions>>

To delete JD234A on System H010 and that System only, you only need delete the record that links those two in the intermediate table:
DELETE SystemPrinter
WHERE PrinterID = 'JD234A' AND SystemID = 'H010'

Maybe you also need to delete the Printer and/or System if there are no records in the intermediate table for that Printer and/or System? Access has its own peculiar brand of SQL, which is not really my forte, but perhaps it will let you do something like this, which will delete the printer if its no longer used by any systems:
IF NOT EXISTS (SELECT PrinterID FROM SystemPrinter WHERE PrinterID = 'JD234A')
    DELETE FROM Printer WHERE PrinterID = 'JD234A'

Does that help get you started?