Delete Record From Another Database Based on Form Field Value

[sherlocked]

Greetings Experts,

I have two associated DB's that share info. While working in database A, I'd like to be able to find, delete & replace the associated record in database B.

I have a form in database A that holds a value I'd like to pass to database B in order to do this.

I can't seem to get the syntax for the deleting portion of the VBA quite right. Below is what I'm currently using, but I'm getting a syntax error. Any thoughts on what I'm doing wrong here?

CurrentDb.Execute "DELETE * FROM tblRecords WHERE & 'Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo' & IN (SELECT AppNo FROM MYDATABASEPATHHERE)"

The second half of my code, where I insert the replacement record, works just fine.

Appreciate any help you can give!

 

[plog]

What is the actual SQL you are running? Don't interpret it from your code, actually print out the SQL that the code is using.

 

[jdraw]

If you are working with split database, you could consider linking to both databases.
However, deleting records from a database - separate from the database you are currently logged into which may be being used by others - seems unconventional.

 

[JHB]

.. Any thoughts on what I'm doing wrong here?

CurrentDb.Execute "DELETE * FROM tblRecords WHERE & 'Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo' & IN (SELECT AppNo FROM MYDATABASEPATHHERE)"

T

You use a ' instead of a " and you've placed them wrong.
If AppNo is a real number then:

CurrentDb.Execute "DELETE * FROM tblRecords WHERE [B][COLOR=Red]"[/COLOR][/B] &  Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & [B][COLOR=Red]"[/COLOR][/B] IN (SELECT  AppNo FROM MYDATABASEPATHHERE)"

If AppNo is a text value then:

CurrentDb.Execute "DELETE * FROM tblRecords WHERE [B][COLOR=Red]'"[/COLOR][/B] &  Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & [B][COLOR=Red]"[/COLOR][COLOR=Red]'[/COLOR][/B] IN (SELECT  AppNo FROM MYDATABASEPATHHERE)"