Joining Form with Subform

Dear all,

In my table, I have these fields:

expense_code
profit_center
date
value

I must put in my form these fields: profit_center and date.

I must put in my subform (type: leafs of data) these fields:
expense_code and value.

As I'm a beginner user of access, I only use the assistent to create
my forms and I have a problem =3D=3D> by assitent I only filter my subform
by the profit_center. I must filter my subform by assistent and the
date. Obs. With this united I input datas in the table, it=B4s not for
consult!

For example:

Datas in the table

expense_code     profit_center      date          value
salary                  industrial        01/01/07        1000
salary                  industrial        01/02/07        1500
salary                  administrative  01/01/07         500

What is showing in my form/subform

Form:
profit_center (combobox): then I select Industrial
date (listbox): then I select 01/01/07 (but it's doesn't work)

Subform:
expense_code       value
salary                    1000
salary                     1500

How can I do to filter my subform for both fields: profit_center and
date? My form and subform are linked with my "exepnses" table.

Thank=B4s

Andr=E9.

0
gatarossi
7/23/2007 10:46:25 AM
access 16762 articles. 3 followers. Follow

2 Replies
374 Views

Similar Articles

[PageSpeed] 57

Andr�

A main form/subform construction is used to display data that is in a
one-to-many relationship (i.e., two tables).  From your description, you are
only using one table.

If one [profit_center] and [date] can have many [expense_code]s and
[value]s, then you need to use two tables.

By the way, the word "date" (and perhaps "value") are reserved word in
Access.  What Access means by them and what you want them to mean may not
always match.  Consider renaming these fields.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/



<gatarossi@ig.com.br> wrote in message
news:1185187585.951833.4250@n60g2000hse.googlegroups.com...
Dear all,

In my table, I have these fields:

expense_code
profit_center
date
value

I must put in my form these fields: profit_center and date.

I must put in my subform (type: leafs of data) these fields:
expense_code and value.

As I'm a beginner user of access, I only use the assistent to create
my forms and I have a problem ==> by assitent I only filter my subform
by the profit_center. I must filter my subform by assistent and the
date. Obs. With this united I input datas in the table, it�s not for
consult!

For example:

Datas in the table

expense_code     profit_center      date          value
salary                  industrial        01/01/07        1000
salary                  industrial        01/02/07        1500
salary                  administrative  01/01/07         500

What is showing in my form/subform

Form:
profit_center (combobox): then I select Industrial
date (listbox): then I select 01/01/07 (but it's doesn't work)

Subform:
expense_code       value
salary                    1000
salary                     1500

How can I do to filter my subform for both fields: profit_center and
date? My form and subform are linked with my "exepnses" table.

Thank�s

Andr�.

0
Jeff
7/23/2007 12:18:46 PM
Dear Jeff,

Ok, now I create more three tables:

1) expenses_plan
fields: - expenses_code
          - expenses_description

2) profit_center
fields: - profit_center_code
         - profit center_description

3) period
field: - period_code

And I made relationships with the table expenses_control
4) expense_control
fields: expense_code =3D expense_code.expense_plan
          profit_center_code =3D profit_center_code.profit_center
         period_code =3D period_code.period
         numbers

I have already tryed everything, but I really don't known how to
create this form/subform:


Form:
profit_center (combobox): then I select Industrial
date (listbox): then I select 01/01/07 (but it's doesn't work)


Subform:
expense_code       value
salary                    1000
salary                     1500
..
..
..

This form/subform is used to imput, to exclude and to change datas,
not only for consult. Can you help with this big problem?

Thanks a lot!!!

Andr=E9.







0
gatarossi
7/23/2007 4:34:09 PM
Reply:

Similar Artilces:

Copy value of field to other form
How do I copy the value of a field from one form to another form that is not part of a subform relationship? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200703/1 hi Matt, mattc66 via AccessMonster.com wrote: > How do I copy the value of a field from one form to another form that is not > part of a subform relationship? You can access any form, if it is loaded, with Forms("OtherForm").Form![FieldName] = Me![FieldName] or Forms("OtherForm").Form![FieldN...

Inserting Date in form
Access 2003 I have inhereted a form and one of the fields should enter today's date automatically. It is not doing it. I right clicked on the text box in design view, went to Properties, but I don't know what to do from there. Any help would be appreciated. Heather Fabries Heather, Locate the DefaultValue property of the text control (must be a Date/Time field as defined in your table) Place this value in the DefaultValue... Date() If you need to capture Date and Time... Now() On any NEW record, today's date will be entered automatically. De...

Hide Button after clicking on the button to open a form
Hi, Please let me know how i can hide a button after clicking on the same button. I am able to click on a button and hide another button but does Access allow you to hide the same button you just clicked on? I used the following code to hide another button but why can't I hide the same button: Me.Command30.Visible = False Thank you so much. You can't hide or disable an control that has the focus. Set the focus to some other control and then you can hide the button. I do not know your applicaiton or what you are attempting to do, but I would suggest that you m...

intermittent error message when editing data in a form
I'm running Access 2003 with the back end on a network server, and multiple users running their individual front ends from their local C drives. The users sometimes get this message when they try to edit data in one of the forms: Write Conflict This record has been changed by another user since you started editing it. If you save the record, you will overw3rite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes. Save Record - C...

import & export data on form
Is it possible for a form to autofill w/ data in table and when rest of form is filled out to export all data back into table? Ex: prefill form with: accounts dollars action When form completed: accounts accounts reviewed dollars dollars reviewed action action reviewed comments Ana, There must be a misunderstanding here. Forms don't *prefill* with data. In fact, forms don't have any data at all, tables do. Forms are bound to a table or a query, or maybe your form is unbound? So where/how is your form prefilling wit...

Drop Down doesn't populate form
It think the issue is with the below criteria. The issue happens with the PO_NO has a letter with the number. If it's just a number it works fine. Any suggestion. stLinkCriteria = "[PO_NO]=" & "'" & SID & "'" Private Sub LookUpPO_AfterUpdate() Dim SID As String Dim stLinkCriteria As String Dim rsc As DAO.Recordset Dim db As Database Dim rs1 As Recordset Dim rs2 As Recordset 'Added to Update Vendor Data Dim rs3 As Recordset Dim rs4 As Recordset 'Dim rs5 As Recordset ...

Default Data Form
I have a basic worksheet for which I like to use the default data entr form. In other words it benefits from using a form but is just no worth a lot of custom form making. However while I do have an auto_open macro that moves to the first ne row in the range after all existing records, and then ends o ShowDataForm. But the default form always opens on the first record i the list. Is there a simple way to make the default form open on the row of th active cell, , instead of at the top of the list ? Thanks. Doug Lon -- Message posted from http://www.ExcelForum.com Maybe something li...

Passwords on a Form
I have a form that I would like to password protect. I first created a button and then created the password input box through VB. The only probelm I had was, if you hit the cancel button on the Password input box, the input box wouldn't close. I was looping the code until the correct password was entered but not sure that's the best way to go about it. What would be the best way to do this? I would greatly aprreciate it. Thanks! The best way is to apply user level security so that all your users must sign on when they open your database. Then, you can allow users to have ac...

Export to Excel with form Progress Bar
I have a form which when clicking the command button transfers (or exports) the data from two separate tables in my database into two separate Excel spreadsheets While this is running, I would like to show its status using a progress bar (built into the form, rather than showing it on the status bar, which is hidden) - I've searched on the net but cannot seem to find what I am looking for. Any suggestions or help would be appreciated. Thanks in advance Dan How about this one: http://accessmvp.com/SDaigle/ProgressMeter.ZIP -- Arvin Meyer, MCP, MVP http://...

opening user form when worksheet activated OR embedding a userform in a worksheet
either of these options i am open to. i do not want the userform to open when the workbook is opened, but rather i want it opened when the specific worksheet is activated basically if worksheets(1).activate then open UF end if this is just the idea. Rightclick on the worksheet tab that should have this behavior. Select view code. Paste this into the newly opened code window (usually on the right side). Option Explicit Private Sub Worksheet_Activate() UserForm1.Show End Sub (Change the name of the userform.) Then back to excel. Change to a different sheet, then select your sheet ...

Convert Template to Form
How can I possibly convert an outlook tamplate to an outlook form? Thanks for your help. "template" is a type of "form" in Outlook, so it's not clear what you = want to do. Can you give us more details?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <ksenya.lim@gmail.com> wrote in message = news:1156945681.610123.4310@74g2...

using controls in forms
Hoi, I want to check a form before sending, if two checkboxes are set. Can anyone help me Thankx Rudi ...

Turn 'On' or 'Off' 'Subtotal' in Access PivotTable Forms
Hi All This is my first time here and am hoping for some assistance. I have an access pivot table in a form and I am able to insert/remove totals and fields in the pivot table. However I am not able to turn 'On' or 'Off' the 'Subtotal' on either the column or row fieldssets using code (ie VBA). Has any one come accross this issue? Any suggestions? Thanks so much Ram ...

Subform Error Message
Hi Guys, I am try to make a main form with a subform for invoices. The tables I have are: Customers : CustomerID, First Name, Last Name, Address etc etc Products : Product ID, Desc, Price, RRP Orders : OrderID, Date, Status, CustomerID Order_Product : ProductID, OrderID I create the main form which works. I create a query of order_products and products with a join and then put it as a subform but when I go to enter a new product via the subform i get this error: http://img100.imageshack.us/my.php?image=screen3ee0.jpg Anyone know why? The query contains a join, and I have the child pare...

Form Query Refresh
I have setup a form with multiple tabs that represent each milestone of the process. A query supports each so that items "move" from one tab to another based on what stage the item is at, allowing the user to select the item & click a button to enter their portion. This so far works great except that the data on all of the tabs is only valid as of the time the form is opened. Is there a way for the information to "refresh" on a regular basis without closing & reopening the form? "David" <dbain3175@gmail.com> wrote in message news:cea2d...

Entering a Range of Dates into a Form
i would like to build a control in a Form that allows me to select a range of dates that will enter into a query, such as >Date()-8. I have the macro and the command button down ,I can only get this control to accept a short date - 1/25/08 etc....I really want to enter >Date()-8. Then the query will return all the data for the last 7 days prior to today. I really want to enter >Date()-8 No you don't. You can't expect users to get that right every time. You are inviting continual crashes and even though they did it, you will get the blame. And, to a certain extent, des...

D Look-up? Auto fill Collumns on a table/Form based on another collumn.
Hello All, I am working on an Education Database, were we will also be tracking the redentials of our employe. On the the Sub Table, I would like to have the the Employee ID field automatically fill when the First and Last name are entered, or visa versa. I think I need to use the Dlookup function but I'm not sure how I would write the formula. Can anyone help? Steve You can bind several combo boxes to the same field. If you update one, the others will update. -- Duane Hookom Microsoft Access MVP "Steve" wrote: > Hello All, > > I am working ...

Filtering a form
Hello everyone, I've run into a problem i'm not sure how to solve. I have a form that with the click of a button filters the form based on the value in a text box. I also have my own controls for record navigation for navigating through the filtered form. There is a text box called "Total Records" that is supposed to display the total number of records in the form after it is filtered using Me.Recordset.RecordCount as the source. This doesn't appear to work properly; for example with one search, Me.Recordset.RecordCount returns "2" when the...

Using Form to update table
Hi I am a little lost. I have a form which has a subform. In the header of the form there is a combo box to selete numbers and a text box to enter a date (not current date) On the subform I have 2 text boxes which onces the 2nd textbox is populated another set is to come underneath ready for completion (continues form). On the main forms footer I have an update button which is to perform a task. What I am lost with is how can I get the subform to work as a continues form so once the 2nd text box is completed another set is ready for entering. and How do I then click on the button to fi...

How to print a simple list of tables, queries & forms ?
How can I print a list of all the tables, queries and forms in my data base? I've tried Tools | Analyze | Documenter | Tables | Options | Select All | ... Include for Table = Nothing ... Include for Fields = Nothing ... Include for Indexes = Nothing We only want a simple list of tables, queries & forms. thanks for any help. Mel Dim o As AccessObject For each o in currentData.AllTables debug.print o.Name Next o For each o in currentProject.AllForms debug.print o.Name Next o For each o in currentProject.AllQueries debug.print o.Name Next o On May 1, 10:38 am, Mel <MyEm...

#Error in subform foreign key
Hi all, I am getting "#Error" in my foreign key id field for two subforms, but not the third. As far as I can tell each is setup in their respective tables similarly. I have tried deleting, changing thpe data type, I've even re-created the whole database (re-created suspect tables and imported everything else). Whenever I try to add values in any field on the subform I get "The value you entered isn't valid for this field..." I'm at a loss. Thanks in advance, Nick "NickX" <nicknnator@aol.com> wrote in message news:1176403733.363533.24828...

text box filter by form
For some reason that I just can't explain, a text box on a form associated with a single primary key field behaves unexpectedly when I do a filter-by-form. Instead of listing all of the records from the source table as choices in combo box mode, I only see "Is Null" and "Is Not Null" as choices. I have an older form from another database that behaves as I hoped this one would, yet there seems to be no property differences between the two text boxes. Can anybody explain what I'm missing please? Having "Is Null" and "Is Not Null" as filt...

AUTONUMBER Field Increments after "UNDO / CLEAR FORM"
I have an autonumber field in a form. When I use an "UNDO / CLEAR FORM" command button (imbedded onto this form) to undo the contents in the form, the AUTONUMBER value of the next record inputted increments by one. Example: I am adding / inputting record number 15 (I have the AUTONUMBERed "RECNUMBER" field visible on my screen form to enable viewing of a specific record), I discover an error and UNDO / CLEAR the entire form using the command ("UNDO") button I've created. When I wish to re-input the data, I see the record number value of the new r...

Data Entry Form and Subform problems
Hi, I have a form and subform that I am trying to use for data entry. However, it's not working out as planned. I have two tables: tblAccount [AccountID] (PK) [DefaultAddressID] ....more fields tblAddress [AccountID] (Combined PK) [AddressID] (Combined PK) .... more fields tblAccount holds Account records including the default address to be used in all transactions, although an account can have more than one address. tblAddress holds all the addresses for all accounts with a combined primary key of AccountID and AddressID. tblAccount.AccountID is an autonumber field. tblDefaultAddre...

Combo box to control two sub forms
I have a combo box on a form that I want to control the data viewed in two sub forms. The combo box list months in the mmm format. One of the sub forms is requests that have been approved with the date (mmm-yy) the other sub form is requests that have been denied with the date (mmm-yy). When I select Jan from the combo box, I want the sub forms to display only the records having to do with that month. Is this even possible? Your combobox provides only a month criteria while your data is both year and month. That's not a problem except when you select a month, your subform d...