VBA RecorSet viewing on a form

I am constructing a recordset from a Table using several computed criteria. This recordset is built from a number of sql statemens using operator such as "Like".

I need to present the final RST as a set of lines in a form (actually a subform from the main form wher I do all the calculations. So I designed the subform and inserted in on the main form.
However, of, course, there are no linking fiels whatsoever.

How do I do to present the RST?
João serras-pereiraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
IrogSintaConnect With a Mentor Commented:
Having "TRUE AND" is fine even for a single criteria.  I just gave you bad code that's why it errored.  If it's a number field, you needed use the last code in my previous post.
0
 
IrogSintaConnect With a Mentor Commented:
If I understand you correctly, you created a SQL statement in a string variable and you want to use this as the RecordSource for your subform.  If that is that case then try this:

Create a query and name it qryTempSQL.  It doesn't matter what the query is, you'll replace its SQL code later.

In your form, after the code for creating your SQL string, add the following lines (assuming your string variable is called strSQL):
qryTempSQL.SQL = strSQL
Me. NameOfSubform.Form.RecordSource = "qryTempSQL"
0
 
mbizupCommented:
Can you post a sample of your database, including your recordset code so that we can take a look at it?

Just curious whether there is an easier/better way to do this.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
João serras-pereiraAuthor Commented:
sure!

The Database is attached (please not that this is still an "on development" database. The Master Form is F0303... and the SubForm is F0304.

If you serach in the code for "EEXG" you'll find the piece of code where I am preparing the SQL statement
CDI.06.07.accdb
0
 
João serras-pereiraAuthor Commented:
I suppose that we need to find another way to sort out the problem.
I duly modified the database and got the error that I amshoing in the screenshots. apparently the query object is not defined. I suppose that the only thing that would be required is to find a manner to set the Control Source of the form to the Record Set that would result from the SQL, no?

screen 1screen 2screen 3
0
 
IrogSintaConnect With a Mentor Commented:
Sorry about that.  I gave you incorrect code.  It should be:

CurrentDb.QueryDefs("Q00_Ficha_Temporaria").SQL = srcStrSQL
0
 
João serras-pereiraAuthor Commented:
Getting close!

Now I am getting "error 3265, item not found in this colletion"

the code now is:


    ' construct a query with  the selected fields and options
    ' EEXG
   
    srcStrSQL = ""
   
    If Nz(Me.F0303_DocID, "") <> "" Then
        srcStrSQL = "(T03_FichaDocumentoSet!T03_DocID Like ""*" & Me.F0303_DocID & "*"")"
    End If
   
    If Nz(Me.F0303_Assento, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_0102_Assento Like ""*" & Me.F0303_Assento & "*"")"
    End If
   
    If Nz(Me.F0303_Acronimo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_0301_Acronimo Like ""*" & Me.F0303_Acronimo & "*"")"
    End If
     
    If Nz(Me.F0303_Expedidor, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_2101_Expedidor Like ""*" & Me.F0303_Expedidor & "*"")"
    End If
   
    If Nz(Me.F0303_DestinatarioInterno, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_2102_DestinatarioInterno Like ""*" & Me.F0303_DestinatarioInterno & "*"")"
    End If
   
    If Nz(Me.F0303_Referencia, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_0101_Referencia Like ""*" & Me.F0303_Referencia & "*"")"
    End If
   
    If Nz(Me.F0303_Titulo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet!T03_0401_Titulo Like ""*" & Me.F0303_Titulo & "*"")"
    End If
   
   
   
    CurrentDb.QueryDefs("Q00_Ficha_Temporaria").SQL = srcStrSQL   <---- problem
    Forms!F0303_RegistosIn_EditFull.RecordSource = "Q00_FichaTemporaria"

    T00_ControlTableSet.Close
    T03_FichaDocumentoSet.Close
   
    Set ControlTableSet = Nothing
    Set FichaDocumentoSet = Nothing
   

'------------------------------------------------------------------------------------------------------


The value for srcStrSQL is as you can see on the snapshot:

code with problem
0
 
IrogSintaConnect With a Mentor Commented:
Replace the exclamation marks in your SQL builder went periods.
0
 
João serras-pereiraAuthor Commented:
Getting closer but till scratching my head...
Now I have updated the code to this, so the recordset is named and properly open, but got another error:

(on the screenshots)

:

Private Sub F0303_Btn_ProcurarRegisto_Click()
   ' procurar o conjunto de registos que correspondem aos critérios preechidos
    ' a procura é baseada em critérios aproximados em qualquer parte dos campos e não em critérios absolutos
   
   
    Dim cdiDB As DAO.Database

    Dim T00_ControlTableSet As DAO.Recordset, _
        T03_FichaDocumentoSet As DAO.Recordset

    Dim Found As Boolean, _
        IBool As Boolean
       
    Dim srcDate As Date
   
    Dim I As Integer, _
        Mf As Integer, _
        Ipos As Integer, _
        NumDias As Integer, _
        L As Integer, _
        AcaoAtribuicao As Integer
   
    Dim srcStrSQL As String
   
    Set cdiDB = CurrentDb
    Set T00_ControlTableSet = cdiDB.OpenRecordset("T00_ControlTable", dbOpenDynaset)
    Set T03_FichaDocumentoSet = cdiDB.OpenRecordset("T03_FichaDocumento", dbOpenDynaset)
     
    T00_ControlTableSet.Edit
    T03_FichaDocumentoSet.Edit
   
    ' construct a query with  the selected fields and options
    ' EEXG
   
    srcStrSQL = ""
   
    If Nz(Me.F0303_DocID, "") <> "" Then
        srcStrSQL = "(T03_FichaDocumentoSet.T03_DocID Like ""*" & Me.F0303_DocID & "*"")"
    End If
   
    If Nz(Me.F0303_Assento, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0102_Assento Like ""*" & Me.F0303_Assento & "*"")"
    End If
   
    If Nz(Me.F0303_Acronimo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0301_Acronimo Like ""*" & Me.F0303_Acronimo & "*"")"
    End If
     
    If Nz(Me.F0303_Expedidor, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2101_Expedidor Like ""*" & Me.F0303_Expedidor & "*"")"
    End If
   
    If Nz(Me.F0303_DestinatarioInterno, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2102_DestinatarioInterno Like ""*" & Me.F0303_DestinatarioInterno & "*"")"
    End If
   
    If Nz(Me.F0303_Referencia, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0101_Referencia Like ""*" & Me.F0303_Referencia & "*"")"
    End If
   
    If Nz(Me.F0303_Titulo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0401_Titulo Like ""*" & Me.F0303_Titulo & "*"")"
    End If
   
   
    If srcStrSQL <> "" Then
        cdiDB.QueryDefs("Q00_Ficha_Temporaria").SQL = srcStrSQL
        Forms!F0303_RegistosIn_EditFull.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_Ficha_Temporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
    End If

    T00_ControlTableSet.Close
    T03_FichaDocumentoSet.Close
   
    Set ControlTableSet = Nothing
    Set FichaDocumentoSet = Nothing
   
End Sub

screen 1

screen2
0
 
IrogSintaCommented:
You did create a query called Q00_Ficha_Temporaria, right?
Also, in your query construction, I don't see where you are creating the beginning part of the query (i.e. SELECT * FROM etc.).  All I see is the WHERE part of the query.
0
 
João serras-pereiraAuthor Commented:
You were right. This piece of code was developed by me 2.5 years ago but using it as a FORM FILTER so I did not put the "SELECT....".

Now I have inserted, and tried to correct the syntax according to your reccomendation.

However, something is still wrong as now the errorcode is different it states that the query does not exist.

By looking at the snippets you may check that the query is really there (!)

However, I am surely doing something wrong as now, when separately launching the query, I am getting another error (snippet 4)


The full code, now is:


Private Sub F0303_Btn_ProcurarRegisto_Click()
   ' procurar o conjunto de registos que correspondem aos critérios preechidos
    ' a procura é baseada em critérios aproximados em qualquer parte dos campos e não em critérios absolutos
   
   
    Dim cdiDB As DAO.Database

    Dim T00_ControlTableSet As DAO.Recordset, _
        T03_FichaDocumentoSet As DAO.Recordset

    Dim Found As Boolean, _
        IBool As Boolean
       
    Dim srcDate As Date
   
    Dim I As Integer, _
        Mf As Integer, _
        Ipos As Integer, _
        NumDias As Integer, _
        L As Integer, _
        AcaoAtribuicao As Integer
   
    Dim srcStrSQL As String
   
    Set cdiDB = CurrentDb
    Set T00_ControlTableSet = cdiDB.OpenRecordset("T00_ControlTable", dbOpenDynaset)
    Set T03_FichaDocumentoSet = cdiDB.OpenRecordset("T03_FichaDocumento", dbOpenDynaset)
     
    T00_ControlTableSet.Edit
    T03_FichaDocumentoSet.Edit
   
    ' construct a query with  the selected fields and options
    ' EEXG
   
    srcStrSQL = "SELECT * FROM T03_FichaDocumentoSet WHERE "
   
    If Nz(Me.F0303_DocID, "") <> "" Then
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_DocID Like ""*" & Me.F0303_DocID & "*"")"
    End If
   
    If Nz(Me.F0303_Assento, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0102_Assento Like ""*" & Me.F0303_Assento & "*"")"
    End If
   
    If Nz(Me.F0303_Acronimo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0301_Acronimo Like ""*" & Me.F0303_Acronimo & "*"")"
    End If
     
    If Nz(Me.F0303_Expedidor, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2101_Expedidor Like ""*" & Me.F0303_Expedidor & "*"")"
    End If
   
    If Nz(Me.F0303_DestinatarioInterno, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2102_DestinatarioInterno Like ""*" & Me.F0303_DestinatarioInterno & "*"")"
    End If
   
    If Nz(Me.F0303_Referencia, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0101_Referencia Like ""*" & Me.F0303_Referencia & "*"")"
    End If
   
    If Nz(Me.F0303_Titulo, "") <> "" Then
        If srcStrSQL <> "" Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0401_Titulo Like ""*" & Me.F0303_Titulo & "*"")"
    End If
   
    Forms![F91_LoggedUser]![CurrentUserID] = ""
    'Me.NameOfSubform.Form.RecordSource = "qryTempSQL"

    If srcStrSQL <> "" Then
        cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL
        Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
    End If

    T00_ControlTableSet.Close
    T03_FichaDocumentoSet.Close
   
    Set ControlTableSet = Nothing
    Set FichaDocumentoSet = Nothing
   
