Error: Too Few Parameters. Expected 1

Feb 11, 2008

Hi All,

I am getting problem "Error: too few parameters. Expected 1" when following Query is executed to updated a Flag Value in a table on Click event of a Submit button.

CurrentDb.Execute "UPDATE Scheduled_Appointment SET Is_Taken = 1 WHERE Scheduled_Appointment_ID LIKE Me.Sch_P_ID"

Where:
Table: Scheduled_Appointment
Column: Scheduled_Appointment_ID [Primary Key]
Column: Is_Taken [ColumnType = Number ]

Text Field: Me.Sch_P_ID [contains the Scheduled_Appointment_ID value for the selected Record on the Form]

Thanks in Advance.

View Replies

Error 3061; Too Few Parameters. Expected 2

Jun 15, 2005

I am running this code, and i am getting this error:

Code:Private Sub SendFormToConsultants_Click() On Error GoTo Err_SendFormToConsultants_Click Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim stSubject As String '-- Subject line of e-mail Dim stCOFNumber As String '-- The COF Number from form Dim stCustomerID As String '-- The Customer ID from form Dim stCompanyName As String '-- The Company Name from form Dim stContactName As String '-- The Contact Name from form Dim stAddress As String '-- The Company Address from form Dim stTRDW As String '-- The TRDW from form Dim stPreReq As String '-- The PreReq from form Dim stWorkLoc As String '-- The Location of Work from form Dim stDelivActiv As String '-- The Deliverables/Activities from form Dim stStartDate As Date '-- The Start Date from Subform Dim stEndDate As Date '-- The End Date from Subform Dim stWho As String '-- Reference to Resources Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error '-- Combo of names to assign COF to stWho = Me.COF_Scheduled__Assigned_Resources__Subform1!Res ourceName stWhere = "Resources.ResourceName = " & "'" & stWho & "'" '-- Looks up email address from Resources varTo = DLookup("[ResourceEmail]", "Resources", stWhere) stCOFNumber = Me!COFNumber stCustomerID = Me.Consultancy_Order_Form_CustomerID stCompanyName = Me.CompanyName stContactName = Me!COFContact stAddress = Me.Address stTRDW = Me.TRDW stPreReq = Me.PreRequisites stWorkLoc = Me.WorkLocation stDelivActiv = Me.DeliverablesActivities stStartDate = Me.COF_Scheduled__Assigned_Resources__Subform1!Sta rtDate stEndDate = Me.COF_Scheduled__Assigned_Resources__Subform1!End Date stSubject = ":: New Consultancy Order Assigned ::" stText = "You have been assigned a new Consultancy Order." & vbCrLf & _ "Consultancy Order Form Number: " & stCOFNumber & _ vbCrLf & _ "Company ID: " & stCustomerID & _ vbCrLf & _ "Company Name: " & stCompanyName & _ vbCrLf & _ "Contact Name: " & stContactName & _ vbCrLf & _ "Address: " & stAddress & _ vbCrLf & _ "Terms of Reference / Description of Work: " & stTRDW & _ vbCrLf & _ "Pre-Requisites: " & stPreReq & _ vbCrLf & _ "Location of Work: " & stWorkLoc & _ vbCrLf & _ "Deliverables / Activities: " & stDelivActiv & _ vbCrLf & _ "Start Date: " & stStartDate & _ vbCrLf & _ "End Date: " & stEndDate & _ vbCrLf & _ "Please reply to confirm Consultancy Order Assignment." 'Write the e-mail content for sending to Consultant DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1 'Set the update statement to disable command button once e-mail is sent strSQL = "UPDATE [Consultancy Order Form] SET [Consultancy Order Form].COFSentToConsultants = 0 " & _ "Where [Consultancy Order Form].COFNumber = " & Me!COFNumber & ";" On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0 'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me!COFSentToConsultants.Requery Me!COFSentToConsultants.SetFocus Me.SendFormToConsultants.Enabled = False Exit SubErr_Execute: ' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If Resume NextExit_SendFormToConsultants_Click: Exit SubErr_SendFormToConsultants_Click: MsgBox Err.Description Resume Exit_SendFormToConsultants_ClickEnd Sub

What does it mean? it doesn't say where i have a problem in my code. What do you think?

View 3 Replies View Related

Too Few Parameters. Expected 1.

Jun 8, 2007

I am using Access to run my database. I am using Coldfusion to power the website. What I need to do is be able to update some fields. The problems first start in the select query, however.

