Runtime error 2342

[Guest]

Can anyone explain why I am getting this error? Is it do do with using a
subquery?:

Private Sub Update_Click()

Dim SQL1 As String

If [Frontagem] > 0 Then
SQL1 = "SELECT E.ExhibitorId, A.CompTickets, A.PreVP, A.ShowVP, E.Frontagem,
E.CompanyName FROM Exhibitors AS E, Allocations AS A WHERE
(((A.Frontagemin)=(SELECT Min(Sub.FrontageMin) FROM Allocations AS Sub WHERE
Sub.FrontageMin >= E.Frontagem)));"

DoCmd.RunSQL SQL1

The error comes on the last line, and yet SQL1 appears to have the correct
value in it.....

 

[Ron Weiner]

DoCmd.RunSql can only be used with action (Update, Append, and Make Table)
queries. Your query is a Select query that returns rows. What did you want
(expect) to happen when that line of code was executed?

Ron W

 

[Guest]

Hi Ron,

What I am wanting is to get the values back and update some fields with
them. I have the proceedure (almost!) working using a macro, but thought vb
would be "neater". The Macro opened the query and then I ran an update based
on the subquery...

This is probably not the best way to do it.

Following your explanation as to why it's not working I've now combined the
select and update - however I am trying to run this from a form and the
fields are not updating - this is probably a forms issue, however if you can
easily think of a better way of finding out a value and updating a control,
based on another control, then I would be happy to hear it.

Many thanks

DoCmd.RunSql can only be used with action (Update, Append, and Make Table)
queries. Your query is a Select query that returns rows. What did you want
(expect) to happen when that line of code was executed?

Ron W

Can anyone explain why I am getting this error? Is it do do with using a
subquery?:

Private Sub Update_Click()

Dim SQL1 As String

If [Frontagem] > 0 Then
SQL1 = "SELECT E.ExhibitorId, A.CompTickets, A.PreVP, A.ShowVP, E.Frontagem,
E.CompanyName FROM Exhibitors AS E, Allocations AS A WHERE
(((A.Frontagemin)=(SELECT Min(Sub.FrontageMin) FROM Allocations AS Sub WHERE
Sub.FrontageMin >= E.Frontagem)));"

DoCmd.RunSQL SQL1

The error comes on the last line, and yet SQL1 appears to have the correct
value in it.....

 

[Ron Weiner]

I am still not sure I understand what you want to do but you can update a
row (or a bunch of rows) pretty easily by executing a query. The basic Sql
Syntax would be:

UPDATE YourTable
SET YourColumn = YourValue,
AnotherColumn = AnotherValue,
....
WHERE SomeColumn = SomeValue

From an event in a form the code might look like:

strSql ="UPDATE tblYourTable " & _
"SET YourColumn=" & txtYourTextBox.Value & _
" WHERE SomeColumn=(<<Your SubQuery Here>>)"
DoCmd.RunSql strSql
Me.Requery

The above example assumes YourColumn is numeric AND that the text box
txtYourTextBox on your form contains a valid number. The Me Requery will
cause the form to go back to the database and (re)display the updated data.

Ron W

Hi Ron,

What I am wanting is to get the values back and update some fields with
them. I have the proceedure (almost!) working using a macro, but thought vb
would be "neater". The Macro opened the query and then I ran an update based
on the subquery...

This is probably not the best way to do it.

Following your explanation as to why it's not working I've now combined the
select and update - however I am trying to run this from a form and the
fields are not updating - this is probably a forms issue, however if you can
easily think of a better way of finding out a value and updating a control,
based on another control, then I would be happy to hear it.

Many thanks

DoCmd.RunSql can only be used with action (Update, Append, and Make Table)
queries. Your query is a Select query that returns rows. What did you want
(expect) to happen when that line of code was executed?

Ron W

Can anyone explain why I am getting this error? Is it do do with using a
subquery?:

Private Sub Update_Click()

Dim SQL1 As String

If [Frontagem] > 0 Then
SQL1 = "SELECT E.ExhibitorId, A.CompTickets, A.PreVP, A.ShowVP, E.Frontagem,
E.CompanyName FROM Exhibitors AS E, Allocations AS A WHERE
(((A.Frontagemin)=(SELECT Min(Sub.FrontageMin) FROM Allocations AS Sub WHERE
Sub.FrontageMin >= E.Frontagem)));"

DoCmd.RunSQL SQL1

The error comes on the last line, and yet SQL1 appears to have the correct
value in it.....

 

[Guest]

Many thanks Ron - I'll give it a go

