Runtime Error '-2147217904 (80040e10) No value given for one or more required parameters.

[Pookiemeister]

USERFORM CONTROLS:

TextBoxes:
txtEmpNum
txtFirName
txtLstName

Buttons:
btnEnter

I'm trying to use Excel's userform to add entries into Access. I want to enter the values entered into the textboxes into Access. Thanks.

Sub ConnectDB_Insert()
    'Path
    Dim strPath As String
    
    'Provider
    Dim strProv As String
    
    'Connection String
    Dim strConn As String
    
    'Connection
    Dim Conn As New Connection
    
    'RecordSet
    Dim rsQry As New Recordset
    
    'SQL Query
    Dim strQry As String
    
    strPath = "C:\Users\i0380332\OneDrive - Sanofi\Documents\Store_Room.accdb"
    strProv = "Microsoft.ACE.OLEDB.12.0;"
    strConn = "Provider=" & strProv & "Data Source=" & strPath
    
    'Connection Open
    Conn.Open strConn
    
    strQry = "INSERT INTO Personnel VALUES(frmPersonnelEntry.txtEmpNum.text,frmPersonnelEntry.txtFirName.text,frmPersonnelEntry.txtLstName.text,frmPersonnelEntry.txtEmail.text)"
    Conn.Execute strQry [B]Error occurs here but it has to do with the above line[/B]
End Sub

 

[saboh12617]

Hello,

Assuming your syntax is correct, the textboxes are all filled, and values match your DB types, i think you should use string concatenation like so

strQry = "INSERT INTO Personnel VALUES(" & _
  frmPersonnelEntry.txtEmpNum.Value & "," & _
  frmPersonnelEntry.txtFirName.Value & "," & _
  frmPersonnelEntry.txtLstName.Value & "," & _
  frmPersonnelEntry.txtEmail.Value & ")"

 

[RoryA]

Also make sure you add quotes around any text entries.

 

[Pookiemeister]

 

[saboh12617]

Hello,
I've never worked with DB so i could be wrong but i think @rory meant this when he mentioned quotes. If you put single quotes as you do, you essentially pass "frmPersonnelEntry.txtEmpNum.Value" as a value in the string, instead of its content. I suggest you add a Debug line below to see it better. Like so:

strQry = "INSERT INTO Personnel VALUES(" & _
  Chr(34) & frmPersonnelEntry.txtEmpNum.Value & Chr(34) & "," & _
  Chr(34) & frmPersonnelEntry.txtFirName.Value & Chr(34) & "," & _
  Chr(34) & frmPersonnelEntry.txtLstName.Value & Chr(34) & "," & _
  Chr(34) & frmPersonnelEntry.txtEmail.Value & Chr(34) & ")"
Debug.Print strQry

 

[Pookiemeister]

Thank you @saboh12617 and @RoryA the code worked until the "Conn.Execute strQry". I removed the txtEmail from my form. Now the error message is:

How my fields in my DB are displayed might be the reason for this error message.
Field 1 AutoNumber
Field 2 Employee Number
Field 3 First Name
Field 4 Last Name
Field 5 (Would be email)

Thanks again

 

[RoryA]

If you aren't inserting values into all the fields, you need to supply the names for the fields you are populating, and in the same order as the values:

strQry = "INSERT INTO Personnel ([Employee Number], [First Name], [Last Name]) VALUES('" & frmPersonnelEntry.txtEmpNum.text & "','" & frmPersonnelEntry.txtFirName.text & "','" & frmPersonnelEntry.txtLstName.text & "')"