There are two different forms. One that is a select box that has the options of "Buyers", "Sellers", "Newsletter". Onchange the select box (named "page"). When I submit that box it needs to populate a second box. That box is populated with the information found in the table that is selected in the first box.

Here is the query that it is using to get the information to populate:

SELECT RE.Information, RE.Page
FROM RE
WHERE (((RE.Page)="#form.page#"));

Does anyone see a problem?

Here's the result:

] Too few parameters. Expected 1.

The error occurred in D:Inetpubaheart4homeadmineditpages.cfm: line 10

8 : SELECT RE.Information, RE.Page
9 : FROM RE
10 : WHERE (((RE.Page)="#form.page#"));
11 : </cfquery>
12 : </cfif>

Thanks,
Brandon

View 2 Replies View Related

Too Few Parameters Expected.1

Nov 2, 2006

I have write the following code but on execution gives the following error. "Too few parameters. Expected.1"

the code is myQuery = "INSERT INTO SummaryHistory (HTimeStamp, HDiaryNo, HDiaryDate, HRecivedFrom, HSentTo, HReffrenceNo, HSentDate, HPageFrom, HPageTo, HRemarks, SummaryID) "

myQuery = myQuery + " VALUES ('" & myTimeStamp & _
"', '" & myDiaryNo & _
"', " & myDiaryDate & _
", '" & myRecivedFrom & _
"', '" & mySentTo & _
"', '" & myReffrenceNo & _
"', " & mySentDate & _
", " & myPageFrom & _
", " & myPageTo & _
", '" & myRemarks & _
"', " & myID & _
" )"

CurrentDb.Execute (myQuery)

on this last line its give error

any idea

View 1 Replies View Related

"Too Few Parameters. Expected..." - Parameter Query Issue

May 8, 2007

Hi All,

I am having an issue with a parameter query, let me try and explain...

I have a table (tblsitelogs) which contains records with dates on. I want to be able to query between 2 dates (from a form which the user specifies), and then export the data from the query, to an excel spreadsheet.

I browsed earlier and found this code to help me export the query to excel: http://www.databasejournal.com/features/msaccess/article.php/3563671. I am experienced in VB (although reletively new to Access), so I have adopted the code and tested it, and it works fine when run by itself.

Here is the query (qryReport)..

"SELECT tblSiteLog.ExchangeCode, tblSiteLog.ExchangeName, tblJobDetails.Phase, tblSiteLog.JobType, tblSiteLog.JobItem, tblSiteLog.Engineer, tblSiteLog.LogEntryDate, tblSiteLog.Result, tblSiteLog.EntryDetails, tblSiteLog.EnteredBy" & _
" FROM tblJobDetails INNER JOIN tblSiteLog ON tblJobDetails.JobID = tblSiteLog.JobID" & _
" WHERE (((tblSiteLog.LogEntryDate) Between " & txtStartDate & " And " & txtEndDate & "));"

When I then run this code

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryReport", dbOpenSnapshot)

I get an error on the bottom line, I get the error:

"Too few parameters. Expected 2".

I have researched and this appears to be an almost normal issue because of the query being a parameter-based query.

I've tried typing in the query in SQL, into VB and then running that instead, but it doesn't seem to filter, it will just display all results.

Feel free to ask more questions into this, I am probably only touching the surface of the problem here. Any help greatly appreciated!

Thanks,

Taz

View 7 Replies View Related

Run-time Error '3061'- Expected 1

Jan 18, 2006

I have a error I need help with. Here is my code for my Global, and My Form Current: See the red for the Error Line, that show up in the VBE and the Blue is the code related to that line. I am using access 2003

'Declare all variables for right-side record counter
Dim bdg As DAO.Recordset
Dim swr As DAO.Recordset
Dim wtr As DAO.Recordset
Dim dmo As DAO.Recordset
''Dim dvt As DAO.Recordset
Dim occ As DAO.Recordset
Dim fre As DAO.Recordset
Dim swrlat As DAO.Recordset
Dim wrtlat As DAO.Recordset

Dim bdgCount As Integer
Dim swrcount As Integer
Dim wtrcount As Integer
Dim dmocount As Integer
Dim dvtcount As Integer
Dim occcount As Integer
Dim frecount As Integer
Dim countswr As Integer 'laterial counter
Dim countwtr As Integer 'laterial counter

