By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

Error 3075 While Using Recordset

P: 68
Hi,

I have a table for the enrollment of the employees:

Employee ID
Session ID
Status(In-Progress;Complete;Cancel;No-Show;Waiting List) as Combo Box
Plan
Remarks

After creating a form for this table, I want when the user change the status of any employee to cancel the form should look for anyone who is enrolled in the Waiting List and change his\her status to In-Progress.

I've been trying to use the below code but i got stupmed wiht this error 3075" syntax error ( missing operator) ..."

if we can help out me I would appreciate it

Expand|Select|Wrap|Line Numbers
  1. Private Sub Status_Click()
  2. Dim Db As Database
  3. Dim Rs As Recordset
  4. Dim Slqupdate As String
  5.  
  6.  
  7. Set Db = CurrentDb
  8. Set Rs = Db.OpenRecordset("Enrollment")
  9. Slqupdate = "UPDATE Enrollment " & _
  10. "SET Status = 'In-Progress' " & _
  11. "WHERE Employee ID =" & Rs![Employee ID]
  12.  
  13. Rs.MoveFirst
  14.  
  15. If Status = "cancel" Then
  16.  
  17. Do While Not Rs.EOF
  18.  
  19. If Rs.Fields("Status") = "Waiting List" Then
  20. DoCmd.RunSQL (Slqupdate)
  21. End If
  22. Rs.MoveNext
  23. Loop
  24. Rs.Close
  25. End If
  26.  
  27. End Sub
Jul 11 '09 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,679
@ahd2008
I'm sure that we can help you with this, but I must admit that I am confused as to the logic. Kindly clarify a few points for me.
  1. You have a Table, let's call it tblEnrollment, that consists of the previously listed Fields.
  2. You have a Form, let's call it frmEnrollment, that is based on tblEnrollment.
  3. You have a Combo Box, let's call it cboStatus, that is Bound to the Status Field in tblEnrollment, and which also lists the 5 possible values for Status as previously listed.
  4. If you change the Status of any Employee on the Form, via cboStatus, to Cancel, you wish to search for 'anyone' in tblEnrollment who has a Status of Waiting List, then change his/her Status to In-Progress?
  5. This is the way I am reading this Thread, but it honestly makes no sense to me.
  6. Please provide further explanation.
Jul 11 '09 #2

P: 68
Sorry if i'm not giving sufficient detials.

You are right about the points you mentioned. let more go more in deep and let's suppose that we have 4 employees enrolled but two of them are in the waiting list.

Employee ID Session ID Status Plan Remarks

1111 1 In-Progress Yes
2222 1 In-Progress Yes
3333 1 Waiting List Yes
4444 1 Waiting List Yes

usually, when one of the employees will not attend the user will manually chnage his\her status to cancel and then look for someone else and change his\her status to In-Progress. However, I want this code to do the job; For instance, if the user changed the status of the employee 2222 in the second row, the code should reach employee 3333 and change his\her status to In-Progress instead of Watiting List. But it shouldnt change the status of employee 4444 because when someone will not attend another one should occupid his place not two.

I tried to fix this error but I couldnt

Hopefully, it is clear now and thx for you help
Jul 11 '09 #3

ADezii
Expert 5K+
P: 8,679
@ahd2008
So, the sequence for changing an Employee's Status from Waiting List to In-Progess, given a Cancel would be by [Employee ID] Ascending.
Jul 11 '09 #4

ADezii
Expert 5K+
P: 8,679
@ADezii
One Method of accomplishing this is by placing the following code in the BeforeUpdate() Event of cboStatus. My only major question is what happens if a User changes a Cancel Status to another Status leaving the data in an inconsistent State? I'll leave that part up to you.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboStatus_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo Err_cboStatus_BeforeUpdate
  3. Dim strMsg As String
  4. Dim intResponse As Integer
  5. Dim MyDB As DAO.Database
  6. Dim rstStatus As DAO.Recordset
  7.  
  8. If Me![cboStatus] = "Cancel" Then
  9.   'Let's make sure before we proceed
  10.   strMsg = "Are you sure you want to set the Status of Employee [" & _
  11.            Me![Employee ID] & "] to 'Cancel'?"
  12.   intResponse = MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton1, "Cancel Prompt")
  13.   If intResponse = vbNo Then
  14.     Cancel = True
  15.       Exit Sub
  16.   Else
  17.     'Make sure at least 1 Employee has a Status of Waiting List
  18.     If DCount("*", "tblEnrollment", "[Status] = 'Waiting List'") = 0 Then
  19.       Exit Sub
  20.     Else
  21.       Set MyDB = CurrentDb
  22.       Set rstStatus = MyDB.OpenRecordset("tblEnrollment")
  23.       Do While Not rstStatus.EOF
  24.         If rstStatus![Status] = "Waiting List" Then
  25.           rstStatus.Edit
  26.             rstStatus![Status] = "In-Progress"
  27.           rstStatus.Update
  28.             Exit Do
  29.         End If
  30.           rstStatus.MoveNext
  31.       Loop
  32.         rstStatus.Close
  33.         Set rstStatus = Nothing
  34.     End If
  35.   End If
  36. End If
Jul 12 '09 #5

P: 68
Thanks a lot of your help and this is exactly what i need.

In regard to your questions:

what happens if a User changes a Cancel Status to another Status
The enrollment table is linked to sessions table so every session has start date and end date. During enrollment time, the user may change the employees status from one to another . After the session end, the user can determine who attended and who didnt that why I included the status ( Complete , No-show). So, there is no action required after the session end we only need to give the opportunity to someone else during enrollment time if someone cancel.

I appreciate your great help and have some questions

what is the major difference if I declare the variables in your code without DAO

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database 
  2. Dim rstStatus As DAO.Recordset 
  3.  
Jul 14 '09 #6

ADezii
Expert 5K+
P: 8,679
@ahd2008
You should always qualify these Type of Declarations with the correct Object Library so there is no possible conflict, and to Bind the Object Variable as early as possible. Case in point, suppose you had References Set to both DAO (Data Access Objects) and ADO (ActiveX Data Objects), and you had the following Declaration:
Expand|Select|Wrap|Line Numbers
  1. Dim rstEmployees As Recordset
Which Recordset are you referring to, DAO or ADO? Access will assume the one you want is the higher Reference in the References Dialog. This, unfortunately, may not be the Recordset you want to work with, and may not contain the same Properties and Methods that you desire.
Jul 14 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.