Open from subform where condition Results

I know this has been brought up, and I have tried EVERYTHING to solve this problem, but it still wont work.

I have tried DoCmd.OpenForm "SalesWon", , , "LeadID = " & Me.LeadID
but I might be doing it wrong. I dont know.

Open Add/EditRecords
And if you go down to the Lead Information section you will see the Disposition combo box. And if you choose 'Won', 'SalesWon' form should pop-up. I made 3 or 4 records for 'SalesWon'. As you can see 'SalesWon' and the 'LeadInformation Subform' are connected by 'Lead ID' field. If there is a record in the 'SalesWon' form that has the same 'Lead ID' field as the subform, I want that record to show. If there are no matches, I want the form to open to a new record. Thanks.

I am currenly working with two Forms
Form 1- Patient Form (PatientNum)
Form 2- Patient History (PatientNum)
I have just viewed a great YouTube video on how to make a button Open a Form and go to a matching record using the Where Condition. I got it to work great, but it only works once when opening the new form from the navigation bar. It doesn’t seem to want to work while I have both forms opened. I have to close Form 2 and used the button on Form 1 to make the event happen.
I am using a simple macro to Open Form 2 with the following Where conditional statement.
[PatientNum]= [Forms]![PatientForm]![PatientNum]
Because I am entering a lot of data I generally have both forms open and flip back and forth using the tabs. I would like, when I press the Open Form button for the Patient Number in Form 1 to match the Patient Number on Form 2.
It has been suggested that I use a subform- and i know how to do this- i would really like to learn how to create the macro so that i can use it in other forms as they are developed.
Thanks.. you guys have never let me down

Hello everyone! I'm a total Access newbie and have no VB or SQL knowledge. I've managed to create a membership database using the help of wizards and internet forums. I've looked for similar questions to mine, but still cannot solve my problem. Any help with my question below is really appreciated! Thanks so much in advance!

Here it is:

I have a form called Birthday People that has:

(1) a combo box called Combo21 that has the values: January, February, March, etc

(2) a subform called Birthday People subform that draws information from a query called Birthday People

When you choose a month (ex. January) in the Combo 21, it filters the information in Birthday People subform to show only the people with birthdays in that month (ex. people w bdays in January).

How Birthday People subform is linked to Combo21:
Link Master Fields: Combo21
Link Child Fields: Birthday Month

All of this works perfectly. Now, I would like to add a button to the main Birthday People form that will open a report that only includes information in Birthday People subform when a selection is made in Combo21 (ex. When January is selected, the subform shows people with January bdays. I want the button to open a report that also shows only people with January bdays.)

The button will open a report I created called Birthday People. This report includes a subreport called Birthday People subreport. Birthday People subreport draws information from Birthday People subform.

Right now, when I press this button with "January" selected in Combo21, Birthday People report opens to show all of the people, even those who don't have birthdays in January.

What should I have as the 'where condition' under OpenReport for the button's macro to filter the information on the report according to what is selected in Combo21?

I would like to open a second form via a command buttom on the main form. I'm avoiding a subform because I don't want it to display until clicking a button.

Main form: frm_Client
Second form: frm_ClientPersonal

In the properties of frm_ClientPersonal I have record source: ClientPersonal (table) and filter: [ClientPersonal]![Client_ID]=[Forms]![frm_Client]![Client_ID]

Then on the command button to open the form I have a macro OnClick to open frm_ClientPersonal with a where condition of [ClientPersonal]![Client_ID]=[Forms]![frm_Client]![Client_ID] and data mode of edit.

My problem - the frm_ClientPersonal opens from frm_Client when I click the button. I can also fill in data. But the data does not save the Client_ID on the record within ClientPersonal so it can't be retrieved a second time.

I'm sure I'm missing something easy, but I can't seem to figure it out.
Thanks for the help.

We recently "upgraded" from ACCESS2000 to ACCESS2007. The good news is that I have not had any real problems. The bad news is having to do some tweaks. Currently, I have a form that uses a subform in datasheet view. The subform uses conditional formatting to highlight incomplete projects. It is based on a filter.

Under ACCESS2000 the subform was slow to display. I have some dlookup statements. Under ACCESS2007 the subform basically "freezes". The actual symptom is that one of the fields flickers. After you move the mouse cursor (doesn't really matter where) the flickering stops and the data displays itself. (Could this imply that the program for whatever reason is trying to communicate with the mouse?).

When I open the subform directly, I don't have the slow/flickering problem either.

I did one test where I removed the conditional formatting, and that resolved the problem of the subform datasheet "freezing".

So far, I have "rebuilt" the forms entirely within ACCSESS 2007, but that has not solve the problem. I am currently experimenting with "repaint" and "requery". So far that has not yet worked. I may be pursuing a "wrong" course of action here.

I am also contemplating doing a stand alone query and referencing the subform to get the data from the query to see if that solves the problem. Which actually leads me to a side question that raises a "bigger" programing issue. Is it better (faster/efficient) for a subform to get its data from a stand alone query or by using a filter? Currently, the subform is based on a filter.

The ultimate "solution" of course would be to remove the conditional formatting if there is no other solution. Any additional advice?


I have an access 2010 db with several forms with multiple subforms. The subforms are based on the same query and are filtered by one of the fields to show subsets of this query data. Basically the query holds 4 fields, a Parent Record ID, attribute ID, attribute type and attribute description.

The main form holds the parent record and the sub forms are linked using this and then filtered to show attributes of one type per sub form.

The issue is that when I open the main form from a button on another form with the record filtered the sub forms don't apply their filters properly. The button was generated automatically by access and the macro has the following where condition on the openform action

="[ClientContact_ID]=" & [ClientContact_ID]

Attached are three screen grabs in a zip as I am not permitted to upload images. The first shows some example query results, the next the filters on the subforms working correctly and the final incorrectly. The subforms both show the same record on the same form but the correct one is opened directly and the incorrect from a button on another form.

Its leaving a placeholder for the 'filtered' records, I assume its empty as the combobox source doesn't match.

This happens on several forms I have created with similar subforms.

Any help greatly appreciated.


Several issues here - something has gone a muck with a subform and not sure whether they all might point to the same thing.

A database serving as an encyclopedia, without all the fields mentioned the encyclopedia table has:
An ID.
A reference field.
A details field.

A subform is linked to the main form in the normal way with a master child methodology.

The subform displays a temporary table which is a list of cross references.

Each time the main form moves to a different record, in the current event a temp table has all records deleted and a new set is established by searching in the details field of all records in the encyclopedia field, a manual button also searches external word docs associated with the data and extends the temp table. Giving a mixture of other records and external references to the current encyclopedia item.

The tables are deleted and established in all cases as they should be, there are no data issues. It is just about display.

Firstly the firing order of the form load events has become somehow inverted: current-open-load
rather than the other way around
the current event is firing twice per navigation

and the following two lines are exhibiting different behaviour which I did not expect:
Forms![Encyclopedia Namadan]!References.Form.Requery

I need the second to get correct data, the first is not clearing Deleted records, but the second causes an Error message at when it is run;
the control not recognised message pops up.

Obviously I am trying to work around the apparent anomolies as I dont seem to have a choice, but things are provioing a bit difficult.

Here is a cut down version of the salient points in the code:

	Private Sub Form_Current()
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    dbs.Execute "Delete * from references"
'After deleting previous references find current set of references
    'Requery the subform to get rid of deleted entries
    Forms![Encyclopedia Namadan]!References.Form.Requery
end sub

Private Sub searchForRefs()
'construct search as a compound of the general search conditions and an exclusion from finding itself
    strSQL = "SELECT * FROM Encyclopedia WHERE Ref  '" & Forms![Encyclopedia Namadan]![Ref] & "'" & strConditions

    Set rstEnc = dbs.OpenRecordset(strSQL)

    If rstEnc.RecordCount > 0 And Not IsNull(Forms![Encyclopedia Namadan]![Ref]) Then
        Do Until rstEnc.EOF

            Call AddReferance(Forms![Encyclopedia Namadan]![EncID], sRef, extract)
    End If
end sub

Private Sub AddReferences(....)

Set dbs = CurrentDb

    Set rstRefs = dbs.OpenRecordset("References", dbOpenDynaset)

    rstRefs!EncID = EncID
    rstRefs!EncRef = EncRef
    rstRefs!extract = extract

End Sub

I hope that title I gave this doesn't completely confuse everyone.

I have a form based on a query. On that main form, it has a combobox so the user can select between ten different historical events (club meetings). In the first tab it shows all the people who were at the event in a datasheet display.

In the next tab, which is where I get totally lost, I want it to show only the people that were at that even AND were those who brought food, and the next tab brought equipment, and the third tab brought drinks (Yes, there is a field called txtAssignment and in it is entered Food, equipment, drinks...).

I noticed a couple examples similar that rebuilt sql strings to achieve this on opening each subform. Is the best way to proceed to use that method?

If so, do I take my first condition from my combobox that selected the event and then add on an "and" to the conditions? If so, I won't say that this is over my head, but if it isn't, I can say that it is darned close; any pointers are greatly appreciated.

I am relearning all of this after being away for about 10 years, and it is very frustrating to find how much I forgot. And embarrassing.


I searched up and down these forums for about 1 hour or so to find a topic related to my problem, but couldn't find anything... I hope this post is not redudant

Anyway, the problem is this one:

I have two tables: tblProjects and tblOrgs which are in a many-to-many relationship with each other through a third table that links them. In a form, frm_projects , i have added a subform where i can create the many to many linkages through combo boxes.

in the subform i have only 1 field from tblOrgs, namely OrgName. What I wanna do, is ondblClick on this name in the subform, I want to open the frm_orgs which would have all the fields in tblOrgs related to the specific record that is clicked.

I managed to use a macro with an openform and a where condition which works if the subform is opened on its own, but does not work when its within the frm_projects. In the latter case, it brings up a window asking for input regarding "Forms!frm_sub_orgs!OrgName" (frm_sub_orgs is the subform)

this is the WHERE condition in the macro:


I hope I have been specific enough...
Thanks anyone

Okay, I have two forms Navigation Form, and a continuous form that shows up as the Navigation Form's subform.

The continuous form is laid out to show me all records based on a search. For each record that's displayed, an command button displays beside it. So, when using the Navigation form, the command button is located within the subform. The command button (btnEditRecord)'s OnClick event is configured as such using a macro:

Form Name: frmEditRecord
View: Form
Filter Name: (blank)
Where Condition = =" [ID]=" & [txtID]
Data Mode: (blank)
Window Mode: Normal

When the btnEditRecord is clicked, frmEditRecord opens as an entirely new window, displaying frmEditRecord and pre-loading all the correct information.

The problem is, I don't want it to open as a new window. What I want to happen is that when btnEditRecord is clicked, frmEditRecord opens in the Navigation Form's subform.

I've tried editing the Where Condition clause with various permutations of [Form]![Navigation Form]![NavigationSubform].[Form]![frmEditRecord]=" [ID]=" [txtID] thinking that might work. Unfortunately, along with breaking data entry, frmEditRecord ALWAYS opens in a new window.

What am I doing wrong, and more importantly, how can I get it right?

This has been a recurring problem I have had. I have a parent form showing some of the contents of a single record in a parent table. This form contains a button that opens a specific subform (using an IF statement based on the contents of another field in the parent table/form. There are actually any of 8 child forms/tables that could be selected and the related record is in only one of them. The relation between the tables is based on the PK of the parent table and the related FK in the child table. and the data is correct in all tables.

My problem is that when I run this button on the parent form/table it returns the appropriate form/table, but displays the first record rather than the related record. In the past (using this same process) I have been successful in getting to the right record by fiddling with the where statement (sometimes putting in a equal sign or taking it out fixes the problem). However, I have no idea what is taking place and this time no matter what I do to the Where statement nothing works. I have not yet placed the final else condition for where the form selection field (VMValIdentType] is empty yet as there is no point at this time.

Here are the button macro contents:

If [VMValIdentType] = "Software"

Form Name: tmpVMValSW
View: Form
Filter Name:
Where Condition =: "[MBIDPK]="&"'"& [MBIDFK] &"'"
Data Mode:
Window Mode: Normal

Else IF [VMValIdentType] = "Another Variable" There are 8 of these


I would just be happy if I had an idea of what I was doing when I am doing this so that in the future I might be able to keep this problem from occurring. I do understand what is supposed to happen, but obviously just enough to be dangerous.

Ok, please bare with me as this may be difficult to explain.

I've created my forms and placed several forms as subforms, and then placed these forms on Navigation Form (I need the tab effects). My problem is that I've been trying to code a command button that is located on a subform that is stored on the Navigation Form. I've done so much re-coding that i can only provide the latest.

Forms and hierarchy:

formNameList ------
formNameCard------ -------(These forms become subforms of)---formContactCard---(This becomes a subform of the Navigation Form)---formMain

So above is the layout of the forms and how they are placed as subforms. So now for the problem: I have a command button on the formNameCard to open a separate form called "formEditComments" and filter based on ID(the Primary Key), which I have had working on the form itself. Now when I open the formMain where all work will happen, this button does not work, it asks for the Perimeter Value of "formNameCard". I need to pass the value held in the subform when opening the new form, but I believe I'm several forms deep and having trouble getting there. Since I am currently working in Macros (Not by choice), this is the Where Condition that I currently have:

[tblComments]![Contact ID]=[Forms]![formMain].[Controls]![NavigationSubform].[Form].[Controls]![txtContactID]
(What I have done is I added an unbound text box to the "formContactCard" which inherits it's value from the subform; in an effort to shortcut to the value needed to carry to the new form)

This is where i was at before:

[tblComments]![Contact ID]=[Forms]![formMain].[Controls]![NavigationSubform].[Form].[Controls]![formNameCard].[Form]
(Here I was trying to get down to the textbox which holds the ID for the contact, but unfortunately the system stopped giving me options to go any further.)

FYI - I am not using any "Link Master Fields" or "Link Child Fields" due to the fact that my subforms "formNameList" and "formNameCard" stop passing values back and forth and I can no longer show my continuous list of contacts and select each one as I want. So i don't know if this is having some effect here, but i need to do this without this feature, or so i believe.

Thanks in advance!

Hi guys. I'm a newbie in MS Access. I only have very basic knowledge in micros. So my database is like:

Main Form: Document Detail
Subform: Document History
Other Form: Request for Document Change
All form has the same primary key "RequestID" as they are all created from the same Table:Request.

What I'm trying to do: When clicking a record in the Subform, the Other Form is opened.

In the RequestID OnClick properties of the Subform, I use the OpenForm action and set the Where Condition to:
[RequestID]=[Forms]![Document History]![RequestID]

- When clicking a record in the Subform on the Main Form, the Other Form won't open. I'll get a popup "Enter Parameter Value".
- However, if I open the Subform alone and click on any record, it opens the Other Form and displays the specific record.

Please help. Thanks very much.

Good day to everyone. This site continues to be constant source of inspiration and learning.

I'm a PMP who uses Access, from time to time, to develop applications to help me with project management. Based on my limited knowledge, I do ok, from time to time.

I seldom use macros, however found one that I'm trying to modify, with little success. I've already tried VBA with even less success.

I have a main from that has a cbobox with: "All";"Today";"This Week";"Last Week";"This Month";"Last Month"

Using the "Today" example I have a macro that needs to filter a subform within the main form.

Here is the Marco:

Condition = [Forms]![IssuesSelectionAndReporting]![OpenedDateFilter]="Today"

Action = ApplyFilter

Where Condition = [Forms]![IssuesSelectionAndReporting]![IssuesSelectionSubform](Year([Opened Date])=Year(Date()) And Month([Due Date])=Month(Date()) And Day([Opened Date])=Day(Date()))

My error is Unidentified function '[Forms]![IssuesSelectionAndReporting]![IssuesSelectionSubform]' in expression.

Can someone help me with my syntax?

Thank you, in advance.

I've got the following code:

Option Compare Database
Public Function getColour() As String
getColour = Me!frmRooms.Form!subfrmChairs.Controls("ColourType s").Value
End Function
Public Function getType() As String
getType = Me!frmRooms.Form!subfrmChairs.Controls("ChairStyle ").Value
End Function
Public Function getCondition() As String
getCondition = Me!frmRooms.Form!subfrmChairs.Controls("Condition" ).Value
End Function
Public Function getArms() As Boolean
getArms = Me!frmRooms.Form!subfrmChairs.Controls("Arms").Val ue
End Function

then the following query:

SELECT Items.ItemID, Items.ItemType, Items.ColourTypes, Items.Condition, Items.Arms
FROM Items
WHERE (((Items.ItemType)=getType()) AND ((Items.ColourTypes)=getColour()) AND ((Items.Condition)=getCondition()) AND ((Items.Arms)=getArms()));

I open my form, move to a record who's subform has some chairs in, run the query and get an "Invalid use of Me keyword" error. Can anyone tell me why and how to get round this?



I'm opening a form using the method below. I want the form to display just one single record, where the ID (coID) matches a value in a subform.

DoCmd.OpenForm "Contact", , , "coID = [Forms]![Consultation]![ConsultationContactsSubform].Form![coID]"

Problem is that the WHERE condition does not seem to have any effect - all records are displayed. Any idea why?

(More details: I'm opening the "Contact" form from a form called "Consultation", which has a subform called "ConsultationContactsSubform", which displays a list of people (contacts) involved in the consultation. The contact ID (coID) is also one of the fields in the subform. I think I have everything correct. I've even tried as a test, setting an unbound text box to the subform coID using the exact same condition. That works fine.)

Hence I'm pretty sure the Where cluase condition is correct. COuld it be something to do with my Contact form settings forcing it to display all records? Contact is a form bound to a table of the same name.

Many thanks.

OK trying my best to figure out an append query but having little luck

Hopefully can explain what I am after.

I have a main form that has a number of fields two of which I need to match up in a where condition: ref and ryear.

On this main form is a subform where the data from the append query will be pasted into.

I need to match the two fields on the main form (ref and ryear) to the ref and ryear fields contained within the query. Once the query has been run it will then append append the record/ s in to the subform.

In the append query itself I have three like conditions

1) Like ref
2) Like ryear
3) Like plot number