Dim sqlbdg As String
Dim sqlswr As String
Dim sqlwtr As String
Dim sqldmo As String
''Dim sqldvt As String
Dim sqlocc As String
Dim sqlfre As String
Dim sqlswrlat As String
Dim sqlwtrlat As String

Dim db As DAO.Database

Private Sub Form_Current()
Set db = CurrentDb()
'Use SQL strings to pull data from the tables
sqlbdg = "SELECT [Building].[PIN] FROM Building WHERE [Building].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlswr = "SELECT [Sewerform].[PIN] FROM [SEWER SERVICE LATERALS] WHERE [Sewerform].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlwtr = "SELECT [water].[PIN] FROM [WATER SERVICE LATERALS] WHERE [water].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlswrlat = "SELECT [SewerMain].[PIN] FROM [SEWER MAIN PRBLEMS] WHERE [SewerMain].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlwtrlat = "SELECT [WaterMain].[PIN] FROM [WATER MAIN PROBLEMS] WHERE [WaterMain].[PIN]='" & Me![ADDRESS3] & "' ;"
sqldmo = "SELECT [Demolition Permits].[PID] FROM [Demolition Permits] WHERE [Demolition Permits].[PID]='" & Me![ADDRESS3] & "' ;"
''There is no PIN field in the development table ==> sqlwtr = "SELECT [Development Permits].[PIN] FROM [Development Permits] WHERE [Development Permits].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlocc = "SELECT [Occupancy].[PIN] FROM Occupancy WHERE [Occupancy].[PIN]='" & Me![ADDRESS3] & "' ;"
sqlfre = "SELECT [Freeze].[PIN] FROM Freeze WHERE [FREEZE].[PIN]='" & Me![ADDRESS3] & "' ;"

Set bdg = db.OpenRecordset(sqlbdg, dbOpenSnapshot)
Set swr = db.OpenRecordset(sqlswr, dbOpenSnapshot)
Set wtr = db.OpenRecordset(sqlwtr, dbOpenSnapshot)
Set dmo = db.OpenRecordset(sqldmo, dbOpenSnapshot)
''Set dvt = db.OpenRecordset(sqldvt, dbOpenSnapshot)
Set occ = db.OpenRecordset(sqlocc, dbOpenSnapshot)
Set fre = db.OpenRecordset(sqlfre, dbOpenSnapshot)
Set swrlat = db.OpenRecordset(sqlswrlat, dbOpenSnapshot)
Set wrtlat = db.OpenRecordset(sqlwtrlat, dbOpenSnapshot)

'Building recordset
On Error Resume Next
If bdg.EOF And bdg.BOF = True Then
bdgCount = 0
Else

With bdg
.MoveFirst
.MoveLast
bdgCount = .RecordCount
End With

End If

'Sewer recordset
On Error Resume Next
If swr.EOF And swr.BOF = True Then
swrcount = 0
Else

With swr
.MoveFirst
.MoveLast
swrcount = .RecordCount
End With

End If

'Water recordset
On Error Resume Next
If wtr.EOF And wtr.BOF = True Then
wtrcount = 0
Else

With wtr
.MoveFirst
.MoveLast
wtrcount = .RecordCount
End With

End If
'Sewer laterial recordset
On Error Resume Next
If swrlat.EOF And swrlat.BOF = True Then
countswr = 0
Else

With swrlat
.MoveFirst
.MoveLast
countswr = .RecordCount
End With

End If

'Water laterial recordset
On Error Resume Next
If wrtlat.EOF And wrtlat.BOF = True Then
countwtr = 0
Else

With wrtlat
.MoveFirst
.MoveLast
countwtr = .RecordCount
End With

End If

'Demolition recordset
On Error Resume Next
If dmo.EOF And dmo.BOF = True Then
dmocount = 0
Else

With dmo
.MoveFirst
.MoveLast
dmocount = .RecordCount
End With

End If

'Development recordset
''On Error Resume Next
''If dvt.EOF And dvt.BOF = True Then
dvtcount = 0
''Else

''With dvt
'' .MoveFirst
'' .MoveLast
'' dvtcount = .RecordCount
''End With

''End If

'Occupancy recordset
On Error Resume Next
If occ.EOF And occ.BOF = True Then
occcount = 0
Else

With occ
.MoveFirst
.MoveLast
occcount = .RecordCount
End With

End If

'Freeze recordset
On Error Resume Next
If fre.EOF And fre.BOF = True Then
frecount = 0
Else

