Limit Query Using A Value On The Form

Oct 8, 2007

Hey Y'all.

I have built a query (Loading_query) that brings in the prefix, number and date_loaded (=null) for each rail car.

I now want to display the rail cars numbers in a drop-down but be limited by the prefix selected in another drop-down on the form.

For example:

If the prefix that was selected = GATX then
the next drop-down should use "Loading_query" to only show the rail cars that are empty (according to date_loaded) and have a prefix that = GATX.....
Still new at this and do not know how to use a value from the form to limit the query.

View Replies


ADVERTISEMENT

Limit Query Based Form

Jan 30, 2008

Hi,

I've attached a zip version of my database (which is still in the early stages of development) as I think it will be tricky to explain what I need. I've studied lots of other threads but can't find the answer I'm looking for.

Basically I have a form "Goods-in" which has a subform in continuous view that lets you pick from a product list. I'm trying to limit this combo to products that relate to this supplier only, but (it seems) because the subform is based on a query I can't update it.


I have tried several approaches but got nowehere. At this stage I don't know whether the problem is with
- how I'm linking the parent and child forms
- the SQL of the combo recordsource
- my relationships
- my overall design
- the weather!
- etc.

I would be eternally grateful if someone could take a look and advise me. The items shaded grey on the forms are just my shorthand for what will be invisible in the final version.

I'm going home now and even though I may read replies tonight, I wont be back in the office until 9am.

Thanks in advance!

View 4 Replies View Related

Query Criteria If On Form Field Limit, Otherwise Do Nothing?

Apr 26, 2006

I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.

I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.

There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?

View 2 Replies View Related

Forms :: Using Form To Limit Results Shown In Query?

Aug 14, 2013

Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.

My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?

View 1 Replies View Related

Forms :: Way To Limit Records Visible In A Form Using Field On That Same Form As A Limitation

Oct 25, 2013

We have a database with one main data entry which stores records in a seperate table. There are several thousand records keyed in using this form. Therefore, alongside the entry we have a search form which allow the user to search for a specific record and display it in the main if he/she needs to edit one.

We have received a request from users for a functionality which allows users to browse all records, using the main form layout, that they have keyed in. I guess what I am asking is: Is there a way to limit the records visible in a form using field on that same form as a limitation (ie. the users name - which is a required key-in)?

View 2 Replies View Related

Limit List Form

Apr 7, 2005

I have a form, which lists all reports i have available in my dateabase.
But there is 1 report which i dont want visable to users, how do i restrict access to this 1 form, either by it not showing on the list or password protecting it.
users have to double click the report name to open it from the list.

TY in advance

View 10 Replies View Related

Limit No Of Records On A Form

Jul 13, 2006

HI I am creating a booking system.
I would like to have a form that only shows 10 records for each event. As this is the max number of people that can attend at any one time. How would I go about this. do I use single form, continous for or what ? thanks for any help
paul

View 1 Replies View Related

Limit Records In Top N Query

May 13, 2005

I have a Top 10 query (it is based on $ amount) and everything works fine except...say I have 1,000 records and 5 are >$0 it shows those fine but since the other 995 records are $0 it shows "all" of them.
I would like to limit the record count in the query to only 10. Within that 10 records if there are $0 records then I would like to show them up to the 10 record limit.

I see both sides I think but you would think that if you say "Top 10" it would be "10".

Thank you :confused:

View 6 Replies View Related

LIMIT Query Problems

Jul 20, 2005

strSQL = "SELECT * FROM Table1 WHERE expire_date>#" & dteDate & "# ORDER BY post_date DESC LIMIT 3;"

If I take the LIMIT part off, it works, but add it on and it causes:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.

/mghdevelopment/index1.asp, line 463


Any ideas?

View 1 Replies View Related

Limit On Query Criteria

Oct 29, 2005

Hello
I have an access 2003 db. I have a csv file that regularly downloads from a propietary system. This file contains about 18,000 records. Some customers have more than one record so there are about 7000 customers. I have linked this table to access to benefit from the regular download changes.
About 150 of these customers are accounts I want to filter out of my db. I tried a query criteria Cust# Not "(Cust#) or (Cust#) and so on. Access will not let me have this many in my query. Is there another way?

View 6 Replies View Related

Help With Query-limit On Days

Dec 26, 2005

I am working on my DB for work and am almost finished with my DB. I have a query that gives me totals for days. This is the code that I have been using.

TotalTMD: IIf([SumOfTMD Total]>=180,IIf(IsNull([SumOfLOA Total]),180,"180"),[SumOfTMD Total])

TotalLOA: IIf([SumOfLOA Total]>=180,IIf(IsNull([SumOfTMD Total]),180,"180"),[SumOfLOA Total])

The "TotalTMD" and "TotalLOA" days are limited to 180 days for each table. However what I need to do now is limit the combination of both fields to 180 days. Is this possible?

