No value given for one or more required parameters?

[Jason V]

I am trying to look up a "CWA Number" from tblLabourTransaction using a
unique Reference Number as criteria. "txtAssRefNumber" is an unbound object
where the user enters the reference number to be searched.

In other words, the user enters the reference number, triggers the event,
and Access returns the CWA Number associated with it from
tblLabourTransaction.

I am getting the following error: No value given for one or more required
parameters.

This is what I have. Any help would be appreciated.

Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection


s = "SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = txtAssRefNumber;"

Set rs = New ADODB.Recordset
rs.Open s, db, adOpenStatic, adLockOptimistic

Me.txtCWANumber = rs.Fields("CWA Number")

Set rs = Nothing
Set db = Nothing

Thanks,JV

 

[Wayne Morgan]

s = "SELECT * FROM tblLabourTransaction WHERE

tblLabourTransaction.Reference_Number = txtAssRefNumber;"

You have txtAssRefNumber inside the quote marks. Access will treat this as
simply part of the text. While the query can resolve the parameter, it would
need the full path to the textbox (i.e. Forms!frmMyForm!txtAssRefNumber).
The other way to handle this is to let VBA get the value and concatenate in
the value.

s = "SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = " & txtAssRefNumber & ";"

 

[Jason V]

Wayne,

Thank you for your reply.....
I tried both of your suggestions but I am still getting errors. When I give
the full path to the text box I get the same error as before.

When I try your second suggestion get this error, " Syntax error (missing
operator) in the query expression 'tblLabourTransaction.Reference_Number ='.
"

Any thoughts?
JV

 

[Wayne Morgan]

Will you copy and paste your attempt at the second option into a message?
Also, is Reference_Number defined in the table as a number or text field? Is
there a value in the textbox txtAssRefNumber? Is txtAssRefNumber located on
the form that the code is running on?

 

[Jason V]

This is my attempt at the second option. I just copied what you had
written...I am not familiar with "concatenation".
s = "SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = " & txtAssRefNumber & ";"

Reference number is defined in the table as a number field.
Yes, I have input a value in txtAssRefNumber. The value I have entered is
also present in the "Reference Number" field in tblLabourTransaction, and a
"CWA Number" is stored with this record in the same table.

Yes, txtAssRefNumber is located on the form that the code is running on.

Thanks, JV

 

[Jason V]

Sorry, I have discovered that with the "contatenation method" the Syntax
error comes up when no value is entered into txtAssRefNumber. If a value is
entered then the original error comes up, "No value given...."

JV

 

[Wayne Morgan]

I would expect an error with nothing entered. You'll need to test for that
before executing this.

Try changing "txtAssRefNumber" to "Me.txtAssRefNumber". Also, place a break
point in the code above this line. step through the code to this line and
the one below it. For the line below it enter

Debug.Print s

Check the Immediate window to see what the value of "s" is. Does it appear
to be correct? If so, continue stepping through the code, which line
generates the error?

 

[Jason V]

I will try your suggestions, though I am unfamiliar with how to step through
code, etc. Maybe this will help:

See the line indicated with (********) below. This is the line that the
debugger is highlighting when I get the error.

Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection

s = "SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = " & Me.txtAssRefNumber & ";"

Set rs = New ADODB.Recordset
rs.Open s, db, adOpenStatic, adLockOptimistic ********

Me.txtCWANumber = rs.Fields("CWA Number")

Set rs = Nothing
Set db = Nothing

I will let you know what I come up with.

Thank you for your time, JV

 

[Jason V]

Wayne,

Using "Me.txtAssRefNumber" VB is correctly identifying the value entered in
the form. It is still getting hung up at

rs.Open s, db, adOpenStatic, adLockOptimistic

with the error, "No value given for one or more required parameters."

I would appreciate any further ideas...........please
JV

 

[Albert D.Kallal]

We will assume that tblLabourTransaction is a table (or query) with NO
parameters, and is simply clean sql).

The code you could use is:

dim strWhere as string

strWhere = "Reference_Number = " & Me.txtAssRefNumber

Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

For the future, save your self pain, suffering, and general misery, and DO
NOT use spaces in your field names....

Also, if reference number a text field, or a number type field (you need to
look this up in the table design).

if ref number IS a text field, then you must surround the condition in
quotes (as you do in ANY standard sql). So, if the ref is a text field, then

change the one line above to :