With fre
.MoveFirst
.MoveLast
frecount = .RecordCount
End With

End If

'Set the values of the recordcounts to the appropriate text boxes

txtbdgcount.SetFocus
txtbdgcount.Text = bdgCount
txtswrcount.SetFocus
txtswrcount.Text = swrcount
txtwtrcount.SetFocus
txtwtrcount.Text = wtrcount
txtdmocount.SetFocus
txtdmocount.Text = dmocount
txtdvtcount.SetFocus
txtdvtcount.Text = dvtcount
txtocccount.SetFocus
txtocccount.Text = occcount
txtfrecount.SetFocus
txtfrecount.Text = frecount
txtcountswr.SetFocus
txtcountswr.Text = countswr
txtcountwtr.SetFocus
txtcountwtr.Text = countwtr

PARID.SetFocus

' Provide a record counter for using with
' custom navigation buttons (when not using
' Access built in navigation)

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)

Me.Text34 = "Record " & Me.CurrentRecord & " of " & lngCount
End Sub

View 6 Replies View Related

Querydef Execute Error: Expected Function

Dec 9, 2005

All I want to do is run a make table query (or append) and show the user how many records were processed.
If I try to set recordset equal to the qdf.Execute I get the Compile error "Expected Function or variable".

I'm not sure what I'm missing...I've searched numerous threads and tried various combos of the execute method.

strquery = "qryEmailGenerate"
Set db = CurrentDb
Set qdf = db.QueryDefs(strquery)
Set rs = qdf.Execute
txtStatus = "Number of email recs: " & rs.RecordCount & vbCrLf

View 2 Replies View Related

Modules & VBA :: Error 3274 - External Table Is Not In Expected Format

Apr 2, 2014

I have a function that exports a number of tables within an access 2007 database to one spreadsheet using docmd.transferspreadsheet.

The error message in the title of this post presents itself during a loop which transfers the tables to the spreadsheet. However, it doesn't always happen, sometimes it completes the process perfectly which is quite frustrating.

Below is the section of code where the error occurs ....

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Table_Exports WHERE Type = 'CRM'", dbOpenSnapshot)
rs.MoveFirst
Do Until rs.EOF
TableName = rs.Fields("Table")
WSName = rs.Fields("WSName")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, NewWBPath, True, WSName
rs.MoveNext
Loop

View 1 Replies View Related

Modules & VBA :: Access 2003 - Compile Error Expected / End Of Statement

Apr 19, 2014

I'm using MS Access 2003 and having difficulty with VBA.

Code:
strSql = "Insert into TBLCONDUTOR (Codigo, pretensao, ncarta, nlicenca, nome, apelido, dnascimento, contacto1, contacto2, email, nbicc, nnif, morada, localidade, cidade, cpostal, _
imagemcondutor, catAM, dataemissaoAM, datavalidadeAM, catA1, dataemissaoA1, datavalidadeA1, catA2, dataemissaoA2, datavalidadeA2, catA, dataemissaoA, datavalidadeA, _
catB1, dataemissaoB1, datavalidadeB1, catB, dataemissaoB, datavalidadeB, catBE, dataemissaoBE, datavalidadeBE,

[Code] ....

View 3 Replies View Related

0x80040e10 Too Few Parameters Error

Sep 9, 2005

I could use some help. I am not a programmer, I am helping someone out.

We have an access database that is accessed using asp webpages. When trying to add students to the database we get the following error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. line 51

Below is the code on the page.

If ((request("REQUEST_METHOD") = "POST") and ((request("CONTENT_LENGTH") > 0) and (request("CONTENT_LENGTH") < 14500))) then %>
<%
LastName = request("Last_Name")
FirstName = Request("First_Name")
MiddleName = request("Middle_Name")
Student = request("Student_Number")
WEng316 = request("WEng316")
WChEn475 = request("WChEn475")
WChEn477 = request("WChEn477")
OChEn391 = request("OChEn391")
OChEn451 = request("OChEn451")
TChEn475 = request("TChEn475")
TChEn477 = request("TChEn477")
TChEn451 = request("TChEn451")

YearOfTest = request("YearOf_Test")

sValue = request("submitValue")

strProvider="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("iisadmin") & "websitelevel3_questions.mdb;"

set cm = Server.CreateObject("ADODB.Command")
set objConn = server.createobject("ADODB.Connection")
objConn.Open strProvider

cm.ActiveConnection = objConn

