SOLVED

Add Where Clause

Super Contributor

Hello experts, I am trying to add a where clause to the below (red text).  It is my code so yes it is not correct.  Do you see where I am wrong? 

 

Dim strSQL As String

DoCmd.RunCommand acCmdSaveRecord

If Not IsNull(Me.cboCurrency) Then
strSQL = "INSERT INTO tblPmtProposal (IDBankAcctfk) VALUES (" & Me!cboCurrency.Column(1) & ")", "[ID]=" & Me.txtID

CurrentDb.Execute strSQL, dbFailOnError

Else

End If

8 Replies
on your Insert SQL, you only have 1 field (IDBankAcctfk) to be inserted:

"insert into tblPMTProposal (IDBankAcctfk) select " & Me!txtID & ";"

Hi,

 

You should explain what you want to achieve, because a WHERE clause for the target table in an append query is not useful. What do you want to filter by the txtID expression?

 

Servus
Karl
************
Access News
Access DevCon

@arnel_gp 

Hi Arnel,

thank you.
Yes, I am inserting only 1 field (IDBankAcctfk).
I am inserting Me!cboCurrency.Column(1)
but I need to insert WHERE "[ID]=" & Me.txtID

Right now, without the WHERE condition, its inserting a record in tblPMTProposal but its not according to the where condition.

 

I think I need to append that WHERE condition to that insert statement somehow.

 

I hope that it makes sense.
Maybe I am not following.

thank you

 

Tony2021_0-1645185286932.png

 

CO ID happens to be 16 too but that is only a coincidence. I should have hidden Co ID column in the pic.
Maybe INSERT is not what I need and its UPDATE? Maybe my approach is not correct and need UPDATE.
best response confirmed by Tony2021 (Super Contributor)
Solution

Hi,

 

If I understand right then you are looking for:

 

strSQL = "UPDATE tblPmtProposal " & _

" SET IDBankAcctfk = " & Me!cboCurrency.Column(1) & _

" WHERE ID = " & Me!txtID

 

Servus
Karl
************
Access News
Access DevCon

 

Perfect. It works! I am sorry but I started off on the wrong foot. I said INSERT but it was UPDATE. I get those mixed up sometimes.

thank you very much.
Sometimes an abrupt learning experience is needed to cement the understanding that eludes us. Continued success with the project.