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

Increment Counter of MS Access Table issue

P: 46
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.
Jan 17 '07 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
<----------------- ^^^^ Very Clear ^^^^ vvvv Unclear vvvv
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.
Up to the arrow everything made perfect sense and was well explained. Unfortunately, from that point I got lost and was really unsure of what you were looking for. Any chance of redoing from that point?
Jan 18 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi techno

I know you've given a very detailed question but I really don't understand what you want. Can you try rephrasing your main question?

Mary
Jan 18 '07 #3

P: 46
Hi mmccarthy,

thanks for yr kind response.

what i need is to be able to increment the counter when iterating through the recordset. As my table consists of pre-defined values so i want it to perform in the way whereby i am able to append the record to the last immediate record of the table.

For instance, StudentID (S1-S5) so i want to append the next record to the table without overwritting all the previous entry (S1-S5). move the recordSet to the last record of the table and insert the values.

BTw, I tried using a loop to iterate through the field of the record for testing / stepping through for errors / purposes but encountering an error when i move to the next record. Any idea hw can that be resolved? I have set a variable for my field.

Dim fld as DAO.Field

Error msg:
fld.Value <object variable or with block variablel not set>

Is that a way wherby i just loop through the record / row of the table without accesing the individual field / column?

RecordSet iteration

rs.MoveFirst - first record
rs.MoveNext - Next record
rs.Movelastt - Last record

Hi techno

I know you've given a very detailed question but I really don't understand what you want. Can you try rephrasing your main question?

Mary
Jan 19 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
I'll take it then (from your completely ignoring my post and pretending I'm not there) that you can do without my help in future. I suppose I can live with that.
Good luck.
Jan 19 '07 #5

P: 46
Hi NeoPa,

Please dnt get the wrong idea that i am ignoring yr mail, honestly speaking you have been a great guidance whenever i post a query and without fail you are always there to respond to my issues.

In fact to make it clear what i need is to be able to append the records to the last records without overwritting the previous records. By looping through the records of the RecordSet.

I have use the rs.Add property but it seems that the first record has been overwritten by the new entry.

I have managed to iterate through the field of the record but when i move to the next immediate record. I encountered an error.

Error: fld.Value<object variable or with bloack variable not set>

I'll take it then (from your completely ignoring my post and pretending I'm not there) that you can do without my help in future. I suppose I can live with that.
Good luck.
Jan 20 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
Hi NeoPa,

Please dnt get the wrong idea that i am ignoring yr mail, honestly speaking you have been a great guidance whenever i post a query and without fail you are always there to respond to my issues.

In fact to make it clear what i need is to be able to append the records to the last records without overwritting the previous records. By looping through the records of the RecordSet.

I have use the rs.Add property but it seems that the first record has been overwritten by the new entry.

I have managed to iterate through the field of the record but when i move to the next immediate record. I encountered an error.

Error: fld.Value<object variable or with bloack variable not set>
As it's past 04:00 I can't post anything of much use atm - but I note your reply with thanks. I'll check it out over the weekend & see if I can find anything.

-NeoPa.
Jan 20 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Hi NeoPa,

Please dnt get the wrong idea that i am ignoring yr mail, honestly speaking you have been a great guidance whenever i post a query and without fail you are always there to respond to my issues.

In fact to make it clear what i need is to be able to append the records to the last records without overwritting the previous records. By looping through the records of the RecordSet.

I have use the rs.Add property but it seems that the first record has been overwritten by the new entry.

I have managed to iterate through the field of the record but when i move to the next immediate record. I encountered an error.

