Queries :: Open A Query Based On Two Or More Fields Of A Continuous Form

Nov 22, 2013

I have a subform which is a continuous form displaying a single combobox of trade names so there may be up to 4 or 5.i want to click on a button on the main form which will open a form based on a query that will display all personnel matching all or 1 of the trades in the subform?

View Replies


ADVERTISEMENT

Queries :: Set Criteria On A Query Based On If A Form Is Open Or Not?

Jun 18, 2015

What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.

Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?

View 3 Replies View Related

Queries :: Dynamically Add Fields To SQL Or Query Based On Form Selection

Nov 6, 2013

Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?

View 3 Replies View Related

Queries :: New Fields Based On Query?

Jul 10, 2013

I have a table containing the area, location and name of someone there is also a field contains a number from 0-10. I need to run a query that will count the number of times a number appears in this field and put this total in a field on its own.

this my table fields are as follows...

Area Location Name Number

And I would like the query to show....

Area Location Name 10's 9's 8's ect...

View 4 Replies View Related

Queries :: Omitting Fields From Query Based On Value

May 13, 2013

I am making a parameter query that looks up quality data by lot number. For some of the lot numbers certain fields of data may be null. How can I omit these fields in the query if they are null?

OR automatically omit them when exporting them data to excel?

View 1 Replies View Related

Queries :: Run Query Based On Number Of Fields

Mar 26, 2013

I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:

Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.

I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).

I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.

View 3 Replies View Related

Create Checkboxes During Form Open Based On Query

Feb 20, 2005

I am working on a database that tracks products. The products are divided into groups (Bath Salt, Stick Incense, Candle, etc.) and these are store in a table. The products themselves are stored in another table.

When a new scent is created, it can belong to multiple groups ... for instance, Strwberry Vanilla could be a perfume, a bath salt, a candle, stick incense.

When the form is opened, I want to read the contents of the groups table and create a check box for each entry.

I do not really want to use a list/combo box for this task ... would prefer to be able to check in a list of boxes to specify what groups to create entries for when a new product is entered.

Thanks
Phil

View 1 Replies View Related

Queries :: Query With Daily Fields Based On Parameters?

Feb 11, 2014

I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:

Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.

This is not currently working.

View 8 Replies View Related

Queries :: Rank Fields Of A Query Based On Another Field

Jan 18, 2015

I have the query below that return a table like:

PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp

What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)

Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]

[code]....

View 1 Replies View Related

Queries :: Query To Run Before Report Based On Criteria Based From Two Combo Boxes On Form

Mar 20, 2013

I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.

View 3 Replies View Related

Forms :: Continuous Form With Command Button On Each Record To Open Another Form

Jul 30, 2014

I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.

At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.

continuous form: "04 - GASTOS_BUSQUEDA"
id field on continuous form: "Gastid"

pop-up (details) form: "GASTOS_EDITAR"
id on pop-up (details) form: "editar_id"

This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):

1)
DoCmd.OpenForm "GASTOS_EDITAR", acNormal, , "[editar_id] = " & Me.Gastid

2)
DoCmd.OpenForm "GASTOS_EDITAR", , , "[editar_id]=" & Me.Gastid

3)
stLinkCriteria = "[editar_id]=" & Me![Gastid]
DoCmd.OpenForm "GASTOS_EDITAR", , , stLinkCriteria

4)
Private Sub btn_editgs_Click()
On Error GoTo btn_editgs_Click_Err
Dim strWhere As String
strWhere = "[editar_id] = " & Me.Gastid
DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere
btn_editgs_Click_Exit:
Exit Sub
btn_editgs_Click_Err:
MsgBox Error$
Resume btn_editgs_Click_Exit
End Sub

View 9 Replies View Related

Forms :: Open Query Based Report On Form With Matching Record

Nov 25, 2013

This is my data:

Table: "Facility Info"
Data in the table: "facility", "city", "date", etc.
Query: "Q Facility"
Report: "R Facility"
Form: "Main Form" is where the data is entered that goes into the "Facility Info" table.

In the "Main Form" there is a dropdown box where I can select the "facility".I would like to add a button to this form that opens my report "R Facility". But this report is a collection of all the facilities and I would like it to just report the ones for the facility that I selected from the dropdown box on my "Main Form".

View 9 Replies View Related

Change Query Based On Form Fields

Dec 19, 2006

I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.

I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.

However, if one of the fields is blank, the query doesn't return anything.

Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.

It's not an error-checking issue, either, as entering info into all of the fields isn't required.

For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.

A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.

There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).

I hope I'm clear in what I'm trying to accomplish :o

Thanks in advance!

View 6 Replies View Related

Sort Continuous Form By Two Fields

Jun 25, 2006

Is it possible to sort a continuous form by two fields?

ie. If two records have the same value in one field, look at the second field to see which one is displayed first.

I was thinking that it might help to combine the two text boxes into one (a not visible text box) and sort it by that, but I don't know how to sort by an unbound, not visible text box either...

