Error on modified Allenbrowne SearchForm

S

sebastico

Hello

I have been trying to get a Form to search in a Access 2003 db based in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen : "Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could you help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID & """) AND "
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND "
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb & """)
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo & """)
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks
 
J

Jeanette Cunningham

Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
S

sebastico

Thanks Jeannette

The line is:
If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """) AND "
End If

Fortunately, early this morning after reading your message I deleted the
line and the filter works, and eureka, and as well after adding the line
again. I don't know why, but works.

Now, I'm trying to to add another filter in my form to search in another
table (MyTable2). The actual filter searches in MyTable1. My database is
relational and normalized. MyTable1 is "one" and MyTable2 is "many".

The new line to search in MyTable2 is:
If Not IsNull(Me.txtFilterFacetaID) Then
strWhere = strWhere & "([FacetaID] Like """ & Me.txtFilterFacetaID & """)
AND "
End If

However, I understand that the property "Me" is not correct for my filter
searching n another table (MyTable). I think I need a code to indicate the
filter to search in another table. I don't know how to do it. Could you
suggest me a way to do it?

I tested this search with sql (I enclose the lines) and works. I have been
searching the web to look for a way to translate sql ti vba, no success.

SELECT [MiTabla1].ObraID, [MiTabla1].Year, [MiTabla1].AutNomb,
[MiTabla2].FacetID, [MiTabla1].Titulo, [MiTabla1].Resumen
FROM MiTabla1 INNER JOIN MiTabla2 ON [MiTabla1].ObraID = [MiTabla2].ObraID;

Again many thanks

Jeanette Cunningham said:
Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

sebastico said:
Hello

I have been trying to get a Form to search in a Access 2003 db based in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen :
"Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could you
help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID & """) AND
"
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND "
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb &
""")
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo & """)
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """)
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

Point1, adding another table to the search.
Add the second table to the search form's record source. You will need to
build a query for the record source and add both tables to the query.


Point 2,
Here is the vba version of the sql.

Dim strSQL As String

strSQL = "SELECT [MiTabla1].ObraID, [MiTabla1].Year, " _
& "[MiTabla1].AutNomb, [MiTabla2].FacetID, " _
& "[MiTabla1].Titulo, [MiTabla1].Resumen " _
& "FROM MiTabla1 " _
& "INNER JOIN MiTabla2 " _
& "ON [MiTabla1].ObraID = [MiTabla2].ObraID"
Debug.Print strSQL


If you search Allen Browne's website, in the solutions or utilities
sections, you will find a download sample that helps to convert sql to vba.
www.allenbrowne.com


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



sebastico said:
Thanks Jeannette

The line is:
If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """) AND "
End If

Fortunately, early this morning after reading your message I deleted the
line and the filter works, and eureka, and as well after adding the line
again. I don't know why, but works.

Now, I'm trying to to add another filter in my form to search in another
table (MyTable2). The actual filter searches in MyTable1. My database is
relational and normalized. MyTable1 is "one" and MyTable2 is "many".

