I have a problem with a cascading form, which writes back to another table. The scenario is:
There are three tables. Users, Departments & SubDepartments.
Each table has an Autonumber set as the primary key .
The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works.
For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.
The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:
On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.
The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];
The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate() Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;" Combo2.DefaultValue = [Combo2].[ItemData](0) Command4.SetFocus End Sub
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function Public Function Sort_1(SortName As String, FieldName1 As String) DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'" End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
am trying to change the tab order but have noticed that some of the fields are missing from the tab order form. I am attaching a screenshot to show which tabs are missing.
Okay I have an Orders Form, which generates a SubTotal Price (ex VAT), VAT, Shipping & Freight Charge based on an IIF statment of being under a 50.00 order apply 20.00 charge. And finally a Order Total with it all added together. This works fine, however I cannot seem to find a way to post these prices in my Orders Table. When I click on Datasheet View for the form, all the details appear as they should.
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer) Me.OrderByOn = True Me.OrderBy = [Contract No] End Sub
I'm creating a database for a flower shop for training purpose. I have a main form with a sub form that has the orders in it. when placing a new order for a client and selecting the product from the combo box, it would be great if the product could show up to the right in the form.
I have no clue if this is possible and how to get it working. I have included the database as an attachment, which way to follow. I remember that I could do this on a web page, but a web page is not the same as a database.
I have a report that generates 100 items in alphabetical order. All of the items are also displayed in a table. I have a bound form attached to the that table, and when the items appear they are not in alphabetical order. Therefore, it is a hassle typing in the data when the form will not appear in order, because instead of going in order I am flipping through pages. What is causing this to happen? And what can I do to fix this?
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression
I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.
I am trying to get get 2 comboxes to refresh from one when cascading. The first combobox (Location) works, but the second one (Catches) doesn't. All I get are the Key values of the catches rather than the values...
I am wondering if it my relationship set up of if my VBA needs to be altered to accommodate the requering the two comboboxes.
Code: Private Sub cboCategory_AfterUpdate() Dim sCatchLocation As String Dim sCatch As String sCatchLocation = "SELECT [tblLocation].[Location_ID], [tblLocation].[LocationCategory_ID], [tblLocation].[Location] " & _ "FROM tblLocation " & _
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.
We've got an Access Database that I've edited in the past, I've got the cascading combo boxes set up so that, depending on which employee is selected, different "rate" options appear as work done. If I open the database afresh and select an employee (for example, employee 9 in Image 1 and employee 19 in image 3) the rates given to that employee are correct.
However, if the database remains open, I can't get the rates to update with the selected employee - even if the employee is clicked, rentered, etc.
I have a cascading combo box that successuflyl returns data from sql server tables venue and date, when I save the record it saves the values but when I re open the record for a particular patient the combo box values are cleared and do not display patients appointment venue and date although it is saved in the database. How can I return the values in the combo boxes which were saved.
I have built several combo boxes in other DB's but haven't had this happen before. I am adapting a template DB to work for me. The form is continuous and I added a field [CategoryID], I want the [ProductID] to filter based on CategoryID choice. Pretty straight forward and it works, but the [ProductID] field will not hold the text value. It's a number field to hold the ID value, and bound column is 1(Select query is ID,ProductName,StandardCost) with ID and StandardCost column widths set to 0cm. I can choose CategoryID, the ProductID combo filters correctly, then when I proceed to the next record, the ProductID goes blank.
I have a form with various input boxes, three of which are combo boxes. These combo boxes need to affect one another. So let's call these cmb1, cmb2 & cmb3.
Hypothetically let's say:
Within cmb1 the list is A, B, C & D
If the user was to choose A, cmb2 would show the options 1, 2, 3, 4, 5 etc.. If the user was to choose B, cmb2 would show the options 2, 4, 6, 8, 10 etc.. if the user was to choose C, cmb2 would show the options 1, 3, 5, 7, 9 etc..
(So, it is possible that if the user chooses A from cmb1, cmb2 would show 1 and if the user chooses C from cmb1, cmb2 would also show 1)
Using the example in the brackets above:
If the user chose cmb1 - A then chose cmb2 - 1, cmb3 would then show the options Option1, Option2, Option3 etc..
BUT
If the user chose cmb1 - C then chose cmb2 - 1, cmb3 would then show the options Random1, Random2, Random3 etc..
At the moment I have a table with 3 columns. Within the table is every possible combination of the drop down, i.e:
-A l 1 l Option1 l -A l 1 l Option2 l -C l 1 l Random1 l -C l 1 l Random2 l Etc...
I have tried to use the example shown on this website but i've had no luck: fontstuff.com/access/acctut10 (If you look at "Example 2", thats what I tried to do)
I have a subform that I use on two parent forms. The subform has a cascading combo box. The LocationID field gets filtered after CompanyID (named: cboCompanies) is updated. The problem is that I can only get the combo box to work on one form (Contacts_All). When I open the second form (Contacts_Clients), it asks for the Parameter Value.I am entering this into the criteria for the LocationID field:
[Forms]![Contacts_All]![SUBFRMContactsCompanies]![cboCompanies] Or [Forms]![Contacts_Clients]![SUBFRMContactsCompanies]![cboCompanies]
So, the "Or" is not working.Do I need to make a separate subform for each parent form or is there some way to get this working? I already feel as if I am managing way too many forms and need to simplify.
updating my table when I use cascading combo boxes in my form.What is happening is that my table is being populated by the xxxxID column vice from the xxxxName column that is being used from that specific table.
here is my visual basic code that I am using to determine what the subsequent combo box will display.
Option Compare Database Option Explicit Private Sub cboPlanktonID_AfterUpdate() ' Set the Family combo box to be limited by the selected Plankton Type Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _ " WHERE OrderID = " & Nz(Me.cboPlanktonID) & _ " ORDER BY FamilyName"
[code]...
Example of the Combo box Row Source is: SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;
My Control Source is PlanktonAnalysis.WaterbodyName
When I fill in the form with the data, The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody
i have a main form which allows me to fill in Order Information within which is a subform which allows me to fill in Order Lines.My Order Lines contain the following fields:
Extrusion Length Qty etc. etc.
Elswhere in my database (tblLengths) I have set up a list of possible lengths for each Extrusion and therefore when an order is filled out I want the user to be restricted only to those lengths that are possible with the particular extrusion they have chosen. This list contains the ExtrusionID and LengthID so that I can query the available lengths for a particular Extrusion.Within my subform I have set the record source to point at the tblLengths and criteria on the ExtrusionID to point to [Extrusion]. I've also put a requery against the Extrusion field so that I can force the combobox to refresh its list of results.
My problem is that I am getting unexpected results each time I add an Order line into my form and reading up I see that what I am trying to do is a big no-no.