End Sub


sn 1


sn 2



sn 3

sn 4
0
 
IrogSintaConnect With a Mentor Commented:
I see a few problems with your code.   But let me ask you first, what if your Me.F0303_DocID =""
In that case your SQL string would end up like
SELECT * FROM T03_FichaDocumento WHERE AND...
0
 
João serras-pereiraAuthor Commented:
You are absolutely right.
this is code on dvelopment and, initially I had srcStrSQL = ""

then I changed, on the last interaction to

  srcStrSQL = "SELECT * FROM T03_FichaDocumentoSet WHERE "

but forgot to update the last if...

...
...
    If srcStrSQL <> "" Then
        cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL
        Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
    End If
0
 
IrogSintaConnect With a Mentor Commented:
Okay, revise your code this way:

Private Sub F0303_Btn_ProcurarRegisto_Click()
   ' procurar o conjunto de registos que correspondem aos critérios preechidos
    ' a procura é baseada em critérios aproximados em qualquer parte dos campos e não em critérios absolutos
    
    
    Dim cdiDB As DAO.DATABASE
    Dim Found As Boolean, _
        IBool As Boolean
        
    Dim srcDate As Date
    
    Dim I As Integer, _
        Mf As Integer, _
        Ipos As Integer, _
        NumDias As Integer, _
        L As Integer, _
        AcaoAtribuicao As Integer
    
    Dim srcStrSQL As String
    
    Set cdiDB = CurrentD

    
    ' construct a query with  the selected fields and options
    srcStrSQL = "SELECT * FROM T03_FichaDocumento WHERE TRUE "
    
    If Nz(Me.F0303_DocID, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_DocID Like '*" & Me.F0303_DocID & "*')"
    End If
    
    If Nz(Me.F0303_Assento, "") <> "" Then
         srcStrSQL = srcStrSQL & " AND (T03_0102_Assento Like '*" & Me.F0303_Assento & "*')"
    End If
    
    If Nz(Me.F0303_Acronimo, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_0301_Acronimo Like '*" & Me.F0303_Acronimo & "*')"
    End If
     
    If Nz(Me.F0303_Expedidor, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_2101_Expedidor Like '*" & Me.F0303_Expedidor & "*')"
    End If
    
    If Nz(Me.F0303_DestinatarioInterno, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_2102_DestinatarioInterno Like '*" & Me.F0303_DestinatarioInterno & "*')"
    End If
    
    If Nz(Me.F0303_Referencia, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_0101_Referencia Like '*" & Me.F0303_Referencia & "*')"
    End If
    
    If Nz(Me.F0303_Titulo, "") <> "" Then
        srcStrSQL = srcStrSQL & " AND (T03_0401_Titulo Like '*" & Me.F0303_Titulo & "*')"
    End If
    
    Forms![F91_LoggedUser]![CurrentUserID] = ""


    If srcStrSQL <> "" Then
        cdiDB.QueryDefs("Q00_FichaTemporaria").sql = srcStrSQL
        Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
    End If

End Sub

Open in new window

0
 
João serras-pereiraAuthor Commented:
Dear IrogSinta,

I am starting to owe a bit much to you.

But I am still a problem remains (please check the screenshots)

I reckon the my code is really off track.

I am including the revised code according to your instructions.
I suppose that the problem is the way the SQL is constructed, mixing columns of type text and integer, leading to invalid syntaxes.

Please just take a quick look to check that and reccomend a line of action so I, may correct. I think that I am using ["] wher I should be using ['] no?

 




ss1

ss 2

Code:


'-------------------------------------------------------------------------------------------------------------------------
Private Sub F0303_Btn_ProcurarRegisto_Click()

    Dim cdiDB As DAO.Database

    Dim T00_ControlTableSet As DAO.Recordset, _
        T03_FichaDocumentoSet As DAO.Recordset

    Dim Found As Boolean, _
        IsSearch As Boolean, _
        IBool As Boolean
       
    Dim srcDate As Date
   
    Dim I As Integer, _
        Mf As Integer, _
        Ipos As Integer, _
        NumDias As Integer, _
        L As Integer, _
        AcaoAtribuicao As Integer
   
    Dim srcStrSQL As String
   
    Set cdiDB = CurrentDb
    Set T00_ControlTableSet = cdiDB.OpenRecordset("T00_ControlTable", dbOpenDynaset)
    Set T03_FichaDocumentoSet = cdiDB.OpenRecordset("T03_FichaDocumento", dbOpenDynaset)
     
    T00_ControlTableSet.Edit
    T03_FichaDocumentoSet.Edit
   
    IsSearch = False
    srcStrSQL = "SELECT * FROM T03_FichaDocumento WHERE TRUE "

   
    If Nz(Me.F0303_DocID, "") <> "" Then
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_DocID Like ""*" & Me.F0303_DocID & "*"")"
        IsSearch = True
    End If
   
    If Nz(Me.F0303_Assento, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0102_Assento Like ""*" & Me.F0303_Assento & "*"")"
        IsSearch = True
    End If
   
    If Nz(Me.F0303_Acronimo, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0301_Acronimo Like ""*" & Me.F0303_Acronimo & "*"")"
        IsSearch = True
    End If
     
    If Nz(Me.F0303_Expedidor, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2101_Expedidor Like ""*" & Me.F0303_Expedidor & "*"")"
        IsSearch = True
    End If
   
    If Nz(Me.F0303_DestinatarioInterno, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_2102_DestinatarioInterno Like ""*" & Me.F0303_DestinatarioInterno & "*"")"
        IsSearch = True
    End If
   
    If Nz(Me.F0303_Referencia, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0101_Referencia Like ""*" & Me.F0303_Referencia & "*"")"
        IsSearch = True
    End If
   
    If Nz(Me.F0303_Titulo, "") <> "" Then
        If IsSearch Then
            srcStrSQL = srcStrSQL & " AND "
        End If
        srcStrSQL = srcStrSQL & "(T03_FichaDocumentoSet.T03_0401_Titulo Like ""*" & Me.F0303_Titulo & "*"")"
        IsSearch = True
    End If
   
    MsgBox (srcStrSQL)
   
    If IsSearch Then
        cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL
        Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
    End If

   
