Updating subject

[lotus18]

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

[Killer42]

...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

Um... could you try to explain a bit further what you want to do? I don't see any "prompting" there, only a notification that "blah was updated".

And what version of VB are you using?

[lotus18]

Hi Killer

Sorry for lack of information, I call that function on another form, then when the user click Save, then if the duplicate = true then a messagebox will de displayed.

Rey Sean

[Killer42]

This does nothing to help explain what you mean by "How can I update the schedule without prompting existing?".

[debasisdas]

What do you mean by I can only update subject if modify it. You need to call the update procedure only if duplicate function returns false. What is this line of code doing .

frmSchedules.LoadSchedules

[lotus18]

What do you mean by I can only update subject if modify it. You need to call the update procedure only if duplicate function returns false.

How can I call the update procedure if the didn't modify the item? It is better to give an example than to explain : )

E.g.

Existing records

Subjects Units
-Math 3.0
-English 5.0
-History 2.0

Then I chose Math to modify, but I change only its units not the subject, then the duplicates now becomes true because Math is already existed.

What is this line of code doing .

frmSchedules.LoadSchedules

This populates the list of schedules on a listview (refresh the items)

[Killer42]

So are you saying that if the subject is changed you want to create a new record, and if it isn't, you want to update the existing record?

[lotus18]

So are you saying that if the subject is changed you want to create a new record, and if it isn't, you want to update the existing record?

I just want to update existing record

[WinblowsME]

How about using the following SQL statements?

Inserts "PE" and 5 in tblCourses if "PE" doesn't exist in tblCourses.

INSERT INTO tblCourses ( Subject, Units )
   SELECT DISTINCT "PE", 5
   FROM tblCourses
   WHERE "PE" NOT In (Select Subject From tblCourses);

Updates Units field if the subject is "PE".

UPDATE tblCourses SET Units = "1000"
WHERE Subject="PE";

[Killer42]

It sounds to me as though you need to have a primary key that you can use to refer to the records, so that you're not dependent on the subject to find them.

[lotus18]

How about using the following SQL statements?

Inserts "PE" and 5 in tblCourses if "PE" doesn't exist in tblCourses.

INSERT INTO tblCourses ( Subject, Units )
   SELECT DISTINCT "PE", 5
   FROM tblCourses
   WHERE "PE" NOT In (Select Subject From tblCourses);

Updates Units field if the subject is "PE".

UPDATE tblCourses SET Units = "1000"
WHERE Subject="PE";

I have no problem for inserting a record. I can detect if the record (subject) is already exist before it saves. It would prompt me that new record is already exist. For updating, I can update subject same as the statement as you given above, but my question is how can I update without prompting that the record is already existing? I know that I have to remove the Duplicate Function before the Update. Refer to my post at #6. Is there any other way how to this?

BTW, I think I've got an idea from the other forum and try it later (I have to do homeworks and study my lessons coz we'll be having a quiz by tomorrow). Maybe I can post here the updates... Thanks for your time : )

Rey Sean

[Killer42]

I think what you need is a smarter duplicate-detection routine which doesn't consider the current record as a duplicate.

In other words, you need to be able to ask it "are there any records other than this one with this subject?". That's where the primary key comes in. You pass the relevant (possibly duplicated) details, and the key of the record you're about to update. The routine checks for the existence of any record which matches the relevant detail(s) and doesn't have the same key.

Does this sound reasonable?

[lotus18]

I think what you need is a smarter duplicate-detection routine which doesn't consider the current record as a duplicate.

In other words, you need to be able to ask it "are there any records other than this one with this subject?". That's where the primary key comes in. You pass the relevant (possibly duplicated) details, and the key of the record you're about to update. The routine checks for the existence of any record which matches the relevant detail(s) and doesn't have the same key.

Does this sound reasonable?

Yes you're right, having a PK it prevents duplication of records. But the thing is, what if my professor would like to change the record (just thinking for his possible questions haha)? I did this by setting the general properties of the field in access (Indexed Yes > (Duplicates OK))

[lotus18]

I did this by setting the general properties of the field in access (Indexed Yes > (Duplicates OK))

Sorry for the trouble guys, this should be (Indexed Yes > (No Duplicates))