Restict closing the main form when some controls on the subform are empty
I have made a Form in Access 2000 which has Main Form & Sub Form. Main Form has (Header File) and Sub-Form has (Detailed File). Relationship between the primary key field of Header Table and Detailed Table is established with Casecade Update Related Fields and Casecade Delete Related Records.
I want that whenever User entered all the inputs in Header file, he must enter necessary records in Detailed file also & if he fails to do so, an alert message is appear to ensure the same & focus is set to controls of sub-form.
The main form (against Header File) has following fields:
1. Issued To: (Combo box, which has Departments name, to whom, material issues).
2. Issue Slip No. (Numeric Primary key field, which has relation to Sub-Form also).
3. Issue Slip Date
4. Remarks (if any)
My main form name is : Issue Main Form
My Subform name is : Issue SubForm
Subform has following fields : ItemID & Quantity issued
Now, please let me know to set focus on controls of sub-form, how we can write the syntex & at which event whether in main form or sub-form.
The sub-form (against Detail File) has following fields:
1. ItemID (Combo box, parts from part master table which are in stock i.e. (Total Receipt ? Total Issues).
2. Quantity Issued (which has following SQL to run in the event of After Update).
UPDATE Item SET Item.IssueQty = NZ(DSum("[Quantity]","Issue_Dtl","[ItemId]=" & [ItemId]),0);
3. Issue Slip No. (Hidden)
An expert who has achieved level 2 by getting 100 points
An expert that got 10 achievements.
An expert that got 5 achievements.
An expert who has written 200 answers of more than 400 characters.
Re: restict closing the main form when some controls on...
Hi there. I didn't notice this was so old until I had already worked on it for some time. Did you get this the way you want it? If not, I suggest either using a BeforeUpdate control to ensure the condition is met that the subform contain data, or validate the particular field(s) with isnull. Thanks for posting back if you still need help, or otherwise rating FixYa!
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
Create a Form from the main table with a subform based on the other data you will want populated. You put the fields in the subform that you want populated from the main form. Go to the properties for the subform and select the data tab, In the data tab, select the Link Master Fields and choose the field you want linked. When you type the ID number in the main form, the other firlds you have in hte subform should populate from the data inthe table.
open the form in design view open the properties dialog box by right clicking in the general form area not in the gray area of the detail section. In the properties click on the format tab. In the format tab goto the default view and select continuous form. This should create a virtical scroll bar once a record in the data base.
You can set a default value in most form items under the properties of that item. Look for "value". You can also do this in code, by setting up a sub routine to set/reset the values on your form. Then just run the sub routine before you open or enter the form.
Private Sub resetForm()
'formname.itemname = value UserForm1.Label1 = "Hello World"
For the second one you have a couple of options, you can find the bottom on the data and store the row index in a variable, use a loop to find the next empty cell in a row, and store the value or you can use the current region method to select all the cells with data continously touching the selected range. Example below
Hi, There a couple of different way you could do this and I have put two lots of VB code below that you can modify and use. Firstly you create the Combobox list by choosing the option that you will type the values yourself, you then type in all your report names on each line. After that you can then go to the VB code editor and paste my code in and modify it by changing my report names, (Customers) etc to your report names. Make sure you choose the Combobox and the On_Click option in the VB editor to paste the code into. I like the first method better which checks the value of the Combobox and then opens the corrosponding report in the maximised form. The second method does the value check before running an assigned macro to open each report. With this you must first assign macros to each report in Access. Remember each of these bits of code must be placed between the "Private Sub" heading and the "End Sub" ending, and change the "Combo23" name to reflect your combobox too. If you need a hand just email me.
If Combo23.Value = "Customers" Then DoCmd.OpenReport "Customers", acViewPreview, acWindowNormal DoCmd.Maximize
If Combo23.Value = "Customers" Then DoCmd.RunMacro "Macro1" DoCmd.Maximize ElseIf Combo23.Value = "Customers 1" Then DoCmd.RunMacro "Macro2" DoCmd.Maximize ElseIf Combo23.Value = "Customers 2" Then DoCmd.RunMacro "Macro3" DoCmd.Maximize End If
Hi Raul from your description it is very unclear what you are trying to do.
First we need to know what information is on the first form, second unless you are trying to do a subtotal of all orders in the subform there is no real need for you to create a subform.
Now if you are trying to create a subtotal form for all records, you will have to set it up in the record source of that subform with group by:
1. Right click on an empty space on the subform
2. Click on properties
3. Choose your table(or query) which is the record source of the main form.
4. Select Payment and items(or what ever you are trying to calculate).
5. Press the group by button
6. In totals instead of group by choose sum.
7. Exit and save
8. Now when you will select the control source you will see "sumofpayment" etc... just select them for each textbox.
9. To create the third text box you will have to use the Expression builder and just do [textbox1]-[textbox2]
* for some reason the nz function does not work in this case.
I hope that helped you I will be able to give you a better answer if you give me more details...
Let me know what happend