insertString = "INSERT INTO Students(LastName,FirstName,MiddleName,StudentNumb er,YearOfTest,Writ316,Writ475,Writ477,Oral391,Oral 451,TW451,TW475,TW477) "
insertString = insertString & "VALUES ('"&LastName&"','"&FirstName&"','"&MiddleName&"',"&Student&","&YearOfTest&","&WEng316
insertString = insertString & ","&WChEn475&","&WChEn477&","&OChEn391&","&OChEn451&","&TChEn451&","&TChEn475&","&TChEn477&")"

cm.CommandText = insertString

'cm.CommandText="INSERT INTO Students(LastName,FirstName,MiddleName,StudentNumb er) VALUES (?, ?, ?, ?)"
'Set objParam=cm.CreateParameter(, 200, , 255, LastName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 200, , 255, FirstName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 201, , 255, MiddleName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 201, , 255, Student)
'cm.Parameters.Append objParam
cm.Execute
%>

<% If (sValue = "1") then
Response.Redirect("viewStudents.asp")

Else %>
<HTML>
<HEAD>
<TITLE>Add Student
</TITLE>

<%
call stylesheet
%>
<script language=javascript>

</script>
</HEAD>
<BODY bottomMargin=15 leftMargin=15 topMargin=15 rightMargin=15 marginwidth="0" marginheight="0" onLoad="loadMe();">
<%
call header
%>
<FORM NAME="form" METHOD=POST ACTION="addStudents.asp">

<INPUT TYPE=hidden name=submitValue >
<table cellSpacing=0 cellPadding=0 BORDER=0 WIDTH="95%" align=center>
<tr>
<td colspan=3 > </td>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>First name:</span></td>
<TD><INPUT TYPE='text' NAME='First_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD>          </TD>
<td ><span class=admintext>Middle name:</span></td>
<TD><INPUT TYPE='text' NAME='Middle_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Last name:</span></td>
<TD><INPUT TYPE='text' NAME='Last_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Student number:</span></td>
<TD><INPUT TYPE='text' NAME='Student_Number' Size='10' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Written Communication:</span></td>
<TD><span class=admintext>Engl 316  </span>
<INPUT TYPE='text' NAME='WEng316' Size='5' MAXLENGTH='3'>    
<span class=admintext>ChEn 475  </span>
<INPUT TYPE='text' NAME='WChEn475' Size='5' MAXLENGTH='3'>    
<span class=admintext>ChEn 477  </span>
<INPUT TYPE='text' NAME='WChEn477' Size='5' MAXLENGTH='3'>    
</TD>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Oral communication:</span></td>
<TD><span class=admintext>ChEn 391  </span>
<INPUT TYPE='text' NAME='OChEn391' Size='5' MAXLENGTH='3'>    
<span class=admintext>ChEn 451  </span>
<INPUT TYPE='text' NAME='OChEn451' Size='5' MAXLENGTH='3'>    
</TD>
</tr>
<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Teamwork:</span></td>
<TD><span class=admintext>ChEn 451  </span>
<INPUT TYPE='text' NAME='TChEn451' Size='5' MAXLENGTH='3'>    
<span class=admintext>ChEn 475  </span>
<INPUT TYPE='text' NAME='TChEn475' Size='5' MAXLENGTH='3'>    
<span class=admintext>ChEn 477  </span>
<INPUT TYPE='text' NAME='TChEn477' Size='5' MAXLENGTH='3'>    
</TD>
</tr>

<tr>
<TD>          </TD>
<td colspan=0 ><span class=admintext>Year of test:</span></td>
<TD><INPUT TYPE='text' NAME='YearOf_Test' Size='10' MAXLENGTH='254'>&nbsp<span class=admintext>(Example:<b>Y2002-03</b>)</span></TD>
</tr>

<tr>
<td colspan=3 > </td>
</tr>
<tr>
<TD>          </TD>
<td colspan=2 >
<table cellSpacing=2 cellPadding=0 BORDER=0 align=left >
<tr><td>
<table cellSpacing=2 cellPadding=0 BORDER=1 align=left bgcolor=#003366>
<tr><td bgcolor=#cccccc> <a href="javascript:form_submit('return');" class=adminlink><b>Save and Return</b></a> 
</td></tr>
</table>
</td>
<td> </td>
<td>
<table cellSpacing=2 cellPadding=0 BORDER=1 align=left bgcolor=#003366>
<tr><td bgcolor=#cccccc> <a href="javascript:form_submit('stay');" class=adminlink><b>Save and Add another</b></a> 
</td></tr>
</table>
</td></tr>
</table>
</td>
</tr>
</table>
</FORM>
<% End If %>

