473,289 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Increment Counter of MS Access 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

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
13 9793
NeoPa
32,554 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
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
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
32,554 Expert Mod 16PB
Please see my PM and resolve issues so that I can continue in this thread.
Jan 23 '07 #14

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

Similar topics

1
by: JJ | last post by:
Hi, usually, I'm not using MS servers, but I have a big problem with a Access table. I should create a web application for a Historical Dipartment. They have create a populated a Access...
8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
3
by: premmehrotra | last post by:
I am using Access 2000 and Oracle 9.2.0.x on a Windows 2000. I have setup Oracle 9.2 ODBC Driver (I have not yet figured how to set Microsoft's Oracle ODBC driver). I am exporting a table from...
2
by: mawi | last post by:
I'd like to copy the comment column from a access-table into the clipboard (or export it anyhow). any suggestions? cheers. mw
1
by: SteveBark | last post by:
Hello all I am currently trying to develop a script that will take a value from an Excel spreadsheet cell and use that to run a query against an Access table to delete all rows that match the...
2
by: AccessHunter | last post by:
Please advice me on this. I need to create a new access database that will used to collect the employee hours from various locations. Every friday supervisors from various locations will be sending...
0
by: ggunter | last post by:
I have an issue that has me completely boggled. How does one write to an Access table (or any table for that matter) other than the one you obtained the data from? I am using VS2005. I have...
18
by: Ehsan arman | last post by:
I am using two recordsets for inputting data into an access table. I have used two loops for each recordset to do so. My intent is to read data into the table from an Excelsheet. My first recordset...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.