MS Acess and VB issues

[technocraze]

Hi guys,

I have a few issues that are yet to be resolved. Below mentioned are the issues. devlopment tool: MS Acess using visual basic
with sql. It would be very much appeciated of any assistance rendered to resolve the issues. Thanks in advance.

1) Data-overwritten issue - insertion

Controls
Combo box(predefined list-studentId) (S1,S2,S3,S4,S5)
Textboxes (studentId,class, subject, grade, course)
Button - Update

I am having an issue with inserting the data to MS Acess table. This happened when i select my data from the drop-down combo
box. I am using a change event for my drop-down combo coz i gt a predefined list of data in my MS Acess table. Nw, I need to
insert the datas (subject, class, grade, course) into the first predfined list, which is first selected item of the cbx. When
I press on the button update, the data gt inserted into the table (Student). But when I select the the first item frm the
cbx, the previous entry gt overwritten by second entry. May I knw how to prevent the data frm overwritten and implement in
such a way that prevent user from entering the same data? I want to prevent user from inserting the data to the table that
have been occupied. I am using an sql insert, and DLookUp function but nt too sure as to whether to use recordSet.

2) Updating issue

Controls
2 combo box -predfined, populated
1 textbox -empty
1 listbox - populated
1 button - Update

I am having an issue with updating the data to MS Acess table. I am using a predefined combo box(1st cbx), populated cbx(2nd
cbx) - data matching the corresponding data from the table, popoulate lbx - data corresponding to the two cbx, empty textbox
(user-input). What i want to achieve is to update the data of the empty textbox based on the values of the listbox. But when
i run the sql query i encountered an error : failed in linked table. May I knw hw can this issue be rectified and any
suggections.

3) Updating issue

Control
2 combo box (1 predefined, 1 populated)
1 listbox (populated)
Bounded textboxes

I am having an issue with binding the chosen data of the populated listbox to the textbox. I am using a predefined combo box
(1st cbx), populated combox box (2nd cbx) - selected data of the cbx corresponding to the table, populated listbox - selected
data of the 2 cbx corresponding to the table, bounded textbox to table controlSource - selected data from the populated
listbox. But when i click on the data from the populated listbox and displayed on the textbox. I encountered a cancelUpadate
and edit error. May I knw what wrong with the codes and hw can it be resolved? I am using recordSet for this implementation
under the Update event. I am nt too sure as hw to manipulate with the recordSet. Hence, code snippets is much preferred.

[XLGibbs]

Appending/Inserting records and Updating records takes a little bit of work.

I suggest starting with a separate small database for practice and to use the FORM wizard to make a form that navigates records and has the built in functionality of adding records ands altering them. Seeing THAT code in action may give you some insight.

Otherwise, let's keep this thread going and tackle one issue at a time. If you have code written, that doesnt work, perhaps you can post it and we can see what there might be to change it and make it work..

[technocraze]

primary key is serialCode

1) Data overwritten issue

Combo change event (clear textboxes)

class.value = ""
subject.value = ""
grade.value =""
course.value =""

Button click event - using rs property to insert

Dim db as DAO.Database
Dim rs as DAO.RecordSet

set db = CurrentDB
set rs = db.openRecordSet("Student")

if(Not IsNull(DLookup("[StudentId","class","subject","grade","course]" "Student", "[StudentId] = "' & Me.ComboStud.value & "')
rs.AddNew
rs("StudentId").value = studId.value
rs("Class").value = class.value
rs("subject").value = subjectCode.value
rs("grade").value = grade.value
rs.Update

else
MsgBox "Insert violation"

End If

rs.close

Error : primary or index cannot be null

using sql stat to insert

Dim db as DAO.Database
Dim rs as DAo.RecordSet
Dim sql as string
Dim counter as long

Set db = CurrentDB
Set rs = db.OpenRecordSet("Student")
Do until rs.EOF
rs.MoveNext
if(Not IsNull(DLookup("[StudentId","class","subject","grade","course]" "Student", "[StudentId] = "' & Me.ComboStud.value & "')
sql = insert into [Student] [tablefields] value [textboxes value]
else
MsgBox "insert violation"

execute query sql

Error: invalid sql stat

AfterUpdate event

Dim rs as Object

set rs = Me.RecordSet.Clone
rs.FindFirst "[StudentId] = '" & Me![Combo34] & "'"
If not rs.EOF then Me.Bookmark = rs.Bookmark

Conclusion

In fact, i wanted to use beforeUpdate event to check whether the table contains any values selected from the combo box before updating in the table, if so cannot update otherwise insert the corresponding to the table and then fires the update event but nt too sure as hw this can be achieve. Let say i have inserted the data from the textboxes in the student table corresponding to the selected item frm the combo box. But after the second entry using the same data from the cbx, it gt overwritten. Above code is the implementation.

3) Data update issue

AfterUpdate event

Dim rs as DAO.RecordSet
Dim db as DAO.Database

set rs = Me.recordSetClone
rs.FindFirst "[SerialCode] = " & Str (Nz(Me![List1],0)
If not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.CouresCode.value = List1.Coulmn(1)

Error : Update or CancelUpdate without using AddNew or Edit

Conclusion

Display the selected value from the listbox and show it in the textbox.

[technocraze]

Hi XLGibbs,

this are the codes for the implementations.

[XLGibbs]

primary key is serialCode

1) Data overwritten issue

Combo change event (clear textboxes)

class.value = ""
subject.value = ""
grade.value =""
course.value =""

Button click event - using rs property to insert

Dim db as DAO.Database
Dim rs as DAO.RecordSet

set db = CurrentDB
set rs = db.openRecordSet("Student")

if(Not IsNull(DLookup("[StudentId","class","subject","grade","course]" "Student", "[StudentId] = "' & Me.ComboStud.value & "')
rs.AddNew
rs("StudentId").value = studId.value
rs("Class").value = class.value
rs("subject").value = subjectCode.value
rs("grade").value = grade.value
rs.Update

else
MsgBox "Insert violation"

End If

rs.close

Error : primary or index cannot be null


If you are adding a new record, you must also add a Primary Key.


using sql stat to insert
Error: invalid sql stat


Your sql statement is invalid for what you intend to do. It appears you are first wanting to check if it exists or not, and if does update the record, if note, add a new one. The Update statements int the 1st part should do that..in theory.




AfterUpdate event
AfterUpdate event
Conclusion

Display the selected value from the listbox and show it in the textbox.

Not sure, let me take a closer look at this. I presume your code is generalized here somewhat..

[technocraze]

Hi XLGibbs,

Alternatively, i tried using the AddRecord command provided by Acess but the new record is not been appended to the table but instead it has been overwritten. I have set the focus for the field under the acNewrc already. By right i shld be able to append the record at the end of the record of the field and nt overwritting the record.

primary key
SerialCode (Autokey)

Fields
StudentId, subjectcode, course, grade

Combo box
StudentId (predefined list) - S1, S2, S3, S4, S4, S5

AddRecord command (by Acess under operation)
acNewRc
StudentId.SetFocus
subjectCode.SetFocus
course.SetFocus
grade.SetFocus

Tks for your assistance.

[technocraze]

Hi Guys
Here is the code for my AddRecord
ps assist on this. Million tks

Private Sub AddRecord_Click()
On Error GoTo AddRecord_Click
DoCmd.OpenForm "Record"
DoCmd.GoToRecord, "Record", acNewRec
Combo1.SetFocus
courseCode.SetFocus
Subject.SetFocus
Grade.SetFocus
Exit_AddRecord_Click:
Exit Sub
Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click

End Sub
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SerialCode] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub