Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View Replies


ADVERTISEMENT

Forms :: Search Field - Subform Does Not Change To Show Matching Records

May 6, 2013

All. using 2010. I have a form and a subform. my master and child links are set but when I scroll thru my form; my subform does not change to show the matching records. I do have an unbound search field for the same field as the master and child links. When I type in the search field for a record; the subform does change to that particular record. Do you think that has anything to do with it?

View 1 Replies View Related

Forms :: Applying Quick Filter In Main Form Causes Data To Not Show In Subform?

Sep 3, 2013

I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.

View 5 Replies View Related

General :: Filter Data Sheet To Show Records With No Date

Mar 20, 2014

I am having trouble with a datasheet its ran from a macro button using BrowseTo command.

I am having trouble with the where condition; I would like to show records where the [FittingDate] is blank...

I have tried isnull() and [FittingDate]=""

View 1 Replies View Related

Modules & VBA :: Create X Number Of Duplicate Records According To A Field On Subform

Jun 13, 2013

I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".

The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.

Code:

Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer

[code]....

View 1 Replies View Related

Forms :: Insert New Data And Show All Records In Subform

Mar 27, 2013

I have a form with a subform. I want to use the main form to insert new data and the subform to show all records that are there. One could say that the after inserting a new record with the fields in the form and save it, it should appear in the subform datasheet view.

Please see attached the sample database..

View 3 Replies View Related

Forms :: Copy Value From Field In Subform To Field In Main Form During Data Entry

Jul 18, 2013

How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.

In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.

So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.

View 10 Replies View Related

Forms :: Data From One Field To Fill In Field In Subform

Nov 26, 2013

I am doing this all as a bound form

My main form got a field named 'PersonID'.

My subform also got a field called 'PersonID'

The subform 'PersonID' populates however the mainform 'PersonID' does not.

I just need to write code, or maybe do something that will copy the number from subform and paste it in the mainform field, maybe at the click of my save button.

View 8 Replies View Related

How To Show Several Records In One Field

May 21, 2012

I have a table: Complaint

Within the table there is a field: CustomerID

There could be anywhere from 1 to 10 records with the same CustomerID.

I would like to show all the complaints for a specific CustomerID on a form in one field.

Right now I have the following Control Source Code:

=[Complaint.PartNumber] & ", ," & [Complaint.PNdescrip] & ", ," & [Complaint.Complaint]

This code will only display 1 record.

I would like the text box to look like this: (All from the same CustomerID number)

Part Number A PN Description A Complaint A
Part Number B PN Description B Complaint B

View 2 Replies View Related

Forms :: Linked Table - Filter By SubForm Date Field

May 10, 2013

I have a form that is based on a linked table of students eligible to complete a online survey. Within that form, I have a sub form (based on another linked table) of students who have completed the form and at what date and time.

Only about 1 in 10 eligible students complete the form.

I'd like to filter by students who have completed the form, and sort by date submitted.

So I have the main form called "students" and the subform with the date submitted is called "New - BasicInfo subform" and they are linked by student ID. The date submitted field in the subform is formatted like "5/6/2013 3:07:32 PM".

I've tried adding this to me "filter" field in the STudents form properties:

Between [Students]![New - BasicInfo subform]![05/06/2013] AND [Students]![New - BasicInfo subform]![10/01/2013]

No luck, I get syntax error missing operator.

View 1 Replies View Related

Show Records With Unique Field

Jun 8, 2006

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

View 1 Replies View Related

General :: Yes / No Field To Show All Records

Oct 25, 2012

I have txt input [txt choice] which has a number (1-9) and then a query which uses the following formula to define which records are visible linked to a Yes/No field

Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,Yes or No)

If the choice is "4" then it works fine, but if the choice is anything but 4 it still shows only the Yes items. If i change the code to

Code:
=IIf([Forms]![frm selection]![txt choice]="4",Yes,No)

Then it does show just the 'No' items but i want it to show 'everything' if the choice is not 4

View 2 Replies View Related

Forms :: Show Selected Subform Items In A Table Field

Dec 9, 2013

Given a subform that lists items:

a
b
c
d
e
f
g

Given a table that contains a coverage field

customer coverage
smith a, b, d, g

How would I create a relationship between a subform and a coverage field such that when i multi select items in the subform, it will show what items are selected in the coverage field as in the example.

View 1 Replies View Related

Forms :: Filter Subform Results By First Letter In Main Form Field

Apr 30, 2013

I have a Main form with a sub form.The main form is a customer record (form view), and the sub form is all other customers (same fields) datasheet view.I have a navigation bar to filter (using different queries) the results in the sub form

same postcode
Same company (different location)
everyone except Main form customer

I cannot get a different filter (Query) to show only customers whose name begins with the same letter as the customer in the main form?I have been trying Like but think I am referencing it wrong, as it prompts me to enter a value rather than using the value in the field in the main form.This is what I have in the query (of the subform)

Customer ID: <>[Forms]![NewAllCustomerListF]![CustomerID]
Customer Name: Like "[Forms]![NewAllCustomerListF]![CustomerName]" & "*"

View 2 Replies View Related

Forms :: Show Records With One Common Field Value

May 23, 2013

I'm a beginner trying to build a simple database for work. I'm trying to track the performance of bonds from month to month, in this case January-April. Each bond has a serial number called Serial_ID, but we wanted to create a record for each month we own the bond, so we created a primary key combining Serial_ID and start date called Serial_Start_Date. So if we own one bond, its January record would have a primary key of "4553123A 2013-01-01" and February would be "4553123A 2013-02-01". I created a form that allows a user to select one individual month's bond and display that month's return. However, I'm struggling to find a way to create a form that allows me to just select the Serial_ID and display the four month's records. Right now there is only one table in the database.

View 1 Replies View Related

Queries :: Show All Records If Any L Field Starts With D

Nov 20, 2014

I need to write a query that shows all records if any 'L' field starts with D. I have written this, but it's only pulling records if L1 starts with D.

SELECT Item, Description, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12
FROM Table5
WHERE ((L1 LIKE 'D*') OR (L2 LIKE 'D*') OR (L3 LIKE 'D*') OR (L4 LIKE 'D*') OR (L5 LIKE 'D*') OR (L6 LIKE 'D*') OR (L7 LIKE 'D*') OR (L8 LIKE 'D*') OR (L9 LIKE 'D*') OR (L10 LIKE 'D*') OR (L11 LIKE 'D*') OR (L12 LIKE 'D*'));

View 14 Replies View Related

Reports :: Report To Show Data Details Selectively For Each Field / Qualitative Data

Apr 16, 2014

I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.

Example:

A1 // B1// C1 // D1// E1//... L1
ID // Response // Cats // Dogs // Elephants //.... Column 10
1 // I like cats // I like cats //(null)//(null)// ... (null)//
2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..//
3 // etc.

However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:

1. Section 1: Show all responses from the Cats bucket where there is data
2. Section 2: Show all responses from the Dogs bucket where there is data
3. and so on

I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.

View 3 Replies View Related

Modules & VBA :: Mandatory Data Entry In Field Before Moving To Another Field

Aug 22, 2014

I have a form that users will use to add new records (customers).

There is a field named VAT_Registration_no

First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:

Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub

and to all other fields:

Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub

If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.

View 3 Replies View Related

Modules & VBA :: Remove Data From One Field If Separate Field IsNotNull

Apr 1, 2014

Is there a way to remove data from one or multiple fields if the value of a separate field IsNotNull?

View 2 Replies View Related

Pulling Field Data From Another Records Field?

Feb 3, 2012

I need filling in field information. I have all records of people in one table, and for all persons i have Fields A B C and D. For PersonA I want to have FieldA and FieldB be tailored to that person, but FieldD to be pulled from PersonB's FieldB when PersonB's FieldA Value is typed into PersonA's FieldC.
If it makes it easier, I want A Record to show