I would like to remove (1) and (2) from the query using a where condition but can not figure out how.

I am familiar using a where condition on a form to open up another form and I trying to do this with the append query. I don't want to open up another form with the query behind it.

Hope this makes sense


A new problem has cropped up.

My form is opening blank. I have tried compact and repair. I imported everything into a new database. I have checked the query to make sure it has data. I have checked the form's properties to make sure Data Entry is set to No on the main form AND the subforms. I deleted the query and built a new one. I even built a new form form the same query - the new form works. So the problem isn't the query.
The last thing I tried was creating a blank form from the same query and copying the controls on the original form into the new one. Everything works. The question now is why? I added Allen Browne's discussion for anyone else who might have the same problem.

I looked on Google and found Allen Browne's explanation - see below, but this does not seem to apply. The images show the settings I am using. I have attached a stripped version. (Note that although the second image shows the textbox control source as tblTransactions.TransactionsID, the control source is actually from the qryTransactions TransactionsID. I was trying to see if the problem was in the query or the table.)

From Allen Browne:
The Cause
It happens when both these conditions are met:

There are no records to display, I have records in the query
and No new records can be added. New records can be added
Condition (a) can be triggered in several ways. Examples:

The form's Data Entry property is set to Yes. (This means the form shows no existing records, i.e. it is for entering new ones only.)
The form has a Filter applied (or is opened with a WhereCondition) that yields no records.
The form is based on a query where the criteria yield no records.
The form is based on a table that has no records.
Condition (b) can be also be triggered by several things:

