DoCmd.RunSQL VBA Coding

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi All,

I need a little help on the coding below. Thank you in advance!

Private Sub cboRecipientID_Dirty(Cancel As Integer)
Dim Response As String
Response = MsgBox("If you change the Recipient than the associated
subsidies will be deleted." & _
"Are you sure you want to change the Recipient?", vbYesNo)
If Not IsNull(cboRecipientID) Then
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL = ("DELETE * FROM tblSubsidy WHERE
tblSubsidy.RecipientID" = "[Forms]![frmSubsidySubFrmPlacement]![RecipientID]")
DoCmd.SetWarnings False
Else
If Response = vbNo Then
Cancel = True
End If
End If
End If
End Sub

A little back ground info -

I have a form,frmplacement, that has its record source from a table called
tblplacement. This table has a combined key and one of the keys is
recipientID another is placementID. The placementID is an autonumber created
when a new record is made in the form frmplacement and table tblplacement. On
this form, frmplacement, the RecipientID is selected from a combobox drop
down.

On frmplacement I also have subform, frmSubsidySubFrmPlacement, who's record
source is a table called "tblsubsidy". This table also has a combined key, of
which one of the keys is recipientID the other is placementID.

The issue:

If a user changes the combobox entry,recipientID , on the placement form and
saves the record a new record is created in the tblsubsidy table. This is due
to the combined key and is expected but not what I want. As such, the
previous record also still exists because of the combined key. However I wish
to have it deleted when the recipientID is changed. The subsidy record must
be associated with the combined placementID and the recipientID.

The VB:

I wish to advise the user when they change the combobox, recipientID, that
the subsidy record associated with that recipientID, will be deleted and when
they click yes the record is deleted.

Hope that makes sense.

Cheers,
Andrew

P.S. For some reason i am getting an error with DoCmd.RunSQL, access is
telling me 'Arguement not optional'.
 
Hi Andrew,

I don't really understand the statement you wrote under the heading "The
Issue", but I'll respond to this statement:
P.S. For some reason i am getting an error with DoCmd.RunSQL, access is
telling me 'Arguement not optional'.
Click to expand...

You need to remove the equals ("=") sign, along with the opening and closing
parenthesis, so that you are just providing a string as the parameter:

Change this:
DoCmd.RunSQL = ("DELETE * FROM tblSubsidy WHERE ...

to this:

DoCmd.RunSQL "DELETE * FROM tblSubsidy WHERE


Change:
Dim Response As String

To:
Dim Response As Long

Reason: The return value of the Msgbox function is a long integer, not a
string. Intrinsic constants, such as vbNo or vbYes, have a corresponding
numeric (long) value.

Here is a suggested re-write that may get you a bit farther along. I used
the .Execute method of CurrentDB, instead of DoCmd.RunSQL, as there are no
warnings that you need to suppress with Set Warnings statements. I also added
error handling. Declaring strSQL as a String allows you to easily include a
Debug.Print statement, if needed, to help troubleshoot, ie:

Debug.Print strSQL



Option Compare Database
Option Explicit

Private Sub cboRecipientID_Dirty(Cancel As Integer)
On Error GoTo ProcError

Dim strSQL As String
Dim Response As Long

strSQL = "DELETE * FROM tblSubsidy " _
& "WHERE RecipientID = " _
& [Forms]![frmSubsidySubFrmPlacement]![RecipientID]

Response = MsgBox( _
"If you change the Recipient, the associated subsidies " _
& "will be deleted." & vbCrLf _
& "Are you sure you want to change the Recipient?", _
vbYesNo, "Please Confirm Action...")

If Not IsNull(cboRecipientID) Then
If Response = vbYes Then
CurrentDb.Execute strSQL, dbFailOnError
Else
Cancel = True
End If
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cboRecipientID_Dirty Procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Andrew said:
Hi All,

I need a little help on the coding below. Thank you in advance!

Private Sub cboRecipientID_Dirty(Cancel As Integer)
Dim Response As String
Response = MsgBox("If you change the Recipient than the associated
subsidies will be deleted." & _
"Are you sure you want to change the Recipient?", vbYesNo)
If Not IsNull(cboRecipientID) Then
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL = ("DELETE * FROM tblSubsidy WHERE
tblSubsidy.RecipientID" = "[Forms]![frmSubsidySubFrmPlacement]![RecipientID]")
DoCmd.SetWarnings False
Else
If Response = vbNo Then
Cancel = True
End If
End If
End If
End Sub

A little back ground info -

I have a form,frmplacement, that has its record source from a table called
tblplacement. This table has a combined key and one of the keys is
recipientID another is placementID. The placementID is an autonumber created
when a new record is made in the form frmplacement and table tblplacement. On
this form, frmplacement, the RecipientID is selected from a combobox drop
down.

On frmplacement I also have subform, frmSubsidySubFrmPlacement, who's
record source is a table called "tblsubsidy". This table also has a combined
key, of which one of the keys is recipientID the other is placementID.

The issue:

If a user changes the combobox entry, recipientID, on the placement form and
saves the record a new record is created in the tblsubsidy table. This is due
to the combined key and is expected but not what I want. As such, the
previous record also still exists because of the combined key. However I wish
to have it deleted when the recipientID is changed. The subsidy record must
be associated with the combined placementID and the recipientID.

The VB:

I wish to advise the user when they change the combobox, recipientID, that
the subsidy record associated with that recipientID, will be deleted and when
they click yes the record is deleted.

Hope that makes sense.

Cheers,
Andrew

P.S. For some reason i am getting an error with DoCmd.RunSQL, access is
telling me 'Arguement not optional'.
Click to expand...
 
Back
Top