SQL forespørgsel i stedet for tabel som datakilde

It should be possible to use either a table or SQL as the records source to your main form, so I suspect that your SQL is wrong.

I cant say that I have ever needed to filer the sub form, but I would think it is possible to use something similar to your example above to filter the sub form

hmmmm....I don't mean.....first filter the main form....then the sub form....no, I want to filter BOTH at the same time!
I have made a filterform, on which there are fields from both the main form and the sub form.

Mainform fields:
Supplier
Producer
Product no.
Invoice number
Batch number

Sub form:
Test no (from 1 to 8)
Weight
Width
Length
Height
Defects


For every record on the main form, I have 8 on the subform and the user might need all records from the main form, where Height between 100 and 1000.

The best would be to make it as a SQL query instead of the tables.....sigh.

Then the SQL which selects the records for the main form alos needs to look at the related records to do this.

SELECT * FROM YourMainTable WHERE ID IN (SELECT ID FROM YourSubTable WHERE Height Between 100 and 1000)

or something like that

try using the query builder to help find the correct syntax.

With a query I get this error:
Run-time error: '-2147352567 (80020009)'
Poster i tabellen "minTabel - underformular" vil ikke have nogen poster på "en"-siden.

SELECT Dato, Tidspunkt, Leverandoer, Producent, Fakturanr, Partinr, Varenr, Deltagere, Bemaerkninger, Godkendt, Signatur, Afvist, [underformular].Proevenr, [underformular].Blokkode, [underformular].Blokvaegt, [underformular].Laengde, [underformular].Bredde, [underformular].Hoejde, [underformular].KantHjoerneFejl, [underformular].[Islommer-lille], [underformular].[Islommer-mellem], [underformular].[Islommer-stor], [underformular].[Huller-lille], [underformular].[Huller-mellem], [underformular].[Huller-stor], [underformular].Karton, [underformular].Pakkemetode, [underformular].Dryptab, [underformular].VisuelBedoemmelse, [underformular].Afskinding, [underformular].[Farlige ben], [underformular].[Farlige finner], [underformular].[Smaa finner], [underformular].[Bloede ben], [underformular].Defekter, [underformular].[Parasitter-blege], [underformular].[Parasitter-synlige], [underformular].Lugt, [underformular].Smag, [underformular].Konsistens, [underformular].[Konsistens-fortegn], [underformular].Farve, [underformular].Totalkim, [underformular].[Totalkim-tegn], [underformular].Coliforme, [underformular].[Coliforme-tegn], [underformular].[E-Coli], [underformular].[E-Coli-tegn], [underformular].Streptokokker, [underformular].[Streptokokker-tegn], [underformular].Stafylokokker, [underformular].[Stafylokokker-tegn]
FROM [Kontrol af blokke] INNER JOIN [underformular] ON (Dato = [underformular].Dato) AND (Tidspunkt = [underformular].Tidspunkt);

I was thinking about doing it as a normal form and drop the main/sub form design.....(the rest of the system is also single-form based, so....), but will it solve my problem?

the SQL you use for the main form should only select fields FROM the main table

But then I can't search the subform...

thats another question :o)

It may be easier to make a select where you have main/sub records JOINED in a detail, but without a better understanding of your requirement then it isnt easy to give a solution.

Well, I have a mainform with 11 fields.
Each record on the mainform, has 8 records on the subform. each record on the subform has 33 fields! :(

It is possible to filter the main form in the manner I suggested. > .. WHERE ID  IN (Select....) where the sub select is built dynamically depending on your search criteria.

WHY does the main form contain records from the sub form? This WILL (can) give you too many records.

Well, it doesn't, guess I wasn't clear enough.
Lets pretend that these are orders instead.
Each customer is a record on the main form.
Each order is a record on the sub form.
Each customer has 8 orders.

Customer info consist of 11 fields.
Orders consist of 33 fields.

My filterform/searchform then filters the data.
For instance, I might want to apply a filter that shows me all the customers that have bought goods worth of £30. the price is related to the order, which is on the subform, and thus I need to filter data on that form.

Kind of like when you open Northwind and look at Kategorier

Sorry, that should have been Ordrer i Northwind
!

Look at this SQL
SELECT * FROM YourMainTable WHERE ID IN (SELECT ID FROM YourSubTable WHERE Height Between 100 and 1000)

The SUB SELECT needs to be built dynamically (in code) depending on the search criteria you enter. In the eaxmple it will be WHERE Height .... which will change dependin on your search criteria.

It will find the ID (foreign key) of ALL records in the (sub) Order table which match the criteria. Then the main table will only show records where the ID (primary key) = Those found IN the sub select.

This will filter records on the main form and the sub form because the two tables are related by ID!

The problem is also that the system is build as subforms in a main form and that these forms don't have subforms themselves. In these forms this code works fine

  With Forms("Indtast")!underformular.Form
    .filter = FilterText
    .OrderBy = "dato"
    .OrderByOn = True
    .FilterOn = DoFilter
  End With

However this new form I'm adding does have a subform....so, you see, the code can filter a "standalone" form perfect, but not a main/sub form constellation

I am very sure I know what you want and I am also sure that the solution (example) I have given can bu used but it WILL require building the main forms records source dynamically.

Sigh, it gets even worse....the guy who designed the system before me, uses the name of the Kontrolkildeelement as part of the name of the reports, so if I use YourMaintable, it still works, but when I use a query it's all down the drain

Keep the query for the report and use SQL in the form

I think I have the solution, just need to "build" it first. (and perhaps it's the same as you were trying to tell me...)

My searchform generates a string, which I use to filter forms with:
With Forms("Indtast")!underformular.Form
    .filter = FilterText
    .OrderBy = "dato"
    .OrderByOn = True
    .FilterOn = DoFilter
  End With

This filterstring either contains data from the mainform or the subform or both.
I just need a query that selects a unique ID from my main table, in this case it's Dato + Tidspunkt that makes up the primary key and append the filterstring to this.
The result, a date and a time, I can use to build another FilterText string and apply this to the main form (it doesn't matter about the subform, cause the steps above will give me records in the mainform, to which a particular subform record belongs, right?

sounds OK, but as I cant see what you have then I cant be sure.
But If you are selecting fields from the main table and the sub table in you rmain form then it may NOT easy to get a unique ID.

Hmmm...not sure what you mean, but I don't think I am.

here's the code so far:

  Dim SQL As String, rst As Recordset
  SQL = "SELECT [Kontrol af blokke].Dato, [Kontrol af blokke].[Tidspunkt] FROM [Kontrol af blokke] INNER JOIN [Kontrol af blokke - underformular] ON ([Kontrol af blokke].Tidspunkt = [Kontrol af blokke - underformular].Tidspunkt) AND ([Kontrol af blokke].Dato = [Kontrol af blokke - underformular].Dato) WHERE " & FilterText
  Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
  While Not rst.EOF  'test to see if results fits the bill
    Debug.Print rst("Dato")
    Debug.Print rst("Tidspunkt")
  Next
  With Forms("indtast")!underformular.Form
    .filter = FilterText
    .OrderBy = "dato"
    .OrderByOn = True
    .FilterOn = DoFilter
  End With

Now I need to make the FilterText string look like this:
([dato] between #02-23-05# and #02-24-05#) and (([Tidspunkt] like '09:24:00'))