(Hopefully!) A Very Easy Question To Answer - Running A Query From A Command Button

Dec 27, 2005

Hi all

I'm currently working through developing my first database.

Basically, what I would like is for a query to run from a command button.

The query should look at a field in a table (a yes/no field) and report back the ones that have been filled in.

I know how to run the query from the access 'Query' window, BUT, I would like this query to run from a click of a command button on a form.

I've looked it up in a book, which explains about SQL, and didn't really understand it. I've also searched on here, but people talk about Macro's, and I don't know anything about them.

Can anyone help?

Cheers

View Replies


ADVERTISEMENT

Quick Easy Answer Needed

Sep 28, 2006

Text field

"CM#000001"

How do i write a where criteria to exclude all records that start with "CM#"

like "CM#*" does not work

like 'CM#' & "*" does not work

thanks

sportsguy

View 3 Replies View Related

Stupid Question Easy Answer?

Dec 2, 2004

I saw that nobody read my first post so I figure I'll post my question here. I am trying to learn VB and am curious about how to make command buttons that will do the next/previous thing in VB and not using a macro. Any advice?

View 4 Replies View Related

2 Subforms.. One Command... One Answer?

Aug 30, 2005

Hiya,
I have a form that uses two subforms. The first subform allows users to enter timecard details and the other subform shows all the hours worked. I have a refresh button on the form so that when users click the button the timecard details from subform one are automatically shown on subform two and the user can then enter more details. However i want my refresh button to only work if a user has enter details in the first subform otherwise i get a load of blank entries in my second subform. Let's say that if hours dont equal zero then the form will refresh else nothing happens. I would be able to do this normally its just that the hours field is in the subform.

Please help.
Andy

View 1 Replies View Related

Running A Update Stock Control Query From A Button

May 20, 2005

Hi

I am a relatively new user to access and wondered if you could help me with a stock control problem.

I have created an update query (qryUpdateQuantityinStock) with the below fields:

QuantityinStock
tblProduct
Update: [QuantityinStock]-Forms!frmCustomerOrder![frmProductOrderLine Subform].Form!Quantity

ProductNumber
tblPrdocut
Criteria: [Forms]![frmCustomerOrder]![frmProductOrderLine Subform].[Form]![ProductNumber]

When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.

The above query does work if you add the following code to the beforeupdate event of the orders subform:
If Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.openquery "qryupdateQuantityinStock"
DoCmd. Setwarnings True
End If

Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.

I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.

Can you help me find a way to run the query from a button?

I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.

Any help would be greatly appreciated (sorry for the long post!). :)

Many thanks

View 1 Replies View Related

How To Query The Caption Of A Command Button

Jan 24, 2006

G'day all

I want to create a form that will be the layout of my storage warehouse. I actualy have a layout on paper but was thinking of doing it on a form for the users to have a better view. The location of the inside warehouse have 60 location or Bin. The outside one has 80 Bins.
This will need 2 query. 1 for the inside Bins and 1 for the outside Bins. They are both the same query except for the caption of each command button.

What will be the beast way to create the query as to the criteria is to read from thee caption of the command button.
If the caption is 50 then give me everything in 50.

Hope this make since.

Thanks you so much.
JP

View 2 Replies View Related

Command Button That Shows A Query

Aug 4, 2005

Hello,
How would I go about in making a command button bring up a report to show a result of items for the end of the month?
For example, when a user clicks on this button it will bring up a report showing all the items that are out of date for the end of the month. I am having problems with the query as for months have different lengths e.g. 28/29/30/31 days. How would I make a query show the end result for each month correct as February if going to be different each time i.e. when it is a leap year. I have got far but having trouble with this part.
Hope you can help.

View 3 Replies View Related

Run Delete Query With Command Button

Nov 11, 2005

Hello,

I have a delete query in my database which I would like to run from a command button on a switchboard. When I use the command button wizard, it does not show delete queries in the menu of available queries. If I create a command button manually I will need VBA code for the event procedure.

Can someone give me an appropriate code for running a delete query with a command button?

Thanks

View 2 Replies View Related

Can I Place A A Command Button In A Query?

Apr 13, 2007

Hi,

I have a query that is run off a form. When the query opens up there is a field you can enter data into. Once you close the query the data saves into the proper place in the table. That all works great.

I'm trying to make this as user friendly as possible. Instead of having someone go up to the "x" in the top right hand corner to close the query can I place a command button in the query that is very noticeable?

Any thoughts are most welcome

Thanks

Eddie.

View 2 Replies View Related

Query Works But Not With Command Button?

