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

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
636
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

VBA Code:
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
 
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
VBA Code:
strQry = "INSERT INTO Personnel VALUES(" & _
  frmPersonnelEntry.txtEmpNum.Value & "," & _
  frmPersonnelEntry.txtFirName.Value & "," & _
  frmPersonnelEntry.txtLstName.Value & "," & _
  frmPersonnelEntry.txtEmail.Value & ")"
 
Upvote 0
Also make sure you add quotes around any text entries.
 
Upvote 0
1739377513091.png
 
Upvote 0
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:
VBA Code:
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
 
Upvote 0
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:
1739379113120.png

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
 
Upvote 0
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:

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

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top