Update query and filter by selection

  • Thread starter Thread starter Lasse T
  • Start date Start date
L

Lasse T

Hi !

I´m using filter by selection in a form. When I have filtered out any
unwanted data i want to run an update query that updates one field based on
a text box in the form.
In other words. How do I "pick up" the current filter from the form and
apply the same filter in the update query.

Thanks in advance

Lasse T
-------------
 
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE MyTable SET MyField = 999 WHERE " & Me.Filter & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
End If
 
Thank you för your answer. I´m afraid I don´t quite know how to use this.
Is it updating the table without using an update query?
I tried it on the onklick of a button but I get error saying something about
needing more criterias.
The Me.filter shows the correct filter. Could I not use that in some way in
the criteria grid of the update query?

Lasse T
-------------
 
Set the On Click property of your button to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Paste the code between the "Private Sub..." and "End Sub" lines.

Yes: it runs the query directly from the code, without the saved query.
 
Ok. That is what I am trying to do.
I get error "3061"
Too few parameters 1 is expected. (translation from swedish)
The line: "dbEngine(0)(0).Execute strSQL, dbFailOnError" are yellow.

Names: Table: "Artiklar", form: "VisaGrupperAlla", the field to be updated:
"Palagg", and the field that holds the new value: (in the form
"VisaGrupperAlla) "PalaggKr"

Lasse T
---------------

Allen Browne said:
Set the On Click property of your button to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Paste the code between the "Private Sub..." and "End Sub" lines.

Yes: it runs the query directly from the code, without the saved query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lasse T said:
Thank you fvr your answer. I4m afraid I don4t quite know how to use this.
Is it updating the table without using an update query?
I tried it on the onklick of a button but I get error saying something about
needing more criterias.
The Me.filter shows the correct filter. Could I not use that in some way in
the criteria grid of the update query?

Lasse T
Click to expand...
Click to expand...
 
Allen said:
Yes: it runs the query directly from the code, without the saved query.
Click to expand...

I always take the term 'query' as somewhat similar to 'question' so the
expression 'to run a query' keeps bumping my eye. UPDATE really is a
command; it is in Access that we call all DML statements queries.

I think that causes a possible unclarity here.
 
Hi Lasse,

You would need to use VBA code to do something like that, example:

Private sub command1_click

Currentdb.Execute "UPDATE Table1 SET Table1.Field = 'test' WHERE " &
Me.Filter & ";"

End Sub

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| From: "Lasse T" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Update query and filter by selection
| Date: Sun, 1 Feb 2004 13:14:04 +0100
| Lines: 15
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 213.150.141.235
| X-Trace: news.uni-berlin.de 1075637500 30526256 213.150.141.235 ([219006])
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berlin
.de!213.150.141.235!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188345
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi !
|
| I´m using filter by selection in a form. When I have filtered out any
| unwanted data i want to run an update query that updates one field based
on
| a text box in the form.
| In other words. How do I "pick up" the current filter from the form and
| apply the same filter in the update query.
|
| Thanks in advance
|
| Lasse T
| -------------
|
|
|
|
 
The error message indicates that there is something in the SQL statement
that Access does not understand, e.g. a misspelt field name.

To find out what it is, add the line:
Debug.Print strSQL
just above the dbEngine(0)(0).Execute line.

Run the code. When it fails, copy the statement from the Immediate Window
(Ctrl+G) into SQL View of a new query, and see if you can figure out what
the statement should be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lasse T said:
Ok. That is what I am trying to do.
I get error "3061"
Too few parameters 1 is expected. (translation from swedish)
The line: "dbEngine(0)(0).Execute strSQL, dbFailOnError" are yellow.

Names: Table: "Artiklar", form: "VisaGrupperAlla", the field to be updated:
"Palagg", and the field that holds the new value: (in the form
"VisaGrupperAlla) "PalaggKr"

Lasse T
---------------

Allen Browne said:
Set the On Click property of your button to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Paste the code between the "Private Sub..." and "End Sub" lines.

Yes: it runs the query directly from the code, without the saved query.

Lasse T said:
Thank you fvr your answer. I4m afraid I don4t quite know how to use this.
Is it updating the table without using an update query?
I tried it on the onklick of a button but I get error saying something about
needing more criterias.
The Me.filter shows the correct filter. Could I not use that in some
Click to expand...
way
in
the criteria grid of the update query?

Lasse T
-------------

"Allen Browne" <[email protected]> skrev i meddelandet
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE MyTable SET MyField = 999 WHERE " & Me.Filter & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
End If


I4m using filter by selection in a form. When I have filtered out any
unwanted data i want to run an update query that updates one field based
on
a text box in the form.
In other words. How do I "pick up" the current filter from the
Click to expand...
form
and
apply the same filter in the update query.
Click to expand...
Click to expand...
Click to expand...
 
Hi again.

I did what you told me and found som minor errors. I corrected them but I
still get the same error.
The strange thing is that if I use the debug funktion and paste the result
into a new query and run that query it works just fine. I then made a new
commandbutton in the form and pasted the string into the onclick event and
it all turned red. Syntax error.

Here are the code that works from a query but not from VBA
UPDATE Artiklar SET Artiklar.Pålägg = [Formulär]![AfRab]![palaggkr]
WHERE (((Artiklar.Fälg)=12));

Here are the code I have on the original button. This code created the above
string from the debug funktion.
Private Sub Kommandoknapp66_Click()
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE Artiklar SET Artiklar.Pålägg =
[Formulär]![AfRab]![palaggkr] WHERE " & Me.Filter & ";"
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If


End Sub

Lasse T
------------------

Allen Browne said:
The error message indicates that there is something in the SQL statement
that Access does not understand, e.g. a misspelt field name.

To find out what it is, add the line:
Debug.Print strSQL
just above the dbEngine(0)(0).Execute line.

Run the code. When it fails, copy the statement from the Immediate Window
(Ctrl+G) into SQL View of a new query, and see if you can figure out what
the statement should be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lasse T said:
Ok. That is what I am trying to do.
I get error "3061"
Too few parameters 1 is expected. (translation from swedish)
The line: "dbEngine(0)(0).Execute strSQL, dbFailOnError" are yellow.

Names: Table: "Artiklar", form: "VisaGrupperAlla", the field to be updated:
"Palagg", and the field that holds the new value: (in the form
"VisaGrupperAlla) "PalaggKr"

Lasse T
---------------

Allen Browne said:
Set the On Click property of your button to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Paste the code between the "Private Sub..." and "End Sub" lines.

Yes: it runs the query directly from the code, without the saved query.

Thank you fvr your answer. I4m afraid I don4t quite know how to use this.
Is it updating the table without using an update query?
I tried it on the onklick of a button but I get error saying something
about
needing more criterias.
The Me.filter shows the correct filter. Could I not use that in some way
in
the criteria grid of the update query?

Lasse T
-------------

"Allen Browne" <[email protected]> skrev i meddelandet
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE MyTable SET MyField = 999 WHERE " & Me.Filter
Click to expand...
&
";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
End If


I4m using filter by selection in a form. When I have filtered
Click to expand...
out
any
unwanted data i want to run an update query that updates one field
based
on
a text box in the form.
In other words. How do I "pick up" the current filter from the form
and
apply the same filter in the update query.
Click to expand...
Click to expand...
Click to expand...
 
Hi and thank you for your answer.
I get exatly the same result with this code as you see in my other post in
this conversation.
Error "3061"
Too few parameters 1 is expected. (translation from swedish)
If I use the debug funktion and paste the result into a new query the query
works.

Lasse T
--------------

"prabha" said:
Hi Lasse,

You would need to use VBA code to do something like that, example:

Private sub command1_click

Currentdb.Execute "UPDATE Table1 SET Table1.Field = 'test' WHERE " &
Me.Filter & ";"

End Sub

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| From: "Lasse T" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Update query and filter by selection
| Date: Sun, 1 Feb 2004 13:14:04 +0100
| Lines: 15
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 213.150.141.235
| X-Trace: news.uni-berlin.de 1075637500 30526256 213.150.141.235 ([219006])
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berlin
de!213.150.141.235!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188345
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi !
|
| I´m using filter by selection in a form. When I have filtered out any
| unwanted data i want to run an update query that updates one field based
on
| a text box in the form.
| In other words. How do I "pick up" the current filter from the form and
| apply the same filter in the update query.
|
| Thanks in advance
|
| Lasse T
| -------------
|
|
|
|
Click to expand...
 
Hi Lasse,

Too Few Parameters normally result in

"...You receive the error message if you do not set the values of all the
parameters in the parameter query in Visual Basic for Applications..."
source: http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

Here's what you are probably seeing:
CurrentDb.Execute "some SQL Syntax with a Where clause and not an
existing query"
(note: the above includes the parameters -- works!!!)

CurrentDb.Execute "existing Update Query with parameters"
(note: the above fails with Too Few Parameters because the
parameters are not passed)

If you are using an existing query with parameters then do the following

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("the name of your existing query")
qdf![the parameter in the query] = "some value"
qdf.Execute

Eric

--------------------
| From: "Lasse T" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Re: Update query and filter by selection
| Date: Mon, 2 Feb 2004 18:29:41 +0100
| Lines: 89
| Message-ID: <[email protected]>
| References: <[email protected]>
<[email protected]>
| NNTP-Posting-Host: 213.150.141.235
| X-Trace: news.uni-berlin.de 1075742833 31671872 213.150.141.235 ([219006])
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA0
5.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfe
ed.freenet.de!fu-berlin.de!uni-berlin.de!213.150.141.235!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188416
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi and thank you for your answer.
| I get exatly the same result with this code as you see in my other post in
| this conversation.
| Error "3061"
| Too few parameters 1 is expected. (translation from swedish)
| If I use the debug funktion and paste the result into a new query the
query
| works.
|
| Lasse T
| --------------
|
| ""prabha"" <[email protected]> skrev i meddelandet
| | > Hi Lasse,
| >
| > You would need to use VBA code to do something like that, example:
| >
| > Private sub command1_click
| >
| > Currentdb.Execute "UPDATE Table1 SET Table1.Field = 'test' WHERE "
&
| > Me.Filter & ";"
| >
| > End Sub
| >
| > I hope this helps! If you have additional questions on this topic,
please
| > respond back to this posting.
| >
| >
| > Regards,
| >
| > Eric Butts
| > Microsoft Access Support
| >
| > "Microsoft Security Announcement: Have you installed the patch for
| > Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
| > you to review the information at the following link regarding Microsoft
| > Security Bulletin MS03-026
| > <http://www.microsoft.com/security/security_bulletins/ms03-026.asp>
and/or
| > to visit Windows Update at <http://windowsupdate.microsoft.com/> to
| install
| > the patch. Running the SCAN program from the Windows Update site will
| help
| > to insure you are current with all security patches, not just MS03-026."
| >
| >
| >
| > --------------------
| > | From: "Lasse T" <[email protected]>
| > | Newsgroups: microsoft.public.access.queries
| > | Subject: Update query and filter by selection
| > | Date: Sun, 1 Feb 2004 13:14:04 +0100
| > | Lines: 15
| > | Message-ID: <[email protected]>
| > | NNTP-Posting-Host: 213.150.141.235
| > | X-Trace: news.uni-berlin.de 1075637500 30526256 213.150.141.235
| ([219006])
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Path:
| >
|
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
| >
|
l.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berlin
| > de!213.150.141.235!not-for-mail
| > | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188345
| > | X-Tomcat-NG: microsoft.public.access.queries
| > |
| > | Hi !
| > |
| > | I´m using filter by selection in a form. When I have filtered out any
| > | unwanted data i want to run an update query that updates one field
based
| > on
| > | a text box in the form.
| > | In other words. How do I "pick up" the current filter from the form
and
| > | apply the same filter in the update query.
| > |
| > | Thanks in advance
| > |
| > | Lasse T
| > | -------------
| > |
| > |
| > |
| > |
| >
|
|
|
 
Hi Eric!
Here is my code:

CurrentDb.Execute "UPDATE artiklar SET artiklar.pålägg =
[Formulär]![AfRab]![palaggkr] WHERE " & Me.Filter & ";"

And here are the other approach:

If Me.FilterOn Then
strSQL = "UPDATE Artiklar SET Artiklar.Pålägg =
[Formulär]![AfRab]![palaggkr] WHERE " & Me.Filter & ";"
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If

Bouth these gives me the same result, too few parameters. If I use
Debug.Print strSQL I get this result:

UPDATE Artiklar SET Artiklar.Pålägg = [Formulär]![AfRab]![palaggkr]
WHERE (((Artiklar.Fälg)=12));

I pasted that into an empty query just to se what happends and I fount that
the query worked.

Artiklar are the items table
Artiklar.pålägg are the field in the table to be updated
[formulär]![AfRab]![palaggkr] are the unbound text box in the form that
holds the value for the update.
As you se there are no existing update query.
The forms recordsource are the items table. No query.

I hope that im just missing some pharanteses or something. This is really
starting to bug me. I have spent weeks and weeks to figure out an easy way
for the user to filter out the data to be updated. I had another solution
but there are hundreds of different combinations in the form so filter by
selection are the perfect way of doing it. If I only could get this to work
I can get on with all the other unsolved problems I have left.

I buy you a beer if you can help me. :-)

Lasse
---------


"prabha" said:
Hi Lasse,

Too Few Parameters normally result in

"...You receive the error message if you do not set the values of all the
parameters in the parameter query in Visual Basic for Applications..."
source: http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

Here's what you are probably seeing:
CurrentDb.Execute "some SQL Syntax with a Where clause and not an
existing query"
(note: the above includes the parameters -- works!!!)

CurrentDb.Execute "existing Update Query with parameters"
(note: the above fails with Too Few Parameters because the
parameters are not passed)

If you are using an existing query with parameters then do the following

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("the name of your existing query")
qdf![the parameter in the query] = "some value"
qdf.Execute

Eric

--------------------
| From: "Lasse T" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Re: Update query and filter by selection
| Date: Mon, 2 Feb 2004 18:29:41 +0100
| Lines: 89
| Message-ID: <[email protected]>
| References: <[email protected]>
<[email protected]>
| NNTP-Posting-Host: 213.150.141.235
| X-Trace: news.uni-berlin.de 1075742833 31671872 213.150.141.235 ([219006])
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfe
ed.freenet.de!fu-berlin.de!uni-berlin.de!213.150.141.235!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188416
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi and thank you for your answer.
| I get exatly the same result with this code as you see in my other post in
| this conversation.
| Error "3061"
| Too few parameters 1 is expected. (translation from swedish)
| If I use the debug funktion and paste the result into a new query the
query
| works.
|
| Lasse T
| --------------
|
| ""prabha"" <[email protected]> skrev i meddelandet
| | > Hi Lasse,
| >
| > You would need to use VBA code to do something like that, example:
| >
| > Private sub command1_click
| >
| > Currentdb.Execute "UPDATE Table1 SET Table1.Field = 'test' WHERE "
&
| > Me.Filter & ";"
| >
| > End Sub
| >
| > I hope this helps! If you have additional questions on this topic,
please
| > respond back to this posting.
| >
| >
| > Regards,
| >
| > Eric Butts
| > Microsoft Access Support
| >
| > "Microsoft Security Announcement: Have you installed the patch for
| > Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
| > you to review the information at the following link regarding Microsoft
| > Security Bulletin MS03-026
| > <http://www.microsoft.com/security/security_bulletins/ms03-026.asp>
and/or
| > to visit Windows Update at <http://windowsupdate.microsoft.com/> to
| install
| > the patch. Running the SCAN program from the Windows Update site will
| help
| > to insure you are current with all security patches, not just MS03-026."
| >
| >
| >
| > --------------------
| > | From: "Lasse T" <[email protected]>
| > | Newsgroups: microsoft.public.access.queries
| > | Subject: Update query and filter by selection
| > | Date: Sun, 1 Feb 2004 13:14:04 +0100
| > | Lines: 15
| > | Message-ID: <[email protected]>
| > | NNTP-Posting-Host: 213.150.141.235
| > | X-Trace: news.uni-berlin.de 1075637500 30526256 213.150.141.235
| ([219006])
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Path:
| >
|
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
| >
|
l.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berlin
| > de!213.150.141.235!not-for-mail
| > | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188345
| > | X-Tomcat-NG: microsoft.public.access.queries
| > |
| > | Hi !
| > |
| > | I´m using filter by selection in a form. When I have filtered out any
| > | unwanted data i want to run an update query that updates one field
based
| > on
| > | a text box in the form.
| > | In other words. How do I "pick up" the current filter from the form
and
| > | apply the same filter in the update query.
| > |
| > | Thanks in advance
| > |
| > | Lasse T
| > | -------------
| > |
| > |
| > |
| > |
| >
|
|
|
Click to expand...
 
Concatenate the value of palaggkr into the string as well:
strSQL = "UPDATE Artiklar SET Artiklar.Peldgg = " & _
[Formuldr]![AfRab]![palaggkr] & " WHERE " & Me.Filter & ";"

If Peldgg is a Text type field, you will need extra quotes:
strSQL = "UPDATE Artiklar SET Artiklar.Peldgg = """ & _
[Formuldr]![AfRab]![palaggkr] & """ WHERE " & Me.Filter & ";"


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lasse T said:
Hi again.

I did what you told me and found som minor errors. I corrected them but I
still get the same error.
The strange thing is that if I use the debug funktion and paste the result
into a new query and run that query it works just fine. I then made a new
commandbutton in the form and pasted the string into the onclick event and
it all turned red. Syntax error.

Here are the code that works from a query but not from VBA
UPDATE Artiklar SET Artiklar.Peldgg = [Formuldr]![AfRab]![palaggkr]
WHERE (((Artiklar.Fdlg)=12));

Here are the code I have on the original button. This code created the above
string from the debug funktion.
Private Sub Kommandoknapp66_Click()
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE Artiklar SET Artiklar.Peldgg =
[Formuldr]![AfRab]![palaggkr] WHERE " & Me.Filter & ";"
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If


End Sub

Lasse T
------------------

Allen Browne said:
The error message indicates that there is something in the SQL statement
that Access does not understand, e.g. a misspelt field name.

To find out what it is, add the line:
Debug.Print strSQL
just above the dbEngine(0)(0).Execute line.

Run the code. When it fails, copy the statement from the Immediate Window
(Ctrl+G) into SQL View of a new query, and see if you can figure out what
the statement should be.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lasse T said:
Ok. That is what I am trying to do.
I get error "3061"
Too few parameters 1 is expected. (translation from swedish)
The line: "dbEngine(0)(0).Execute strSQL, dbFailOnError" are yellow.

Names: Table: "Artiklar", form: "VisaGrupperAlla", the field to be updated:
"Palagg", and the field that holds the new value: (in the form
"VisaGrupperAlla) "PalaggKr"

Lasse T
---------------

"Allen Browne" <[email protected]> skrev i meddelandet
Set the On Click property of your button to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Paste the code between the "Private Sub..." and "End Sub" lines.

Yes: it runs the query directly from the code, without the saved query.

Thank you fvr your answer. I4m afraid I don4t quite know how to use
this.
Is it updating the table without using an update query?
I tried it on the onklick of a button but I get error saying something
about
needing more criterias.
The Me.filter shows the correct filter. Could I not use that in
Click to expand...
some
way
in
the criteria grid of the update query?

Lasse T
-------------

"Allen Browne" <[email protected]> skrev i meddelandet
Dim strSQL As String
If Me.FilterOn Then
strSQL = "UPDATE MyTable SET MyField = 999 WHERE " &
Click to expand...
Click to expand...
Me.Filter
&
Click to expand...
Click to expand...
 
Back
Top