Oct 19, 2011

I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.

I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.

View 8 Replies View Related

Forms :: Copying Command Button Appearance Properties To Other Command Buttons

Dec 17, 2013

I am rewriting an old Access 2003 database in Access 2010. When creating new command buttons, the current theme gives them a default appearance. I need to apply this appearance to old command buttons. I know there is a way to select the default button and apply its properties to others quickly. I have done it before but didn't write the process down .

View 2 Replies View Related

Making A Command Button Of A Delete Query?

Feb 16, 2006

Hi!

I'm having problems making a command button(Ta bort från engelska till svenska) of a delete query.
The name of the query is"Ta bort från engelska till svenska", which is also the name of the button on the form(huvudformulär)

Could someone please help me.
I'll enclose my zipped database.

Nille

View 2 Replies View Related

VBA Code To View A Query From A Command Button Within A Form

Dec 20, 2005

I have set up a command button within a form and wish to output results from a query for a specific company. The query has been set up.

View 1 Replies View Related

Modules & VBA :: Append Query On Click Of Command Button

Oct 18, 2013

I would like to make an append query to piece together multiple tables into a holding table, once the command button is clicked.

a) how to make an append query in vba,
b) How to call an append query on click of a command button?

View 1 Replies View Related

Have Subform Display Query Results With Command Button

Nov 13, 2014

I have a form with a subform (FindBuffer_Form) which I built from a Query (FindBuffer_Query) within the query criteria I have the following (Like "*" & [Select the main Batch Number] & "*")in order to search a particular number.

I want to be able to run the Query from a command button on the main form and have the results appear in the subform, If I have the Source object of the subform set to Query.FindBuffer_Query I get prompted twice to answer the Criteria before the form opens, after the form opens the subform is generated as per the search criteria.

Ideally, I want the form to open with no data in the subform run the query from command button and have the result in the subform.

View 9 Replies View Related

Reports :: Create Query From Command Button And Export To Excel

Apr 22, 2014

In Access, it is possible to create a query from a command button and export to excel?

View 1 Replies View Related

Change Value In Query From Command Button Based On Selection In Combo Box?

Jun 3, 2015

I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.

I have another query that will only show the line item selected from the list box. This means the query will only have one line item.

I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.

How can I accomplish this? Also, how can I make a button that deletes the selected line item?

View 3 Replies View Related

Reports :: Automate Printing From A Command Button With Selected Data From A Query?

Jul 15, 2013

How can I automate different printers for different reports on the same MS Access 2007 database, without having to select the printer each time?

There are two reports that are printed on the same database. Previously, I had automated form buttons to print the reports, without having to select the printer each time. This was about 8 years ago, but I don't remember how I did this. Also, I don't know SQL. Nothing against code, but I did not know how to program, and just MS Access 2007 access itself.

View 6 Replies View Related

Adding A Button To The Switchboard Should Be Easy, Right?

Jan 3, 2006

Hi! Everyone on this forum has been very helpful so far, and I could really use some expertise now. I'm trying to update an existing DB designed by someone else (no longer here). I need to add a button to the switchboard for 2006, but I don't understand the code that has been written for the form I am trying to update. I've posted the code below. If anyone can help me decipher it enough to add my button, I would really appreciate it. My new button should be the 9th one. Thanks in advance:

************************************************** *
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 9

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If

' Close the recordset and the database.
rst.Close
dbs.Close

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim dbs As Database
Dim rst As Recordset

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn

' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If

