Delete specific record from table using a value from a from?

General discussion on MS Access and its databases.

Delete specific record from table using a value from a from?

Postby Tansenn » Mon May 12, 2014 9:06 am

Hello All,
Please help me.

I have a form where a user enter an ID and some details. This is saved in Table A.
The ID is also in Table B - What I want is, once the user saves it in Table A-I want it to be deleted from Table B. Ultimately, Table A has all information and Table B will only have those IDs that haven't been entered in Table A.
Basically what is entered in Table A shouldn't be there in Table B.
Is there a way to do this using a macro?
I did find a VBA code online - what is wrong here? I am not very familiar with VBA . This code also suppreses the warnings.

Private Sub Submit_Enter()

DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
where [Claim ID] = Forms![FRM_PendsAssign]![Claim ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
DoCmd.SetWarnings True

End Sub

Kindly help me. Thank you for your time and help.
Tansenn
 
Posts: 28
Joined: Tue Mar 25, 2014 6:27 am

Re: Delete specific record from table using a value from a f

Postby Jill R. Lipe » Mon May 12, 2014 10:42 am

Hi,

CurrentDb.Execute "DELETE FROM TBL_ClaimsToBeAssigned WHERE [Claim ID]=" & Me.[Claim ID]
That method does not require the Set-warnings lines.
To use a macro, would have to create and save Access DELETE query object. Then macro can run the query object.
Thanks
Jill R. Lipe
 
Posts: 33
Joined: Mon Mar 24, 2014 6:30 am


Return to MS Access- Help Forum

Who is online

Users browsing this forum: No registered users and 0 guests

cron