Trying to execute SQL from a command botton. 1

[macfil]

I have a command botton which I would like to run a SQL.

Private Sub Command3_Click()
Dim SQL As String
SQL = "select * from inv1 where Bin = '13'"
CurrentDb.Execute (SQL)
End Sub

I keep getting an error on CurrentDB.Execute (SQL) highlite in yellow.

What's wrong with this code? Is something missing?
Thanks

 

[dhookom]

You can only "Execute" action queries such as update, delete, append, and make table queries. If you want to view the datasheet view of a SQL statement, create a saved query and open it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

Thanks dhookom

What I wanted to do is to have an sql query to use in all 65 command botton on a gif picture. Its all the same qeries except for the WHERE clause. It change anywhere from 01 to 65. I was hoping not to have 65 queries.
Any advise on where I could go on.

Thanks
JP

 

[dhookom]

There are lots of methods to use dynamic criteria values in a query. Is the query the record source of a form or report?

How can a gif picture contain 65 command buttons? Apparently there is a form involved here.

Please tell us what you have and what you want to accomplish.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

Hi dhookom

I have a storage wharehouse that I want to click on a botton to see the content of that Bin. What I made was a form and I put a gif picture of the layout of all the location. I created 65 command botton set to transparent so I could see the layout better.
What I wanted to accomplish was say if I want to see the content of Bin # 05, click on botton 5 and see the content.
I only have one botton working (for now) with onclick event that run 1 query. The query open as datasheet.
The query is the same except for the Bin #.

Thank you.

 

[dhookom]

Create a continuous or datasheet form based on your table.
Then use code like:

Dim strWhere as String
strWhere = "[Bin] = '13'"
DoCmd.OpenForm "frmYourName", , , strWhere

I would only write this code once in a function and call the same function with the click on any command button.

Is there any property of the command button that identifies the bin number?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

In the Caption of the botton is identified as Bin # 01, Bin # 02 and so on.

Having a funtion, how would I change the Where [Bin] = ??

Thanks

 

[dhookom]

-Create a datasheet form "frmBins" to display your query
-Add the function below to your form's module
-Edit the On Click property of each command button to:
=OpenBinForm("01")
=OpenBinForm("02")
=OpenBinForm("03")
etc

Function OpenBinForm(strBin As String)
    Dim strWhere As String
    strWhere = "[Bin]='" & strBin & "'"
    DoCmd.OpenForm "frmBins", acFormDS, , strWhere
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

Thank you so much.

I will give it a try this afternoon.

Thanks again

 

[macfil]

I created a module and enter the code.
The record source of the form frmBin is my query qryBin
On the onClick event of my command button I enter =OpenBinForm("01")
When I run it I get an error message.
The expression OnClick you entered as the event property setting produced the following error: the expression you entered has a function name the inside layout can't find.
What have I done wrong?
Thanks

 

[dhookom]

Did you enter the function into your form's module?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

I'm going to sound stupid here, but where in the form module do I enter the function.
I'm just a beginer in Access, I have a lot to learn.

Thanks

 

[dhookom]

Don't worry about not know this. After 10+ years of using and supporting Access, I make too many assumptions. This is one of the topics I hope to include in my new Access web site when it gets finished.

When viewing the form design, select View->Code. You are now in the module behind your form where your form code is stored. Any code you (or a wizard) writes in here will be available to the form and its properties.

You should see something like this at the top:

Option Compare Database
Option Explicit

You move your cursor below these lines and paste my code from earlier in this thread. Your code window should now look like:

Option Compare Database
Option Explicit
--------------------------------------------------
Function OpenBinForm(strBin As String)
    Dim strWhere As String
    strWhere = "[Bin]='" & strBin & "'"
    DoCmd.OpenForm "frmBins", acFormDS, , strWhere
End Function

Make sure you have a form named "frmBins" or change the code to include your form name. You can then add the function to a control's On Click property:
On Click: =OpenBinForm("01")



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

Hi Duane

You just made my day. I just learn something today. I had no clue about module behind the form.

I did what you said and voila, everything was working on the fisrt tryed.

Thank you so much.
JP

 

[dhookom]

Welcome to the world of VBA! It's amazing what can be accomplished with a little knowledge of coding.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

Hi

Since everything was working so good for my inside storage, I decided to to the same thing for my outside storage area.
Everything was going fine for the piles, but when it came for the Bins its a different story.
My Bins are by section
Ex: 22, 22a, 22b, 22c, 22d...
How do I get the Like "22*" so I can see the whole section 22 and not just the Bin 22.

Thanks
JP

 

[groleau]

This may not help much, but I have successfully used

Function Do_Something(Parameter As Some_Type)
' (or 'Sub')

DoCmd.RunSQL ("A bunch of SQL " & _
Parameter & _
" the rest of the SQL;" )

end sub ' Do_Something

on many occasions. There is also a tool somewhere out there called "A2Ksqlgrabber.zip" that can transform a GUI query into VB/SQL.


--
Wes Groleau

http://www.Parkview.com/

 

[dhookom]

You might be able to use code like:

Function OpenBinForm(strBin As String)
    Dim strWhere As String
    strWhere = "[Bin] Like '" & strBin & "*'"
    DoCmd.OpenForm "frmBins", acFormDS, , strWhere
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[macfil]

HI

I'm getting a Syntax error (missing operator)in query expression "[Bin]=Like '22*"

Thanks
JP

 

[groleau]

Syntax error (missing operator)in query expression ?


Compare:

"[Bin]=Like '22*"
"[Bin]=Like '22*'"

Subtle difference

--
Wes Groleau

http://www.Parkview.com/