Referencing a field from a form to use in a SQL query

[Harm Veenstra]

I am new to "programming"in MS access and i have a small problem.

I would like to fill a record with information gathered from 3 different
things.
1. The system dat
2. The selection from a listbok with 2 columns where i use the information
in column number 2.
3. A count statement which is based on a query from the information from the
2nd column of the listbox (formfield) and the table

Below is the SQL statement i use to

SELECT Date() AS datum, Count(Klachttabel.Werkmaatschappij)+1 AS wmCnt
FROM Klachttabel
WHERE Klachttabel.Werkmaatschappij=FORMS!Afkorting;

The last section after the WHERE is causing the problem FORMS!Afkorting is
not recognized.

Can any body help me with this.

 

[TomU]

The SQL string should look like this...
"SELECT Date() AS datum, Count(Klachttabel.Werkmaatschappij)+1 AS wmCnt
FROM Klachttabel
WHERE Klachttabel.Werkmaatschappij=" & FORMS!formName!controlName;

You need to specify the form name and the control in it that contains the
value to compare to the table field Werkmaatschappij.

TomU