The form's Allow Additions property is set to No.
The form's Recordset Type property is set to something other than Dynaset.

I asked a similar question a little while ago, and some answers made me decide to basically just start over with a different design/structure. I have a customers form, and an Orders form, with Order Detail subform. I am trying to put a button in my Customers form that when clicked, it opens the Orders form for that customer, but the Order Details subform is blank, or in Add mode. Right now I can get it to open to the current customer, but it's to the record of the first of any previous orders. How can I get it to a new order?

Currently the onclick event is an embed macro where:
Action is OpenForm
Form Name is Orders
Condition is ="[Customer ID]=" & [ID]

I can also get it to open the whole form in add mode, and then just select the customer from the dropdown list, but I'm trying to eliminate that step.
Any suggestions???

I've got DB which tracks Events and Contacts; i.e. contacts and the events they attended, and events and the contacts who attended them.

I based this form off of the Students DB Template Microsoft provides. I have 2 data forms, 'Contact Details' and 'Event Details', which the user can open, look at data directly about that, say, contact (like address, phone, etc), and also click on a tab to view a subform datasheet of all events the person has attended. Same setup for Events and a tab for all participants.

In both forms, a SELECT DISTINCT query runs off of the EventID or ContactID rowsource in the datasheet which allows the user to select events or contacts, respectively, that are already in the DB and add them to that list.

