UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter Subform With Connection, Access 2010    
 
   
Joserobmoraes
post May 26 2019, 07:58 AM
Post#1



Posts: 72
Joined: 14-October 10



Hello everyone, I'm having a question, how can I filter a subform with tables not used?

Today I use this function to save in the table (BackEnd).
I make the instance in the form like this:

Dim dbBanco As DAO.Database, rsFavorecido As Recordset
Dim strPath As String, SenhaBd As Variant

After:

Public Function fncAbreConexao()
SenhaBd = DecryptData(DLookup("Senha", "tblCaminhoBE"))
strPath = DLookup("[Path_0]", "tblCaminhoBe")
Set dbBanco = OpenDatabase(strPath, False, False, "MS Access;PWD=" & SenhaBd)
Set rsFavorecido = dbBanco.OpenRecordset("TblFavorecido")
End Function

Public Function fncFechaConexao()
dbBanco.Close
Set dbBanco = Nothing
End Function

And in the save button I call the function to save in the table:

On Error GoTo Erro
fncAbreConexao
Me.BtTxt.SetFocus
rsFavorecido.AddNew
rsFavorecido("Favorecido_Nome") = Me.TxtNome
rsFavorecido("Favorecido_Contato") = Me.TxtContato
rsFavorecido("Favorecido_Telefone") = Me.TxtTelefone
rsFavorecido("Favorecido_Observacao") = Me.TxtObservacao
' Atualizando a tabela
rsFavorecido.Update
'Mensagem de confirmação
Call Confirma("Confirmação de dados!", "* " & TxtNome & "", "Registro adicionado com sucesso.", _
"CAD_Favorecido")
Me.TxtNome.SetFocus
MakeTransparent Me.hWnd, 100
fncFechaConexao
Sai:
Exit Sub
Erro:
MsgBox " Erro ocorrido: " & Err.Number & Err.Description
Resume Sai:

But now comes the problem, I have a subform "CAD_FavorecidoDet" inside the form "CAD_FavorecidoLista"


In the "CAD_FavoriteList" has a field called "TxtSearch" which when typing would filter the subform "CAD_FavorecidoDet"


With linked tables works easy, now in this transition model it does not work

Anyone have any ideas?





--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 
theDBguy
post May 26 2019, 08:42 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,538
Joined: 19-June 07
From: SunnySandyEggo


Hi. Check out the Recordset.Filter property.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Joserobmoraes
post May 26 2019, 09:28 AM
Post#3



Posts: 72
Joined: 14-October 10



I need to load the subformular CAD_FavorecidoDet

--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 
theDBguy
post May 26 2019, 09:40 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,538
Joined: 19-June 07
From: SunnySandyEggo


Yes. I thought you might. How are you loading it now? I imagine after the user enters a search term, you would apply a filter to the recordset and then reload the subform.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Joserobmoraes
post May 26 2019, 02:26 PM
Post#5



Posts: 72
Joined: 14-October 10



I'm trying to load my sibform like this:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT * FROM TblCliente"
Call AbreCon(strSQL, dbOpenDynaset)


rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

Set Me.Form.Recordset = rst

but of the error saying that it does not find the table

How can I carry it?

--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 
theDBguy
post May 26 2019, 02:56 PM
Post#6


Access Wiki and Forums Moderator
Posts: 75,538
Joined: 19-June 07
From: SunnySandyEggo


Hi. What does this code do?
CODE
Call AbreCon(strSQL, dbOpenDynaset)
Weren't you trying to remove all linked tables from your database? If so, then using this
CODE
Set cnn = CurrentProject.Connection
would probably cause this code to fail.
CODE
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Because, if you removed all linked tables, then the CurrentProject won't have any table in it called "TblCliente."

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Joserobmoraes
post May 26 2019, 04:50 PM
Post#7



Posts: 72
Joined: 14-October 10



Well let me explain again, in a module I use this code:

Public Sub AbreCon(ByVal iSql As String, Optional Tipo = dbOpenDynaset)
'Tipo = dbOpenDynaset ... para recordsets
' dbOpenSnapshot ... para consultas

strBanco = DLookup("[Path_0]", "tblCaminhoBe")
SenhaBd = DecryptData(DLookup("Senha", "tblCaminhoBE"))

DoCmd.Hourglass True

Set db = DBEngine.OpenDatabase(strBanco, False, False, "MS Access;PWD=" & SenhaBd)

Set rs = db.OpenRecordset(iSql, Tipo, False, dbPessimistic)

DoCmd.Hourglass False

End Sub


I want to load a subform using this function

Call AbreCon

but I'm not getting popular since I'm using the connection of unbound tables

to add and edit the entries is quiet, this part I got,I use it to open the connection

Call AbreCon("Select * From TblCliente", dbOpenDynaset)


Any idea how I can load the subform?





--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 
theDBguy
post May 26 2019, 04:58 PM
Post#8


Access Wiki and Forums Moderator
Posts: 75,538
Joined: 19-June 07
From: SunnySandyEggo


Try modifying your function to return a recordset that you can use to load the subform. For example,
Public Function FunctionName(...) As DAO.Recordset

So then, you could do something like:

Me.SubformName.Recordset=FunctionName(...)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Joserobmoraes
post May 26 2019, 05:35 PM
Post#9



Posts: 72
Joined: 14-October 10



would you have any examples?

--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 
Joserobmoraes
post May 27 2019, 07:46 AM
Post#10



Posts: 72
Joined: 14-October 10



Today my insert, change and delete codes are working in form, but today my problem is loading the subform

To use the functions in the forms I use this code that is in a module called: ModConnect

Option Compare Database
Dim dbBanco As DAO.Database, rsFaved As Recordset
Dim strPath As String, PasswordBd As Variant
Public Str As String
Public strBanco As String
Public db As DAO.Database
Public rs The DAO.Recordset
Public RsDup As DAO.Recordset


Public Sub AbreCon (ByVal iSql As String, Optional Type = dbOpenDynaset)
'Type = dbOpenDynaset ... for recordsets
'dbOpenSnapshot ... for queries
strBanco = DLookup ("[Path_0]", "tblCaminhoBe")
PasswordBd = DecryptData (DLookup ("Password", "tblCaminhoBE"))
DoCmd.Hourglass True
Set db = DBEngine.OpenDatabase (strBanco, False, False, "MS Access; PWD =" & PasswordBd)
Set rs = db.OpenRecordset (iSql, Type, False, dbPessimistic)
DoCmd.Hourglass False
End Sub


Public Sub DateCon ()
DoCmd.Hourglass True
Set rs = Nothing: Close
Set db = Nothing: Close
DoCmd.Hourglass False
End Sub


As I said these command lines I use in the form, which works perfectly, my problem is loading a subform using the AbreCon ()

As an example I would have the clients table:

TblCliente

Customer_Code (Auto)
Customer_Name (Text)
Phone_Customer (Text)

Anyone have any ideas for help?

Thank you.

--------------------
Access Lean Systems
José Roberto
www.accessleansystems.com.br
Facebook Google Plus
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 11:54 AM