How To Let User To Select A Table To Run?

Aug 17, 2007

Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;


I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.

for eample if user choose varcurve1
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;

if user choose varcurve2
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;



is there a way to do that?

Thanks

View Replies


ADVERTISEMENT

Modules & VBA :: Allow User To Select Record From Subform And Add It To Table - Insert Statement

Jun 18, 2013

I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"

Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")

[Code] ....

View 1 Replies View Related

Calc User Totals Table 1 And Enter In User Records In Table 2

Oct 28, 2004

I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".

My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.

Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.

Thanks in advance.

Regards Peter

View 5 Replies View Related

Parameter Query - Allow User To Select All

Oct 10, 2006

I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?

View 14 Replies View Related

Forms :: User To Select Only One Of Checkbox Out Of Three

Jan 15, 2014

I have three check boxes in my form and i want the user to select any one of the check box, if not, i want to display an error msg? How to do it?

View 2 Replies View Related

Select Query Based On A User Choice

Nov 13, 2006

Hi,

I would like to run a simple select query, where the CRITERIA is based on the user choice.
I have a form with a combo box, with a few choices, and a button that will trigger a macro with one query for now. I can store the choice in to a variable e.g. "town", using Microsoft VB code.
How can I transfer the variable to the CRITERIA field in the query, so whenever I chose a specific "town", my query will select the records for that specific town.
I want to use one query, and I don't want to have to "hard type the criteria in to the cells' query", but I would like to use a variable that can change and will be read it from the user choice through combo box.

If I am not clear enough, please let me know.

Thank you!

View 2 Replies View Related

User Select ID And Get The Form With Data By That ID, But What If The ID Not Exists??

May 19, 2006

Hello everyone,

I've made a form where the user can fill in a field ID... and than by clicking on a button another form will be opened with the data of that person (with that ID that's filled in). But if the user fill in an ID that is not currently in the database, there will be also shown another form, but than with no data.. Now I want that if the ID is not known, to display a message or something (like "this ID is not known, try again") and not to open the form..

I've used the following code under the button:

Private Sub openenFormulier_Click()
On Error GoTo Err_openenFormulier_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Onderzoek"

stLinkCriteria = "[Qr_Ant_PG.ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openenFormulier_Click:
Exit Sub

Err_openenFormulier_Click:
MsgBox Err.Description
Resume Exit_openenFormulier_Click

End Sub


Hopefully someone can help me!

Thanks,
Ankie

View 2 Replies View Related

Union Query - Possible For User To Select Tables ?

Jun 28, 2006

I have Access2003 and a problem creating a simple method of constructing a union query.

I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.

I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.

Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?

Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.

I am confident, perhaps without good reason , that this must be possible ....

any help much appreciated.

View 14 Replies View Related

Reports :: User Select Data For Report

Aug 22, 2013

How to proceed and what is the "accepted" version of events.I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.

My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report. So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!

Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...

View 2 Replies View Related

Modules & VBA :: Prompting User To Select Answer

Aug 7, 2013

I'm trying do a Select Case on a form control where the user is prompted for information depending on the value in the control.

Here's a generic example:

If field1 = "Vegatable" then
Select 1 for Brocolli
Select 2 for Lettuce
Select 3 for tomato

If field1 = "Fruit" then
select 1 for "Banana"
select 2 for "Apple"
select 3 for "Peach"

View 9 Replies View Related

Forms :: Form That Allows User To Select A Record In A Combo Box

Jul 3, 2014

I have a form that allows the user to select a record in a combo box. When this selection is made I want a 2nd form to open and give the user a place to enter additional info about that item selected. I have the forms working correctly but now I would really like to add the 2nd form to the first form as a subform but when I try this I get the message"The expressing is typed incorrectly or is too complex to be evaluated..." There are no fields on the first form that link directly to the 2nd form - because the combo box in an unbound combo (the selection in the combo box is what the 2nd form opens base on) I tried to add this as a subform with no parent/child info and I get the message stated.

I was hoping to make the 2nd form not visible until the selection was made in the combo box then make it visible for the info to be entered. Would really like it to be on the same form not as a separate form opening.I believe I just found that the reason for the message. It seems not to be a subform problem but is because the selection in the combo box needs to be made BEFORE the form can open. So now the question is - how can I make this a subform and avoid this error when the main form first opens. I will test the visibility and see if not visible until the selection clears this up.

View 10 Replies View Related

Forms :: How To Allow User To Select From Current Approved Values

Jul 13, 2014

I have a combo box that needs to only show approved values for any current data input but also be able to show legacy values (that were approved at one time but are no longer approved).How can I only allow the user to select from current approved values but still be able to pull a legacy record and show the legacy value.

Table structure

tblRepairReason
ID (Nnumber)
Name (Text)
ApprovedValue (Yes/No)

The recordsource of the combo box:

Code:

SELECT tblRepairReason.ID, tblRepairReason.Name, tblRepairReason.ApprovedValue
FROM tblRepairReason
WHERE (((tblRepairReason.ApprovedValue)=True));

View 14 Replies View Related

Forms :: Allow User To Browse Path And Select File?

Jan 8, 2014

In my database i wanna use the DoCmd.CopyObject command with a button like as

DoCmd.CopyObject "Destination path can be browse and select file", "AcInformation", acTable, "AcInformation"

Image of the plane:

how can do this?

View 1 Replies View Related

Queries :: Select Change ID From Changes Made To User Accounts

Jun 12, 2013

I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.

What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.