Since "TotalLOA" is more serious, I would like to use what ever days are in this field and then include what ever days are left over to total 180 days from "TotalTMD" days.

Maybe I could use this as an example:

Name TMDDays LOADays Total days
John 190 20 210
Jim 80 90 170
Tony 250 190 440

Should display:
Name TMDDays LOADays Total days
John 160 20 180
Jim 80 90 170
Tony 0 180 180

Any help would be greatly appreciated.

View 2 Replies View Related

General :: Limit Of Controls On A Form

Jan 12, 2014

"Microsoft Office Access can't create any more controls on this form or report.If you have deleted from this form or report in the past, you may be able to rename the form or report and then add more controls to it."

I tried:
- rename the form -- > same problem
- copy/paste the form --> for some reason it's not duplicating (don't know why)

Is there a limit to insert controls on a form?

View 7 Replies View Related

Dynamic Form And Memory Limit?

Oct 6, 2011

I've built a function that open a form in Design mode, read records from a table about appointments and create a grid with textboxes representing one appointment each. On the left there are labels with hours and as column headers there are doctors names. Practically each column shows each doctor's daily appointments (it is a medical clinique).

Every time i change the date all controls are deleted and rebuilt.

But after three or four day I pass I get a message of memory limit.

The question is general : Why the memory finish if I close every variable I use during the creation with the instruction:

set frm = nothing
set newTxt = nothing
rst.close
set rst = nothing

View 5 Replies View Related

Limit Query By Part Of A Field

Sep 1, 2005

I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.

Any pointers in the right direction would be greatly appreciated.

Thanks

Jubb

View 2 Replies View Related

Limit Query Results By Running Sum

Apr 5, 2005

I have an unbound form with a subform which is bound to a query. The subform prioritizes and individually lists all projects my company has on its agenda and the project’s corresponding cost. Is it possible to enter a dollar figure (which would represent our budget) on the main form and have the subform list only the top prioritized projects whose running total costs would fit within the budget figure stated? Any help would be appreciated as I don’t even know if this is possible. Thanks.

View 4 Replies View Related

Limit Query Results To Last 30 Days

Dec 12, 2013

I am querying a linked ODBC table with a date column called "db_created_tms" that has dates in formatted like "2/25/2004 8:54:02 PM" , because it is a linked table I can't edit the data type. How can I limit my query results to the last 30 days.

My query without the date limit currently looks like this:

SELECT QBReportAdminGroup_v_txn_po_line.doc_num_h AS po, QBReportAdminGroup_v_txn_po_line.quantity_qnty AS qty, QBReportAdminGroup_v_lst_item.name AS REFERENCE, QBReportAdminGroup_v_txn_po_line.unit_price_amt AS cost, QBReportAdminGroup_v_lst_vendorODBC.name AS vendor, "" AS LOCATION
FROM (QBReportAdminGroup_v_txn_po_line INNER JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_po_line.item_id = QBReportAdminGroup_v_lst_item.id) INNER JOIN QBReportAdminGroup_v_lst_vendorODBC ON QBReportAdminGroup_v_txn_po_line.vendor_id = QBReportAdminGroup_v_lst_vendorODBC.id;

View 2 Replies View Related

Limit User From Accessing A Specific Form

Jan 11, 2008

I am having a problem restricting a user from a certain form.
I have a module that detects if a user or admin based on the roles entered in the employees table.

If their windows login is not listed in the employees table, they will not be able to view any of the forms.

Here's my problem, I want the users to access the switchboard, but they should not be able to get into the reporting button.

here's my code:
If LOAStatusbtn.Caption = "View Report" _
And Not admin _
Then
'useraccess denied because not an admin
error_text = "You do not have privileges to access this screen." & vbCr & "Please contact an administrator"
MsgBox error_text, , "No Privileges"

End If

If they click on LOAStatusbtn and their role is not admin, then a message should pop up.

I searched the forum and could not find anything similar to this.

Thank you,

View 4 Replies View Related

Forms :: Is There Any Limit To Number Of Fields In A Form

Jul 8, 2014

I am new to Access and I am developing a form. I have already read and found useful resources on internet but there is something I am not able to sort out.I have seen that the limit of fields I can enter in Access is 255 and my survey is far below that number.So no issue at all when I created the fields in Data view.However, the issues started when I created a form.

I created a form by Create>Form.The last few fields, at the very bottom of the form, are squeezed onto each other as if there was not any space available. Is there a limit to the number of fields I can have in a form? There should not be if I am allowed to enter up to 255 fields. How can I go about it? Should I alternatively create 2 forms? I would rather not to though.

View 13 Replies View Related

Queries :: Limit On Embedded IF Statement In Query

May 23, 2014

I have the following code

