Run Time Error 3021

[Shamli]

Hello Everyone,
I am getting the runtime error when performing this.

1. I have a continuous form.


Scenario 1:
When a user selects an item (Say Item1) from the combo box1, several other Combo box/Text box value gets requery. (This is working well)
Scenario 2:
The user performs above action, then he goes to the second record set, tries to select (Item 5) from the combo box1, Now result should be all the record set's combo box1 should change to Item5, All the other combobox value gets locked. (This is working well too) Except I am getting an error message (3251).

Scenario 3:
The user Performs the first action as selecting Item 5, Now I am getting Runtime Error 3021.
I understand there is no current record. How to solve this.
Code for the after update event of the combo box:

Private Sub CmboElement_AfterUpdate()

  
    
If CmboElement.Value = 5 Then '5 = Non Billable Element
 
LResponse = MsgBox("Do you want to Proceed?", vbYesNo, "Note")
If LResponse = vbYes Then
PrintBttn.SetFocus

 With Me.RecordsetClone
 .MoveFirst
 Do While .EOF = False
 .Edit
 .Fields("Saturday").Value = "0"
 .Fields("Sunday").Value = "0"
 .Fields("Monday").Value = "0"
 .Fields("Tuesday").Value = "0"
 .Fields("Wednesday").Value = "0"
 .Fields("Thursday").Value = "0"
 .Fields("Friday").Value = "0"
.Fields("ElementID").Value = 5
.Fields("ProjectID").Value = 268 '268 = Non Billable Project ID

Me.ProjectID = 268
Me.CmboProject.Locked = True
Me.CmboElement.Locked = True
Me.Monday.Locked = True
Me.Tuesday.Locked = True
Me.Wednesday.Locked = True
Me.Thursday.Locked = True
Me.Friday.Locked = True
Me.Saturday.Locked = True
Me.Sunday.Locked = True
 .Update
 .MoveNext
 Loop
 End With
 RecordsetClone.Close
 
 Set RecordsetClone = Nothing

Else
Me.CmboElement.SetFocus
Me.CmboProject.Locked = False
Me.Monday.Locked = False
Me.Tuesday.Locked = False
Me.Wednesday.Locked = False
Me.Thursday.Locked = False
Me.Friday.Locked = False
Me.Saturday.Locked = False
Me.Sunday.Locked = False
End If
Else
Me.CmboElement.SetFocus
Me.CmboProject.Locked = False
Me.Monday.Locked = False
Me.Tuesday.Locked = False
Me.Wednesday.Locked = False
Me.Thursday.Locked = False
Me.Friday.Locked = False
Me.Saturday.Locked = False
Me.Sunday.Locked = False
End If
End Sub

[ranman256]

theres no need to write all this code, when a simple update query can set these fields all at once.
no syntax errors.

[CJ_London]

and please don't just quote the error number - the error description is much more informative, often telling exactly what the problem is.

Good that you are using the code tags - shame you don't use indenting - would make your code much easier to read

[Shamli]

Thanks for the reply. I tried the update query. But getting this error.

Below is my code:

sSQL = "UPDATE Timesheet_T SET Timesheet_T.ElementID = 5, Timesheet_T.ProjectID = 268, Timesheet_T.Saturday = 0" & _
"WHERE (((Timesheet_T.WeekEnding)=[Forms]![Edit_Timesheet_F]![WeekEndingbx]) AND ((Timesheet_T.EmployeeID)=[Forms]![Edit_Timesheet_F]![Name]));"
CurrentDb.Execute sSQL

Error I am getting is:

theres no need to write all this code, when a simple update query can set these fields all at once.
no syntax errors.

[CJ_London]

you cannot pass a reference to the form that way, you need to use the values

sSQL = "UPDATE Timesheet_T SET Timesheet_T.ElementID = 5, Timesheet_T.ProjectID = 268, Timesheet_T.Saturday = 0" & _
"WHERE (((Timesheet_T.WeekEnding)=" & [Forms]![Edit_Timesheet_F]![WeekEndingbx] & ") AND ((Timesheet_T.EmployeeID)='" & [Forms]![Edit_Timesheet_F]![Name] & "'));"

Other issues:

no space before WHERE - you can see this in your error message
Name is a reserved word - so in your case could refer the form name, not the control called name. You should change it to something more meaningful
I've assumed Name is text and not a PK even though you are comparing with an ID. If it isn't remove the single quotes

[Shamli]

Thank you for your help


I tried this code:

sSQL = "UPDATE Timesheet_T SET Timesheet_T.ElementID = 5, Timesheet_T.ProjectID = 268, Timesheet_T.Saturday = 0 WHERE (((Timesheet_T.WeekEnding)= " & weekend & ") AND ((Timesheet_T.EmployeeID)=" & EmployeeName & "));"
CurrentDb.Execute sSQL

1. There is no space before where. I am not sure which space you are denoting to. I can see that in the previous error.
2. The query above doesn't show any change to the form.
3. weekend is a date field. And EmployeeName is an ID value stored in the text box.

I am not sure if I am using the right syntax for the date field and the text box.







.ElementID = 5, Timesheet_T.ProjectID = 268, Timesheet_T.Saturday = 0" & _
"WHERE (((Timesheet_T.WeekEnding)=" & [Forms]![Edit_Timesheet_F]![WeekEndingbx] & ") AND ((Timesheet_T.EmployeeID)='" & [Forms]![Edit_Timesheet_F]![Name] & "'));"[/CODE]

Other issues:

no space before WHERE - you can see this in your error message
Name is a reserved word - so in your case could refer the form name, not the control called name. You should change it to something more meaningful
I've assumed Name is text and not a PK even though you are comparing with an ID. If it isn't remove the single quotes[/QUOTE]

[CJ_London]

my response was to your original code, why are you presenting a different one?

1 don't understand your response
2 why would you expect it to?
3 so remove the single quotes

am signing off now and out of the office for most of tomorrow - hopefully someone else can take up the issue