Add Form Information to a Table

问题:

Option Compare Database

Private Sub cmdAdd_Click()
    CurrentDb.Execute "INSERT INTO Overtime(Todays_Date, Employee_Name, " & _
        "Start_Date, End_Date,Comments) " & _
        " VALUES(" & Me.txtCurrentday & ",'" & Me.txtName & "','" & _
        Me.txtBegin & "','" & Me.txtEnd & "','" & Me.txtComment & "')"

    Me.Refreshenter        
    cmdClear_Click
End Sub

Private Sub cmdClear_Click()
    Me.txtCurrentday = ""
    Me.txtName = ""
    Me.txtBegin = ""
    Me.txtEnd = ""
    Me.txtComment = ""

    Me.txtCurrentday.SetFocus
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Hello, I have created a Form and a Table in Microsoft Access 2010. The Form is called pbicovertime it has five unbound text boxes which all have unique names and three buttons. I would like the information that has been entered in the Form to be added to the Table called Overtime when the Add button is pressed. The code above does add the data from the Form to the table, however I get a Run-timer error '3061": Too few parameters. Expected 1 error message after closing and reopening the database. So initially everything seemed to be working fine. All the information entered in the Form was being added to the correct column in my Overtime Table. The issue took place after closing and reopening the database. I am not really sure how to proceed from this point.

FYI this is my first time working with Forms in Access !

回答1:

Open your table as a recordset and add a row. That will avoid complications based on required/missing quotes or date delimiters in the values you're adding.

Option Compare Database
Option Explicit ' <- add this

Private Sub cmdAdd_Click()
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Overtime",  dbOpenTable, dbAppendOnly)
    With rs
        .AddNew
        !Todays_Date = Me.txtCurrentday
        !Employee_Name = Me.txtName
        !Start_Date = Me.txtBegin
        !End_Date = Me.txtEnd
        !Comments = Me.txtComment
        .Update
        .Close
    End With
    'Me.Refreshenter ' <- what is this?
    cmdClear_Click
End Sub

If the original missing parameter error was because of a misspelled field name, this code will throw an error on one of the lines between AddNew and Update, so you should be able to quickly identify which name is misspelled.

Note: Always include Option Explicit in the Declarations sections of your code modules. And then run Debug->Compile from the VB Editor's main menu. Correct anything the compiler complains about before you spend time troubleshooting the code.

I don't know what Me.Refreshenter is. It looks like a misspelling of Me.Refresh. If so, that is something Option Explicit will warn you about. However, if you wanted Refresh, I suggest you substitute Me.Requery. The reason is that Refresh will pull in changes to any of the existing rows in the form's recordset, but not newly added rows. Requery gets new rows in addition to changes to existing rows.

回答2:

I'm willing to bet it's this line that it's crashing on.

CurrentDb.Execute "INSERT INTO Overtime(Todays_Date, Employee_Name, " & _
    "Start_Date, End_Date,Comments) " & _
    " VALUES(" & Me.txtCurrentday & ",'" & Me.txtName & "','" & _
    Me.txtBegin & "','" & Me.txtEnd & "','" & Me.txtComment & "')"

Specifically the Me.txtCurrentday, because it will be evaluated as straight text, and depending on how your PC is setup, it may be confusing SQL. e.g., it might look like this:

INSERT INTO Overtime(Todays_Date, Employee_Name, Start_Date, End_Date,Comments) 
VALUES ( Dec 1, 2013, 'JoeSmith', 'Jan 1, 2013', 'Dec 31, 2013', 
'Some important comment');

Dates you should encompass in #'s:

INSERT INTO Overtime(Todays_Date, Employee_Name, Start_Date, End_Date,Comments) 
VALUES ( #Dec 1, 2013#, 'JoeSmith', #Jan 1, 2013#, #Dec 31, 2013#, 
'Some important comment');

and it will go smoother. Also building up the SQL that way leaves you vulnerable to injections (either as an attack or error). Imagine if the comment was "This is Susie's Job", in which case that extra apostrophe would mess up the insert.