Error: fld.Value<object variable or with bloack variable not set>
If your code is not toooo long, then post it in here to look at.
I would expect rs.Add to do what you want it to - not an update.
Maybe the problem lies elsewhere in your code.
Another, unrelated reason why you may get that error code is that the project has been reset. This normally happens when debugging only (unless using Windows 98 when it seems to happen randomly :().
Jan 21 '07 #8

P: 46
Hi NeoPa,

Application
MS Acess version 2003, visual basic

here is the code, tks. Whenever this code is executed it just overwrite the current record. what i want to achieve is to be able to insert the record after the end of the record. For instance S1-S5 is my defined list so S6 will be my newly inserted record. I tried using AddRecord operation (acNewRec) but the record jux gt overwritten. It would be very appreciated if u can assist in checking the logic of the code. Thanks a million!!!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save()
  2.  
  3. Dim rs as DAO.RecordSet
  4. Dim db as DAO.Database
  5. Dim fld as field
  6.  
  7. Set db = CurrentDB
  8. Set rs = Me.RecordSet
  9. Set rs = db.OpenRecordSet("Select * from Student",dbOpenDynaset)
  10.  
  11. rs.MoveFirst
  12. Do while Not rs.EOF
  13. For Each fld in rs.Fields 'iterate through the field of the records
  14. Debug.Print fld.Name & "value:" & fld.Value 'display the field record
  15. Next fld 'move the next field
  16. rs.MoveNext (fld.Value<object variable or with block variable not set>)loop 'iterate <jump to end if without adding the values>
  17.  
  18. If(IsNull(DLookup("QtyCheckin", "TransactionList"))) Then 'check for null entry in field before insertion 
  19. rs.AddNew
  20. rs("Studentid").value = Me.StudentId.Value 'add the controls to the recordSet
  21. rs("Subject").Value = Me.Subject.Value
  22. rs(Course").Value = Me.Course.Value
  23. rs("Grade").Value = Me.Grade.Value
  24. rs("Intake").Value = Me.Intake.Value
  25. rs.Update
  26. rs.Bookmark = rs.LastModified
  27. rs.close 'close recordSet
  28. End If
  29.  
  30. Set db =Nothing 'reset database
  31. Set rs = Nothing 'reset recordSet
  32.  
  33. Private Sub Combo1_AfterUpdate()
  34.     ' Find the record that matches the control.
  35.     Dim rs As Object
  36.  
  37.     Set rs = Me.Recordset.Clone
  38.     rs.FindFirst "[Serialcode] = " & Str(Nz(Me![Combo1], 0))
  39.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  40.     End Sub
Jan 22 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
I really need the actual code. This wouldn't run at all as there are too many basic errors in it (The type of problem the compiler would look at and refuse even to start executing - EG the Loop command lost on a previous line and has never been executed as it is all lower case).
I like the idea to add comments to show where the problem lies, but you need to add them to the code and then compile & test it. When (and only when) that's been done, Copy/Paste the code in a post here. Then I'm looking at the code you're talking about. In this case I'm certainly not I'm afraid.
Jan 22 '07 #10

P: 46
Hi NeoPa,

those in black are the comments. I am encountering an error when the code step at rs.MoveNext at the end of the first record when moving to the second record. Another is when the all the existing record have been iterated, it jux jump to the end if staement without going through rs.AddNew. tks

Error: fld.Value(Object variable or with block variablel not set>

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save()
  2.  
  3. Dim rs as DAO.RecordSet
  4. Dim db as DAO.Database
  5. Dim fld as field
  6.  
  7. Set db = CurrentDB
  8. Set rs = Me.RecordSet
  9. Set rs = db.OpenRecordSet("Select * from Student",dbOpenDynaset)
  10.  
  11. rs.MoveFirst
  12. Do while Not rs.EOF
  13. For Each fld in rs.Fields 'iterate through the field of the records
  14. Debug.Print fld.Name & "value:" & fld.Value 'display the field record
  15. Next fld 'move the next field
  16. rs.MoveNext (fld.Value<object variable or with block variable not set>)
  17. loop 'iterate <jump to end if without adding the values>
  18.  
  19. If(IsNull(DLookup("QtyCheckin", "TransactionList"))) Then
  20.  'check for null entry in field before insertion  
  21.  
  22. rs.AddNew
  23. rs("Studentid").value = Me.StudentId.Value 
  24. 'add the controls to the recordSet
  25.  
  26. rs("Subject").Value = Me.Subject.Value
  27. rs(Course").Value = Me.Course.Value
  28. rs("Grade").Value = Me.Grade.Value
  29. rs("Intake").Value = Me.Intake.Value
  30. rs.Update
  31. rs.Bookmark = rs.LastModified
  32. rs.close 'close recordSet
  33. End If
  34.  
  35. Set db =Nothing 'reset database
  36. Set rs = Nothing 'reset recordSet
  37.  
  38. Private Sub Combo1_AfterUpdate()
  39.     ' Find the record that matches the control.
  40.     Dim rs As Object
  41.  
  42.     Set rs = Me.Recordset.Clone
  43.     rs.FindFirst "[Serialcode] = " & Str(Nz(Me![Combo1], 0))
  44.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  45.     End Sub
Jan 22 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
I have to admit to being a bit confused here.
This looks like code you would find within an Access database, and yet it clearly isn't (There are signs - alluded to in post #10 - that show this code has never been run from within Access). If this is from somewhere outside of Access I need to know, as otherwise I don't know my starting point.
If this is supposed to be run from within Access then, clearly, you have not done as requested and copied the code after compiling and testing it :confused:
Please let me know if I've misunderstood something here. I don't want to be critical where it's not warranted, but I don't think I have what I need (requested).
Jan 22 '07 #12

P: 46
this is the code i have used for my application. Though the record have been appended in the table but it also overwrite the present record. For instance i have selected S1 from the combo box and append to the table and insert name (Mary) under column name but when i select S1 again and insert the record Sam under column name. It gt appended at the end of the recordSet but same time Mary gt overwritten by Sam. I suspect this gt to do with the RecordSet but nt sure hw to go about doing it. I have tried but it turn it to be the same. Another thing I wanna seek for for yr kind consultant is to use Add New Record operation to append the record. May i knw hw to go about achieving. I have tried that too but same case. tks.

If Not rs.EOF
rs.MoveLast
rs.AddNew
rs("StudentId").value = SrudentId.value
.
.
.
rs.Update
rs.Bookmark = rs.LastModified
Jan 23 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
Please see my PM and resolve issues so that I can continue in this thread.
Jan 23 '07 #14

Post your reply

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