Error 3075 While Using Recordset

ahd2008

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

 Private Sub Status_Click()

Dim Db As Database

Dim Rs As Recordset

Dim Slqupdate As String
 
Set Db = CurrentDb

Set Rs = Db.OpenRecordset("Enrollment")

Slqupdate = "UPDATE Enrollment " & _

"SET Status = 'In-Progress' " & _

"WHERE Employee ID =" & Rs![Employee ID]
 
Rs.MoveFirst
 
If Status = "cancel" Then
 
Do While Not Rs.EOF
 
If Rs.Fields("Status") = "Waiting List" Then

DoCmd.RunSQL (Slqupdate)

End If

Rs.MoveNext

Loop

Rs.Close

End If
 
End Sub

ADezii

Quote:

Originally Posted by ahd2008

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

 Private Sub Status_Click()

Dim Db As Database

Dim Rs As Recordset

Dim Slqupdate As String
 
Set Db = CurrentDb

Set Rs = Db.OpenRecordset("Enrollment")

Slqupdate = "UPDATE Enrollment " & _

"SET Status = 'In-Progress' " & _

"WHERE Employee ID =" & Rs![Employee ID]
 
Rs.MoveFirst
 
If Status = "cancel" Then
 
Do While Not Rs.EOF
 
If Rs.Fields("Status") = "Waiting List" Then

DoCmd.RunSQL (Slqupdate)

End If

Rs.MoveNext

Loop

Rs.Close

End If
 
End Sub

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.

You have a Table, let's call it tblEnrollment, that consists of the previously listed Fields.
You have a Form, let's call it frmEnrollment, that is based on tblEnrollment.
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.
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?
This is the way I am reading this Thread, but it honestly makes no sense to me.
Please provide further explanation.

ahd2008

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

ADezii

Quote:

Originally Posted by ahd2008

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

So, the sequence for changing an Employee's Status from Waiting List to In-Progess, given a Cancel would be by [Employee ID] Ascending.

ADezii

Quote:

Originally Posted by ADezii

So, the sequence for changing an Employee's Status from Waiting List to In-Progess, given a Cancel would be by [Employee ID] Ascending.

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

 Private Sub cboStatus_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_cboStatus_BeforeUpdate

Dim strMsg As String

Dim intResponse As Integer

Dim MyDB As DAO.Database

Dim rstStatus As DAO.Recordset
 
If Me![cboStatus] = "Cancel" Then

  'Let's make sure before we proceed

  strMsg = "Are you sure you want to set the Status of Employee [" & _

           Me![Employee ID] & "] to 'Cancel'?"

  intResponse = MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton1, "Cancel Prompt")

  If intResponse = vbNo Then

    Cancel = True

      Exit Sub

  Else

    'Make sure at least 1 Employee has a Status of Waiting List

    If DCount("*", "tblEnrollment", "[Status] = 'Waiting List'") = 0 Then

      Exit Sub

    Else

      Set MyDB = CurrentDb

      Set rstStatus = MyDB.OpenRecordset("tblEnrollment")

      Do While Not rstStatus.EOF

        If rstStatus![Status] = "Waiting List" Then

          rstStatus.Edit

            rstStatus![Status] = "In-Progress"

          rstStatus.Update

            Exit Do

        End If

          rstStatus.MoveNext

      Loop

        rstStatus.Close

        Set rstStatus = Nothing

    End If

  End If

End If

ahd2008

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

In regard to your questions:

Quote:

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

 Dim MyDB As DAO.Database 

Dim rstStatus As DAO.Recordset

ADezii

Quote:

Originally Posted by ahd2008

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

In regard to your questions:

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

 Dim MyDB As DAO.Database 

Dim rstStatus As DAO.Recordset

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

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.

Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

re: Error 3075 While Using Recordset

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about