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

Updating subject

lotus18
100+
P: 866
I have this code

Expand|Select|Wrap|Line Numbers
  1. Public Function Duplicate(ByRef adRecordset As ADODB.Recordset, ByRef Subject As _
  2. String, ByRef Course As String) As Boolean
  3.     Duplicate = False 'Default value
  4.  
  5.     'Opens and checks the connection
  6.     SetConnection
  7.     CheckConnection adRecordset
  8.  
  9.     adRecordset.Open "Select SubjectCode From LoadSchedules Where SubjectCode='" & _
  10.     Subject & "' And Courses.Title='" & Course & "'", dbConnection, 1, 1
  11.     'Checks if there is duplicate subject
  12.     If adRecordset.RecordCount > 0 Then
  13.         Duplicate = True
  14.     Else
  15.         Duplicate = False
  16.     End If
  17.     Set dbConnection = Nothing
  18. End Function
  19.  
  20. '====================================
  21.  
  22. Public Sub Update(ByVal ScheduleID As String, ByRef SubjectCode As String, ByRef _
  23. Lecture As Integer, ByRef Laboratory As Integer, ByRef StartTime As Date, ByRef EndTime _
  24. As Date, ByRef DayID As String, ByRef RoomID As String, adForm As Form)
  25.     'Opens and checks the connection
  26.     SetConnection
  27.     'CheckConnection adRecordset
  28.  
  29.  
  30.     'Update course
  31.     dbConnection.Execute "Update Schedules Set [SubjectCode]='" & SubjectCode & _
  32.     "',[Lecture]=" & Lecture & ",[Laboratory]=" & Laboratory & ",[StartTime]=#" & _
  33.     StartTime & "#,[EndTime]=#" & EndTime & "#,[DayID]='" & DayID & "'," & _
  34.     "[RoomID]='" & RoomID & "' Where [ScheduleID]='" & ScheduleID & "'"
  35.     Unload adForm
  36.  
  37.     frmSchedules.LoadSchedules
  38.     MsgBox ScheduleID & " has been successfully updated.", 64, "Updated Schedule"
  39.     frmSchedules.LoadSchedules
  40.     Set dbConnection = Nothing
  41.     Exit Sub
  42.  
  43.  
  44. End Sub
  45.  

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
Jan 28 '08 #1
Share this Question
Share on Google+
13 Replies


Expert 5K+
P: 8,434
...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?
Jan 29 '08 #2

lotus18
100+
P: 866
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
Jan 29 '08 #3

Expert 5K+
P: 8,434
This does nothing to help explain what you mean by "How can I update the schedule without prompting existing?".
Jan 29 '08 #4

debasisdas
Expert 5K+
P: 8,127
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
Jan 29 '08 #5

lotus18
100+
P: 866
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)
Jan 29 '08 #6

Expert 5K+
P: 8,434
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?
Jan 29 '08 #7

lotus18
100+
P: 866
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
Jan 30 '08 #8

P: 58
How about using the following SQL statements?

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblCourses ( Subject, Units )
  2.    SELECT DISTINCT "PE", 5
  3.    FROM tblCourses
  4.    WHERE "PE" NOT In (Select Subject From tblCourses);
Updates Units field if the subject is "PE".

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblCourses SET Units = "1000"
  2. WHERE Subject="PE";
Jan 31 '08 #9

Expert 5K+
P: 8,434
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.
Jan 31 '08 #10

lotus18
100+
P: 866
How about using the following SQL statements?

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblCourses ( Subject, Units )
  2.    SELECT DISTINCT "PE", 5
  3.    FROM tblCourses
  4.    WHERE "PE" NOT In (Select Subject From tblCourses);
Updates Units field if the subject is "PE".

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblCourses SET Units = "1000"
  2. 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
Jan 31 '08 #11

Expert 5K+
P: 8,434
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?
Feb 1 '08 #12

lotus18
100+
P: 866
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))
Feb 1 '08 #13

lotus18
100+
P: 866
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))
Feb 1 '08 #14

Post your reply

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