value in subform

[Jean-Paul]

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) = "
& Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jeff Boyce]

Jean-Paul

What happens if you take that SQL string and create a new query to paste it
into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1 FROM
[Adressen metaal] WHERE [Adressen metaal].Bezoeken = True And
DatePart('ww'; [Datum_bezoek]) = " & Me!weeknummer & " ORDER BY
[Adressen metaal].Naam;"

Errormessage remains

JP

Jean-Paul

What happens if you take that SQL string and create a new query to paste it
into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and decrease
the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) = " &
Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jeff Boyce]

Not sure why. Hopefully one of the other newsgroup readers can spot what's
happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen
metaal] WHERE [Adressen metaal].Bezoeken = True And DatePart('ww';
[Datum_bezoek]) = " & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"

Errormessage remains

JP

Jean-Paul

What happens if you take that SQL string and create a new query to paste
it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) = "
& Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jean-Paul]

Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main" form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are displayed,
NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon
opening the form... Why??

Hope you can help.
JP

Not sure why. Hopefully one of the other newsgroup readers can spot what's
happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen
metaal] WHERE [Adressen metaal].Bezoeken = True And DatePart('ww';
[Datum_bezoek]) = " & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"

Errormessage remains

JP

Jean-Paul

What happens if you take that SQL string and create a new query to paste
it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) = "
& Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jeff Boyce]

Jean-Paul

Are you saying that the subform is "connected" to the mainform ONLY via this
query? If so, that is an unusual work-around for the way Access usually
handles main form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main"
form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are displayed,
NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon opening
the form... Why??

Hope you can help.
JP

Not sure why. Hopefully one of the other newsgroup readers can spot
what's happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen
metaal] WHERE [Adressen metaal].Bezoeken = True And DatePart('ww';
[Datum_bezoek]) = " & Me!weeknummer & " ORDER BY [Adressen
metaal].Naam;"

Errormessage remains

JP

Jeff Boyce wrote:
Jean-Paul

What happens if you take that SQL string and create a new query to
paste it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) =
" & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jean-Paul]

I'm affraid so..
So please could you explain the correct way to get the job done?
I never understood very well how to achieve this so maybe... with your
help...
(All I know about acces is what I read over here, or tried it out
myself, I am a physical therapist, so this explains a lot I think)
Thanks in advance

JP

Jean-Paul

Are you saying that the subform is "connected" to the mainform ONLY via this
query? If so, that is an unusual work-around for the way Access usually
handles main form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main"
form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are displayed,
NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon opening
the form... Why??

Hope you can help.
JP

Not sure why. Hopefully one of the other newsgroup readers can spot
what's happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam, [Adressen
metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen
metaal] WHERE [Adressen metaal].Bezoeken = True And DatePart('ww';
[Datum_bezoek]) = " & Me!weeknummer & " ORDER BY [Adressen
metaal].Naam;"

Errormessage remains

JP

Jeff Boyce wrote:
Jean-Paul

What happens if you take that SQL string and create a new query to
paste it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching the
weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek]) =
" & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jeff Boyce]

Jean_Paul

The approach I'm more familiar with starts with a main form, then adds a
subform control. When you open that subform control's properties, there are
two ("parent", "child") properties that allow you to pick the field/control
in the main form which connects the subform.

This way, when a different record is selected/shown in the the main form,
the subform only displays records (from the record source for the subform)
which correspond to the selected main form record.

A common use for main form/subform construction is to allow the display of
"many" related records (via the subform) for a particular "one" record (via
the main form).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm affraid so..
So please could you explain the correct way to get the job done?
I never understood very well how to achieve this so maybe... with your
help...
(All I know about acces is what I read over here, or tried it out myself,
I am a physical therapist, so this explains a lot I think)
Thanks in advance

JP

Jean-Paul

Are you saying that the subform is "connected" to the mainform ONLY via
this query? If so, that is an unusual work-around for the way Access
usually handles main form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main"
form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current
one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are displayed,
NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon
opening the form... Why??

Hope you can help.
JP

Jeff Boyce wrote:
Not sure why. Hopefully one of the other newsgroup readers can spot
what's happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1
FROM [Adressen metaal] WHERE [Adressen metaal].Bezoeken = True And
DatePart('ww'; [Datum_bezoek]) = " & Me!weeknummer & " ORDER BY
[Adressen metaal].Naam;"

Errormessage remains

JP

Jeff Boyce wrote:
Jean-Paul

What happens if you take that SQL string and create a new query to
paste it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching
the weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek])
= " & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jean-Paul]

Thanks again for your kind reply,
Alas
I don't see any "parent","child" propertie...
Could you tell me where to find? this might be the solution....

Greets
JP

Jean_Paul

The approach I'm more familiar with starts with a main form, then adds a
subform control. When you open that subform control's properties, there are
two ("parent", "child") properties that allow you to pick the field/control
in the main form which connects the subform.

This way, when a different record is selected/shown in the the main form,
the subform only displays records (from the record source for the subform)
which correspond to the selected main form record.

A common use for main form/subform construction is to allow the display of
"many" related records (via the subform) for a particular "one" record (via
the main form).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm affraid so..
So please could you explain the correct way to get the job done?
I never understood very well how to achieve this so maybe... with your
help...
(All I know about acces is what I read over here, or tried it out myself,
I am a physical therapist, so this explains a lot I think)
Thanks in advance

JP

Jean-Paul

Are you saying that the subform is "connected" to the mainform ONLY via
this query? If so, that is an unusual work-around for the way Access
usually handles main form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main"
form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current
one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are displayed,
NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon
opening the form... Why??

Hope you can help.
JP

Jeff Boyce wrote:
Not sure why. Hopefully one of the other newsgroup readers can spot
what's happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1
FROM [Adressen metaal] WHERE [Adressen metaal].Bezoeken = True And
DatePart('ww'; [Datum_bezoek]) = " & Me!weeknummer & " ORDER BY
[Adressen metaal].Naam;"

Errormessage remains

JP

Jeff Boyce wrote:
Jean-Paul

What happens if you take that SQL string and create a new query to
paste it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching
the weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal] WHERE
[Adressen metaal].Bezoeken = True And DatePart('ww'; [Datum_bezoek])
= " & Me!weeknummer & " ORDER BY [Adressen metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP

 

[Jeff Boyce]

Jean-Paul

Open the main form in design view.

Highlight the control that holds the sub-form.

Open the properties of that control.

("where" you see the properties may depend on which version of Access)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks again for your kind reply,
Alas
I don't see any "parent","child" propertie...
Could you tell me where to find? this might be the solution....

Greets
JP

Jean_Paul

The approach I'm more familiar with starts with a main form, then adds a
subform control. When you open that subform control's properties, there
are two ("parent", "child") properties that allow you to pick the
field/control in the main form which connects the subform.

This way, when a different record is selected/shown in the the main form,
the subform only displays records (from the record source for the
subform) which correspond to the selected main form record.

A common use for main form/subform construction is to allow the display
of "many" related records (via the subform) for a particular "one" record
(via the main form).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm affraid so..
So please could you explain the correct way to get the job done?
I never understood very well how to achieve this so maybe... with your
help...
(All I know about acces is what I read over here, or tried it out
myself, I am a physical therapist, so this explains a lot I think)
Thanks in advance

JP

Jeff Boyce wrote:
Jean-Paul

Are you saying that the subform is "connected" to the mainform ONLY via
this query? If so, that is an unusual work-around for the way Access
usually handles main form/subform construction.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff for your reply...
But, I have to clearify a bit...
This is a situation that puzzles me for a while already...

I have a form.
On this form an entryfield and a subform.

This subform is a continuous form.

This subform is based upon a recordset, based upon a query
In this query I have a a command that refers to the entryfield of the
"main"
form((([forms].[visited].[weeknumber])=DatePart("ww",[Datum_visit])))

In my case the entryfield are the weeknumbers... default, the current
one
Now,
When I open the main form, the entryfield shows the current weeknumber
(which is correct), the subform doesn't show anything (which is wrong)

next to the weeknumber I have a "-" pushbutton.
Clicking this decreases the weeknumber with 1
there is also a "+" pushbutton to increase with 1

When I click - or the + THEN the correct continuous forms are
displayed, NOT upon opening.
I use the command:
Me!weeknumber = Me!weeknumber - 1
If Me!weeknumber = 0 Then
Me!weeknumber = 52
End If
Me!Week_sub_bezoek.Requery

for the "-" button.

What confuses me is: the query is correct but doesn't trigger upon
opening the form... Why??

Hope you can help.
JP

Jeff Boyce wrote:
Not sure why. Hopefully one of the other newsgroup readers can spot
what's happening here...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Had to modify a bit...
New sql=

sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].bezoeken, DatePart('ww';[Datum_bezoek]) AS Expr1
FROM [Adressen metaal] WHERE [Adressen metaal].Bezoeken = True And
DatePart('ww'; [Datum_bezoek]) = " & Me!weeknummer & " ORDER BY
[Adressen metaal].Naam;"

Errormessage remains

JP

Jeff Boyce wrote:
Jean-Paul

What happens if you take that SQL string and create a new query to
paste it into? Does it run?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with a number (=the number of the current week)
Next to this number I have 2 pushbuttons: + and - to increase and
decrease the number (getting next or previous week.

On this form I created a subform. I get data from a table matching
the weeknumber.

When I click (as an example) the - pushbutton, I wrote

Me!weeknummer = Me!weeknummer - 1
If Me!weeknummer = 0 Then
Me!weeknummer = 52
End If
Dim sql As String
sql = "SELECT [Adressen metaal].*, [Adressen metaal].Naam,
[Adressen metaal].Datum_bezoek, [Adressen metaal].bezoeken,
DatePart('ww';[Datum_bezoek]) AS Expr1 FROM [Adressen metaal]
WHERE [Adressen metaal].Bezoeken = True And DatePart('ww';
[Datum_bezoek]) = " & Me!weeknummer & " ORDER BY [Adressen
metaal].Naam;"
Forms![bezochten].Form!Week_sub_bezoek.RecordSource = sql

I get an error on the last line saying
the object doesn't support this...

How to solve this problem?

Thanks
JP