A:PersonA's FieldA,
B:PersonA's FieldB,
C:PersonB's FieldA,
D:PersonB's FieldB

when PersonB's FieldA is manually entered into PersonA's FieldC.

View 7 Replies View Related

Forms :: Field To Show Count Of Records Between Dates

Apr 30, 2014

I'm creating a form to count the number of employees with birthdays between 2 dates. There are 2 unbound date fields; Start_Date and End_Date. I have an Employee table with DOB field. I've been stuck on how to get the field to return the correct number of employees that fall within the 2 dates.

View 2 Replies View Related

Modules & VBA :: How To Add Another Combo Box To Filter A Field

Jan 23, 2014

I have some code in which I enter a startdate and a enddate into textbox's and a combo box where I enter a client name when I run the code it will show all record's generated with in the date range and same client name set in the combo box "cboclient" what I would like to be able to do is have another combo box call cboclient2 which will allow me to enter another client name so then when the report opens it will show me with in the date range set in startdate and enddate and filter the client field by cboclient and cboclient2 so for e.g.

startdate
01/01/14

enddate
11/01/14

cboclient
peter

cboclient2
steve

Run code show all record in date range and with client name peter and steve

Here's the code I have

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.

[Code] ....

View 7 Replies View Related

Modules & VBA :: Errors With Custom Filter - Data Full Of Records With Multiple Columns

Sep 11, 2013

Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.

Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.

The code I have at the moment is as follows:
'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()

Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = " & Me.cmbFilter4 & ""
Me.FilterOn = True

End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.

View 9 Replies View Related

Modules & VBA :: Sum Of Field Of A Field In 2 Different Subform

Jul 20, 2015

I am trying to create a simple budget planner. So far, I have the following table:

tbl_account [id|acc_type|acc_name|balance|memo]
tbl_expense [id|exp_date|exp_amt|exp_acct|exp_payee|exp_type]
tbl_expense_cat [id|cat_name|cat_type]
tbl_income [id|inc_date|inc_amt|inc_acct|inc_payee|inc_type]
tbl_income_cat [id|cat_name|cat_type]

The relation now are:

[tbl_account:id] ---> [tbl_income:exp_acct] --> [tbl_income:id]
|_______> [tbl_expense:inc_acct] --> [tbl_income:id]

I have 3 subform on the main form:
[account_sf]
[expense_sf]
[income_sf]

I have set all them to continuous. Now I have added 3 account in the tbl_account.

I wonder how to SUM all the income of certain account, and minus all expense of certain account, and have them showed in the subform tbl_account.

Here is the attach pics: Untitled.jpg

View 6 Replies View Related

Show Only One Occurance Of Data In A Field

Aug 14, 2005

I have the following table:
Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm
1.002007.909.757.9077.03
1.002008.209.758.2079.95
1.002018.009.750.000.00
1.002018.109.750.000.00
1.002027.809.757.8076.05
1.002037.209.757.2070.20
1.002047.909.757.9077.03
1.002057.709.757.7075.08
1.002062.809.752.8027.30
1.002077.409.757.4072.15
1.002087.809.757.8076.05

And i want to show only the last occurance of the Hole Number field. e.g:

Blast NoHole NumberHole Depth TapeBCM/MORE mORE bcm

1.002008.209.758.2079.95

1.002018.109.750.000.00
1.002027.809.757.8076.05
1.002037.209.757.2070.20
1.002047.909.757.9077.03
1.002057.709.757.7075.08
1.002062.809.752.8027.30
1.002077.409.757.4072.15
1.002087.809.757.8076.05

However I cannot figure out if the LAST function dose this. When i have applied it hole 202 & one of the 200's dissapeared. However 202 should not, but 201 should??

View 10 Replies View Related

Queries :: Show Most Often Used Data In Field

Jun 29, 2014

How can i make a query which shows the most used data in a field, i saw how i can show the biggest or smallest but i didn't see how many times is each data used in a field.

My field is for years, and i wanna see each year and how many times it is used.

Is this possible to do?

View 4 Replies View Related







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