Hi There,
I know this subject has been beaten to death, but I have searched the forum and cannot find a post relating to my particular problem.
I have three cascading combos on a subform that work fine and dandy when I open the subform on it's own, but when I open the main form that the subform is embedded into, I get the following message [IMG]cascade[/IMG]
I know I am missing something simple.
Any advice would be most welcome.
Basically i have a form which logs error corrections. The users enter a 'date received'. My user has now asked to have a date due to automatically be calculated by adding six days. I have done this with no problem:
=DateAdd("y", +6, [date received])
My form is not unbound, this date due field is the only unbound field i have. This date does not need to be saved in the table, just to appear when the user opens the form, which it does.
The problem which i am having is that i have a report which runs of a query, printing out the error correction, when the user types in the auto number required. The records added after i put the date due field, will not print out and when i run the query and type in the auto number of these records they do not appear...yet they are stored in the table. Obviously this is due to the 'date due' field, but im not sure how.
I really don't understand what is wrong. Could someone please give me an insight into what maybe wrong or tell me what to search for in the forum, because the searches i have tried have not come up with anything.
Thank you
I have a SQL query in which I have created a parameter to be entered by the user.
This works ok, however... everytime the user is asked to enter the parameter twice...
How can I stop access from asking users the second time?
Cause... the way I see it, one time should be enough and the query itself doesn't state the line in which the parameter is twice either...
Thx!
Hi guys,
A friend of mine has recently created a simple database which holds details of childrens story books. It logs the name of the Author, the storyline, its character etc in various tables...
He's currently creating some simple queries for his database. However, there is one query which neither he nor I can figure out, I'll try to explain to the best of my ability.
Basically, the 'story line' field in his database explains the basic plot of the book, some of the plots have the words "son", "dad", "father" and "boy" in them.
Now, he wants to create a simple query, which looks up and displays data which contains the keywords "dad", "father", "son" etc in them. So far he's tried...
Like "*Son*" Or Like "*Father*" etc...
This worked, briefly. The query was bringing up storylines with both contained the words "son" and "father" , which is correct. However, the query was also bringing up story lines with the word "song" in them, or any word which remotely related to one of the keywords, but didn't contain the exact criteria in the query (son, father etc).
In basic terms, how do you create a query which only brings up words that match the EXACT query criteria? In this case, 'Son' or 'Father' or 'Dad' etc? Rather than words which only briefly relate to the criteria, such as 'Song' or 'Fat' etc?
Thanks for your cooperation, feedback and examples would be highly appreciated.
Thanks all,
Jonathan.
Heya, I know this is probably something simple as I know I have done sql problems like this before. However, I am wanting to show cars which have the same name but are of different colours (without duplicates), I figured I could just use a SELECT DISTINCT? However, when I do this it just brings up one car name and one colour when there are multiple colours.
SELECT DISTINCT car.carname ,car.colour
FROM cars;
I am guessing its because the SELECT DISTINCT statement is applied to both the name and colour.
If somebody could just tell me what I am doing wrong because I am guessing its something simple yet I am too frustrated to see what it is now!
Thanks =)
I have no idea wheather this is supposed to go in Queries or Forms, so feel free to move this to either.
Here's my problem:
I made a query that looks at three text boxes on a form. I'm not sure how to set up the buttons correctly so I just bluffed it (like always :) ). Every time I run the query it keeps wanting me to re-enter the data that it was supposed to look at anyway! If you need more info, just ask. Thanks.
Anonymous_354
P.S.: I'm really new at this. I just learned about Access a week ago.
P.P.S.: I use Access 2000
I have a search form passing text from unbound controls to a query. These two fields (AuthorName & Title) of the query are from two different tables (Authors & Papers) set up in a many to many join. The 3 dummy records I am using are as follows:
Record 1
AuthorName: Smith (Author 1)
Title: SmithTitle
Record 2
AuthorName: Smith (Author 1)
AuthorName: Jones (Author 2)
Title:SmithJonesTitle
(i.e. two authors for this record)
Record 3
Author: Jones (Author 2)
Title: JonesTitle
If I stick in 'Jones' into the Author field and 'JonesTitle' in the Title field I correctly get 1 result from the query (record 3). However, oddly, if I put in Smith and SmithTitle I get two records (2 x record 1)!!!!! There aren't two records!!! Similarly if I leave both blank I get all the records and again there is a duplicate of the Smith record (and only this one is duplicated!)
The query criteria are:
[AuthorName] Like [Forms].[Search]![Author] & "*"
[Title] Like [Forms].[Search]![Title] & "*"
Any ideas???
Thanks
Hi All,
This is my first post so can I just say how great and useful this forum has been for me since I joined! I have found pretty much everything I have wanted without having to ask, so thank you everyone!
I am only beginning with Access at the moment, as I try to implement some improvements to the way my company works!
I have one really simple, but annoying issue at the moment and I cannot find the answer anywhere!
I have a currency filed in my table. I want to format it in USD, but USD is not available in the format options, just £ and €. I cannot see where the hell I get the USD $ format from?
I know this is probably well simple and I am being a plum!
Hi!
I am trying to find a solution for this many to many relation and combos! I ve managed to do the one-to-many as the Mile-o example but i have trouble getting the same result right on many-to-many relations
as you know access needs us to create an extra table for many-to-many relations! but the example talks about the current an the previous table! how can i have 3 cascading combos with many to many relations to each other???
i also have a fourth but this one is related as one to many with the 3rd!
for more information i have:
Many Cars-to-many Categories of brands of parts
Many Categories-to-Many Brands of parts
One Brand-to-Many parts
the many parts combo is also a record selector!
In the beginning I had one table of parts with 4 foreign keys then i changed it to the current one with the previous chain of relation ending to parts with one foreign key! is this better???
Thanx! I hope I made my proble as clear as i could :)
Please Heeeeeeeeeeeeeeeeelp :) :)
Can anyone kindly help me with the attached
database. I need to synchronize 3 combo boxes.
based on the choice made in the previous box.
I have seen it done before using text fields but
not using numeric fields as in my case here and
without using queries.
I have set the RowSource for the 3 boxes on the
OpenForm event and have removed all my code
attempts.
Thanks for your help
Hello!
I got a database with two tables in it.
Site table has following fields: ID,SiteID
Customer table got following fields: DropID, Dropname, SiteID.
These two tables are connected in a One-to-Many relationship.
I got a form with two combo's . I want to select "SiteId" from the first combo and based on the selection i do in the first combo i want the second combo to just show the relevant data (dropid and dropname) for that "Site".
I got one table named "Site" and one table called "Cutomers".
The database contains alot of other stuff and much of it is in norwegian so im not sure how usefull it would be if i posted the mdb here but if someone think its usefull then i will post it.
I can choose from the combo boxes and it works fine but the connection between the two are not working. Not matter what i choose in combo1 i get all recrods in combo2.
Can anyone help?
wbr
Espen
I am new at programming in Access and I am writing a database for our church. I have 2 tables, one called “Donators” and the other “Donations”. In the Donators table I have their Last Name and First Name along with their address etc. In the Donations form I use a combo for the Last Name and a separate combo for the First Name. I did this so the person entering data doesn’t have to use a mouse but simply tab and type. Both combos are bound to the ID number. Without using a cascading combo, all First Names appear not just the ones relating to the Last Name. I checked the examples on cascading combos and tried them but without success. I could really use someone’s help. Thanks.
View 3 Replies View RelatedI am making a Invoice DB. I have made a combo for Suppliers which takes data from SuppliersTable and data is recorded in InvoiceTable. I have another Combo for Services which takes data from SuppliersTable and records up in InvoiceTable. And I have another field (PaymentPeriod) dependent on ServiceCombo
I want to make Services Combo dependent on Supliers.
How can I do that?
Combo 1 = SuppliersCombo
SuppliersTABLE: suppliers data origin Field: SupplierMaster
InvoiceTable: supplier data destination Field: Supplier
Combo 2= ServiceCombo dependent on Suppliers Combo
SuppliersTABLE: suppliers data origin Field: ServiceMaster
InvoiceTable: service data destination Field: Service
Form Field: PaymentPeriod dependent on ServiceCombo
SuppliersTABLE: suppliers data origin Field: PaymentPeriodMaster
InvoiceTable: paymentperiod data destination Field: paymentperiod
How can I do these dependent?
I hope I explainned myself clearly, I am access first timer.
Ok I just cant get my head round this stuff!
I have created 2 tables
TBL_ObjectList contains data for objects in the night sky
e.g.
IDMessier_IDNGC_IDCommon_Name Type DistanceConstellation Apparent_Mag
1M1NGC 1952Crab NebulaSupernova remnant6.3Taurus9
TBL_Observations contains observing data where the Object field looks up the data in TBL_ObjectList
e.g.
ObservationIDObjectDateNotes
1M122/11/2007blah blah
I created a form FRM_Observations with a combo box to select the object e.g. M1 and it fills in the rest of the data for the object in the form.
My question is: How does this then relate to TBL_Observations?
When I switch to a new observation record the object selection stays at the one for the previous record.
I know this is probably basic stuff but I just cant see it.
Is there a simple online "how to" for this stuff? as I just cant seem to get to grips with it.
Many thanks for your patience :P
Paul
(No scripting knowledge)
Right i have a project form which has a sub form of location
One project can have many locations
therefore it is nescarry to enter more than one location
Problem is with the cascading combo boxes i have for country to region to sub region, is that you can enter one fine, but if you enter another is deletes the information from the first entry
can anyone fix this please
i have uploaded a sample
Hi All,
I have a query about cascading and combo boxes, which I was kindly, advised about previously. However, I’m stuck as to how to cascade using the selections from more than one combo box. Here are the tables involved
tblLine
fld LineID (PK)
LineDesc
tblMachine
MachineID
MachineDesc
tblLine_Machine
LineMachineID (PK)
LineMachineDesc
LineID (FK)
MachineID (FK)
tblCategory
DTCategoryID (PK)
DTCategoryDesc
tblDTReason
DTReasonID (PK)
DTReasonDesc
tblCat_Mach_Reason
CatReasonID (PK)
DTCategoryID (FK)
MachineID (FK)
DTReasonID (FK)
What I’m trying to do is create a form to enter downtime incidences and would like the downtime reason to be selected following a series of combo boxes. The first combo box selects the Line on which the downtime has occurred, using this, the next combo box selects the machine. I would like the category selected independently but then need the Downtime Reason selected based on the selections in the machine combo box and the category combo box. This is where I am having the problem.
I can get the machine combo box to display machines dependent on the Line selected and I can get the downtime reasons combo box to display machines dependent on either the machine selected or the category selected, but I don’t know how to get it to do it based on both.
e.g. the category may be mechanical, but there are reasons that are mechanical and specific to each machine.
Any help with this would be most appreciated.
Regards
Keji
I've asked this question before, but no one's answered it yet. How do I create a form where I can enter many records for projects (from tblProjects) and associated workstreams (from tblWorkstreams), so that I can select a project from a combo and then pick a workstream from a filtered list?
With help from Colm, I've been able to set up the combo's and get the filter to work, but unfortunately, the Project combo (cboProjectSelect) filters the cboWorkstreamSelect for ALL records! This means that only one project can be selected for ALL records.
The way it's been set up so far, is there is a query which lists Workstream, WorkstreamID and ProjectID, and the criteria for ProjectID in that query is: [forms]![frmTshtEntry]![frmTshtProj]![cboProjectSelect]. The cboWorkstreamSelect combo is then based on this query.
I am really stuck on this, and any help would be greatly appreciated!
Sunil
Hi,I shall be grateful for any help.
My problem
Tables:
Cars
Customer
Invoice
TblMake
Tbl Model
I have used cascading combos for Make and Model. They work fine. I have set up tblMake as follows:
ID
Make
I have make tblModel as follows:
ID
Make
Model
I use these cascading combos on a form. The data from this form goes into the "Cars" table. I have set the "Column Bound", "Width" etc. to show the name of the car, not the number.
However, I have created a search form. This uses a SELECT...... case in the Row Source. The details of each car are shown in a unbound listbox. This select case is supposed to show "Make" and "Model", which it does, however - they are numbers!!!
Please can anyone let me know how to change it so it shows the actual names of the car.
Thank you
OK, here is the problem. I have the following tables:
Customers
Consignment
ConsignDetails
Employees
Products
Suppliers
CompanyColors
CompanySizes
and I am creating a form to view the items currently on consignment with the customer. I have the main form 'Consignment' that holds the CustomerID, ConsignID, DateStarted, Employee (SalesPerson), Inventory Interval, and Closed (Y/N)... The subform lists in datasheet view the records in form 'ConsignDetails' that have the same ConsignID. The controls on the subform are :
ProductID
UnitPrice
Quantity
Color
Size
The ProductID is selected in a combobox from all products. The UnitPrice defaults to a DLookup() from the products table based on the ProductID, but can be changed and stored. The Quantity is a manual entry field. Color and Size are both supplier-dependent fields, in other words, the supplier of the product has unique Size and/or color codes or descriptions. I have no problem creating a combobox either in the AfterUpdate event of the ProductID or in the MouseDown event of the Color or Size field to limit the drop-down list to only those Colors/Sizes from the CompanyColors / CompanySizes tables with the same SupplierID as the Product in the first control.
So far so good, but here is where I am beating my head against the wall. Every time I choose a product from a different supplier, the Color and Size controls display ONLY the items from the current Supplier, not the items from the Supplier of the Product on their individual records. I have seen ways to make the combo popluate a locked textbox, hide the combo, and display only the text, but in a data entry application the user will be primarily on the keyboard, and the solutions seem to make it almost impossible to do without using the mouse over a continuous form.
I am using Access 2002 (ver 10.6501.6735) SP3 to create an ADP using MSDE to link to SQL7 tables....
Any help will be appreciated, I have spent almost two weeks trying different approaches and may be too close to the forest to see the trees.
Thanks,
Bruce Davis
OK ive got my 2 vaguely working combos which select the surgery, and the staff.
On the same form I want a couple of tickboxes which show for example, whether a person attended the course, if they had to pay etc (all yes/no answers).
How do I get the tickbox to update? i have tried to requery it but nothing happens, it just stays as what the last thing done to it was.
SHould i be using a subform here do you think?
Heya
Ive got my combos working fine now (thanks to all you kind folk on this forum!) but now that I have opened the main form (in which the combo box form is nested), once I have selected an item in the first one, and then click on the 2nd combo, a parameter box comes up saying "Forms!Training Record!Course_title_combo" and wants a value.
I cant see why this doesnt work when the form is nested, any ideas?
Gazz
Hello to all !
I have this db where i wantto include 4 and if possible 5 cascading combos where the user can select records from a table and view them in a subform which is in datasheet view.
All is going well until i try to add the 4th combo.Then when i select the 4th combos selection the subform displays nothing.
If someone could point me to the right direction i would be most thankful.
I have attached the db.
Thx for all replies :)
Hi Guys,
I have two unbound cascading combo boxes on a form that limit the data for a stored field.
I am designing this as a subform to show software installed on a PC (hence the need to have a continuous form). As the manufacturer, software package and edition are already stored in my database, I am storing only the ID of the edition to limit duplicating data.
I would like to show the values in the cascading combos that relate to the edition ID stored as well as filtering the edition ID using the combo boxes (i guess a filter and a reverse lookup)
Please see attached pic. First record was entered as Microsoft Autoroute. New record for Manufacturer Frontier, change the previous records combos.
Thanks for you help
Ben
I'm trying to filter a combo based on the selection in another combo.The twist is that I need the row source of both combos to include an "*", "(All)" selection, as I am using these combos to filter a listbox. When I add the Union query with "*", "(All)", the row source in the 2nd combo is ignoring the where clause.
After making a selection in the Comp combo, the Emp combo should be filtered. It works with no Union in the row source, but does not work with the Union in the row source.
Hello,
i'm not sure how to write a query, maybe it'll take VBA function to accomplish this, but I need to display the results for all invoices for an item.
Little background:
1. This set of invoices are of only one particular product line as it's this product line that needs this special handling. These are Guitar orders.
2. Each guitar item can have option items on the same invoice. So basically these are all considered item numbers.
3. The guitar starts as the bare / basic guitar. The customer can choose to select one or many options for a custom guitar.
4. These orders will need to have special guitar programming codes that need to be entered into their guitar programming machines. Call these "Codes".
5. Depending on the option items for the various guitar items, the Codes could vary.
6. Need to be able to display ONLY the Codes specific to the guitar or guitar and option combinations.
I have several tables:
1. Guitar for all the guitar items
2. Options for all the option items
3. ProgrammingCodes for all the programming Codes
4. ProgramCodes for creating the above combinations with a field named ComboID.
5. GuitarHeader - the invoice number and guitar item
6. GuitarDetails - the invoice number and option item (invoice number to invoicenumber link to the GuitarHeader)
Then there's a main form and a sub-form for the end-user (guitar programmer).
1. The main form is the Guitar items
2. The sub-form is the ProgramCodes fields
this form is used for this end-user select the Guitar and options (where necessary) and select the Codes and enter the ComboID for all.
for instance:
These are all the possible codes for AE185.
AND depending on the Options selected for AE185 on invoices, the Codes will differ.
The ComboID is the differentiator.
Code:GuitarOptionCodeComboIDAE185 185RR 1AE185 186RHT1AE185 187RT 1AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B 4AE185BB188RT38B 4
so for the Codes that repeats in the ComboID is only because the Option(s) also requires that Code as well as the other Codes.
for instance, if the invoice ONLY has Option 38 from the Options defined for Codes, then the result would be:
Code:AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B4AE185BB188RT38B4
Code:Invoice Guitar Codes123456 AE185 185RR 186RHT 187RT38 <-- say this invoice had Option 38 that was the only match234567 AE185 185RR 186RHT 188RT-B <-- because BB was the only one that matches345678 AE185 185RR 186RHT 188RT38B <-- because both 38 & BB matches
So guess the question is how do I write a query or function (VBA) to be used in a query to get the results for ONLY the matching options so the correct codes for just these options are displaying?
It's difficult to me in figuring that out. how to make sure that the result does not show the ALL the codes that match but ONLY the codes in the combinations. Again, hence the reason why I got to the point of having Combination IDs to differentiate that.
hope I made sense here. Not sure how else to ask the question other then to try and provide examples
let me know how else to explain if this is not helping.
I'm trying to follow Allen Browne's - Combos with Tens of Thousands of Records [URL] ....
However, I keep getting the error...
Compile Error:
Invalid use of Me keyword
(This being a new module)
That's how it shows in his code (the use of me.comboname.rowsource) but it's not working for me.