Any suggestions?
cheers, joe

View 1 Replies View Related

Forms :: Continuous Subform Based On Same Table As Main Form

Jul 4, 2013

i'm creating a database which holds all animals. i'm trying to make the parents selectable in a subform based on the same table as the main form - this doesn't work for whatever reason ("table is already opened exclusively by another user,..."). it can't just be one field or a query, it needs to be a subform because besides name and eartag i also want to display a small picture of the parent and only the filename is being stored.

i'd really like it to be selectable records in a continuous subform, so the risk of selecting a wrong id is minimized. is there a way around this not being able to use the same table for a subform?

View 14 Replies View Related

Locking Fields For One Record On A Continuous Form

Jul 22, 2005

How can i apply the following code to the immediate record in my subform. I would like when the assigned check box value = true, then the fields for that record become locked.

Ive written the following code to an onclick event for a check box labeled "assigned" on my form, the problem is it executes on all the records in my form.

Q. How do i apply the code to only the immediate record?


Private Sub Assigned_Click()
If Me.Assigned.Value = True Then
Me.Serial_Number.Enabled = False
Me.Component_Group_ID.Enabled = False
Me.TypeID.Enabled = False
Me.Description.Enabled = False
Me.Status.Enabled = False

Else
Me.Serial_Number.Enabled = True
Me.Component_Group_ID.Enabled = True
Me.TypeID.Enabled = True
Me.Description.Enabled = True
Me.Status.Enabled = True

End If

End Sub

View 3 Replies View Related

Populate Unbound Fields On A Continuous Form

Oct 5, 2004

Can I populate unbound fields on a continious form, if it is possible would you give me some code to look at or direct me to an article

Phil

View 2 Replies View Related

Forms :: Sort Continuous Form By Two Fields?

Jul 6, 2013

I have a form I would like to sort by:

first - by App_Flag (a yes/no field) - have all the clients with "yes" on top

second - by App_Date - from the earliest to the latest

The query sorts it fine but the form does not - even if I requery the form (added a requery button).

Can a macro or coding make it sort it the way I need it?

View 7 Replies View Related

Modules & VBA :: Sort Continuous Form On Two Fields

Jul 20, 2015

I am using the following by Allen Browne to sort one field. But now I need to sort on two fields.

Code:
Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
On Error GoTo Err_SortForm 'Provided by Allen Browne
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")

[Code] ....

View 10 Replies View Related

Forms :: Format Text Boxes On Continuous Form Based On System Date

Aug 14, 2014

I have a continuous form (2003) with 6 text boxes (StartTime, StopTime, Comments etc...). I would like all the text boxes to have a gray background if the StartTime for that row is less than Today().

View 5 Replies View Related

Forms :: Access 2010 / Show Image Based On Click Continuous Form Record?

Oct 29, 2014

I have continuous form in Access 2010 and I would like when user will click on record, the image, for instance, imgTest become visible for that record. But for other records the image not visible. How it to do?

View 6 Replies View Related

Modules & VBA :: Populate Word Fields From Access Continuous Form

Jun 3, 2015

I have a form that I have exported certain fields into a word doc (it is up and running just fine). I created bookmarks in word and put some VBA into my access form, so when I click on 'Create word report' it pops up and automatically populates the record I am on. Here is the tough question,

How to make this work with a continuous form? My main form has several subforms, one being a continuous form. The main form shows one bridge at a time. The sub continuous form shows information for all of the bridges spans (could be anywhere from 1 to 9).

View 4 Replies View Related

Queries :: Filtering Query Based On Form

Jan 16, 2014

I have a form named form1. In the form I have two unbound text boxes formated as general date; startdate and enddate are the text box names. In my query criteria for the ContactDateTime field I put the following code

Code:
[Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]

When I run it I don't get any results. So it runs but no records come up. I have about five queries that run when I click a button and I want to be able to just select start and end dates once on the form and have all the quieries run.

View 3 Replies View Related

Queries :: Search Query Based On Form?

Nov 13, 2013

I am unable to get multiple search output using the below code in queries, the search text is based on form :

Search form name : Find
Query name : Search
Table name : Sheet1
[forms]![find]![city]

The above works fine if it is only used in one of the fields, but as soon as I have it in multiple fields the output is null.

I am aware that i can used Like "*" code, but the thing I want to find records which are perfectly matching based on the form.

View 14 Replies View Related

Queries :: Dynamic Query Based On A Form (ComboBox)

Jan 22, 2014

We have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.

We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.

We want to create a query that pulls the correct field based on the value in the combo box.

This is what I have that doesn't work:

MyField: [Query1].[SumOf] & Forms![Main Menu]![test]

How to do this?

View 4 Replies View Related

Queries :: Change Query Field Based On A Form

Oct 29, 2013

I need a query's field to change to what is selected in a combo box.

Example,

I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.

I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.

View 3 Replies View Related







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