The new line to search in MyTable2 is:
If Not IsNull(Me.txtFilterFacetaID) Then
strWhere = strWhere & "([FacetaID] Like """ & Me.txtFilterFacetaID & """)
AND "
End If

However, I understand that the property "Me" is not correct for my filter
searching n another table (MyTable). I think I need a code to indicate the
filter to search in another table. I don't know how to do it. Could you
suggest me a way to do it?

I tested this search with sql (I enclose the lines) and works. I have been
searching the web to look for a way to translate sql ti vba, no success.

SELECT [MiTabla1].ObraID, [MiTabla1].Year, [MiTabla1].AutNomb,
[MiTabla2].FacetID, [MiTabla1].Titulo, [MiTabla1].Resumen
FROM MiTabla1 INNER JOIN MiTabla2 ON [MiTabla1].ObraID =
[MiTabla2].ObraID;

Again many thanks

Jeanette Cunningham said:
Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

sebastico said:
Hello

I have been trying to get a Form to search in a Access 2003 db based in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen :
"Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could you
help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID & """)
AND
"
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND "
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb &
""")
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo &
""")
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """)
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks
Click to expand...


.
Click to expand...
Click to expand...
 
S

sebastico

Jannette

Thank you very much for your suggestion.

That's the same idea I had. Seems to be easy, but I am lost.

Point 1.
When I double click the form selector, the form properties is displayed and
in the Record source I see MyTable1. If I double click the three point to the
right end of the record source, this message is displayed:You invoked the
Query Builder on a Table?

If I click Yes I am able to add MyTable2, but I don't know what to do next.
Could you tell describe what I have to do? This is very important due that
for a user (in my case high scholl students) is easier to filter in a form
rather than in a query design.


Point 2.

I will try to add the vba version code of the sql to my code in order to
filter as well in MyTable 2.

I also will search Allen Browne's website to see how to convert sql to vba.

Again many thanks

Jeanette Cunningham said:
Point1, adding another table to the search.
Add the second table to the search form's record source. You will need to
build a query for the record source and add both tables to the query.


Point 2,
Here is the vba version of the sql.

Dim strSQL As String

strSQL = "SELECT [MiTabla1].ObraID, [MiTabla1].Year, " _
& "[MiTabla1].AutNomb, [MiTabla2].FacetID, " _
& "[MiTabla1].Titulo, [MiTabla1].Resumen " _
& "FROM MiTabla1 " _
& "INNER JOIN MiTabla2 " _
& "ON [MiTabla1].ObraID = [MiTabla2].ObraID"
Debug.Print strSQL


If you search Allen Browne's website, in the solutions or utilities
sections, you will find a download sample that helps to convert sql to vba.
www.allenbrowne.com


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



sebastico said:
Thanks Jeannette

The line is:
If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """) AND "
End If

Fortunately, early this morning after reading your message I deleted the
line and the filter works, and eureka, and as well after adding the line
again. I don't know why, but works.

Now, I'm trying to to add another filter in my form to search in another
table (MyTable2). The actual filter searches in MyTable1. My database is
relational and normalized. MyTable1 is "one" and MyTable2 is "many".

The new line to search in MyTable2 is:
If Not IsNull(Me.txtFilterFacetaID) Then
strWhere = strWhere & "([FacetaID] Like """ & Me.txtFilterFacetaID & """)
AND "
End If

However, I understand that the property "Me" is not correct for my filter
searching n another table (MyTable). I think I need a code to indicate the
filter to search in another table. I don't know how to do it. Could you
suggest me a way to do it?

I tested this search with sql (I enclose the lines) and works. I have been
searching the web to look for a way to translate sql ti vba, no success.

SELECT [MiTabla1].ObraID, [MiTabla1].Year, [MiTabla1].AutNomb,
[MiTabla2].FacetID, [MiTabla1].Titulo, [MiTabla1].Resumen
FROM MiTabla1 INNER JOIN MiTabla2 ON [MiTabla1].ObraID =
[MiTabla2].ObraID;

Again many thanks

Jeanette Cunningham said:
Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello

I have been trying to get a Form to search in a Access 2003 db based in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen :
"Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could you
help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID & """)
AND
"
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND "
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb &
""")
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo &
""")
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """)
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks




.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

Delete the current contents of the Record Source, with the cursor in the
Record source box, do Ctl + F2.
This will bring up a query design.
Add both tables to the query and choose whichever fields you want for the
query.
Close the query window and say Yes to the save prompt.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sebastico said:
Jannette

Thank you very much for your suggestion.

That's the same idea I had. Seems to be easy, but I am lost.

Point 1.
When I double click the form selector, the form properties is displayed
and
in the Record source I see MyTable1. If I double click the three point to
the
right end of the record source, this message is displayed:You invoked the
Query Builder on a Table?

If I click Yes I am able to add MyTable2, but I don't know what to do
next.
Could you tell describe what I have to do? This is very important due that
for a user (in my case high scholl students) is easier to filter in a form
rather than in a query design.


Point 2.

I will try to add the vba version code of the sql to my code in order to
filter as well in MyTable 2.

I also will search Allen Browne's website to see how to convert sql to
vba.

Again many thanks

Jeanette Cunningham said:
Point1, adding another table to the search.
Add the second table to the search form's record source. You will need to
build a query for the record source and add both tables to the query.


Point 2,
Here is the vba version of the sql.

Dim strSQL As String

strSQL = "SELECT [MiTabla1].ObraID, [MiTabla1].Year, " _
& "[MiTabla1].AutNomb, [MiTabla2].FacetID, " _
& "[MiTabla1].Titulo, [MiTabla1].Resumen " _
& "FROM MiTabla1 " _
& "INNER JOIN MiTabla2 " _
& "ON [MiTabla1].ObraID = [MiTabla2].ObraID"
Debug.Print strSQL


If you search Allen Browne's website, in the solutions or utilities
sections, you will find a download sample that helps to convert sql to
vba.
www.allenbrowne.com


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



sebastico said:
Thanks Jeannette

The line is:
If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """)
AND "
End If

Fortunately, early this morning after reading your message I deleted
the
line and the filter works, and eureka, and as well after adding the
line
again. I don't know why, but works.

Now, I'm trying to to add another filter in my form to search in
another
table (MyTable2). The actual filter searches in MyTable1. My database
is
relational and normalized. MyTable1 is "one" and MyTable2 is "many".

The new line to search in MyTable2 is:
If Not IsNull(Me.txtFilterFacetaID) Then
strWhere = strWhere & "([FacetaID] Like """ & Me.txtFilterFacetaID &
""")
AND "
End If

However, I understand that the property "Me" is not correct for my
filter
searching n another table (MyTable). I think I need a code to indicate
the
filter to search in another table. I don't know how to do it. Could you
suggest me a way to do it?

I tested this search with sql (I enclose the lines) and works. I have
been
searching the web to look for a way to translate sql ti vba, no
success.

SELECT [MiTabla1].ObraID, [MiTabla1].Year, [MiTabla1].AutNomb,
[MiTabla2].FacetID, [MiTabla1].Titulo, [MiTabla1].Resumen
FROM MiTabla1 INNER JOIN MiTabla2 ON [MiTabla1].ObraID =
[MiTabla2].ObraID;

Again many thanks

:

Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello

I have been trying to get a Form to search in a Access 2003 db based
in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen :
"Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could
you
help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID &
""")
AND
"
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND
"
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb
&
""")
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo &
""")
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen &
""")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks




.
Click to expand...


.
Click to expand...
Click to expand...
 
S

sebastico

Jeannette.
My search Form in now working. Excellent.
Many thanks

Jeanette Cunningham said:
Delete the current contents of the Record Source, with the cursor in the
Record source box, do Ctl + F2.
This will bring up a query design.
Add both tables to the query and choose whichever fields you want for the
query.
Close the query window and say Yes to the save prompt.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sebastico said:
Jannette

Thank you very much for your suggestion.

That's the same idea I had. Seems to be easy, but I am lost.

Point 1.
When I double click the form selector, the form properties is displayed
and
in the Record source I see MyTable1. If I double click the three point to
the
right end of the record source, this message is displayed:You invoked the
Query Builder on a Table?

If I click Yes I am able to add MyTable2, but I don't know what to do
next.
Could you tell describe what I have to do? This is very important due that
for a user (in my case high scholl students) is easier to filter in a form
rather than in a query design.


Point 2.

I will try to add the vba version code of the sql to my code in order to
filter as well in MyTable 2.

I also will search Allen Browne's website to see how to convert sql to
vba.

Again many thanks

Jeanette Cunningham said:
Point1, adding another table to the search.
Add the second table to the search form's record source. You will need to
build a query for the record source and add both tables to the query.


Point 2,
Here is the vba version of the sql.

Dim strSQL As String

strSQL = "SELECT [MiTabla1].ObraID, [MiTabla1].Year, " _
& "[MiTabla1].AutNomb, [MiTabla2].FacetID, " _
& "[MiTabla1].Titulo, [MiTabla1].Resumen " _
& "FROM MiTabla1 " _
& "INNER JOIN MiTabla2 " _
& "ON [MiTabla1].ObraID = [MiTabla2].ObraID"
Debug.Print strSQL


If you search Allen Browne's website, in the solutions or utilities
sections, you will find a download sample that helps to convert sql to
vba.
www.allenbrowne.com


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Thanks Jeannette

The line is:
If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen & """)
AND "
End If

