Using VB to look up and insert a value.

[Guest]

I had this question posted already but never finalized a solution:

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:

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

The error is occrring in this line:
rs.Open s, db, adOpenStatic, adLockOptimistic

Any ideas?

 

[Wayne Morgan]

Jason,

Take a look at the last answer to your previous thread. It was placed there
this morning. Does it help?

 

[Guest]

Wayne,
Sorry for being such a pain but I am still struggling with this. I have
also tried Albert's suggestion using dlookup and am getting different errors
(see the previous thread).

I don't quite understand how I can relate your example to my DB....(still
quite new at this). You said that I never define and open the connection.
Doesn't the line, "rs.Open s, db, adOpenStatic, adLockOptimistic" do this????
I have used this same code (well....similar)in other situations and never
had a problem, the only difference is that in the other cases the criteria
(txt.AssRefNumber in this case) was not a variable, it was a set value
written into the code.

Would it be possible for you to alter my code to include the changes you
were trying to explain below?

Thanks a lot for your patience.
Jason

 

[Wayne Morgan]

The rs.Open opens the Recordset, but the connection that you have defined in
db, one of the parameters in this statement, hasn't been opened yet. Do you
need a new connection? If not, you may be able to use
CurrentProject.Connection instead. That would change the line to

rs.Open s, CurrentProject.Connection, adOpenStatic, adLockOptimistic

If you do need a new connection, then prior to the rs.Open statement, you'll
need to define the connection and open it. In the example, these were the
lines

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

where Cnxn would be db in your code. Since you've already Dim'ed db as a New
Connection, I believe you can skip the Set Cnxn statement, but you'll need
the other two.

 

[Guest]

When I change the line to, "rs.Open s, CurrentProject.Connection,
adOpenStatic, adLockOptimistic" I get the same error about No value
given.......

This is another set of code that I have that works perfectly...but like I
said, the criteria is set (1). Maybe this will give some insight.

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

s = "SELECT * FROM tblBatchNumberGenerator WHERE
tblBatchNumberGenerator.Key = 1;"

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

Me.txtBatchUnbound = rs.Fields("Batch Number")

Set rs = Nothing

Thanks again.
Jason

 

[Wayne Morgan]

In that case, try the full path to the form.

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

I tried your code and it works. Another thing that may help would be to put
in a Debug.Print statement before the rs.Open statement to see what "s"
really is.

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

This will print the string in "s" to the Immediate window. Is what it shows
correct? Is txtAssRefNumber on the form with the code (i.e. not on a parent
form with the code in a subform or vice-versa)? Where is
tblLabourTransaction located? If it's an external table, has it also been
included in the current file as a linked table?

 

[Guest]

With or without the full path I get the same result.....

This is what the immediate window shows when I use Debug.Print s:

SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = 6;

000006 is the number I enter in txtAssRefNumber and is the number located in
tblLabourTransactions under "Reference Number". tblLabourTransactions is
located in this database

Is that what the immediate window should be showing?

Thanks,Jason

 

[Guest]

Could it have something to do with data types other something?
txtAssRefNumber is an unbound object of which you can't specify the data
type....right? "Reference Number" in tblLabourTransaction is a
number.....Just a thought.

 

[Guest]

I've got it!! All I did was remove the space from the "Reference Number"
field in tblLabourTransactions and in the VBA code and it works now. Like
this:

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.ReferenceNumber = " &
Forms!frmDeleteLabourTransaction!txtAssRefNumber & ";"

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

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

Set rs = Nothing
Set db = Nothing

Thank you for all of your help Wayne, wish I could buy you a drink.

Jason

 

[Wayne Morgan]

Possible, if so try wrapping it in a CLng to change it to a Long data type
or whatever number type you have the field defined as.

Example:
s = "SELECT * FROM tblLabourTransaction WHERE
tblLabourTransaction.Reference_Number = " & CLng(Me.txtAssRefNumber) & ";"

Other options:
CDbl for Double
CSng for Single
CCur for Currency
CInt for Integer
CByte for Byte

Have you tried just entering 6 instead of 000006 to see if it makes a
difference? If so, then it may be treating it as a string because of the
leading zeros. If so, the appropriate conversion function above should help.

Open the table in design mode and look at the data type for the field, then
in the properties sheet at the bottom, look at the Size property. Are the
numbers in the table stored with the leading zeros (not just formatted that
way, but actually stored that way)?