My issue is this: in the Contact Details form, if you go to enter into the events subform datasheet a NEW record (a new event not in the DB yet), a window prompts you saying this record is not in the DB and do you want to enter a new record. If you click YES, it opens the 'Events Details' to a new, blank form and you can enter away.

This is the code under the On Got Focus for this subform:

	Condition- [Screen].[ActiveForm].[Name]=[Form].[Name]Action- Requery
Arguments- =[Screen].[ActiveControl].[Name]

This is under On Dbl Click:

Action- StopMacro

Arguments-Events Details, Form, , ="[ID]=" & [Screen].[ActiveControl], , Dialog


Action- Requery
Arguments- =[Screen].[ActiveControl].[Name]

I've copied the same form for my 'Events Details' subform, to select contacts from the existing DB in order to track who was at the event the same way as above for contacts. So, I followed the same kind of setup as the 'contact details' subform, but changed the code under On Dbl Click to:

Action- StopMacro

Arguments-Contact Details, Form, ,  ="[ID]=" & [Screen].[ActiveControl], , Dialog

I get the prompt that the record is not in the DB, but it doesn't open a new, blank contact detail form. Do I need to change something else? I'm not sure where to go from here. Everything else about this form is working properly, just not this. I'm not sure how the [Screen].[ActiveControl] command works or what it is trying to call.

I'm using Access2007 and Windows XP.


Not finding an answer? Try a Google search.