End Sub

'------------------------------------------------------------------------------------------------------------------------
0
 
IrogSintaCommented:
Are you sure you used the revised code? The code you posted is still your old code.
0
 
João serras-pereiraAuthor Commented:
Yeap... I am pretty much positive on that (it shows on the previous message - ID: 38356770)

You now see a new boolean variable [IsSearch] that accounts for the situation is the srcStrSQL is modified or not. And you have a MsgBox showing the content of the string (with the snippet) on the screenshot.

But I think that the srcStrSQL string is reaally badly constructed by me, only I do not have the skill to makee it proper.
0
 
IrogSintaCommented:
I meant for you to replace all your code with the one I gave you instead.  Just use copy and paste.
0
 
IrogSintaCommented:
Let me explain briefly the changes I have in my code:

I added WHERE TRUE to the end of srcStrSQL so that you don't have to worry about adding "AND".  That's why you don't see the If..Then statements.
I removed T00_ControlTableSet since it is not being used.
I removed T03_FichaDocumentoSet since it was being used incorrectly.
I use single quotes instead of double quotes inside your SQL string.

Again, just try copying and pasting it over your code and see what the result is.
0
 
João serras-pereiraAuthor Commented:
Well - A really BIG thanks.


(sorry for taking so long to respond, I do live in west africa and had a power shtudown for 5 hours)

