SQL form insert generating parm request

[Paul Wagner]

I'm using the code below in a form. Each msgbox yields a correct entry, including the Insert string. However I am getting an "Enter parameter" dialog box for each of the 3 values and don't know why. Is my syntax on the Insert statement in error?

Private Sub Add_FB_Detail_Click()

Dim AcctTypeCode As String
Dim AcctBudget As String
Dim BudgetYear As String
Dim strSQL As String
Dim StrBudgetNbr As String

AcctTypeCode = Forms.frmAddFBRec.AcctType.Column(0)
BudgetYear = Forms.frmAddFBRec.FiscalYear.Column(0)
StrBudgetNbr = [Forms]![frmFacultyBudgetHeader]![BudgetNumber]

MsgBox StrBudgetNbr
MsgBox AcctTypeCode
MsgBox BudgetYear
strSQL = "INSERT INTO tblFacultyBudgetDetail (FBH_FK, AccountTypeCode, BudgetYear) Values (StrBudgetNbr, AcctTypeCode, BudgetYear);"
MsgBox strSQL

DoCmd.RunSQL strSQL


End Sub

 

[john471]

Your strSQL is not using the value of the variables, it is instead creating new parameters, which generate the input boxes.

Try this instead.

strSQL = "INSERT INTO tblFacultyBudgetDetail (FBH_FK, AccountTypeCode, BudgetYear) Values ('" & StrBudgetNbr & "', '" & AcctTypeCode & "', '" & BudgetYear & "');"

HTH

John

 

[Paul Wagner]

Yep, that was it. Thanks for the education. And many more thanks for the generosity of assisting and sharing what you know! It is much appreciated.

Paul

 

[john471]

You are welcome.

It is always nice when people take the moment to let you know you helped them. I also think that, in many cases, doing so helps forum searchers to know which solution(s) to their problem they might look into and which may not quite be the answer, as well as letting other would-be-helpers know that the problem has been put to rest.

Happy Holidays.

 

[john471]

This may be even better ???

It is not exactly my area of expertise; but I've just noticed in This Thread that you may be able to reference the form controls directly from the SQL without going via variables.

e.g.

Private Sub Add_FB_Detail_Click()

    DoCmd.RunSQL "INSERT INTO tblFacultyBudgetDetail (FBH_FK, AccountTypeCode, BudgetYear) Values ([Forms]![frmFacultyBudgetHeader]![BudgetNumber],[Forms]![frmAddFBRec]![AcctType],[Forms]![frmAddFBRec]![FiscalYear])"

End Sub

I haven't tried it, so no guarantees !!!