I am still not sure I understand what you want to do but you can update a
row (or a bunch of rows) pretty easily by executing a query. The basic Sql
Syntax would be:

UPDATE YourTable
SET YourColumn = YourValue,
AnotherColumn = AnotherValue,
....
WHERE SomeColumn = SomeValue

From an event in a form the code might look like:

strSql ="UPDATE tblYourTable " & _
"SET YourColumn=" & txtYourTextBox.Value & _
" WHERE SomeColumn=(<<Your SubQuery Here>>)"
DoCmd.RunSql strSql
Me.Requery

The above example assumes YourColumn is numeric AND that the text box
txtYourTextBox on your form contains a valid number. The Me Requery will
cause the form to go back to the database and (re)display the updated data.

Ron W

Hi Ron,

What I am wanting is to get the values back and update some fields with
them. I have the proceedure (almost!) working using a macro, but thought vb
would be "neater". The Macro opened the query and then I ran an update based
on the subquery...

This is probably not the best way to do it.

Following your explanation as to why it's not working I've now combined the
select and update - however I am trying to run this from a form and the
fields are not updating - this is probably a forms issue, however if you can
easily think of a better way of finding out a value and updating a control,
based on another control, then I would be happy to hear it.

Many thanks

DoCmd.RunSql can only be used with action (Update, Append, and Make Table)
queries. Your query is a Select query that returns rows. What did you want
(expect) to happen when that line of code was executed?

Ron W
Can anyone explain why I am getting this error? Is it do do with using a
subquery?:

Private Sub Update_Click()

Dim SQL1 As String

If [Frontagem] > 0 Then
SQL1 = "SELECT E.ExhibitorId, A.CompTickets, A.PreVP, A.ShowVP,
E.Frontagem,
E.CompanyName FROM Exhibitors AS E, Allocations AS A WHERE
(((A.Frontagemin)=(SELECT Min(Sub.FrontageMin) FROM Allocations AS Sub
WHERE
Sub.FrontageMin >= E.Frontagem)));"

DoCmd.RunSQL SQL1

The error comes on the last line, and yet SQL1 appears to have the correct
value in it.....

 

[Guest]

Brilliant - it all now works

What I was doing was using a value on a form (size of stand) to get a set of
values (ticket allocations). So the user clicks on a button to get the
"standard" allocations, but can also type in their own values, so the value
needs to be stored since it is used later.

Many thanks Ron - I'll give it a go

I am still not sure I understand what you want to do but you can update a
row (or a bunch of rows) pretty easily by executing a query. The basic Sql
Syntax would be:

UPDATE YourTable
SET YourColumn = YourValue,
AnotherColumn = AnotherValue,
....
WHERE SomeColumn = SomeValue

From an event in a form the code might look like:

strSql ="UPDATE tblYourTable " & _
"SET YourColumn=" & txtYourTextBox.Value & _
" WHERE SomeColumn=(<<Your SubQuery Here>>)"
DoCmd.RunSql strSql
Me.Requery

The above example assumes YourColumn is numeric AND that the text box
txtYourTextBox on your form contains a valid number. The Me Requery will
cause the form to go back to the database and (re)display the updated data.

Ron W

Hi Ron,

What I am wanting is to get the values back and update some fields with
them. I have the proceedure (almost!) working using a macro, but thought vb
would be "neater". The Macro opened the query and then I ran an update based
on the subquery...

This is probably not the best way to do it.

Following your explanation as to why it's not working I've now combined the
select and update - however I am trying to run this from a form and the
fields are not updating - this is probably a forms issue, however if you can
easily think of a better way of finding out a value and updating a control,
based on another control, then I would be happy to hear it.

Many thanks

:

DoCmd.RunSql can only be used with action (Update, Append, and Make Table)
queries. Your query is a Select query that returns rows. What did you want
(expect) to happen when that line of code was executed?

Ron W
Can anyone explain why I am getting this error? Is it do do with using a
subquery?:

Private Sub Update_Click()

Dim SQL1 As String

If [Frontagem] > 0 Then
SQL1 = "SELECT E.ExhibitorId, A.CompTickets, A.PreVP, A.ShowVP,
E.Frontagem,
E.CompanyName FROM Exhibitors AS E, Allocations AS A WHERE
(((A.Frontagemin)=(SELECT Min(Sub.FrontageMin) FROM Allocations AS Sub
WHERE
Sub.FrontageMin >= E.Frontagem)));"

DoCmd.RunSQL SQL1

The error comes on the last line, and yet SQL1 appears to have the correct
value in it.....