Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006

    Unanswered: Increment counter of MS Acess table issue

    Hi guys,

    I have an issue with incrementing a counter in a table using VB in MS Acess environment. Below mentioned is an instance.

    Implementation logic

    Table fields
    Serialno = pk Datatype text
    StudentId Text
    Course Text
    Subject Text
    Intake Number

    Design View
    Combo box (Unbounded) - defined list (S1, S2, S3, S4, S5)
    Textbox1 - ControlSource StudentId
    TextBox2 - ControlSource Course
    TextBox3 - ControlSource Subject
    TextBox4 - ControlSource Intake

    Serialno StudentId
    1 S1
    2 S2
    3 S3
    4 S4
    5 S5
    6 S6 (new record)

    When user select the values from the combox, it will list the corresponding values in the textboxes of the table.

    But what I need is to select the same value like say s1 and position at the 6 row of the table and increment the counter by 1, which meant that SerialNo will show six in the table.

    In addition, I need to check the record of the field and make sure it is empty before the insertion and not overwritting the existing the current record but append the new record to the next row of the recordSet where the fields is empty.

    I do knw that rs.MoveFirst is to move to the first column and rs.MoveNext is move to next record but nt too sure as to hw to iterate the recordSet to check for null fields values before insertion. Do I use DLookup or loop through the field? In addition,

    Does anyone knw hw can this be achieved using RecordSet properties?
    rs.MoveFirst, rs.MoveNext, rs.AddNew or sql insert query. Do I use AddRecord or save operation for this implementation? I also need to filter duplicate values of the StudentId mean the values only show once.

    Any guidance, code snippets, logic will be very much appreciated.

  2. #2
    Join Date
    Oct 2003
    I'm sorry but its been a long morning already and I don't know if my response is entirely what you want but here goes.

    Firstly, to ensure that you don't create duplicate entries you need to go into the design view of the table and change the index property of the required field to say "Yes(No Duplicates)". This will prevent you creating duplicate StudentNumbers.

    Secondly, is it not possible to simply make the serial number an autonumber within the design view of the table? This will mean that with each new record the serial number automatically increases.


  3. #3
    Join Date
    Nov 2006
    error when index property is set to Yes(No duplicate) when combo box is being activated.

    Error: This will create duplicate unless remove the duplicate and redefined the index

Posting Permissions

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