473,322 Members | 1,736 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Using one access form column to filter another.

4
I am programming an access database and want to take a project build and update the quantities on the master list. the project build table has the part id and the quantity needed and the master list has all the part id's and i want to filter it down to the specific parts used in the project as specified in the project build table. i want this to happen after i trigger an event on the report generated from the project build.
May 10 '16 #1
10 1173
zmbd
5,501 Expert Mod 4TB
Are you talking about something along these lines:+ Cascading Combo/List Boxes

Also, keep in mind, reports are normally just that, a report. In theory, they really should be the terminus point of the data handling. IMHO, there should not be any interactive controls on a report (despite the fact that MS seems to allowed this, often what is allowed is later found to not be a good thing :) )
May 10 '16 #2
PhilOfWalton
1,430 Expert 1GB
Do I gather you have 2 questions, the first being to update the stock quantity on your master table as you select the parts from the project build table. The second thing you appear to want to do is print those stock quantities.
I am concerned that you do not mention a table for your project, so you have no method of checking where your stock has gone.
If you can clear this up I'm sure you will get further help.

Phil
May 10 '16 #3
zmbd
5,501 Expert Mod 4TB
@Phil,
OP has one main question and the condition - otherwise I would have either deleted the secondary question or closed the thread:
[kkDubs]
+ i want to filter it down to the specific parts used in the project as specified in the project build table.

+i want this to happen after i trigger an event on the report generated from the project build.
For now let us stick to that within this thread.

@kkDubs
If Phil is correct and there is a secondary question related to the stock please start a new thread.

++ We do try to limit threads to one topic at a time otherwise things can become very muddied.


Thnx
-Z
May 11 '16 #4
kkDubs
4
@Phil, @zmbd you are correct in that i have one main question of how to filter a table column based on another tables column. the condition part was just asking what the vba code to call this table from a say dblclick() on a report. Maybe i can specify further with is it possible to use WHERE clause or ApplyFilter on a subform to limit the records to display only the ones that have the same part # as the ones in the build table. the master table has 100 parts and i want to limit the displayed subform of the 100 down to the 20 part #'s in the build table then modify them.

Thanks for any help
KK.Dubs
May 11 '16 #5
PhilOfWalton
1,430 Expert 1GB
All you need is a simple query with both tables.
something like
Expand|Select|Wrap|Line Numbers
  1. SELECT TblMaster.PartID, TblMaster.StockQty, TblParts.PartsUsed, 
  2. FROM TblMaster INNER JOIN TblParts ON TblMaster.PartID = blParts.PartsID;
  3.  
This will only show items in the master table that have the same PartID as in your project build table.

Phil
May 11 '16 #6
kkDubs
4
@Phil my problem with that is in a query i cannot update the values in the table from it. When you run a Query like this (which i already have) you cannot change the values in the fields when you run a query.
May 11 '16 #7
zmbd
5,501 Expert Mod 4TB
kkDubs,
++ Did Phil's query provide the information you are after?
Most queries are "updatable" in nature; however there are reasons that would prevent an update, please read thru this article:
+ Reasons for a Query to be Non-Updatable

Are any of these conditions met? If so then we may be able to work thru them and help you to build something that will help.


++ Aid you read thru "+ Cascading Combo/List Boxes"
From your first post, this sounds more like what you were after.

++ Also we need to be very clear here on terminology

FORMS - are NOT reports

REPORTS are NOT forms

Normally, FORMS have your user interactive controls such as command buttons, drop-down lists, combo-boxes, etc...

REPORTS are just that... reports. In general practice, reports are static representations of the final data (i.e. a sales invoice or a student report card) that is generated AFTER the user interaction on a FORM.

I know it seems like semantics, and I do apologize if I appear to be going "over board" on this - by way of explanation, many people mis-use these two terms and it can create a lot of confusion within the discussion.
May 11 '16 #8
kkDubs
4
@zmbd i don't think what i am trying to communicate is coming across right. i have two tables where i can modify the fields to my hearts desire but what i want to do is filter one table to match the part # from the other one to display more detailed info on only the parts from the chosen project. the problem with a query is i cannot click on a field and say change the quantity in stock or the quantity needed like i can in a table.
May 12 '16 #9
PhilOfWalton
1,430 Expert 1GB
Please let us have full details of your 2 tables.
The problem I am having is that I can't see what links the parts in the Project build table.
Surely there is another table for the project.

As an example, say I was doing a cook book.
I have a table of Recipes
Expand|Select|Wrap|Line Numbers
  1. TblRecipes
  2. RecipeID   PK
  3. Recipe
  4.  
1 Pastry
2 Meringue

Then I have a table of ingredients (your Master List Table)
Expand|Select|Wrap|Line Numbers
  1. TblIngredients   PK
  2. IngredientID
  3. Ingredient
  4.  
1 Flour
2 Water
3 Eggs
4 Sugar

Finally a table to link the two together
Expand|Select|Wrap|Line Numbers
  1. TblJoinRecipeIngredient   (Your Project Build Table)
  2. RecipeID        FK
  3. IngredientID    FK
  4. Qty
  5.  
Then we can build recipies (I am the worlds worst cook)
So we have for pastry
Expand|Select|Wrap|Line Numbers
  1. 1    1    6 ounces
  2. 1    2    3 spoonfuls
  3. 1    3    2 
  4.  
Meringue
Expand|Select|Wrap|Line Numbers
  1. 2     3    6
  2. 2     4    10 ounces
  3.  
What I am looking for is your equivalent of the TblRecipe

Phil
May 12 '16 #10
zmbd
5,501 Expert Mod 4TB
Most queries are editable/updateable which leads me to supect something is mismatched in your basic database design.
Phil is quite right in that we have too little information about your database structure.
Normalization is normally your best friend as well as a properly designed database.
Please read thru Database Normalization and Table Structures

From your last post just one more question:
Are you after something along these lines
Selecting the record in the first subform (parent)
Brings up the related records from the second table
Selecting a record in the second subform (child)
Brings up the related records in the third table (grandchild)



The way I've designed this requires no VBA or Macro scripting to run.

NOTE: Because this is an example on how to use subforms and table relationships; I've used separate tables for parents, children, and grandchildren. This is not the way I would normally design a genealogy type database - Allen Browne actually has a design I would use for such at database.

> I have a variation on the theme
Form_A is unbound
Three subform-controls on Form_A: sfc_A, sfc_B sfc_C
Two textbox controls (like the one shown above) one that takes the required information from sfc_A that sfc_B uses to link the two and the second takes the information from sfc_B to link sfc_B to sfc_C

Same concept as the image; however, sfc_A, sfc_B, and sfc_C are in datasheet view and it's a bit more obvious to some of my users how to navigate the records.

Form_A serves as just the container for the subforms and the requisite controls. It also has the company logo and some short/simple instructions on how to use the form.
May 12 '16 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Afton | last post by:
I would like to make a form that filters a report by Supervisor and by starting and ending date. I have the supervisors in a combo box, and that works. However, I do not know how to code to let...
3
by: Michael Ramey | last post by:
How can controls on a Windows Form be accessed (or referenced) from another Class? I know how to do it from another Form. The following doesn't work even though the Control Modifiers property is...
1
by: SoxFan44 | last post by:
Hi, I have a DataGridView on my Form1 and a public function that can add a row to it. I want to be able to access that member function from another class. I'm confused as to how to do this. Can...
1
by: Paez | last post by:
Hi there. How can I modify a form control property from another form. Let's say, I have a frmMain that has a ToolStripMenuItem with the current user info. Then, I open another form...
1
by: ielamrani | last post by:
Hi, I have a form with the following text boxes: ID name Open Date Comment. I would like to add a button to the form that opens Outlook with all the text boxes info in it. In other words,...
0
by: charlie weaver | last post by:
Is it possible to send an email in HTML format using and Access Form created with VB 6? Currently my form sends as RTF, but I cant seem to figure out how to send in HTML format.
0
by: jesiecraig | last post by:
Hello, I am new to VBA programming - thank you for your help. I am using a form in Access 2007 to update my database. I use a submit button on the form to send all the values into Access. I...
0
by: Elaine Huseby | last post by:
I have a form "PartsAndInvoices that has all the parts in the database listed in split form view. I want the user to use Access 2007's filter section on the ribbon to choose mutiple filters....
2
Seth Schrock
by: Seth Schrock | last post by:
I have a search form that currently pulls up all the records at the beginning and then uses the form's filter property to filter down the results based on what is typed into the search boxes. As...
3
by: Hagridore | last post by:
I may be about to surrender on this...I've asked in other forums and am not getting the answer I thought I'd get. I'm creating an Access database that I ultimately want to compile into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.