codes with FILTER

[Guest]

I have a unbound form with 2 controls only. One control is ProductCode which is a combo box bounded to a query. Another control is the description which is a text box. When the ProductCode is selected from the pull down list, I want to retrive the description from the product master table. Here is my codes and the error says .Filter is invalid use of property. can anyone help me? TI

Dim cnn1 As New ADODB.Connectio
Dim rst1 As New ADODB.Recordse

Dim intProductCode As Strin

Set cnn1 = CurrentProject.Connectio
rst1.Open "tblProduct", cnn1, adOpenKeyset, adLockOptimistic, adCmdTableDirec

intProductCode = Me!ProductCod
rst1.Filter "[ProductCode] = intProductCode

Me!ProductDescription = rst1!ProductDescription

 

[Nikos Yannacopoulos]

Joe,

A bit of an overkill? All that's needed in your Event code is:

Me. ProductDescription =
DLookup("[tblProduct].[ProductDescription]","tblProduct","[tblProduct].[Prod
uctCode]=" & Me.ProductCode )

(it's all one line, just in case it gets wrapped).

HTH,
Nikos

I have a unbound form with 2 controls only. One control is ProductCode

which is a combo box bounded to a query. Another control is the description
which is a text box. When the ProductCode is selected from the pull down
list, I want to retrive the description from the product master table. Here
is my codes and the error says .Filter is invalid use of property. can
anyone help me? TIA

Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

Dim intProductCode As String

Set cnn1 = CurrentProject.Connection
rst1.Open "tblProduct", cnn1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

intProductCode = Me!ProductCode
rst1.Filter "[ProductCode] = intProductCode"

Me!ProductDescription = rst1!ProductDescription

 

[Van T. Dinh]

Try:

rst1.Filter "[ProductCode] = " & intProductCode

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I have a unbound form with 2 controls only. One control

is ProductCode which is a combo box bounded to a query.
Another control is the description which is a text box.
When the ProductCode is selected from the pull down list,
I want to retrive the description from the product master
table. Here is my codes and the error says .Filter is
invalid use of property. can anyone help me? TIA

Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

Dim intProductCode As String

Set cnn1 = CurrentProject.Connection
rst1.Open "tblProduct", cnn1, adOpenKeyset,

adLockOptimistic, adCmdTableDirect

intProductCode = Me!ProductCode
rst1.Filter "[ProductCode] = intProductCode"

Me!ProductDescription = rst1!ProductDescription
.

 

[Guest]

Yes, you are right. I trust there is a simple way. Unfortunately, I am study using VBA using ADO method. I am trying to write a small piece of code for practise but it seems it is very difficult. Appreciate if you or anyone can point out my mistake. Much appreciation.

 

[Guest]

It does not pass through compilation. It says "Compile Error: Invalid use of property". The ".Filter" is highlighted.

 

[Brendan Reynolds]

You're missing the "=" ...

rst1.Filter = etc.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

It does not pass through compilation. It says "Compile Error: Invalid use

of property". The ".Filter" is highlighted.

 

[Guest]

Oh ... my head is getting bigger and bigger !!
Brenden, I followed your suggestion and it passed through compilation. But I got a run-time error saying
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
I went through some of my reference books. I can't tell my mistake. Please help !!!!

 

[Brendan Reynolds]

To answer, Joe, I would need to see the changed code. If there's a 'debug'
button in the error message dialog, it would also help to know which line of
code is highlighted when you click that button.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Oh ... my head is getting bigger and bigger !!!
Brenden, I followed your suggestion and it passed through compilation.

But I got a run-time error saying:

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
I went through some of my reference books. I can't tell my mistake.

Please help !!!!

 

[Guest]

Hi Brendan, here is the changed code

Private Sub ProductCode_AfterUpdate(

Dim cnn1 As New ADODB.Connectio
Dim rst1 As New ADODB.Recordse

Dim intProductCode As Strin

Set cnn1 = CurrentProject.Connectio
rst1.Open "tblProduct", cnn1, adOpenKeyset, adLockOptimistic, adCmdTableDirec

intProductCode = "[ProductCode] = " & Chr$(34) & Me!ProductCode & Chr$(34
rst1.MoveFirs
rst1.Filter = intProductCode <------- this is the line highlighted when I clicked the debug butto
Me!ProductDescription = rst1!ProductDescriptio

rst1.Clos
cnn1.Clos
Set rst1 = Nothin
Set cnn1 = Nothin

End Su

 

[Van T. Dinh]

Joe

If you use ADO, I think you need to use the *single* quote as the delimiter
for String values like:

intProductCode = "[ProductCode] = '" & Me!ProductCode & "'"

(for clarity, single-quote +double-quote after the 2nd equal sign and
double-quote + single-quote + double-quote at the end.

I don't use ADO often enough but I think you need to create a new Recordset
for the resultant Recordset after setting the Filter Property.

OTOH, it is probably much more efficient if you use a Query / constructed
SQL with criteria (that returns only the required Product) to create the
Recordset and in this case, you don't even need to filter after the creation
of the Recordset.

Check Access VB Help on the Filter Property of the ADO Recordset. There are
also sample codes in the Help topic (A2K2).

--
HTH
Van T. Dinh
MVP (Access)

Hi Brendan, here is the changed code.

Private Sub ProductCode_AfterUpdate()

Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

Dim intProductCode As String

Set cnn1 = CurrentProject.Connection
rst1.Open "tblProduct", cnn1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

intProductCode = "[ProductCode] = " & Chr$(34) & Me!ProductCode & Chr$(34)
rst1.MoveFirst
rst1.Filter = intProductCode <------- this is the

line highlighted when I clicked the debug button

 

[Guest]

Thanks Van. Your answer is perfect. Actually I am reading a core reference book of A2002. It also explains exactly like you say. However I hit another strange situation and I will post in different thread. BTW, do you think I should post it on general discussion or programming vba group

Thank you very much again.