Updating subject in schedule

[lotus18]

Hello World

I have this code

Public Function Duplicate(ByRef adRecordset As ADODB.Recordset, ByRef Subject As _
String, ByRef Course As String) As Boolean
    Duplicate = False 'Default value
    
    'Opens and checks the connection
    SetConnection
    CheckConnection adRecordset
    
    adRecordset.Open "Select SubjectCode From LoadSchedules Where SubjectCode='" & _
    Subject & "' And Courses.Title='" & Course & "'", dbConnection, 1, 1
    'Checks if there is duplicate subject
    If adRecordset.RecordCount > 0 Then
        Duplicate = True
    Else
        Duplicate = False
    End If
    Set dbConnection = Nothing
End Function

Public Sub Update(ByVal ScheduleID As String, ByRef SubjectCode As String, ByRef _
Lecture As Integer, ByRef Laboratory As Integer, ByRef StartTime As Date, ByRef EndTime _
As Date, ByRef DayID As String, ByRef RoomID As String, adForm As Form)
    'Opens and checks the connection
    SetConnection
    'CheckConnection adRecordset
    

    'Update course
    dbConnection.Execute "Update Schedules Set [SubjectCode]='" & SubjectCode & _
    "',[Lecture]=" & Lecture & ",[Laboratory]=" & Laboratory & ",[StartTime]=#" & _
    StartTime & "#,[EndTime]=#" & EndTime & "#,[DayID]='" & DayID & "'," & _
    "[RoomID]='" & RoomID & "' Where [ScheduleID]='" & ScheduleID & "'"
    Unload adForm
    
    frmSchedules.LoadSchedules
    MsgBox ScheduleID & " has been successfully updated.", 64, "Updated Schedule"
    frmSchedules.LoadSchedules
    Set dbConnection = Nothing
    Exit Sub

End Sub
I use the duplicate function to check if the subject is already existing before the update function, now my problem is this, I can only update subject if modify it. How can I update the schedule without prompting existing? Hope you understand my post

Rey Sean

[si_the_geek]

The Update statement changes an existing record. If you want to add a new record (I think that's what you meant), you need to use an Insert statement.


Note that there are three other issues with your code, the first is that you don't .Close the connection, but you do release it. It is important to .Close your connection when you have finished with it. Assuming that the SetConnection routine opens it, you should add an extra line to each of your routines, like this:

...
    dbConnection.Close
    Set dbConnection = Nothing

The next issue is that the .RecordCount property is not the correct method to use to check if there are records.. depending on the circumstances, it may not have been set to the value yet (instead it will be -1, or "not sure"). What you should be using instead is the EOF and BOF properties, eg:

    If NOT (adRecordset.BOF AND adRecordset.EOF) Then
      'there are records
    Else
      'there are no records
    End If

The final issue I spotted isn't that important, but it helps to read (and thus check/modify) your code.. instead of using "magic numbers" at the end of your call to .Open (eg: .. , dbConnection, 1, 1 ), you should use the constants instead (eg: .. , dbConnection, adOpenForwardOnly, adLockReadOnly ). These values will always be exactly the same (as you have a Reference to ADO), but the named version will be much clearer.

[lotus18]

OK. thanks or replying, I have a code in in adding a new record but how can I Update the changes of an existing record without prompting that there is existing subject? Sir, if you open the connection (dbConnection) yes we have to close it of course, but is it necessary to close the connection if i'm using dbConnection.Execute rather than dbConnection.Open?

[si_the_geek]

OK. thanks or replying, I have a code in in adding a new record but how can I Update the changes of an existing record without prompting that there is existing subject?

I'm not sure what you mean.. can you clarify?

Sir, if you open the connection (dbConnection) yes we have to close it of course, but is it necessary to close the connection if i'm using dbConnection.Execute rather than dbConnection.Open?

That is irrelevant - you cannot use the connection (dbConnection.Execute or anything else, such as recordset.Open sql, dbConnection ) unless you have already opened it.

If SetConnection actually opens the connection, you need to close it as I said.

[lotus18]

Sir, how can I modify the existing subject without duplicates? I'm using Access as a back end. I can prevent it from duplicating the records directly in access (FieldName > Yes (No Duplicates). Is there other way to prevent it?

[si_the_geek]

The way you have set up the field in Access means that you cannot have duplicates in that field (no matter what the values of the other fields). This also applies when using it from VB.

When trying to create a duplicate, you should get an error (which you could show to the user), or you could check for a duplicate yourself first - by running a Select statement like you are doing. Your current Select statement isn't really right as it uses the values of two fields, and you should be checking only one.

If you are changing the value of other fields (but leaving that field the same), then you don't need to check for duplicates in that field (as you know the work has already been done, when that value was set).