Code:
SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1"
Set qresults = CurrentDb.OpenRecordset(SQL_SELECT)
Do While Not qresults.EOF
multichange_id = qresults.Fields(1)
qresults.MoveNext
Loop
qresults.Close

View 5 Replies View Related

Give User Ability To Select Different Time Frames On A Report

Aug 11, 2015

I want to give the user the ability to run a report but display records based on different user defined time frames. So when I run the report it allows (on the Enter Parameter Value prompt) user to enter 30, 45 60 etc (to represent days). The report then looks at all records with a start date in the next x days based on what user enters

For a separate report, I want to be able to run the same report but give user (based on drop down) ability to view all reports or only those from today's date only.

View 3 Replies View Related

Make User Select From A Drop Down List If A Number In Another Field Is Entered?

Feb 13, 2006

Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(

Anybody please help me out?
-------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then
MsgBox "Please select an Absence reason"
Cancel = True
End If

View 2 Replies View Related

Modules & VBA :: Allow User To Select A Location To Save Just A Copy Of Image In JPG Format

Dec 6, 2013

Access 2010:

The database I'm working on stores label images, or better stated, it stores file paths where the label images are stored. These paths are used in reports and forms to view the label images within the database. What I'd like to do is to create a "save" or "email" button that will allow the user to select a location to save just a copy of the image in jpg format. I realize that it is possible to imbed the jpg image into a report and export it as a pdf. What I'd really like to do is find a way to save the image (perhaps by copying it from the path in the database and simply relocating it) and preserve the jpg extension if that is possible.

I am also aware that there are third party applications that will convert pdf reports into jpg files however network restrictions keep me pretty limited in my ability to download third party software.

View 2 Replies View Related

Use Combo Box Of Date Received So User Can Select To Display Receipt Form

Jan 12, 2013

How to use combo box that drops down the date received from which the user can select todisplay "receipt form" on and after that date. [need to use macro]

View 4 Replies View Related

Forms :: Combo Box To Allow A User To Select Date And Relevant Work Details Appear In Subform

Jun 10, 2013

I have created a simple database, with 2 x tables. The first is a 'Week Ending' table, with a dateid key and Week Ending field. I have created several week-ending dates in order for a user to input details of work done the previous week. The second table contains the details of the work.

I have a one to many relationship between the dateid in the first table, and a dateid of the work details table.

I created a query based on the work details table, and created a subform from that. In a main form I linked a combo box to allow a user to select a date and the relevant work details appear in the subform. However the Week Ending field only shows one date, not a list of the dates I created.

View 7 Replies View Related

Queries :: Select Query To Pull Records Based On Multiple Strings Entered By A User?

May 1, 2013

Is it possible to run a basic select query to pull records based on multiple strings entered by a user?

I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'

This allows the user to input one MMDBID and the records are retrieved from the db.

I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.

But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above

SQL statement (Like) is below :

SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));

View 10 Replies View Related

Forms :: User Select Subform Filters Other Subform

Oct 8, 2013

i have a form with 2 subforms. when the user selects a record in subform one. the date of that record filters subform 2. in the source query of subform 2 i have this under criteria

Code:
[Forms]![frmeventbuild]![frmBUILD]![EVENT_DATE]

View 3 Replies View Related

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

View 4 Replies View Related

Reports :: Select Most Recent Record From A Table That Is Linked To Another Table

Jul 13, 2014

I have a basic database design, well I think so anyway. It only has two tables:

1. tblClientInfo
2. tblNotes

Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.

I then have two forms:

1. Claims Loss Form
2. tblNotes_DatasheetSub1

So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).

Example of a print out of what it is doing:

John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)

It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.

How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?

I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.

I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.

The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.

View 2 Replies View Related

Select Records From One Table That Does Not Exist In Another Table

Dec 11, 2006

Hi Guys!

Need help in putting up SQL string.

I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.

What I want to do is to view customer information that does not have customer logins.

Is it possible to do in MS Access 2003?

Regards,
Aga

View 7 Replies View Related

SELECT Row N FROM TABLE

Sep 20, 2006

Hello,
I am trying to familiarise myself with MS Access and have the following problem:
Lets say I have a Table A with two columns as follows:
Name Total
Tom 1000
Mary 200
Brigid 150
Niamh 75

My question is how can I - using SQL only - select the nth, ie 3rd or 4th row?

I have spent some time on this and it irks me becasue it is probably very simple.
Any help or advice will be gratefully received.
Best Regards,
Tony

View 4 Replies View Related

Select Records In Table 1 That Are Not In Table 2

Oct 3, 2007

I have two tables [Reviews] and [Results].

When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.

I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria:
- The Review Status = 'Not Accepted'
- There are zero records in [Results] for that ReviewID

I can't figure out how to do it.

Here is an example of the tables where all record are OK...

[Reviews]
ID...Status
1....Accepted
2....Not Accepted
3....Accepted
4....Not Accepted
5....Not Accepted
6....Accepted

[Results]
ReviewID....ResultID
2................5
2................6
4................3
5................3
5................7

Here is how [Results] would look if there is a data integrity problem, where the results for Review 5 are missing....

[Results]
ReviewID....ResultID
2................5
2................6
4................3

So I want to structure a query that would return the following...

ReviewID
5

Because this is a Review with Status = 'Not Accepted' but there is no corresponding record(s) in [Results].

I guess in plain English it would be like this...

SELECT ReviewID
FROM [Reviews]
WHERE (ReviewID is in [Reviews]) AND (ReviewID is NOT in [Results])

Can this be done in a single SQL query?

View 10 Replies View Related







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