Increment counter of MS Acess table issue

[technocraze]

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

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

[OBP]

If you use rs.addnew it will be a new record and you can write to it your incremented value.
To increment the value use rs.movelast, read the value in to a string.
Split the string in to left string the letter ("S") and right string the value (5).
Increment the value.
recombine the the strings.
write to the new record.

[OBP]

Unless I misunderstand you this can be done with VBA but it is not necessary.
In the table use an Autonumber in the field with the format set to \S00000
this will automatically increment the record for you.
I do not understand this part " 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"
What happens to the "s1"?

[technocraze]

Hi OBP,

what if i want to check for empty field before the insertion. Can i use DLookup ISNull function to check for empty fields before the insertion iterate the field of the recordSet.

I have tried using that but am encountering an error indicating object variable or with block variable not set. Any idea what that mean?

Code logic

Dim fld as DAO.Fields
Dim rs as DAO.RecordSet

For Each fld in rs.fields
Debug.Print rs.Name rs.value (print the field name and value of the field)
next fld
rs.Move Next (error: object variable or with block variable not set)

Am i right to say that rs.Movelast is the move to the last record of the RecordSet. Like what i have mentioned earlier my table consists of five preset record and i want to insert the six record as the newly inserted record and increment the Serialno by 1 which meant that the table shld show Serialno 6. Is that a way that this can be achieved? Is there a way to filter the combo box and shw only distinct StudentId like 'Select distinct StudentId from Student'

Dnt quite understand what u are implying over here?

Split the string in to left string the letter ("S") and right string the value (5).
Increment the value.
recombine the the strings.
write to the new record.[/quote]

[technocraze]

Hi OBP,

what if i want to check for empty field before the insertion. Can i use DLookup ISNull function to check for empty fields before the insertion iterate the field of the recordSet.

I have tried using that but am encountering an error indicating object variable or with block variable not set. Any idea what that mean?

Code logic

Dim fld as DAO.Fields
Dim rs as DAO.RecordSet

For Each fld in rs.fields
Debug.Print rs.Name rs.value (print the field name and value of the field)
next fld
rs.Move Next (error: object variable or with block variable not set)

Am i right to say that rs.Movelast is the move to the last record of the RecordSet. Like what i have mentioned earlier my table consists of five preset record and i want to insert the six record as the newly inserted record and increment the Serialno by 1 which meant that the table shld show Serialno 6. Is that a way that this can be achieved? Is there a way to filter the combo box and shw only distinct StudentId like 'Select distinct StudentId from Student'

Dnt quite understand what u are implying over here?

Split the string in to left string the letter ("S") and right string the value (5).
Increment the value.
recombine the the strings.
write to the new record.[/quote]

In addition , do u have any idea as to what this means
[Microsoft ODBC][SQL Srver] object name not found "Table Name"? I am using Acess as my front end and sql server as my backend. I have set the necessary config for ODBC (System, File DSN) and get external source _ linked table but still encountered this error. It seems to imply that the table cannot be found. I have used the linked table name but to no avail.

[OBP]

Sorry, I only know Access databases.
I still don't see how you can have data in the field when it is a new record.

[geekgirlau]

Remember that your table could potentially be in any order, so it's not a good idea to assume that the last record has the largest sequential number. As OBP suggested, what you need to do is:

Find the maximum ID used (DMax would be one way to do this)
Increment the maximum number - you need to look at the ID without the "S" in order to increment it, for example Mid(MaxID,2)+1
Use rs.AddNew to add a new record

[Imdabaum]

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


You might have already thought about this, but the way the combo box is set up, you will have to update it everytime a new student is added won't you? Why not bind it to the StudentId in the table? Then no matter how many students you get the box just updates automatically. I mention this because here at work we have a similar combo box setup... It's rediculous, but every year someone is paid to add the new year to the defined list... Luckily this year that person was me hopefully I get to put in 2008 next year... but still you save your work money if you can make your application self maintained.



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.

Could you explain this to me? I'm a little lost at what this is suppose to do.

[OBP]

You are not the only one who is confused, me too.
I think the Op. is trying to use VB and SQl to do what Access does for naturally.
I have seen this approach before and it seems to have something to do with "Data Integrity" and better "Control", but it makes for an awful lot of extra work for very little gain.
But that is just my opinion.

[technocraze]

Hi guys,

thanks for the kind response. I will post back with a clearer objective.

[technocraze]

Hi guys,

Here is the logic of the code. tks, ps assist in looking at the code and possible point out the errors.


Private Sub Save()

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

Set db = CurrentDB
Set rs = Me.RecordSet
Set rs = db.OpenRecordSet("Select * from Student",dbOpenDynaset)

rs.MoveFirst
Do while Not rs.EOF
For Each fld in rs.Fields 'iterate through the field of the records
Debug.Print fld.Name & "value:" & fld.Value 'display the field record
Next fld 'move the next field
rs.MoveNext (fld.Value<object variable or with block variable not set>)loop 'iterate <jump to end if without adding the values>

If(IsNull(DLookup("QtyCheckin", "TransactionList"))) Then 'check for null entry in field before insertion
rs.AddNew
rs("Studentid").value = Me.StudentId.Value 'add the controls to the recordSet
rs("Subject").Value = Me.Subject.Value
rs(Course").Value = Me.Course.Value
rs("Grade").Value = Me.Grade.Value
rs("Intake").Value = Me.Intake.Value
rs.Update
rs.Bookmark = rs.LastModified
rs.close 'close recordSet
End If

Set db =Nothing 'reset database
Set rs = Nothing 'reset recordSet

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

[JimmyTheHand]

:hi: Technocraze

Thank you for PM-ing me this problem. You have me really confused here. Looks like the ways our brains work are very defferent. I need better understanding of your project. Please answer the following questions as simply as you can.

1) What is the purpose of the table "Student"? What information is stored in it?

2) What is the purpose of the form?
Now, something like "When I choose S4 in the combobox, I want to check the fields if they got data and if not then insert a new record... etc." would be a wrong answer.
Something like "I want to create a new record for every new student" would be a right kind of answer.

3) What initiates the creation of a new record? E.g. click on a button, etc.

4) What is the source of the data that goes into the new record when it's created?

I migth not be around for a few days, but I'm sure these answers will help others, too, to see clearly.

Jimmy