473,574 Members | 3,052 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Increment Counter of MS Access Table issue

46 New Member
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 9822
NeoPa
32,564 Recognized Expert Moderator MVP
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 Recognized Expert Moderator MVP
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
technocraze
46 New Member
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,564 Recognized Expert Moderator MVP
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
technocraze
46 New Member
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<objec t 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,564 Recognized Expert Moderator MVP
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<objec t 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,564 Recognized Expert Moderator MVP
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<objec t 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
technocraze
46 New Member
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,564 Recognized Expert Moderator MVP
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

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

Similar topics

1
6342
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 database using unicode compression field (for ancient language). I would like to export this table into MySQL o Postgres, but it's
8
20339
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 Type = Value List. Row Source = "1; "Above"; 2; "Below"; 3; "Equal"". When I try to SELECT <field> FROM <table> in my C++ application through ADO, I...
6
18831
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 the data in the excel file that if the first character in the excel file cell is numeric it will read and write only numeric values only. If I...
3
4061
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 Access to Oracle and I get following error: ODBC Call Failed: ORA-12571: TNS Packet Failure Error I see table and its indexes created in Oracle....
2
1802
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
2336
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 cell. I have tried to do this by first using a recordset and then secondly using a db.Execute (sqlstring). The idea of using the recordset was...
2
6250
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 a spreadsheet that has the following fields;Agency ID,Name,In Date,Out Date, Feb1 week,Feb2 week..until Jun5 week.Data from these spreadsheets needs...
0
1067
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 googled, checked other sites similiar to this one, and checked the MSDN site. I can't find anything other than populating a dataset and writing changes...
18
2802
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 selects all columns from the excelsheet and does what it is supposed to do. The second recordset selects only a handful of columns from the Excelsheet,...
0
7841
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7762
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8105
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7859
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8137
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3793
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2274
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1101
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.