strWhere = "Reference_Number = '" & Me.txtAssRefNumber & "'"

Note that I used single quotes to surround the ref number as it was some
what easer then using a " (double quote).

 

[Wayne Morgan]

Albert is right, it you only need one value, you can probably use DLookup().

However, I took another look at your code. You Dim db as a New
ADO.Connection, but you never define and open the connection. I was looking
at the following example in the Help file for a comparison.

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable

 

[Jason V]

When I use DLookup, I get a different error, Run-time error '2001', "You
canceled the previous operation".

The error is occuring here:
Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

Do you know why this might be happenning? I can't figure it out.

Thanks,
JV

 

[Douglas J Steele]

That misleading error message generally means that you mistyped one of the
names in your DLookup statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I use DLookup, I get a different error, Run-time error '2001', "You
canceled the previous operation".

The error is occuring here:
Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

Do you know why this might be happenning? I can't figure it out.

Thanks,
JV

We will assume that tblLabourTransaction is a table (or query) with NO
parameters, and is simply clean sql).

The code you could use is:

dim strWhere as string

strWhere = "Reference_Number = " & Me.txtAssRefNumber

Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

For the future, save your self pain, suffering, and general misery, and DO
NOT use spaces in your field names....

Also, if reference number a text field, or a number type field (you need to
look this up in the table design).

if ref number IS a text field, then you must surround the condition in
quotes (as you do in ANY standard sql). So, if the ref is a text field, then

change the one line above to :

strWhere = "Reference_Number = '" & Me.txtAssRefNumber & "'"

Note that I used single quotes to surround the ref number as it was some
what easer then using a " (double quote).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Jason V]

Thanks for the comment......I went through it with a fine tooth comb but
didn't find any naming errors.

That misleading error message generally means that you mistyped one of the
names in your DLookup statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I use DLookup, I get a different error, Run-time error '2001', "You
canceled the previous operation".

The error is occuring here:
Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

Do you know why this might be happenning? I can't figure it out.

Thanks,
JV

We will assume that tblLabourTransaction is a table (or query) with NO
parameters, and is simply clean sql).

The code you could use is:

dim strWhere as string

strWhere = "Reference_Number = " & Me.txtAssRefNumber

Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

For the future, save your self pain, suffering, and general misery, and DO
NOT use spaces in your field names....

Also, if reference number a text field, or a number type field (you need to
look this up in the table design).

if ref number IS a text field, then you must surround the condition in
quotes (as you do in ANY standard sql). So, if the ref is a text field, then

change the one line above to :

strWhere = "Reference_Number = '" & Me.txtAssRefNumber & "'"

Note that I used single quotes to surround the ref number as it was some
what easer then using a " (double quote).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Douglas J. Steele]

Including whatever's in strWhere?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the comment......I went through it with a fine tooth comb but
didn't find any naming errors.

That misleading error message generally means that you mistyped one of
the
names in your DLookup statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I use DLookup, I get a different error, Run-time error '2001',
"You
canceled the previous operation".

The error is occuring here:
Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

Do you know why this might be happenning? I can't figure it out.

Thanks,
JV

:

We will assume that tblLabourTransaction is a table (or query) with
NO
parameters, and is simply clean sql).

The code you could use is:

dim strWhere as string

strWhere = "Reference_Number = " & Me.txtAssRefNumber

Me.txtCWANumber = dlookup("[CWA Number]","tblLabourTransaction", strWhere)

For the future, save your self pain, suffering, and general misery,
and DO
NOT use spaces in your field names....

Also, if reference number a text field, or a number type field (you
need to
look this up in the table design).

if ref number IS a text field, then you must surround the condition
in
quotes (as you do in ANY standard sql). So, if the ref is a text
field, then

change the one line above to :

strWhere = "Reference_Number = '" & Me.txtAssRefNumber & "'"

Note that I used single quotes to surround the ref number as it was
some
what easer then using a " (double quote).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Wayne Morgan]

Doug,

He found a typo in the 2nd thread that he started.

 

[Jason V]

Wayne/Doug,

Just to clarify, it wasn't a typo. "Reference Numer" was the field in
tblLabourTransaction, in VBA I referenced it as "Reference_Number". What I
did was I changed the table field name from "Reference Number" to
"ReferenceNumber" and the VBA code from "Reference_Number" to
"ReferenceNumber". When I did this the code worked.

Jason

Doug,

He found a typo in the 2nd thread that he started.