Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2006
    Posts
    40

    Unanswered: MS Acess and VB issues

    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.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well we don't know your code or SQL statements ... Post them.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2006
    Posts
    40

    VBA data overwritten & update issues

    Hi guys,
    this are the codes for the implementations.

    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.

  4. #4
    Join Date
    Nov 2006
    Posts
    40

    VBA AddRecord command issue

    Hi MOwen

    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.

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Typically, when I'm creating a new record from info on a form, I do something like
    PHP Code:
    Dim dbsLCO As DAO.Database
    Set dbsLCO 
    CurrentDb
    Dim rstTable 
    As DAO.Recordset
    Set rstTable 
    dbsLCO.OpenRecordset("TblName"dbOpenDynaset)
    With rstTable
        
    .AddNew
        
    ![FldName1] = Me.txtbox1
        
    ![FldName2] = Me.txtbox2
        
    'continue as needed
        .Update
    End With 
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •