I have a subform which is for payment, it has a calculated field which is called TotalPayment, with expression:
=Nz([Forms]![Reservations2]![Days]*[Forms]![Reservations2]![Rate],0);
in the control source. When a value is displayed in this field an ID number should be assigned to the PaymentID field, but this does not happen, the field remains null.
I have a continuous subform that displays lots of questions. This specific form has 10 questions on it. Each question has one answer and the users are required to select one answer for each question. I am trying to do some data validation before the data gets saved to my table to ensure that users have answered all the questions. I found some very useful code online and tweaked it to suit my needs. The validation works perfectly on subforms with only one question, as I have some other tabs that use it.
On the specific form that has the issue, the validation works as expected when no question has been answered, but when a user answers only one question and leaves the remaining nine questions blank, the validation does not work. I guess it is as a result of the continuous form repeating the different questions and ms access thinking that the data entered for one question alone is complete when it actually isn't.
Code:
Private Sub Ctl4_frm_Staff_Exit(Cancel As Integer)
Dim ctrl As Control, EmptyStr$
For Each ctrl In Form_subFrm_staffQuestion.Controls
If InStr(1, ctrl.Tag, "Required") > 0 Then
If IsNull(ctrl.Value) Or ctrl.Value = vbNullString Or Len(ctrl.Value) = 0 Then
[Code] ......
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
=[frmNewPurchaseOrderDetails subform2].[Form]![txtsubfrmSupplierID]
did i mess up in the code or did i do something to the relationship between the form and the table?
I created a table name HEAD with column names (ID, Begin, End, Month, Year and Quarter)
Begin and End are dates. I haven't entered any data in Month, Year and Quarter fields. In the table properties I kept a validation rule of ([Begin]<=[End]). It worked.
I Created a form named FORM. I named the control source for Begin and End to Begin and End from HEAD table. For the Month, Year and Quarter control sources I wrote
=MonthName(Month([End]))
=Year([End])
=Format([End];"q"". Quartal")
The form consists of all the data ID, Begin, End, Month, Year and Quarter values too..
My problem is the data of Month, Year and Quarters are not saving in the HEAD table from the FORM.
The FORM data of Begin and end dates are saving into the HEAD table but not the month, year and quarters.
And another question...
In my database I have a button on a form which leads to a subform popping up. On the subform popping up, I display data from the first form. However, unless I first save the original form and then go to the subform, the data isn't passed on. How can I make the button both open up the subform and save the form so that the data is passed on?
Again, any help is much appreciated.
I have one problem with subforms. I have one bound main form and two bound subforms: one subform is used to display existing records in datasheet view while another is used to enter new records in form view, but this latter subform for new records will not save automatically, nor will it save by using the me.refresh or me.dirty=true in the unload event of the subform control. The parent child relationship is intact. I can even see the primary key of the new row in the subform but it does not appear in the table that the subform is based on. This primary key adds by one automatically.
I have struggled and finally made work a combo box in the main form that finds existing records but also is able to add new records when the value entered is not on the list. Below is the code I used. I am not sure if this code is overriding Access's save ability of the subform?
Private Sub Combo316_AfterUpdate()
Dim n As String
gblvariable = cbobox
Me.Requery
n = DLookup("ID", "[Customers Extended]", "[Customer Name] = '" & Me!Combo316 & "'")
[Code] .....
I have a subform and I want to save the username to the bound table in this subform.
I've tried creating a textbox (CompBy) and then setting the default value to = Environ("UserName") but this doesn't work.
I've tried placing the following in the afterupdate event of the subform:
Me.CompBy = Environ("username")
DoCmd.Save
But this doesn't work either.
find the attached MS access DB for INVOICE printing . I am facing two issues there .
1. {form INVOICE } sub-total must be Calculated from line total on {INVOICE DATA Subform} , I know this can be accomplished by adding a new field at sub-form and calling that value to main form's sub-total field .
2. And the real issue is i want to save this value from subtotal on form INVOICE to table field name subtotal .
3.I wish to make a reporting format for each INVOICE NUMBER , whenever report is being generated , the report shows more than 1 invoice details .
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
I have a form that stores its data in a table called Participants. Within that table is a field called Payment Due Date. On the form, this field is populated via the following:
Private Sub PaymentDueDate_Exit(Cancel As Integer)
If Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Bi-Weekly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 14)
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] < 30 Then
Me![PaymentDueDate] = Me![StartDate]
ElseIf Me![PaymentSchedule] = "Monthly" And Me![SentenceLength] > 30 Then
Me![PaymentDueDate] = (Me![StartDate] + 30)
ElseIf Me![PaymentSchedule] = "Paid" Then
Me![PaymentDueDate] = Me![StartDate]
End If
End Sub
My problem is after the field is populated in the form, the data does not store in the table. Have I written something wrong? Any help would be appreciated. Thanks
I want to append/populate all data from one table to subform?
It is possible?
I managed to populate data from one table to another but my relations between form and subform isn't good, because record that is supposed to be filled as the "link master fields" is left blank.
Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.
http://i30.photobucket.com/albums/c329/oohmygod831/screengrab-1.jpg
and this is the sql for the query
SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4
FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift
WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year]))
GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);
What I'm trying to do: I have created an unbound field within a subform's footer to calculate the average of the displayed record values. The subform is in datasheet view. The records are returned based on a query with a relationship between two tables. I need the calculated data (which I currently have displayed on the main form) to populate within the master table.
I can't figure out how to automatically do this. I created a simple command to get it there, but I'd like the user not to need a button to display a calculation.
Main form based on TableA; subform based on TableB.
Here's how I have it set up (Btw, I suck at SQL I just figure this is easiest to read):
Query SQL looks like this: SELECT tblB.Field1, tblB.Field2 FROM tblB.Field1 INNER JOIN tblA ON tblB.Field1 = tblA.Field1
Main form: unbound txtbox = [Forms]![frmA]![subfrmA].Form.[txtAvg]
The field that is averaged is tblB.Field2. There are potentially a bunch of records displayed in the subform.
I need that [txtAvg] field to save to TableA. My command button is simply Me.tblA.FieldName = Me.unboundtxtbox. I'd like to do away with that.
I tried to use the on current or on load event for the main form; but the calculated field value is 0 until it calculates it. It seems like there is a short delay before the value shows up on the form at which point the on current or on load events don't pick up the calculated value; just the 0 that is initially there.
I have created a form with data pulled from tables and queries, I need a way to create a submit button and have it saved to a table I have created. and also clear the data from the form, also to give a warning if not all fields have been filled in. the Form is frmTM and the table that I need it sent to is Team Member Data.
View 1 Replies View RelatedI have a continuous form it has a field (DateStart) and a field (DateStartCarryOver). There is an event on (DateStart) for the (DateStart.Value) to be passed to a new record in (DateStartCarryOver).
My probem is that the new record dosent get saved (i.e. it dosent get an ID so a record isnt created. How can I create that record once the data has bee pased to it?
I have a field on a form [vin] that is based on a table, except i changed control source to a Dlookup to pull the vin number in from another table so i wont have to type it when i enter the record. However, this field is not saving when i save the record. I cannot just make a relationship because we reuse the same vehicle numbers, when we sell van 1 with vin 123, and replace it with a new van with Vin 456, we still call it van 1, so the unique vin is the only way to distinguish the two.
View 3 Replies View RelatedI have a main form and subform. The main form is bound to a table of invoices and the subform to a table of invoice items. I'm picking up the invoice number from the form to save it to the invoice items table, so I need to save the record for the invoice before saving the invoice items from the subform.
View 4 Replies View RelatedI have a subform with records of call details (date, time, subject) - the date and time are autogenerated and subject is a text field. After entering text in the subject field and then clicking on the close button in the main form, or clicking anywhere else in the main form, the record is saved to the table. However, if I enter text in the subject field and then move up to view previous records (within the subform) and then click the close button in the main form the record is not saved in the table. How can I either save the record before allowing the user to move to another record within the subform or before exiting the subform set the focus to the new record so that it will be saved on exit?
View 4 Replies View Relatedi have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
View 14 Replies View RelatedI have a subform within a form and the subform has the child records for the main form. When I move from the main form to the subform by tabbing the records in the main form are saved. However, in the subform after it is completed if a certain field in a groupbox is chosed another form opens up which contains records in the many side of the relationship with the first subform. The problem is that the first subforms records aren't saved because the originall form isn't closed and I don't want it closed...How would I save those subform records when the new form opens up?
Thanks,
I have a Traits table that has fields TraitKey and Trait and I'm looking for the subform to show one record for each trait so that I can give the employee a score for each trait.
I'm trying to save that score in an EmployeeTraitScores table that has the fields EmpID, Trait, Score.
If I bind the subform to the traits field, the continuous form shows each one properly but then I don't know how to save to the scores to the EmployeeTraitScores table.
If I bind the subform to the EmployeeTraitScores table then it can save a record but I can only get the Dlookup to show the first trait.
Access 2010. one of the forms i load into a navigation form is in datasheet view and some vba re-sizes all the columns as it loads in.
However, this then means that when i try to navigate away to another form i always get prompted whether to save the form or not.
because the form is in a navigation subform i can't use:
Code:
DoCmd.Save acForm, Form_NavigationForm.NavigationSubform.Form.Name
' or this...
DoCmd.Save acForm, "my_form"
in both cases i get an error : the object my_form isn't open
For some reason when I change the order around of the columns in a split form or subform 90% of the time it wont save (right click save, file save, etc..) and have to constantly redo it until it finally works. Am I missing something obvious as to why this is happening?
View 3 Replies View RelatedThis has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
View 3 Replies View RelatedI had a problem earlier with a subform (posted May 30/2013, "Subform is blank") whose database was fairly complex (for me, at least), so problems I had with the subform may have had many causes. So I decided to build a very simple test database for employees and their phone numbers. There are three tables: AllContacts, for the names, PhoneNumbers for the phone numbers, and PhoneNumberType for the type of number (office, mobile, home, etc.).
Each employee may have several phone numbers. A main form is for entering firstname and lastname, and the subform is for entering the phone number(s), and the type of number (office, mobile, home, etc.) for each one.
The problem I'm having is that the subform will not accept data in its Number Type field. The error is "The field cannot be updated because another user or process has locked the corresponding record or table. (Error 3164)." I'm the only user, and the control has no locked fields. The Phone Number field will also not accept data: "Cannot add record(s); join key of table 'PhoneNumbers' not in recordset."
I suspect my table relationships may be incorrect, but I don't see where.
I am trying to do a simple Do-while loop to display master and relevant transactions in form/subform environment under Access 2003.Below are some of my statements:
Dim dbs As DAO.Database
Dim TRANS1, TRANS2 As DAO.Recordset
Set TRANS1 = CurrentDb.OpenRecordset("Master", dbOpenDynaset)
Set TRANS2 = CurrentDb.OpenRecordset("Transaction", dbOpenDynaset)
[code]....
then I ha e a main and subform creation with at least the variable "text456" to ensure the master transaction code (i.e. the transcode) has been located and are in the main form. In the subform that I have created, I have a condition as [forms]![mainform].[text456] under the "transcode" field in relevant query file. I have also checked that in the property of subform, both the child and parent linkages are stored the "transcode" variable.
But when I run the program, it only show the "transcode" in main form but no data showing in the subform.