Fortunately, early this morning after reading your message I deleted
the
line and the filter works, and eureka, and as well after adding the
line
again. I don't know why, but works.

Now, I'm trying to to add another filter in my form to search in
another
table (MyTable2). The actual filter searches in MyTable1. My database
is
relational and normalized. MyTable1 is "one" and MyTable2 is "many".

The new line to search in MyTable2 is:
If Not IsNull(Me.txtFilterFacetaID) Then
strWhere = strWhere & "([FacetaID] Like """ & Me.txtFilterFacetaID &
""")
AND "
End If

However, I understand that the property "Me" is not correct for my
filter
searching n another table (MyTable). I think I need a code to indicate
the
filter to search in another table. I don't know how to do it. Could you
suggest me a way to do it?

I tested this search with sql (I enclose the lines) and works. I have
been
searching the web to look for a way to translate sql ti vba, no
success.

SELECT [MiTabla1].ObraID, [MiTabla1].Year, [MiTabla1].AutNomb,
[MiTabla2].FacetID, [MiTabla1].Titulo, [MiTabla1].Resumen
FROM MiTabla1 INNER JOIN MiTabla2 ON [MiTabla1].ObraID =
[MiTabla2].ObraID;

Again many thanks

:

Hi sebastico,
which line gives the compile error?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hello

I have been trying to get a Form to search in a Access 2003 db based
in
allenbrowne SearchForm. The fields are one table:
ObraID(String)
Año(String)
AutNomb(String)
Titulo(String)
Resumen(Memo)

Nevertheless, on clicking the "filter" the db displays in Resumen :
"Compile
error:Method or data member not found"

Due to this error, I don't know if all the code is correct. Could
you
help
me to fix this error?

My code is:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFilterObraID) Then
strWhere = strWhere & "([ObraID] = """ & Me.txtFilterObraID &
""")
AND
"
End If


If Not IsNull(Me.txtFilterAño) Then
strWhere = strWhere & "([Año] = """ & Me.txtFilterAño & """) AND
"
End If


If Not IsNull(Me.txtFilterAutNomb) Then
strWhere = strWhere & "([AutNomb] Like ""*" & Me.txtFilterAutNomb
&
""")
AND "
End If


If Not IsNull(Me.txtFilterTitulo) Then
strWhere = strWhere & "([Titulo] Like """ & Me.txtFilterTitulo &
""")
AND "
End If


If Not IsNull(Me.txtFilterResumen) Then
strWhere = strWhere & "([Resumen] = """ & Me.txtFilterResumen &
""")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Sin criterio", vbInformation, "Nada que hacer."
Else
strWhere = Left$(strWhere, lngLen)

(Ctrl+G).
'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "Usted no puede agregar nuevos autores al formulario de
búsqueda.", vbInformation, "Permiso denegado."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

Many thanks




.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modified Allenbrowne Search Form displays error 2
Filtering a multiple item form 1
Search Form with a SubForm 3
Extra ) error in search form 9
Allen Brownes ECount() 5
Allen Browne's Search Form - Debugging help needed 2
Help with VBA with an unbound form 4
Filter for words at the beginning or end using a Form Command Butt 5

Top