Select Case rst![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rst![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "WZMAIN80.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rst![Argument]

' Run code.
Case conCmdRunCode
Application.Run rst![Argument]

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rst.Close
dbs.Close

HandleButtonClick_Exit:
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

************************************************** ******

View 5 Replies View Related

Running Mdb From Command Window

Dec 13, 2005

Hello - Here's my problem:

I've created a mdb that i'm trying to run from a command window. When I type "vemacro.mdb" at the command prompt the mdb runs fine. But - after it runs I get a popup error window that says "cannot find the file "f:vevemacro.mdb" (or one of its compenents)." But - it DID find it and ran it just fine.

I'm running ms access 2003 and windows 2000.

Can anyone tell me what is causing this to happen??

Thanks!
Caitlin

View 1 Replies View Related

Forms :: Change Records Source And Run Query - Onclick Event Of Command Button

Apr 8, 2013

I have a tabbed form in a navigation form with a chart on it. The records source of the chart is a query. The query runs when you click the tab and takes a long time. I changed the Row Source of chart to "" and that eliminates the query running on form load. I've read many posts on changing that row source when a command button is clicked. I tried

Code:
Me!Suspend_Trending_Dashboard![chart].RecordSource = suspend_trend_CHART

in the onclick event of the command button. This doesn't work. I've tried many variations of the syntax. I don't know if I have to tell the query to run after the row source is changed.

On a side note, the query criteria is based on beginning and end dates entered into text boxes on the form. This all works if the query loads when the form is opened.

View 6 Replies View Related

Forms :: Command Button To Unlock And Lock Fields / Edit Button?

Feb 19, 2014

I have created a web-database (? - There are globes over all the forms and tables icons) based on the Issues & Tasks template. This means that most of the data is entered and seen on the "Main" form, which has two tabs - Open Issues and Closed Issues. I have created a form that allows people at my work to input the necessary data and save it, so that it will show up on one of the two tabs. However, once a record has been created, I want to be initially locked if the ID/PK is clicked, so that data can't be changed or entered inadvertently.

SO, I changed the code so that when the ID/PK for a record is clicked, it brings up a different form, but one that looks exactly like the one that is brought up when entering a new form, but I locked all of the fields so that the information cannot be changed. It seems from what I have read that I can create a button on this form so that when clicked, it unlocks the fields on the form so that they can be changed, and then when clicked again it will lock the fields again. Is this true? If so, how can I do it? Or is there something similar I can do? I have seen codes that I could copy and paste, but I cannot figure out the place to copy and paste codes in Access 2010.

I have changed the Form properties so that Data Entry and all the "Allows" are set to No...

View 9 Replies View Related

Running A Batch File From Command Prompt Using VBA

Apr 25, 2008

Hi,

I know this can be done but I'm a novice at VBA. I have a batch file AutoLoop.bat which I run from command prompt at the moment. The idea is to run a VBA routine behind the onClick event of a command button in Access which would run this batch file in command prompt automatically. Say if the batch file path is I:AUTOGENLOOPGENAutoLoop.bat,
what VBA code can be used.

Thanks for any help.

View 1 Replies View Related

Difference Between A Command Button And A 'label' Button

Aug 12, 2005

Hi, I have got a small problem and maybe someone could advise me.
I am creating a customised command button from a label button. The new button works fine but I can't apply the 'requery' function to it, if i do an error occures and i am being prompt to save the data first???? :confused: :


Private Sub Labelsearch_Click()
Me!itemquery.Requery
End Sub

Private Sub Labelsearch_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.Labelsearch.SpecialEffect = 2
Me.Labelsearch.BackColor = 255
Me.Labelsearch.ForeColor = 10092543
Me.Labelsearch.FontItalic = True
Me.Labelsearch.FontBold = True
End Sub

Private Sub Labelsearch_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.Labelsearch.ForeColor = 255
Me.Labelsearch.FontItalic = False
Me.Labelsearch.FontBold = True
End Sub

Private Sub Labelsearch_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
'Come back to initial state when button release'
Me.Labelsearch.SpecialEffect = 1
Me.Labelsearch.BackColor = 16373685
Me.Labelsearch.ForeColor = 8388608
Me.Labelsearch.FontItalic = False
Me.Labelsearch.FontBold = True
End

If I create a command button with the wizard and assign the code :
me!itemquery.Requeryto the on_click event my form is working fine.
Why is his code is not working if I assign it to a label? :o

View 6 Replies View Related

Query Criteria Question, Quick Answer Needed

Aug 23, 2006

Thanks for helping me out with this question. Im sure this is pretty easy to do. I have a query with a few paramaters set up... one of them runs a custom function all i want to do is this

query one
Average water flow:Avg([WaterFlow])

when run this just shows me the average water flow for the database... now if i have another criteria i want to base off the average how do i get the query to realize that. for example it would look something like this

[Average water flow]/[currentFlow]

how do i reference another criteria? hopefully this isnt to hard to understand.

View 1 Replies View Related

Command Button To Set Value

Apr 20, 2006

I have been working on this for awhile, and can't figure it out for the life of me!

I have a form set up with a drop down box that lets me pick from values from a table based off of the Primary Key of that table (Row Source = SELECT Order.OrderID, Order.OrderName from Order). I have a button on the form that I want to set a value (Closed) from the table entry based off of the order selected.

Pseudo code would go something like this.

Closed.Value = True Where Table.Order.OrderID = SelectedOrderID



Table.Order is a Table Named Order (obviously)
Closed is a yes/no field in my Order table
OrderID is the primarykey of the table Order
SelectedOrderID is the drop down box.

Any help would be greatly appreciated!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved