Subform / Combo Query
Apr 27, 2005
Hi Folks
Attached is an example of what I'm looking to do.. I'm new to VB code and not great on queries either.
I'm looking to simply choose the department from the dropdown combo box on the form and the subform above it to update with showing all the results containing the department from the selection.
Not sure on the best way to do this, either via a query or from the table directly, or even some way to use the drop down to filter the table being displayed on the subform.
Anyone got any ideas. Thanks
View Replies
ADVERTISEMENT
Jul 25, 2005
Hello All,
Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.
I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.
ComboBoxes include; Year. Movie. Country.
What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.
I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.
' Code to change the movie information, when they select the combo box
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.
View 3 Replies
View Related
Apr 27, 2013
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;
Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
View 11 Replies
View Related
Jul 26, 2013
All I am trying to do is get a subform to requery after choosing a name from a combobox on the subform itself, thus updating the records on the subform. The main form name is "BasicTestOneF" the subform name is "ICminiBasicF" and the combobox name is "Combo4"
I have the combobox requery on After Update, but I still get the "Enter Parameter Value" popup. I've spent the last hour and a half just trying to get this simple thing to work and have gone through I don't know how many sites and forum posts, tried every combination of VBA code I've found, and I still can't get it to work.
View 14 Replies
View Related
Mar 28, 2014
I'm an Access newbie and I'm having trouble restricting a combo box on a subform based on the selection of the combo box on the form.The combo box (Combo1) on the main form (FrmOrganisation) gives a list of companies, I want the combo box (Combo2) in the subform (SbFrmEmployees) to list people only from that company. I then have a subform within that subform that displays information about the employee.
View 3 Replies
View Related
Feb 2, 2015
Main form frmSiteDetails
frmRooms (subform) holds Room information for the rooms on each floor. This form has LinkMasterField [SlaveFloorsID] and LinkChildFields [FloorsID]
frmFloors subform holds floor information for each floor in a property. This form has LinkMaster [SiteID] and LinkChildFields [SiteID]
Both these forms sit on a Tab Control (TabCtl57) and do work properly.
What I would like to do is to also have a Combo to pick a floor number from frmFloors and filter frmRooms. Both these forms have FloorsID fields. This would save a user having to go back to frmFloors to pick a different floor and then go back to frmRooms to view the room data for the new chosen floor.
View 2 Replies
View Related
Sep 18, 2006
hi, i have a combo box in a subform.
the value of the combo box is depanding on the main form.
i want the list in the combo box can be displayed but the value of the combo box must not to be edited.
how i can do this? cann't use combo1.enable = false as the list has to be displayed. please help.
View 2 Replies
View Related
Apr 6, 2005
I have created a main form containing information on classes that I conduct. On this main form I also have a subform containing student information related to the class. Within the subform there is a combo box that contains student’s license number, when this number is selected from the combo box all fields in the subform are automatically populated. When I enter a license number that is not in the db the NotInList function opens the students form, information is entered into the students form then closed returning to the subform. However the fields in the subform are not automatically populated. This is my first post I hope I have attached a copy of the database.
Please help! I have been reading and working for a solution for a Long….Long…….Long………Time.
Thanks
Mike
View 2 Replies
View Related
Mar 3, 2005
I have a main form which is linked through a [PackageID] field to a subform. i wast to use the same form for both shipments in and out. the subform has a list of serial numbers add for each [package id].
On the main form i have addresses ect and then a combo box onthe [PackageID] field which brings up all the serial numbers that came in, in that package.
This is perfect for sending a large container back out unopened, however there are times when the database will need to 'pick and choose' serial no.s from several packages. To do this i made the [SerialNo] field on the subform a combo box - looking up all the serial no.s in stock - however when a serial no is selected it doesn't update the other 15 fields in the subform.
Any sugestions? Got a feeling thats its quite an easy thing to do but its beaten me!
Many thanks
Ed
View 2 Replies
View Related
Jun 24, 2005
I am a newbie to access and am having some difficulty with a subform. I have searched the forum, but could not find an exact match to my problem.
The following is a simplified version of my problem:
Tables: TSerialNumber, TLocation, TSubLocation(Location and SubLocation Fields)
Form: ServiceForm
SubForm: ServiceSubForm
Form and SubForm linked by SerialNumber
In the SubForm: I have 2 ComboBoxes: Location, SubLocation
Location - Row Source is the Table TLocation
SubLocation - Row Source is a Query of SubLocation relating to the Location
I need the SubLocation field to be requeried OnChange from the Location ComboBox.
If I use the subform as a form I can get this to work by using a macro to requery the SubLocation, but when I try to use it as a subform I get an ApplyFilter Error.
Thanks in advance for any help.
View 3 Replies
View Related
Aug 15, 2005
I am trying to add a combo box to a subform with problems related to selection of the listed fields.
More specifically, I have created a form, subform and a combo box in the subform all based on separate queries similar to a sample database on this forum which seems to work. The combo box properties are as follows
Control Source is an Autonumber field
Row source is a query
Column count is set to 2
Column Widths are 0cm;3.5cm
Bound Column is Column 1
Despite these settings being similar to the sample database, when I try and select an option in the combo box I get a message saying "control cannot be uodated as linked to an autonumber field".
I have tried imitating the forum sample databe and have changed various things including the bound column to 2 but then I have separate problems where the selection I made in the combo box is repeated in the next field in the subform.
I hope this makes some sense. I have attached a sample of my database (db2) and a sample database taken from the forum which appears to work as I would like mine to.
Many Thanxs in advance for any help
View 2 Replies
View Related
Dec 4, 2005
hi
im trying to create a form that has something of the following structure
the main parent form has customer details which is straight forward
the main form also has a combo box which contains the name of a supplier
the first subform has supplier details so that when i click on the above combo and select a supplier their details come up in here.
now i also have a second subform on the main form which tells me the orders that this customer will make this shows me all the orders the customer made relevent to the above suppliers products
i know the above sounds like a strange implementation - i dont know if its been done already but i was hoping someone could point me the right direction for a correct implementation to do this.
many thanks
View 1 Replies
View Related
Dec 11, 2005
Hello,
Having Trouble with a Form, Currently the Form is Setup so Data Results can Be Stored, with a Combo Box to Enter a Result RED/Amber/Green. when the Result is Amber or Red, there is a Subform on the Right were People Need to Enter a Value ie the Reason for the Red/Amber. This all Works Fine, However People have a Habit of Not Entering the Values on the Sub Form. I carnt Find a Way to Make the Subform Demand Data if the Value on the Main form is Red/Amber.
Any One have a Idea on the way to do, i.e is there a Property I am Missing or Do i Need to Change the Form so that the Subform Lauches after Update based on result ?
Thanks for Looking
View 3 Replies
View Related
Mar 9, 2006
I have a form with a combo box on it.
I want to use the combo box to query a subform.
So basically when you click on the record in the combo box it will filter the subform based on the selection.
Is there is a simple way or do I need to be more specific about the requirements ?
Any help would be greatly appreciated.
Domble
View 2 Replies
View Related
Sep 28, 2006
hi guys i have a main form called Passengers coming from tblPassengers table and then it has a subform to select the destinations for each passenger.
My subform (continuous) consist of entering up to 3 destinations for each passenger so i created a combo box with this row source:
SELECT DestinationID, Destination FROM tluDestination
WHERE DestinationID NOT IN(SELECT DestinationID FROM tblDestinations WHERE PassengerID = Forms!formname!txtPassengerID);
The reason is that i want that when the user enters a destination, when they go to select another one , the selected one is excluded from the list. but it does not happen... can someone help me with this , i am going nutss
i attached the db to see if anyone can help...
View 5 Replies
View Related
Oct 18, 2006
This is so simple but I can't seem to make it work. I've poured through about 30 posts and tried all kinds of AfterUpdate event strings to no avail. Help!
Here is the simple setup. I have one table that lists employees and their corresponding teamleader. A grouped query provides the record source for a combo box. The idea here is that you select a teamleader from the combo box and a subform updates to show all the corresponding employees. This subform is based on a simple query of all fields on the table.
combo box = cboteamleader
mainform = frmTechMaintenance
subform = frmEmployees
I've finally settled on this...
Private Sub cboteamleader_AfterUpdate()
Me!frmEmployees.Requery
End Sub
...as having the greatest chance of success based on other posts. Not sure why it doesn't work. It just doesn't update.
View 6 Replies
View Related
Dec 28, 2006
I have a subform that contains two combo boxes, 'cboPrimary_Source' and 'cboSecondary_Source'. When you select the primary source e.g. 'Direct Mail' a number of related options appear for selection in the secondary source combo box e.g. Letter, Email, Fax. When you open the subform only, this works just fine using the requery.
However, when you open the main form 'Prospects' where the subform sits it does not work. The primary source combo can be selected, but when you select the secondary source combo an error message appears 'Enter parameter value: Forms!Subdatasheet_Subform!cboPrimary_Source'.
The code used on the Subdatasheet_Subform is as follows:
Option Compare Database
Private Sub cboPrimary_Source_AfterUpdate()
Me!CBOSecondary_Source.Requery
Me!CBOSecondary_Source.SetFocus
End Sub
Private Sub Form_AfterUpdate()
Me!CBOSecondary_Source.Requery
End Sub
Private Sub Form_Current()
Me!CBOSecondary_Source.Requery
End Sub
Any suggestions would be gratefully received!
View 8 Replies
View Related
Jan 2, 2007
An Access newbie, and I have searched the forum and found hundreds of related posts but none of those helped.
Here's the issue -
1 . I have a form with a text box and database navigation button to pick the "Frequency" from tblFrequency.
2. The subform(datasheet view) displays the records from another table related to the "Frequency" shown in the textbox.
The table relationships are set up and the above set up works perfectly fine. Just that I would like a Combo Box to replace the text box and database navigation button in the main form.
I can create a combo box that successfully retrieves frequencies from tblFrequency and I enter the field names in the Link child/master fields. But then the selection in the combo box has absolutely no affect on the records in the subform.
I am using Access 2000. Any ideas?
Thanks in advance,
rr
View 7 Replies
View Related
Jan 19, 2005
Hi,
I am trying to update a subform by selecting a record in a combo-box that corresponds to a particular test when i select the said test i want an adjacent subform to change to the form displaying the test data in the subform window i have tried to use VBA to achieve this but so far have been unsucessful does anybody know how this can be done?
Regards
Gavin Cleary
View 1 Replies
View Related
Mar 23, 2006
I want to limit the values in a subform using a combo box. Basically I've got a Main Form that has name field etc. Selecting a different record shows me a different entry on the subform relating to a different name but now I want to use a combo box to limit what I see in the sub form another level. Any help would be wonderful.
View 1 Replies
View Related
Aug 29, 2014
I have a form with a sub-form. I have tried to use combo box in the subform but on selection of the items, they replicate to all rows available. How do i use combo box in a sub-form
View 1 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
Mar 13, 2005
I'm creating a database to assist with the sales operations of a hat shop. I have a form for creating new orders called 'Invoice' where the order number and date is set.
Within this form is a subform called 'Invoice_Order' (Continuous layout), this form is used for assigning order items to an order number.
Within the subform there are two combo boxes which search the Stock table.
1st ComboBox- diplays hat styles (E.g- v1/v2/v3/v4...)
Problem is; different hat styles are available in different colours based on the material used. I would like the second CoboBox to display a list of colours depending on the style of hat chosen.
At present i can only get it to display a list of colours for all hat styles.
Does that make sense?
Any help would be most appreciated
View 4 Replies
View Related
Apr 6, 2005
I have a form that I have linked (master/child) to a combo box on my main form. I have a requery command for the subform set to the "After Update" event on the combo box. When the combo box is updated originally, the subform updates. However, if the combo box is updated after having a value, the subform does not update.
First off, is the requery command even the right command to use to update the subform? (it's source object is a table) Second, is there a reason it wouldn't work if that is the correct command?
Any ideas?
View 2 Replies
View Related
Jun 29, 2005
I have a combobox that:
1. When adding a new record, I want the user to be able to select any value from the combo box. I then have 3 other fields that automatically fill themselves in. I set their sources to [combobox].Column(x), 'x' being their resepective column in the combobox.
2. When the user is simply looking at a record and not adding a new one, I want the combobox (and, therefore, all 4 fields) to be locked to prevent accidental changing, etc.
First of all, I know that comboboxes should be avoided, if possible - I would like to use a subform (I think) if I can, but I don't know how to do it. What steps would I need to take to implement a subform for this process.
Secondly, whether I use a combobox or a subform, is it possible to have records locked when the user is only "viewing" them and unlocked when they need to add a record? Otherwise, whatever the default values are for those 4 fields will remain for that record since the user cannot change them. Hopefully, this makes sense. Any improvements to my idea would be appreciated.
I have installed a checkbox next to the combobox. After the user selects an option from the combobox, they can check the checkbox to "lock record." I know its a bit primitive, so to speak, but I think it will serve its purpose. If they ever need to change the record, they can uncheck the checkbox and proceed if necessary.
This will work, but it is not ideal obviously. Once the user enters that data, it most likely will never change. Something more permanent would be more desirable.
View 4 Replies
View Related
Dec 13, 2005
Hello everyone!
I think I am asking the impossible, but can't think why it can't be done!
Here's the dilema:
I am working with an unbound form to search a Mobile Register.
On the form I have an unbound combo box which looks up User details from a table (LKP_User).
When I select a user, I want the subform below to filter all the details for that user and show me which mobiles they have.
PROBLEM:
Combo Box has 3 fields, User_ID, User, Dept_ID. User_ID is bound column but not displayed in combo (only User) is displayed.
I need to link the User_ID with the User_ID on the subform, but don't know how to tell it to look at column 1 of the combo box.
Can anyone point me in the right direction? Do I need some fancy code to get this to work?
Cheers in anticipation
NKA
View 6 Replies
View Related
