Field to show Day of Week

[YNWA]

Hi, I have a Date of Session field with a calendar attached, when I click the date in the calendar I want the field below it (Day of Week) to populate with the day of the week.

This used to work but over the past months after various edits to the form, it has stopped working and only shows the Day of Week after you click the submit button (to submit the payment info to the table).

Can anyone help me out? I have attached the DB and the problem is in the form "frmPayments"

I have the following code in the form...

Private Sub DtmSessionDate_Change()
Me.DayofWeek = Format(Me.DtmSessionDate.Value, "dddd")
End Sub
Private Sub DtmSessionDate_Click()
Me.DayofWeek = Format(Me.DtmSessionDate.Value, "dddd")
End Sub

Thanks
Will

 

[CEH]

Try this........
=Format(DtmSessionDate.Value,"dddd")
On your control source of "Day of Week"

 

[Galaxiom]

The Value property is the current value held in the RecordSource of a bound control.
Use the Text property for the current contents of the control.
Curtis has the right idea with using the control source rather than coding the population of the box.

Put this as the control source of the DayofWeek box:
=Format(DtmSessionDate.Text,"dddd")

The events then need only requery the DayofWeek textbox.
Me!DayofWeek.Requery

 

[YNWA]

Thanks for that.

I have a button on the frmMain to open the Payments form, is it possible to link the button so when you click it, the combo box in the payment form shows the name of the person who was previously on the frmMain?

ie. John record is showing on frmMain, click button and payment form shows John and his details in the header combo box?

 

[YNWA]

I have put those controls in but when I click the submit button now, I can the message "You can't assign a value to this object".

Here is code for my submit button which works fine when the control source of Day of Week is empty...

Private Sub CmdSubmit_Click()
On Error GoTo Err_CmdSubmit_Click
'Add new record
    Dim Rs As DAO.Recordset
    Set Rs = CurrentDb.OpenRecordset("Payments")
    Rs.AddNew
    Rs("GPID") = Me.TxtID
    Rs("DateOfSession") = Me.DtmSessionDate.Value
    Rs("TypeOfRate") = Me.Type_of_Rate.Column(0)
    Rs("RateOfPay") = Me.Rate_of_Pay
    Rs("HoursWorked") = Me.Hours_Worked
    Rs("MileageClaim") = Me.Mileage_Claim
    Rs("NonCont") = Me.NonCont
    Rs("TotalClaim") = Me.TxtNetPay
    Rs("PensionRate") = Me.TxtPensionRate
        Rs.Update
    Rs.Close
    Set Rs = Nothing
'Clear the controls
    Me.DtmSessionDate.Value = Date
    Me.DayofWeek = Format(Date, "dddd")
    Me.Type_of_Rate = ""
    Me.Rate_of_Pay = ""
    Me.Hours_Worked = ""
    Me.Mileage_Claim = ""
  
    Me.TxtNetPay = ""
'Refresh the list box and reset the focus
    GetHistory
    Me.CboEmployees.SetFocus
    Me.CmdSubmit.Enabled = False

Exit_CmdSubmit_Click:
    Exit Sub
Err_CmdSubmit_Click:
    MsgBox Err.Description
    Resume Exit_CmdSubmit_Click
    
End Sub

 

[Galaxiom]

I have put those controls in but when I click the submit button now, I can the message "You can't assign a value to this object".

You no longer need the code to assign a value to the DayofWeek box. It is handled by its Control Source and the Requery. Just remove the DayofWeek line from you procdure.

 

[missinglinq]

That message, when you try to do

Me.DtmSessionDate.Value = Date

would indicate to me that DtmSessionDate

that it has a Control Source in place, maybe

= Date

and you cannot enter or assign a value to a control that has a calculation as a Control Source. Go into Form Design View, select the textbox, and see if there is anything in its Control Source Property.

 

[YNWA]

Hi all sorted. Thanks everyone. Galxio I used your example. But changed the .Text to .Value.

Where about would I put the Requery?

 

[YNWA]

I put it in the code events change and click.

2 things remain, when I submit the payment, all works now, great, but how do I get the payment date and day of week to show a blank field rather than a date and day once the payment is sent?

I have deleted the code

"Me.DtmSessionDate.Value = Date
Me.DayofWeek = Format(Date, "dddd")" and also just taken away the = Date part and put = "".

It works but when I click the date again, I need to click the calendar twice before it will put a date in the field.

 

[Galaxiom]

things remain, when I submit the payment, all works now, great, but how do I get the payment date and day of week to show a blank field rather than a date and day once the payment is sent?

I assume the date control is bound to the field so you would have to move to a new record. Otherwise you would have to unbind the control and use commands to save it. Does it really matter?

You should use a different name for the Date contol and field. Date is a reserved word and it can cause trouble. It makes it quite confusing to read your code.

It works but when I click the date again, I need to click the calendar twice before it will put a date in the field.

Might need one click to get focus and another to load the date. Perhaps try taking the focus to the calender using code ready for the user to click the entry.