<% Else %>
<HTML>
<HEAD>
<TITLE>Add Student
</TITLE>

<%
call stylesheet
%>
<script language=javascript>

</script>
</HEAD>
<BODY bottomMargin=15 leftMargin=15 topMargin=15 rightMargin=15 marginwidth="0" marginheight="0">
<%
call header
%>

please help ASAP

thank you

View 1 Replies View Related

Runtime Error 3061 Too Few Parameters

Jul 1, 2005

Hello Access friends,
What is wrong with the following code (modified the module from http://members.iinet.net.au/~allenbrowne/AppInventory.html ):
I Keep getting a runtime error 3061 Too few parameters . Expected 1 on the line highlight below.
I have the reference MS DAO 3.6 selected and I am using access 2000 and calling the module from a command button in a form.
Thanks in advance for taking the time to help me out.

Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim strProduct As String 'vProductID as a string.
Dim strAsOf As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last transaction.
Dim lngQtyAcq As Long 'Quantity acquired since incoming transaction.
Dim lngQtyUsed As Long 'Quantity used since outgoing transaction.

If Not IsNull(vProductID) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
strProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm/dd/yyyy") & "#"
End If

'Get the last transaction date and quantity for this product.
If Len(strAsOf) > 0 Then
strDateClause = " AND ([TransacDate] <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 [TransacDate], [Quantity] FROM [Transactions] " & _
"WHERE ((ProductID = " & strProduct & ")" & strDateClause & _
") ORDER BY TransacDate DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(![TransacDate], "mm/dd/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close

'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & strAsOf
Else
strDateClause = " >= " & strSTDateLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If

'Get the quantity acquired since then.
strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityAcq " & _
"FROM [Transactions]" & _
"WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Incoming')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close

'Get the quantity used since then.
strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityUsed " & _
"FROM [Transactions]" & _
"WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Outgoing')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)
End If
rs.Close

'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If

Set rs = Nothing
Set db = Nothing
Exit Function
End Function

View 3 Replies View Related

Modules & VBA :: Error 3061 - Too Few Parameters

Jul 9, 2013

Access 2003

This statement works great.

Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """);"

I'd like to include FileID (number Long Integer) and use the value from MyForm RecordID (autonumber)

This gives me a Run-Time error '3061': Too few parameters. Expected 1.

Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile, FileID) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """, me.RecordID);"

View 3 Replies View Related

Error 80040e10 - No Value Given For One Or More Required Parameters In SQL String

Sep 29, 2015

I have the error 80040e10-No value given for one or more required parameter inside a SQL string and I know that the problem is some/one of my variables passed, however I cannot found the mistake. See down here my code. I got experience in VBA but no much in ACCESS so the query have some problems.

I got the following parameters used inside of the string (I can see before open the recordset that all of them have valid values);

TotalGj is a number result of a dsumTemp1 is a textMketer is a text

My code is down here

Sub Enterdata()
Dim Mketer As String, MonthBR As String
Dim HECAmount As Currency
Dim RsCustomers As ADODB.Recordset, RsFfeesMonth As ADODB.Recordset
Dim Cnx As ADODB.Connection

[Code] .....

View 5 Replies View Related

Modules & VBA :: ConcatRelate Function - Error 3061 Too Few Parameters

Sep 18, 2014

I have been struggling with getting the syntax right for the ConcatRelate function. I have looked at other peoples examples and mine seems to have exactly the same syntax but it is giving me an error.

My Sql is

SELECT qr_RiverGroup.River, ConcatRelated("Site_ID","qr_RiverGroup","[River] = '" & [River] & "'") AS Expr1
FROM qr_RiverGroup;

View 7 Replies View Related

Modules & VBA :: Query Parameters Generating Error 3061 With OpenRecordset

Sep 9, 2013

I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.

This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:

Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails

Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)

[Code] ....

View 6 Replies View Related

Modules & VBA :: Opening A Query With Parameters - Data Type Conversion Error

Jun 11, 2013

Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.

Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"

Here's my Code:

Set db = CurrentDb
Set qd = db.QueryDefs("qryMY_DATA")
qd.Parameters(0) = Me.txt_ReferenceID
Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)

