Need record to update 1

[Overmyhead2]

Have a form with a subform. The subform has 4 fields for the user to enter data into. If data is entered into all 4 fields the record updates the table properly. But the user wants only to enter data into 1 field and come back another day to finish entering data into the other 3 fields.

The problem is that when data is entered into only one field the record is not added to the table.

I checked the other 3 fields that are being left blank and the required property is set to no. I put a save record event on the field that has data inputed into. Nothing seems to work.
Victoria

 

[RickSpr]

I don't see any reason this shouldn't work, as long as the subform is bound to a table or an updatable query, and the field being input is bound to a field in that table or query.

What are the record sources for your form and subform? How are the tables related? What are the Master and Child Link Fields on the form and subform, respectively? What are you doing in code behind the form and subform? Rick Sprague

 

[Overmyhead]

Thanks Rick for responding. You have already helped alot with this form. 2 tables-
ASPNames(auto#,ASNumber,Name,Site,PercentF), ASPData(auto#,ASNumber,Day,Free,Paid,Reduced,ServeDate,Operator,TimeStamp).
1 to many linked on the ASNumber.
Main form ASPData rec source is table ASPNames.
subForm subASPData rec source is qry1 which is based on ASPData.
Master and child Link fields are ASNumber.
Qry1 has fields: Day,Paid,Reduced,Free,ServeDate,Operator with ServeDate and Operator hidden.
It selects records based on frmMain's textbox Month and textbox Year.

SELECT ASPData.Day, ASPData.ASNumber, ASPData.Paid, ASPData.Reduced, ASPData.Free, ASPData.ServeDate, ASPData.Operator
FROM ASPData
WHERE (((DatePart("m",[ASPData].[ServeDate]))=[Forms]![ASPData].[Month]) AND ((DatePart("yyyy",[ASPData].[ServeDate]))=[Forms]![ASPData].[Year]));

One event procedure on subform Free field: It combines Mo and Yr from mainfrm ASPData with Day on subASPData to a Datestring unbound textbox on mainfrm ASPData and updates the table ASPData field ServeDate.

Private Sub Free_AfterUpdate()
Dim db As Database
Set db = CurrentDb()
ServeDate = Forms![ASPData]!DateString
End Sub
Also on mainform ASPData:
Private Sub Month_AfterUpdate() and Private Sub Year_AfterUpdate() that you helped me with. It checks for valid entry and then makes the subform visible.

Private Sub Month_AfterUpdate()
subASPData.Visible = False
If IsNull(Me.Month) Then Exit Sub
subASPData.Requery
'subASPData.Visible = True
End Sub

Private Sub Year_AfterUpdate()
subASPData.Visible = False
If IsNull(Me.Month) Then Exit Sub
subASPData.Requery
subASPData.Visible = True
End Sub
The user enters a Month and a Year and moves the frmMain record selector to the desired ASNumber. The subform displays 4 fields.
Day ,#F,#P,#R.

 

[RickSpr]

(Gee, it's nice to have complete information without having to beg for it!)

Your SQL statement for Qry1 doesn't include ASNumber. It has to be included, or your subform will show all records for the given month and year, not just those with the same ASNumber as the main form.

I have a better picture of your application than I did in the earlier posts. There's a problem with having DateString on the main form. It only gets evaluated when the main form loads a record, not when Day is entered in the subform. Let's back up a bit.

Delete DateString from the main form. You don't need it (unless it's there for the user?). Instead, use the subform's BeforeUpdate procedure to set ServeDate. (This is exactly the sort of thing a form's BeforeUpdate is intended for.) The event procedure is:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        On Error Goto ErrorHandler
        Me!ServeDate = DateSerial(Forms!ASPData!Year, Forms!ASPData!Month, Me!Day)
    ErrorExit:
        Exit Sub
    ErrorHandler:
        Me!Day.SetFocus
        Beep
        MsgBox "Please enter a valid day for the month and year"
        Cancel = True
        Resume ErrorExit
    End Sub

You said ServeDate is "hidden" in the query, but I'm not sure what you meant by that. In any case, you need a control on the subform for it; you can make it invisible.

Since only Free (and not Paid and Reduced) had the code, you had to update Free to make the ServeDate get updated. But since ServeDate is in your recordsource, you can just use a control bound to it to update it--much simpler. I didn't understand before that ServeDate was in your recordsource, or I wouldn't have suggested db.Execute to update it. Now that you have a bound control, get rid of the Free_AfterUpdate procedure.

One more thing: The subform's BeforeUpdate procedure has an error handler to trap the error that would occur if Month, Day, and Year can't be converted to a valid date (either one of them isn't numeric, or the day isn't valid for the month, e.g. April 31). If an error occurs, the update is canceled and the user is prompted to correct the Day. Year and Month must already be valid, or the subform wouldn't find any records to display, and Day couldn't have been entered. Rick Sprague

 

[Overmyhead]

This form is working better than I had hoped for. Your changes made it much nicer and answered my question also! The users were having to enter the DAY as 2 digits due to the way I built the datestring with "/" "/" . I like this DateSerial(Forms!ASPData!Year, Forms!ASPData!Month, Me!Day).
Appreciate your help.

Victoria