Please help with this error, everytime i open a form from my switchboard and i click on one button(suppose to open another form) it throws the attached message on picture. My VBA code is :

Option Compare Database

Private Sub AddAppointment_Click()
Dim rsAppointments As Recordset
Dim strTime As String
Dim dtTime As Date

strTime = Form_AppointmentInformation.cboHour & ":" & Form_AppointmentInformation.cboMinute

Set rsAppointments = CurrentDb.OpenRecordset("Select * from Appointments")
rsAppointments.AddNew
rsAppointments.Fields(1) = Form_AppointmentInformation.AppointmentDate
rsAppointments.Fields(2) = Form_AppointmentInformation.cboHour & ":" & Form_AppointmentInformation.cboMinute

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If RecordsetClone.RecordCount = 2 Then
MsgBox "Client made enough bookings and now qualifies for free voucher."
End If

End Sub

Private Sub Form_Load()
If RecordsetClone.RecordCount = 3 Then
Me.AllowAdditions = True
Me.AllowEdits = False
End If
End Sub

Just to be clear, the code you've shown is in a module: you didn't try to enter it directly as a value in the Properties window of the form.

Not sure whether this is the whole problem, but your code for AddAppointments_Click is incomplete:



Realistically, you're better off using field names rather than Fields(1) and Fields(2) in your statement. Also, note that if the second field is a Date/Time field (as opposed to a text field), your code will have an error.

Hi Doug, Thanks for your reply. Much appreciated.
Attached please find my complete code that i exported and the screen shot with the error.And yes the second field is the Date/Time . Please compare my code with the error and see where i might be going wrong.
Thanking you in advance.

Which button are you clicking on when you get that error?

Go into the form in Design mode and select that button. Check the Properties window, and find the On Click event. Is its property set to [Event Procedure]? If so, click on the ellipsis (...) to the right of that and ensure that you're taken into the VB Editor in the midst of the appropriate code.

I see you chose not to follow my advise to use named fields, not numbered field. Since you're using SELECT * for the recordset, that really can backfire... Are you positive of the order in which the fields are being returned? You do realize that the Fields collection starts numbering at 0: Fields(1) and Fields(2) will be the second and third columns. And since you say Fields(2) is a Date/Time field, not a Text field, use



or



(the first is preferable)

Thank Doug, the previous error is now gone but when i click on "add record" it throws "The expression On Click you entered as the event property setting produced the following error. a problem occured while....with the OLE server or ActiveX Control
The "add Record" 's Property Sheet is set to On Click->[Event Procedure] and it has
On Click:
Private Sub btnAddRecord_Click()
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.GoToRecord , , acNewRec
End Sub

The rest of the code has the following...
On load :
Private Sub Form_Load()
If RecordsetClone.RecordCount = 3 Then
Me.AllowAdditions = True
Me.AllowEdits = False
End If

On Click
Private Sub Form_Click()
End Sub

Before Update:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If RecordsetClone.RecordCount = 2 Then
MsgBox "Client made enough bookings and now qualifies for free voucher."
End If
End Sub

Is this strictly an Access database, or are you storing the data in some other DBMS? Are you using any ActiveX controls on the form?

Have you done a compact and repair recently? Sometimes that can fix unusual problems.

Thanks Doug, I added a new "add record" button and this time i am controlling it using the macro, it saves data to its table but does not clear thereafter, so i have to manualy remove what was typed in. How can i add a macro to add record to table and the clear it off the list boxes or fileds ?

It may be possible, but I don't use macros and am not sure how you'd do it.

If you're only talking a few controls, write a macro (or VBA) simply to put Null in each control.

Thanks Doug, give me few VBA lines to do that...Thanks