Help!! How do i update field with value i select from Combo Box

[freespiritcherishes]

Hi Everyone.. or anyone!

I have a date field called 'CDDueDate' on a form called Diary, based on a Diary Query, based on the Diary Table. I have a combo box that lists dates generated by SQL to select from. It has no control source, i can see dates from today till the end of 2005 in the Row Source and the type is set to Value List.

Once I have selected a date, how do i get it to store it in my CDCueDate field. It doesn't save to the underlying query or table?

I tried setting the Control Source to CDDueDate thought the combo is called cboDateSelect, but it comes up with Msg "Write Conflict, da dee da, with Save Record, Copy to Clip Board or Drop changes".

If i click Save Record, a msg comes up;

"The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.

If this is a macro, open the macro in the Macro window and remove the action the forces a save (for example GoToControl).

If the macro includes a SetValue action, set the macro to the AfterUpdate property of the control instead.

If this is a function, redefine the function in the Module Window."

If i follow the instructions in this message, the combo box stays blank no matter how much i select a value.

If i click ok again, it comes up with "Update or CancelUpdate without AddNew or Edit". And if i click ok again, it comes up with, Microsoft has encountered and Error.. da dee da, prompts to create backup and send error report.

Does anyone have any ideas?
freespirit

 

[lupins46]

What happens if you key in the value into the CDDueDate textbox and save the record? Does that work OK?

 

[freespiritcherishes]

Yes. works fine if i enter date direct into CDDueDate field!

freespirit

 

[freespiritcherishes]

Hi anyone? Desperate need of some assistance.

I have a small sample db which demonstrates the use of my combos using a simple piece of code that i know works. It works for 3 of my combox but not for the other 4, and I am tearing my hair out to understand why because the code is the same for all the combos. Would anyone mind taking a look?

freespirt

 

[PHV]

Why not set the value of the CDCueDate control in the AfterUpdate event procedure of the combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[freespiritcherishes]

I have. For all 7. But its only working for 3!
freespirit y don't u take a look at me sample?
freespirit

 

[TheAceMan1]

How are ya freespiritcherishes . . . . .

Post the code for one that doesn't work!

See Ya! . . . . . .

 

[freespiritcherishes]

owhr Aceman, thank goodness its you!

I have one Main Form with 8 Tabs, one Subform

On the Tabs I have the AllDay, Monday, Tuesday and so on to Sunday.

The Subform simply allows me to enter diary notes.
I have name, date, time, item body.

When the focus moves from one tab to another, the record source of the same subform changes to the relevant query. CDQ Query - AllDays , 01Mon query filters just monday diary notest, tuesday, tuesday diary notes and so on.

The date field on the subform is called CDDueDate. However, i have 7 combos on the same Mon to Sunday that allow me to lookup Mondays dates from here till the end of time, tuesdays dates and so on. The combos sit on the CDDueDate and are hidden. When the focus moves from tab to tab, not only does the record source change, but the CDDueDate is hidden, and the combo for that Tab (Day) is made visible allowing the user to select a date. On the after update event of that combo, the value is inserted into the CDDueDate behind the scenes if you know what I mean. The code for the subform is as follows:

Private Sub Form_Load()
Dim dtVar As Date
Dim dtStop As Date

Me.cboMonday.RowSource = vbNullString
Me.cboMonday.Value = vbNullString
Me.cboTuesday.RowSource = vbNullString
Me.cboTuesday.Value = vbNullString
Me.cboWednesday.RowSource = vbNullString
Me.cboWednesday.Value = vbNullString
Me.cboThursday.RowSource = vbNullString
Me.cboThursday.Value = vbNullString
Me.cboFriday.RowSource = vbNullString
Me.cboFriday.Value = vbNullString
Me.cboSaturday.RowSource = vbNullString
Me.cboSaturday.Value = vbNullString
Me.cboSunday.RowSource = vbNullString
Me.cboSunday.Value = vbNullString

dtVar = Date

dtStop = DateAdd("yyyy", 1, dtVar)

For dtVar = dtVar To dtStop

