|
|
|
codes with FILTER |
|---|
| message from =?Utf-8?B?Sm9l?= on 20 May 2004 |
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
|
| Nikos Yannacopoulos replied to =?Utf-8?B?Sm9l?= on 20 May 2004 |
Joe,
Me. ProductDescription =
DLookup("[tblProduct].[ProductDescription]","tblProduct","[tblProduct].[Prod
uctCode]=" & Me.ProductCode )
(it's all one line, just in case it gets wrapped).
HTH,
Nikos
"Joe" <anonymous@discussions.microsoft.com> wrote in message
news:A6F1F7CB-9FD4-457A-8F44-5903E6945EC8@microsoft.com...
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
adCmdTableDirect
|
| =?Utf-8?B?Sm9l?= replied to Nikos Yannacopoulos on 20 May 2004 |
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.
|
| Van T. Dinh replied to =?Utf-8?B?Sm9l?= on 20 May 2004 |
rst1.Filter "[ProductCode] = " & intProductCode
HTH
Van T. Dinh
MVP (Access)
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
adLockOptimistic, adCmdTableDirect
|
| =?Utf-8?B?Sm9l?= replied to Van T. Dinh on 20 May 2004 |
It does not pass through compilation. It says "Compile Error: Invalid use of property". The ".Filter" is highlighted.
|
| Brendan Reynolds replied to =?Utf-8?B?Sm9l?= on 20 May 2004 |
rst1.Filter = etc.
|
| =?Utf-8?B?Sm9l?= replied to Brendan Reynolds on 20 May 2004 |
"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 replied to =?Utf-8?B?Sm9l?= on 20 May 2004 |
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.
|
| =?Utf-8?B?Sm9l?= replied to Brendan Reynolds on 20 May 2004 |
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
Me!ProductDescription = rst1!ProductDescription
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub
|
| Van T. Dinh replied to =?Utf-8?B?Sm9l?= on 21 May 2004 |
Joe
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).
|
| =?Utf-8?B?Sm9l?= replied to Van T. Dinh on 20 May 2004 |
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.
|
|
