subform displays only one record :(

[silentwolf]

Hi again! I know it has been discussed on the net but I can still not get it to work.
I have a searchform with many different typ of txt boxes to filter a subform wich is based on a qyr_ContactAll
The subform property is set to datasheet few and when I apply a flter it shows me how many records it has found. All good!
But for some reason I can not get all records to show on the datasheet few of my subform.
Where can be the problem?
Would be nice if someone can tell me what needs to be changed in order to make this work.

 

[Norie]

It's really hard to tell without further information.

How exactly are you filtering?

What are you actually filtering?

PS I think you might have posted in the wrong place - this is for Questions in Other Languages. I'm sure admin will move it to the Access forum if you ask nicely.

 

[fairwinds]

Moved to access forum!

 

[silentwolf]

Hi guys and thanks for moving it to the right forum!!!

Here is how I did manage the code

Hi Norie!!
Nice to hear from you again!!
Well I did not even notice that I did put it into the wrong forum!
How can I get them to move it for me??
Sorry I am not that often here and I never have posted it in this forum
But I would ask nicely
Pleaaaaaaaaase

So here how I have filtered the form.

 Private myCriteria As String
 
 Private Function Filterbedingung() As String
     Dim ArgCount As Integer
    
     ' Initialisiere die Argumentenzahl.
     ArgCount = 0
     myCriteria = ""
      '*************************************************************************
     ' Nur hier muß man die Zeilen anpassen:
     ' Variable1 = Feldname im Formular in dem das Suchwort eingegeben  wird
     ' Variable2 = Feldname im Formular der gefiltert werden soll
     '                                                 (Feld aus der  Datenbank)
     ' Variable3 = Typ zum Angeben des Datenfeldes
     '             1 = Datum
     '             2 = String, alle die das Suchwort entahlten, egal wo
     '             3 = Zahl
     '             4 = String, findet nur die die genau übereinstimmen
     '             5 = ja/nein vom Typ Boolen
     ' Optional bAnd = Hier kann man dann noch angeben ob man die  Kriterien mit
     '                 AND (True) oder OR (False) verknüpfen möchte.
     '                 Wenn Argument bAnd weggelassen => mit AND  verküpft.
     ' Beispiel:
     ' SQLString Me!Sucheingabefeld, "Datenfeld", mycriteria, ArgCount, _
     '           2, False
     ' => Hier wird mit OR (Oder) verknuept.
     ' Beliebig erweiterbar, wenn man beispielsweise 5 Felder filtern
     ' will muß man die folgende Zeile gerade 5 mal kopieren und anpassen
      '*************************************************************************
     ' SQLString Variable1, Variable2, mycriteria, ArgCount, Variable3
      '*************************************************************************
 '    SQLString Me!DtDatum_Suchen, "DtDatum", myCriteria, ArgCount, 1
     SQLString Me.txt_FirmName, "Kon_FirmName", myCriteria, ArgCount, 2
     SQLString Me.txt_Nname, "Kon_Nname", myCriteria, ArgCount, 2
     SQLString Me.txt_Vname, "Kon_Vname", myCriteria, ArgCount, 2
     SQLString Me.txt_FirmName, "Kon_FirmName", myCriteria, ArgCount, 2
     SQLString Me.txt_id, "Kon_id", myCriteria, ArgCount, 3
     SQLString Me.txt_Kon_Typ, "Kon_Typ_id_f", myCriteria, ArgCount, 3
     SQLString Me.txt_Referenz, "Referenz_id_f", myCriteria, ArgCount, 3
     SQLString Me.txt_Anrede, "Anrede_id_f", myCriteria, ArgCount, 3
    
     ' Falls kein Kriterium spezifiziert wurde, gebe alle Datensätze  zurück.
     If myCriteria = "" Then myCriteria = "True"
     Filterbedingung = myCriteria
 End Function
 
 Private Sub cmd_Filter_Click()
     Me.Filter = Filterbedingung
     Me.FilterOn = True
 End Sub
 
 Private Sub cmd_FilterAus_Click()
     Me.FilterOn = False
     myCriteria = "" 'Nur wenn ihr die Variable Global speichert
     Me!txt_Anrede = Null
     Me.txt_FirmName = Null
     Me.txt_id = Null
     Me.txt_Kon_Pos = Null
     Me.txt_Kon_Typ = Null
     Me.txt_Nname = Null
     Me.txt_Referenz = Null
     Me.txt_Vname = Null
 End Sub
 
 Private Sub Form_Open(Cancel As Integer)
     DoCmd.Maximize
 End Sub

that is one part of the code and the other is in a module

 Public Sub SQLString(FieldValue As Variant, FieldName As String, _
                      Criteria As String, ArgCount As Integer, _
                      Typ As Integer, Optional bAnd As Boolean = True)
      '*************************************************************************
     'In diesem Modul muß und darf nichts geändert werden
      '*************************************************************************
     ' Erstelle Kriterien für die WHERE-Klausel.
     If Nz(FieldValue, "") <> "" Then
         If bAnd Then
             ' Füge "Und" hinzu, falls andere Kriterien vorhanden sind.
             If ArgCount > 0 Then Criteria = Criteria & " AND "
           Else
             ' Füge "Oder" hinzu, falls andere Kriterien vorhanden sind.
             If ArgCount > 0 Then Criteria = Criteria & " OR "
         End If
         Select Case Typ
           Case 1 'Datum
             Criteria = Criteria & FieldName & "= #" & _
                        Format(CDate(FieldValue), "mm-dd-yyyy") & "#"
           Case 2 'String Like
             Criteria = Criteria & FieldName & " Like '*" &  FieldValue & "*'"
           Case 3 ' Zahl
             Criteria = Criteria & FieldName & " = " &  Str(FieldValue)
                                  'Fehlendes = eingefuegt Willi Wipp  2004.10.07
                           'Fehlendes Str(...) eingefuegt Willi Wipp  2007.08.07
           Case 4 'String =
             Criteria = Criteria & FieldName & " = '" &  FieldValue & "'"
           Case 5 'Ja/nein
             If FieldValue = "Ja" Or FieldValue = "True" Or _
                FieldValue = True Then
                 Criteria = Criteria & FieldName & " = -1"
               Else
                 Criteria = Criteria & FieldName & " = 0"
             End If
         End Select
         '  Inkrementiere die Zahl der Argumente.
         ArgCount = ArgCount + 1
     End If
 End Sub

Hope my tags are still ok :[

 

[Norie]

Well the first thing you should check is whethere or not the SQL/criteria all that code is producing is correct and in the right syntax.

One thing you might need to consider is that the criteria for a filter sometimes works slightly differently than the 'normal' criteria you would use in a query.

Also, have you considered not using a filter and actually changing the record source of the subform or even using another form?

 

[silentwolf]

Norie thanks for your input!
1.) As I found that code on the net and only changed it to my needs I am not exactly sure how to check the syntax

What I have at the moment is a form called frm_CardList, this form has tab controls on it with pge_AllCards, pge_Customers, pge_Supplier, pge_Employee, pge_Personal.
For pge_AllCards I have a subfrm_CardAll, pge_Customer, subfrm_Customer, and so on.
Those subforms are based on a qyr_CardAll, qyr_Customer, qyr_Supplier... where the filter is already applied so there are only records shown like Customer on the customer Tab and so on.
What I like to do is be either able to open a search form via command button or filter the records straight from the form.
The filter should be either Id, Surname, Firstname, CompanyName.
So I hope that made it a bit more clear what I am after. As there are always so many different ways of doing things in vba I was going with what I found on the net.
But if there is a better way doing it I am very happy to hear about it!

 

[Norie]

It's not a problem using code from the net but you can't really rely on it.

Anyway you can just debug it like you would any other code.

First create some breakpoints (F9) in the code, I would suggest you do that for each of the sub/functions headers to check they are actually being run.

Then if they are being run step through the code using F8 and add watches (right click variable Add Watch...) or open the Locals Window.

That should give you a start with your code.

As for the setup, by tabs do you mean you have a form with a multitab control on it and each tab has data on it?

If you do I honestly think that's not a good set up.

What tables do you have in the database?

I'm guessing at least a customer table and supplier table.

The way I might approach that is for each of those table create a continuous form.

In recent versions of Access, eg 2010, there is a wizard for that, but it isn't too hard to do manually.

In these continuous forms I would have the main fields for each record, basically the ones that will let you identify the customer/supplier.

Sorry if I'm going off on a tangent, but that's how I would start things.

If your interested I can explain the idea further, if your not no problem.

 

[silentwolf]

Norie I do thank you for your help!
I really am struggling with debug codes and I never really do I mean I dont work with the watch windows and so on.
It just don't have any experience with it
However I thought it is called tab control? Where you have pages on the form.
Page1 Page2; and you can ad more pages on that form.
Well that is what I have (Registersteuerelement) in my lingo
I have only one table with all contacts in it and I can not really change that because each contact has an id number I am using.
So what I am doing is have a tabcontrol or multipages where I have on each tab a subform as explained before.

 

[Norie]

Even if you only have one table I wouldn't recommend using a tabbed form.

I'm not saying it won't work but I can't see any advantage of using it and can think of some disadvantages.

You've also mentioned a subform, is that subform based on the same table as the main form?

Also, why can't you have more than one table?

What's in the table exactly? Do you have multiple records for each contact?

 

[silentwolf]

Now I wrote for the last half an hour to explain it all over again and now the **** thing is lost in cyber space I could cry!!!!!!!!!!!!!!!!!!!