Code:
'*** Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset

I've been all over the forums and tried several different approaches, all to no avail. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.

View 10 Replies View Related

Passing Parameters Thru Combo Selection: ERROR:Data Type Mismatch Criteria Expression

Oct 17, 2006

I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why?
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));

I have spent so much time onthis already and i am sick of it :mad:

Attached is my db. Please help me out here.

View 2 Replies View Related

Modules & VBA :: Email Function Using Optional Parameters - Optional Argument Error

Aug 12, 2015

I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.

Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _

[Code] .....

View 4 Replies View Related

OnDirty Not Responding As Expected

Mar 22, 2006

I have tried to display a message asking if you are sure you want to change data when you enter a field that already contains data. This is via a combo box.

Even when the entry is new, the message comes up. I have tried using OnDirty, OnChange, and AfterUpdate, but I always get the message on new input. Any suggestions?

Thanks

View 3 Replies View Related

Err Msg : External Table Is Not In The Expected Format.

Feb 4, 2007

A lady at work created an Access database that imports a dBase 5 file and when I go to import this file on User #1 PC it gives the error;

"External table is not in the expected format."

I can use the same Access database and same dBase file to import (stored on a shared network drive) from User #2 & #3's PC's and all works well. The only problem is that it is User #1 job to do this.

I unloaded Access 2003 and reloaded it and still the same results. All 3 PC's are identical via hardware and software.

It use to work until this past Tuesday when it started to give the error.

My steps are:
open the "Sales" Access database
select the prices table
Get External Data / Import and then select Prices.dbf
Then the error message comes up.

I am not an Access database guy and could use some help!

Thanks

WhooTAZ

View 2 Replies View Related

Query Returning MORE Results Than Expected

Mar 2, 2007

Hi all:

running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.

SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments
FROM [User] INNER JOIN Class ON User.UserID = Class.UserID
ORDER BY Class.ClassDate DESC;

It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?

thanks:confused:

View 3 Replies View Related

Query Not Returning All Expected Data

Sep 7, 2007

O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.

I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./

Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms

I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.:confused:

Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??

Thanks.

View 9 Replies View Related

Compare Actual Records To Expected

Jan 28, 2008

Not even sure where to start so if this isn't a query question, please point me in the right direction.

I work in a plant that has a number of cranes. Each crane should be inspected each shift (three shifts per day), each day (less Sunday).

I have exactly what I need to capture the inspections being done. Inspections are identified by crane #, shift, and date.

How do I determine if an inspection hasn't been done? How do I compare the inspections that have been entered against the standard of inspections for each crane, each shift, each day?

I am below average with Access on my best day so please type slowly and be specific. Thanks for any help you can give.

Forrest

View 5 Replies View Related

Reports :: Report Is Not Returning Expected Averages

May 9, 2013

My database is set up to track call evaluations with 4 fields for number data (S, A/C, C/E and B) each of these have a possible point total. I also have a percentage field to track out of total possible points.When I run my query I get a list of each of the totals for each of the evaluations with the associates names (as expected).I take that query and try to run a report wizard to give me an average socre for each associate. and the system returns averages of 0 or an odd number that does not make sense.when I use the =Avg([fieldname]) process I get an accurate average of the total but can not get it to do a "subtotal" for each associate.

View 1 Replies View Related

Queries :: Query Returning 2 Records When Only One Is Expected

Aug 7, 2015

I'm running a query from two related tables in the database.the table relationships and the query design. Instead of doing what I want it to do/what I think I'm asking it to - which is show the sum total number of weeks on the program for each student - it shows me the number of weeks for that student just for that claim, not the total for the student overall. I've added some 'dummy data' (2 records in the claim table relating to 1 student), and the query then returns the student twice in the results.

View 1 Replies View Related

Referential Integrity: Cascade Update Not Working As Expected???

Nov 2, 2006

Hi,
I have a database with a couple of tables. The primary table has a primary key called "StaffNo". I checked the relationships between the primary table and each related table, if I have ticked on the boxes for referential integrity, cascade updates. All done. If I now create a query, which selects the primary table and one related table and enter a new StaffNo, I would expect to see the new StaffNo not only in the primary table but also in the related one. But there is nothing. Do I expect wrong? Have I missed sth.? I read a few posts in this and other forums reg. referential integrity, but I have no clue, what's wrong. :confused:
Any quick help is much appreciated.
Thanks :)

View 1 Replies View Related