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...
I have various comboboxes within a continious subform where you select a main field, and then it should let you only select the relent fields below it.
Example:
1.Main Product (HS_level1): Live animals
2.Sub Product (HS_level2): Live sheep and goats
3.Product (HS_level3): Sheep, live
...other fields on amounts, etc.
Adding the first product to the list is easy. Query I use:
Private Sub HS_level1ID_AfterUpdate() 'make next HS levels empty and requery the list of products Me.HS_level2ID = Null Me.HS_level2ID.Requery 'the query that is been run selects only HS_level2 that are linked with HS_level1 Me.HS_level3ID = Null Me.HS_level3ID.Requery 'jump to next HS_Level field DoCmd.GoToControl ("HS_level2ID") End Sub
But when I add the second product to the CONTINUOUS form, all hell breaks loose and the previous record's values also gets reset.
How can I ensure that VBA only runs for the selected record?
What I am trying to do is put a subform within a subform. The trouble is I want both forms to be continuous forms.
when I put the subform within a subform, access comes up with the following message:
************************************************** ******************** A form with a subform object cannot have its DefaultView set to Continuous forms.
You tried to add a subform to a form in design view. MSAcess will reset the property to Single Form. ************************************************** *********************
which it does my higher level form becomes a form only showing single records at a time whilst the subform is a continuous form.
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
I want to add a command button to my form that enables me to sort all records on the form into a Word document using mailmerge or automation as a "Purchase Order". This Word document will be saved with the name of the Order, then i will try to add another fonction to send this "purchase order" as an attachement with outlook. The main problem is that I couldn't merge records on the continuous subform including details about the Order: items, quantity,price....
Please note that I'm a beginner on Access and this is my first project.
I have a form and a linked subform which are linked by Purchase Order Number. When items have been delivered on the purchase order, the user should update the continuous subform with the number of items that have been delivered. This works fine.
The problem I have is using an exit button that does not save any of the changes to either the data in the form or the subform. It doesn't matter if there is a prompt to alert the user that there have been changes made.
I have been messing around with DoCmd.RunCommand acCmdUndo in the on click event of my exit button which kind of works when changes has accidentally been made, but is very unsophisticated - any ideas?!
right I have searched and searched the forum but obviously this isnt as simple as I thought it would be!
I have a main form called "frmAllStudents" based on a query called "qryAllStudentsMainScreen". The unique field in all forms is Admission Number.
I have on this form a data-entry subform called "TeachingAssessment" where a member of staff puts in teaching Assessment.
I have a second continuous subform called "previousTeachingAssessment" which lists all the previous teaching assessment.
The problem I have is that unless i select another student or open and close then the previous teaching assessment doesnt update. How do I requery to get this to show on the continuous form?
Now, what I want to do is the following: I want a form that will display all orders with all products.something like this:
IdOrder | CompanyName | Products
1 | ABC | ComboBox with products 2 | XYZ | ComboBox with products
How do I do it? It's easy with everything else, the ComboBox gives me headakes. I can't seem to link the Products to the correct Order so that each combobox displays only the items for that specific Order.
Please see attached pic. When I put the focus in some fields, the dividing line is chopped. In other fields, it's not. All controls are identical in format. If I copy a control which doesn't do this, and paste & replace one which does, the two controls simply swap their behavior.
Does anyone know why this happens and how I might prevent it?
Im sure this must be easy to do (but cant do it myself...)- how do I set a continuous form subform to display the last record when a new main form record is opened? Whatever I do, the continuous subform displays the first record and I have to scroll down to get to a new record, which is getting tedious.
I have buttons that enable and disable editing/adding information on a continuous form.
When you click add the form then is set to allowadditions and shows you the * record. Is it possible to have that display at the top of the list instead of the bottom? If not, what would be the best method to move the focus to the new record when someone clicks add?
I have a main form with a subform in continuous format. The subform displays the list of records and has a delete key allowing the user to delete that line (record) All works except if the user clicks the delete button on the new record line. Then there is an error message. Rather than use error trapping I would like to code a message OR simply beep to confirm nothing was done OR have the delete button disabled until something is entered into the record. The records do have a autonumber which I have in a hidden text box. I tried the following and even though the code shows that LineID does in fact = Null the code does not fire.
If Me.LineID = Null Then DoCmd.Beep Exit Sub End If
I have attached an image of what i am trying to achieve so here goes:
I need to add data from one subform to another - problem is they are both continuous forms and both subforms.
The part in green is where it needs to go, and the white is where the data is held. The links have already been created, i just need to get the info from subform 1 to subform 2.
I have attached code but it only moves line selected / first line.
Dim dst As DAO.Recordset Set dst = Me.frmAS9102_Material_LINK.Form.RecordsetClone With dst .MoveFirst Do While Not .EOF
Hi everybody, In a continuous form, If I use a comboboxe with a criteria looking up a field in the same record, it seems that the combo boxe only looks up the value of the firsrecord, Iam not sure I understand why? I was wondering if it there was any method to be able to use Cascading combo boxe in a continuous form.
how to assemble cascading combo boxes in a continous form. I wasn't having any luck in my database so I decided to create a clean test database file and just try and get the concept to work.
I've got 3 tables, Products, Categories and Orders and the form is based on Orders. In the form you select a Category from a combo box and then when you click the Product combo box it should only display products in the selected category. Technically that works. But if you click any other combo box under Products it will always display the same products as whichever combo box you click first. Until you close the form and reopen it.
Please see the attached images (screen shots from my db).
I'll start by explaining the function of this form. Invoices are raised through a different area of the system - they then appear in the outstanding invoice list in my payment processing screen while awaiting payment. When a payment is received the user creates a payment using the controls at the top of the form. When created - payments will appear in a list box at the bottom of the screen. I then want the user to be able to highlight a payment from the list and type the amount to allocate against each line in the outstanding invoices subform. Then (once happy with the allocation) press the "Allocate" button which will cycle through the allocations the user has made inserting rows into my Transaction table - allocating the payment against multiple invoices.
The fields displayed in the outstanding invoices subform are selected in a query which calculates the amount still outstanding. In trying to illustrate my aim I've added an unbound text box to the subform (the column Allocate). What I'd like to be able to do is type a different amount against each outstanding invoice. I understand this is not the way that unbound controls work in continuous forms - i.e. that they are multiple instances of the same control hence when I type 100 against the first item in the list it appears against every item in the list. I can't use a bound control because the query is not updateable - and in any case, the entry won’t exist in the transaction table until it has been inserted anyway.
I'm pretty competent with Access and VBA but haven't really explored recordsets - I'm usually able to achieve what I want by binding my forms to stored queries. This, however, is a bit more of a complex problem than I'm used to dealing with.
So, to the point then. I guess my first question is - is it possible to achieve this? I'm sure it must be. If so please can someone try to shed some light?
Secondly, I have an idea but have absolutely no idea of how to implement it. I seems to me what I would need to do is build a temporary recordset for the form and add a field to it i.e. the "Allocate" field I've been talking about. The user would then be able to manipulate this recordset because the form wouldn't be directly accessing a query. Nor would there be any unbound controls on the form. Once the user had typed in all of the allocation figures they are happy with they would be stored in my temp recordset. They could then hit the "Allocate" button which would cycle through the recordset inserting rows into my Transaction table (as long as various validation rules were passed).
Does this sound possible? If so can anyone give me any help or point me in the direction of any material they know of that might be of any help?
Thanks in advance for your help.
To give a flavour of how I originally built this form I've attached another image - PaymentProcessingOld.gif. It's a similar kind of setup. Payments are created at the top of the screen and appear in the list box bottom left. The outstanding invoice subform serves only as a point of reference. The user actually allocates payments by selecting the item from a combo box and typing the amount. This builds the transaction I've talked about above. It's quite straight forward when there are only a few payments and allocations, but in practice payments are usually large and split across up to 100 different invoice lines. That makes it a very tedious task.
I'm having problems trying to control default values on a continuous subform. I need to be able to have the continuous subform look up values based on a CBO on the subform, populate controls on the subform with those values, and then store the values in a table that has a foreign key linked to a control on the main form. I'm not a complete newbie at this, but for some reason I can't figure this out. The closest I've gotten to this was using a Dlookup in the afterupdate of the CBO, but that only returns one value from the table regardless of what value is selected in the control. Any suggestions?
Sure hoping someone can help me with this. I have a main form with a continuous subform. I am trying to filter the subform "behavior" combo box with another combo box on the subform "learningtype". The subform filter works fine if only the subform is open, although I can't add records because it would require a record in the main form. However, now that I've added the subform to the mainform, when I open it it asks for the parameter value of the filter combo box on the subform. Can someone help me, please...I'm desperate. I'm attaching a sample for you to look at.
I'm working on a purchasing database where if the user checks a box on a subform, another field needs to be disabled. The code works, but since this is a continuous subform, it disables that field on every row. Is there any way to make it stick to the row the user is working on?
Here's the code:
If Me!acs_puchase = -1 Then Me!inventory_po.Enabled = False ElseIf Me!acs_puchase = 0 Then Me!inventory_po.Enabled = True End If
I am trying to use this code to filter a continuous subform based on two criteria but getting a data type mismatch error.
It works fine if I filter by just one or another. Perhaps it is not possible
Code: Dim intSpouseEntityID As Integer intSpouseEntityID = Nz(DLookup("[EntityID]", "qryEntitiesLocations", "[ContactIDNumber] =" & Me.Spouse), 0) If intSpouseEntityID > 0 And Not IsNull(Me.subformContactsHomeAddress.Form.EntityID) Then MsgBox ("There are two spouse addresses please delete one and try again") DoCmd.Save DoCmd.OpenForm "frmContactAddresses", , , "EntityID=" & Me.txtEntityID Or "EntityID =" & intSpouseEntityID End If
I have a continuous subform on a form. I have applied conditional formatting with expression is.. so that the formatting on one object (field on the subform) depends on whether there is anything in another field on the same subform named "Remarks".
My problem is that I this is not working to my expectations. First of all when the form with the subform is loaded the conditional formatting does not appear immediately. One has to run over the fields with the mouse with the hope that the conditional formatting appears.
Secondly the conditional formatting is not working well as they should.
I am setting up a subform in continuous with combobox, but when I enter the data on the first combobox, it duplicated to all the following records. Is there a way to stop that?
i would like to have on my continuous subform fields from my table but only as a non-editable list (I have a dedicated mod form to actually modify the values), and I don't really like the way "Locked" and/or "Enabled" work, especially on combo boxes. To me, the arrow shouldn't be there: it looks like you can modify it, but you can't; it's a bit confusing and counter intuitive.
So what I did is replace those combo boxes with text boxes, and used DLookUp in the Control Source. Now I can lock and disable the text boxes and it looks just as I want it: you can't select the text, and most importantly there's no dropdown arrow.It works, but it's kind of a lot of work compared to what it is with the cbo. Also, since those are calculated values, they take a fraction of a second to appear, as opposed to the nearly instantaneous display of the values with the cbo, and there's only 5 entries in my test database.
I have an IIF function specified on a continuous sub-form as below - basically it checks to see if the value of two text boxes is equal, and if they are it displays 'And', if not it displays the value of the column EntitlementName. I'm trying to work around grouping not working in continuous forms.
When I view the form directly it works no problems but when I view the form as a sub-form instead of showing the correct output it shows #Name? Both the text boxes I refer to have the correct values in them.
