Pop up property and record Deletion 1

[khwaja]

I have a strange situation and hope someone can shed some light on this.

I have a form, which is based on a main table, and a sub form based on query with a 1toM relationship with the main table. This form works well except for the fact that when the form has its pop up property set to 'yes', I am unable to delete a full record (including its relationship). But when I turn this property off, I can delete a record. I am using a command button (I created through the Wizard) for deletion of record. This command button has the following code attached.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Looks like it is to do with this generic code. Could we amend the code to enable me to delete the record whether or not it is a pop up form?

Cheers

 

[Quehay]

Is the button on the parent form? You do want to have the child form (we discussed the "independent child form" previously) not only pop-up, but modal as well--this prevents someone from resuming focus on the parent form and changing the parent record, leaving the child form adrift.

The irritating thing I've found is that if you have a toolbar with record buttons (and it appears that it's the same with parent form command buttons as well) they are ignored when a modal form is open. So any record controls for the child form must be on that child form; but this won't allow you to delete Parent&Child with a cascading delete.

Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[khwaja]

Jeff

I have the delete command button in the header of the form which has a subform showing address details for the relevant record. It is not an independant child form. When the pop up option is on, it does not delete the record (nothing happens when I click the command button) but when it is non pop up, I can use the command button to delete the full record.

Does this clarification change anything?
Regards

 

[Quehay]

You got me...I don't know why Access would do that (but since when does Access have to make sense).

You could always do something like this behind the button (I'm assuming that you have cascade deletes on in the relationship):

strID = Cstr(me.ID)

strSQL = "DELETE FROM YourTable WHERE ID=" & strID

DoCmd.RunSQL strSQL Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[khwaja]

Thanks a lot. I a not very confident using code. I moduified your code as under but it results in 'variable' not found. Please note that LocNo is the PK in tablw Main1.

I am sure I am making some silly mistake. CCould you kindly amend the code please?


Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

strLocNo = CStr(Me.LocNo)

strSQL = "DELETE FROM Main1 WHERE LocNo=" & strLocNo

DoCmd.RunSQL strSQL

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

 

[Quehay]

I misinformed you: You need to have "Delete * from TableName" (I've been working in Oracle lately and the * isn't needed in that context).

This code just worked at getting that Northwind tramp Nancy Davolio out of the Emps table:

[tt]
Dim strID As String

Dim strSQL As String
strID = Me.EmployeeID

strSQL = "Delete * from employees where employeeid=" & strID

DoCmd.RunSQL strSQL

[/tt] Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[khwaja]

Thanks. It is still coming back with a compile error 'Variable not found'. LocNo PK is a number if it has any bearing on the code. My code looks like this.

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim strLocNo As String

Dim strSQL As String
strID = Me.LocNo

strSQL = "Delete * from Main1 where LocNo=" & strID

DoCmd.RunSQL strSQL

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

 

[Quehay]

You've attempted to use a variable that you haven't declared. (Is 'Option Explicit' in effect?--this would appear at the top of a module and indicates that Access/VBA will require variables to be declared)

[tt]
Dim strLocNo As String

Dim strSQL As String
strID = Me.LocNo

[/tt]

If you replace strID with strLocNo you should be in businesss. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[khwaja]

Much apprciate your passions and help.

Regards