Code:
Gap: IIf([Q1]=2,"GAP",IIf([Q2]=2,"GAP",IIf([Q3]=2,"GAP",IIf([Q4]=2,"GAP",IIf([Q5]=2,"GAP",IIf([Q6]=2,"GAP",
IIf([Q7]=2,"GAP",IIf([Q8]=2,"GAP",IIf([Q9]=2,"GAP",IIf([Q10]=2,"GAP",IIf([Q11]=2,"GAP",IIf([Q12]=2,"GAP",
IIf([Q13]=2,"GAP",IIf([Q14]=2,"GAP",""))))))))))))))

I need to add Q15 and Q16 but there appears to be a limit on how many IIf statements I can embed and I get an error that my formula is too complex.

View 3 Replies View Related

Queries :: Limit Date Outputs In Query

Jul 30, 2013

I have a query that is pulling information from 2 tables: airport codes and orders. I was able to select the 7 states I wanted without any problem. I am trying to limit the "order date" by using the criteria ">=#1/1/2011#". I want the query to only choose those orders that were dated 1/1/2011 to the present.

No matter what I place in the criteria for the date, the data never changes. I even tried "=date()" just to see if it would change. I tried "Between #1/1/2011# And #12/31/2013#" and the results were the same. This should be a simple task (as were the states) but I don't know why it is not yielding the desired results.

View 7 Replies View Related

Forms :: Limit Filter To Current Row Of Continuous Form

Jul 28, 2015

I've built a basic inventory management system that allows a business to add products and the ingredients (and labour etc.) that go into them so they can work out costs and profit.We've run into a bit of a usability problem in the sense that they have 700 different ingredients and so adding them to products has become a little unwieldy as the lists in the comboboxs are massive.

What they've asked me to do is to add a filter combobox to the Ingredients subform of their New Product page that allows them to constrain the Ingredients by type, to make the lists more manageable. So if you imagine they are entering a record for a new cake and need to add Eggs as an ingredient they could either...

a) Scroll through the list of 700 ingredients as they do currently and find "Size 3 chicken egg".

...or...

b) Use a combobox on the ingredient row to pick "Eggs" which would in turn filter the ingredients combobox on that row from 700 ingredients down to a handful e.g."Size 1 chicken egg", "Size 2 chicken egg", "Size 3 chicken egg", "Size 1 duck egg" etc.

the filter combobox applies to all rows in the subform! Is there a clever way of telling the Filter to only apply itself to the current row of the continuous form?My code is...

Code:
Private Sub FilterRMs()
Dim mySql As String
If Me.RMTypeFilter.Column(1) = "999" Then
Me.FilterOn = False

code]....

View 2 Replies View Related

Forms :: Navigation Form - Limit Access To Tabs

Feb 7, 2015

I have navigation form with 2 tabs I'd like to limit access to users with certain type of account.

There are 2 tables in database that contains user names and types of accounts (tblSecurityLevel and tblUser), and they are used to set user name, login and security level.

It works this way: when you open form, it checks if PC name corresponds to name in UserLogin field (tblUser), if it does it gives appropriate access level depending on set user security, if PC name is not on the list user will be logged as guest.

So far, I used this code and it works great for limiting access to 1 tab:

Code:
Private Sub Form_Load()
Dim UserLogin As String
Dim userLevel As Integer
UserLogin = Environ("Username")
Me.TxtLogin = UserLogin

[Code] ....

Is there a way to expand the code and add second tab (or even third) with this table structure (I'd like to be able to keep login which 1st checks for pc name).

View 4 Replies View Related

Forms :: Limit Number Of Records That Can Be Accessed In A Form

Mar 14, 2014

I have a database that has transactions since year 2009. I want the users to only be able to view and edit transactions that were entered in the last 6 days. I was initially able to do this by putting a filter in the Property Sheet section of the form and choosing Yes for Filter on Load.

However, a user was able to discover that by clicking on Clear All Filters, the filter I set was also cleared, thus making them see all the transactions beginning year 2009.

View 1 Replies View Related

Forms :: Limit User Access In Form And Report

Jun 19, 2015

what is the best way and good practice in limiting user's access in form or report.For example, i have 2 users in my UserTable, one is Administrator and the other is just ordinary user.In case i want the ordinary user to open the form in "Read Only" mode, and the Administrator in "Edit" mode.

Do I need to make a code for each form to be opened or there's another easy way to handle this? (I am thinking for additional fields in UserTable to store their individual rights, but after that i don't know what to do next)

View 1 Replies View Related

Any Limit To Number Of Functions / Subs In Form Module?

Jan 8, 2015

Is there a limit to the number of fucntions/suroutines in a Form's module. I got an overflowerror when compiling and I moved a fucntion from the Forms mdoule to another module and the error did not re-appear.

View 5 Replies View Related

Queries :: Limit Query To Selected Items In A List Box

Nov 27, 2013

I want to limit the results in a query to the selected items on a list box - how do I do this?

View 4 Replies View Related







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