Append query (INSERT) problems

[OTWarrior]

When I try the following sql in vb, it comes up with an inputbox for the value of [txtContactName] (which is the name of a textbox containing a string on the form where the code is fired from).

for example, if I had typed "Fred" in [txtContactName], then the following would show: (kinda looks like)
inputbox("Fred","Enter Parameter Value")

the sql I am trying to run is:

"INSERT INTO tbl_DOM_ProviderContacts ( nProviderID, nBlockNumber, nDiaryID, lkupProviderBlockType, anBookingID," _
& "curPackageCost, dteDateOfContact, dteTimeOfContact, txtContactName, lkupContactOutcome, curTSA_Cost )" _
& "SELECT qry_DOM_ProviderLookup_ByContract_CofC.nProviderID, qry_DOM_ProviderLookup_ByContract_CofC.nBlockRef, " _
& "tbl_DOM_Diaries.anDiaryID, qry_DOM_ProviderLookup_ByContract_CofC.anContractTypeID, tbl_DOM_Diaries.nDomBookingID, " _
& "qry_DOM_ProviderLookup_ByContract_CofC.nContractedCost, Date() AS Expr1, Time() AS Expr2, " & [txtContactName] _
& " AS Expr3, " & [lkupContactOutcome] & " AS Contactoutcome," & [curTSA_Cost] & " AS TSA " _
& "FROM qry_DOM_ProviderLookup_ByContract_CofC RIGHT JOIN tbl_DOM_Diaries ON qry_DOM_ProviderLookup_ByContract_CofC.careID=" _
& "tbl_DOM_Diaries.lkupTypeOfService WHERE (((qry_DOM_ProviderLookup_ByContract_CofC.nProviderID) Like " _
& [nProviderID] & ") AND ((qry_DOM_ProviderLookup_ByContract_CofC.nBlockRef)= Int(ReturnBlockRefFromSwift(" & [nSwiftID] _
& "))) AND ((tbl_DOM_Diaries.nDomBookingID) Like " & [anBookingID] & ") AND ((tbl_DOM_Diaries.lkupDomProviderName) Is Null));"

the piece in red is where it gets this value from. but the string it gets should just be inserted in the field (txtContactName) on the table.

EXTRA: If I remove the fields which cause this issue, I end up with error 3129 "invalid SQL satement, expected "DELETE", "INSERT",etc". What am I doing wrong?

[OBP]

If you did this as a Query it would throw up Froms![Formname]![[txtContactName]]
not just [txtContactName].

[OTWarrior]

it's throwing up the contents of the item on the form, which is why I find it so confusing (hence the earlier example). I can understand if it can't reference the form item, but it can find it and read it's contents, but it doesn't seem to know what to do with it.

and when I run this as a query (as in open it from the access screen rather than use vb code to run it) it works absolutly fine.

[OTWarrior]

Fixed it:

sql = "INSERT INTO tbl_DOM_ProviderContacts ( nProviderID, nBlockNumber, nDiaryID, lkupProviderBlockType, anBookingID, curPackageCost, dteDateOfContact, dteTimeOfContact, txtContactName, lkupContactOutcome, curTSA_Cost, lkupSPTWorker )" _
& "SELECT qry_DOM_ProviderLookup_ByContract_CofC.nProviderID, qry_DOM_ProviderLookup_ByContract_CofC.nBlockRef, tbl_DOM_Diaries.anDiaryID, " _
& "qry_DOM_ProviderLookup_ByContract_CofC.anContractTypeID, tbl_DOM_Diaries.nDomBookingID, qry_DOM_ProviderLookup_ByContract_CofC.nContractedCost, " _
& "Date() AS Expr1, Time() AS Expr2, '" & [txtContactName] & "' AS ContactName, forms!frm_DOM_ProviderRecord!lkupContactOutcome AS " _
& "Contactoutcome, " & [curTSA_Cost] & " AS TSA, " & GetUserID() & " AS user " _
& "FROM qry_DOM_ProviderLookup_ByContract_CofC RIGHT JOIN tbl_DOM_Diaries ON qry_DOM_ProviderLookup_ByContract_CofC.careID = tbl_DOM_Diaries.lkupTypeOfService " _
& " WHERE (((qry_DOM_ProviderLookup_ByContract_CofC.nProviderID) Like " & [nProviderID] & ") AND " _
& "((qry_DOM_ProviderLookup_ByContract_CofC.nBlockRef)=" & Int(ReturnBlockRefFromSwift([nSwiftID])) & ") AND ((tbl_DOM_Diaries.nDomBookingID) Like " _
& [anBookingID] & ") AND ((tbl_DOM_Diaries.lkupDomProviderName) Is Null));"