Deleting from main table instead of sub table

[sherriross81]

Hello,

I have 2 tables:
Monitor - This list all monitors in inventory and all details like screen
size, etc.
MonitorAttach - This lists all monitor and computer relationships that have
been created.

I have a main form that lists all monitors that are attached the the
selected computer. There is a button that allows the user to detach a
monitor. It opens up a screen that is driven by a query that joins the two
tables together so that it shows the monitor and all the monitors details
since the monitor details are not stored in the MonitorAttach table just the
unique identifier.

The user can select one of the monitors and delete. Instead of it deleting
the relationship from MonitorAttach it deletes it from that table and the
Monitor table making is so the monitor no longer exists in the database. Is
this because I am deleting from a query that ties both together so I can view
all details? Is there any way around this?

 

[Dale Fye]

Sherry,

You need to delete the relationship. To do that, the code behind the button
should execute code similar to:

Private Sub cmd_Detach_Click

Dim strSQL as string

strSQL = "DELETE * FROM MonitorAttach " _
& "WHERE Monitor_ID = " & me.txt_Monitor_ID
currentdb.execute strsql, dbFailOnError

me.requery

End Sub

Did you use the wizard when you added the command button to the form? If
so, you probably told it to use the Monitor table, rather then MonitorAttach
when deleting the record.

The me.requery line just requeries the form that the button is on, so that
it no longer shows the linkage between the monitor and the computer.

HTH

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[sherriross81]

Thanks that worked perfectly. I didn't know I could write sql like code with
this program. That will be very helpful for future projects.

 

[Dale Fye]

Sherry,

Another way to do that would be to write a parameter query and save it.
Then, when you want to run that query, you would have to pass it a parameter
and execute the query. This is a lot quicker for a simple deletion, but for
more complex queries, it is often easier to build the query in design view,
add a parameter declaration to it, and pass it the parameters. For more
information on this, google on: Access +parameter +query +execute

HTH
Dale