I have just added a

Me.F0304_ListaRegistosIn.Form.Recalc

after running the query

It now opens and the query runs perfectly. I reckon that, on the code:


  If srcStrSQL <> "" Then
        cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL
        Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
        Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
        Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
        Me.F0304_ListaRegistosIn.Form.Recalc
    End If

I can just delete the "if", no? srcStrSQL will never be ""....


Depending on criteria it either shows or not the selected records. You may see on the screenshot.

The only problem I have remaining on this is that, when I enter the master form or when I, for instance click the search button the subform should be empty.

Any clues on how to do it?

(I have manually tried to delete de query result content, but, of course it will try to delete the original table as well... whicch I do not want

I am sending again a screenshot so you may see


ss
0
 
IrogSintaCommented:
You're right, you can just remove the If.. Then portion.  I missed that.  You can change that so that your list will be empty when there are no selections to:
If right(srcStrSQL, 11) = "WHERE TRUE " Then
    srcStrSQL="SELECT * FROM T03_FichaDocumento WHERE T03_DocID='zzzz'"
End If

cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL
Me.F0304_ListaRegistosIn.Form.RecordSource = "Q00_FichaTemporaria"
Set Q00_FichasSelecionadasDef = cdiDB.QueryDefs("Q00_FichaTemporaria")
Set Q00_FichasSelecionadasSet = Q00_FichasSelecionadasDef.OpenRecordset(dbOpenDynaset)
Me.F0304_ListaRegistosIn.Form.Recalc

Open in new window

I used zzzz but you could put whatever you want as long as there will never be a DocID with this.
0
 
João serras-pereiraAuthor Commented:
Well.... Thanks again for the code.

Bit still something is not right.

When starting I am getting a "Type Mismatch" error code.
Then, I went to the Q00 query and found that, because docID is a number, on the selection criteria, for a number, it is using the "zzzz..." so a changed the "zzzzz" by a number and then got the same problem.

I think that, when a I need to add criteria for numbers I should delete the ['], no?
0
 
IrogSintaConnect With a Mentor Commented:
Yes, any fields that are set to Number data type should not have single quotes in the criterias.  So your line for DocID should be:
srcStrSQL = srcStrSQL & " AND (T03_DocID Like *" & Me.F0303_DocID & "*)"

An instead of zzzz, you need to use a number that would never be in a docID
srcStrSQL="SELECT * FROM T03_FichaDocumento WHERE T03_DocID=999999"

I'm concerned about your use of wildcards for DocID.  Did you really want to return records that contain the number in Me.F0303_DocID?  For example, if the user entered the number 23, using a wildcard will return records such as 123, 230, 102359.  I'm inclined to think that your statement should really be:
srcStrSQL = srcStrSQL & " AND (T03_DocID = " & Me.F0303_DocID & ")"
0
 
João serras-pereiraAuthor Commented:
Ok. Once again thanks.

I have pasted in your suggestion but still got an error on the expression. It stops at the line:


   cdiDB.QueryDefs("Q00_FichaTemporaria").SQL = srcStrSQL


Run Time error 3075 (mission operator) ...

I am attaching the snippet

Maybe, when there is a single field to search, then we should omit both the TRUE and the AND?

Anyway your comment is really good and I am accepting it. The initial idea was to have the resulting records as you describe. But being numerical, it will be much bettter to generate a SQL statement as for dates, something like "greater than and lesser than"

ss
0
 
João serras-pereiraAuthor Commented:
Dear IrogSinta -

It is working PERFECTLY. I would really like to compensate you for the tremendous additionale effort that you dedicated to this situation. I am a real SQL rookie (never could understand the differences from the " and the ' (in the old days we used to just use eitherdouble "or double ' '. Now there is a combination...

I tried to google on this "TRUE" to learn a bit more but could find no good article on it.

anyway many thanks and I would really like to compensame more than this 500 limit points (you really earned them and much more).
Anyway, this project is continuing and I believe that I will need further help before its completion.

Now I am going to finalise this routine code (there is no point in putting the selection of a number in pair with the other "LIKE" selections, put date range selection, etc.

Once again a BIG THANKS!!!!
0
 
IrogSintaCommented:
Let me explain the TRUE.  Each of the criteria in your SQL statement evaluates to either TRUE or FALSE depending on whether a match is found or not.

(T03_DocID  = " & Me.F0303_DocID & ")"
(T03_0102_Assento Like '*" & Me.F0303_Assento & "*')"

Instead of using TRUE, I could have added 1=1
SELECT * FROM T03_FichaDocumento WHERE (1=1) AND (T03_DocID=100)

Open in new window

Since 1 equals 1, that evaluates to TRUE, so I just used TRUE instead.
I hope that clears it up a bit for you.
0
 
João serras-pereiraAuthor Commented:
'got it and thanks again. Sorry to caus so much trouble and work
0
 
IrogSintaCommented:
Not a problem.  You're quite welcome.
0
All Courses

From novice to tech pro — start learning today.