Select Case Weekday(dtVar)
Case vbMonday
Me.cboMonday.RowSource = Me.cboMonday.RowSource & dtVar & ";"
Case vbTuesday
Me.cboTuesday.RowSource = Me.cboTuesday.RowSource & dtVar & ";"
Case vbWednesday
Me.cboWednesday.RowSource = Me.cboWednesday.RowSource & dtVar & ";"
Case vbThursday
Me.cboThursday.RowSource = Me.cboThursday.RowSource & dtVar & ";"
Case vbFriday
Me.cboFriday.RowSource = Me.cboFriday.RowSource & dtVar & ";"
Case vbSaturday
Me.cboSaturday.RowSource = Me.cboSaturday.RowSource & dtVar & ";"
Case vbSunday
Me.cboSunday.RowSource = Me.cboSunday.RowSource & dtVar & ";"
End Select

Next

End Sub

The code for when the user adds a new record regardless of which tab they choose is:

Private Sub AddMCMDFRec_Click()

Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

If Me.RecordSource = "02Mon" Then
CDDueDate.Visible = False
cboMonday.Visible = True

ElseIf Me.RecordSource = "03Tue" Then
CDDueDate.Visible = False
cboTuesday.Visible = True

ElseIf Me.RecordSource = "04Wed" Then
CDDueDate.Visible = False
cboWednesday.Visible = True

ElseIf Me.RecordSource = "05Thu" Then
CDDueDate.Visible = False
cboThursday.Visible = True

ElseIf Me.RecordSource = "06Fri" Then
CDDueDate.Visible = False
cboFriday.Visible = True

ElseIf Me.RecordSource = "07Sat" Then
CDDueDate.Visible = False
cboSaturday.Visible = True

ElseIf Me.RecordSource = "01Sun" Then
CDDueDate.Visible = False
cboSunday.Visible = True
End If
End Sub

and the AfterUpdate code for the combos is:

Private Sub cboMonday_AfterUpdate()
If Me.RecordSource = "02Mon" Then
Me.CDDueDate.Value = Me.cboMonday.Value
End If
End Sub

Private Sub cboTuesday_AfterUpdate()
If Me.RecordSource = "03Tue" Then
Me.CDDueDate.Value = Me.cboTuesday.Value
End If
End Sub

Private Sub cboWednesday_AfterUpdate()
If Me.RecordSource = "03Wed" Then
Me.CDDueDate.Value = Me.cboWednesday.Value
End If
End Sub

Private Sub cboThursday_AfterUpdate()
If Me.RecordSource = "04Thu" Then
Me.CDDueDate.Value = Me.cboThursday.Value
End If
End Sub

Private Sub cboFriday_AfterUpdate()
If Me.RecordSource = "05Fri" Then
Me.CDDueDate.Value = Me.cboFriday.Value
End If
End Sub

Private Sub cboSaturday_AfterUpdate()
If Me.RecordSource = "06Sat" Then
Me.CDDueDate.Value = Me.cboSaturday.Value
End If
End Sub

Private Sub cboSunday_AfterUpdate()
If Me.RecordSource = "01Sun" Then
Me.CDDueDate.Value = Me.cboSunday.Value
End If
End Sub

The problem is, when i add a new record under Monday, Tuesdays and Sunday tab, the combos update the date CDDueDate field with the value i selected, but it doesn't do it for the other days, yet they all have the same code.

I know the code is repetitive and longwinded, but Im an amateur and do not have the know how to make the code more efficient. Thanking you in advance!

freespirit
aka donna

 

[PHV]

You problem is here:
Private Sub cboWednesday_AfterUpdate()
If Me.RecordSource = "0[highlight]4[/highlight]Wed" Then
Me.CDDueDate.Value = Me.cboWednesday.Value
End If
End Sub

Private Sub cboThursday_AfterUpdate()
If Me.RecordSource = "0[highlight]5[/highlight]Thu" Then
Me.CDDueDate.Value = Me.cboThursday.Value
End If
End Sub

Private Sub cboFriday_AfterUpdate()
If Me.RecordSource = "0[highlight]6[/highlight]Fri" Then
Me.CDDueDate.Value = Me.cboFriday.Value
End If
End Sub

Private Sub cboSaturday_AfterUpdate()
If Me.RecordSource = "0[highlight]7[/highlight]Sat" Then
Me.CDDueDate.Value = Me.cboSaturday.Value
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[freespiritcherishes]

OMG {goodness)!!!!
I have been racking my brains and could not figure it out. Thank you very much PHV!!

Obviously going blind looking at all this code. Works a treat.

Thanks again!
donna