Add value to database

Posted on 2013-06-04
Last Modified: 2013-06-09

I am working on one project. Currently I have attached the following.

1.      New Forecasting Tool.xls
2.      ForecastToolDatabase

There is a worksheet named ‘Home’. In this there is an icon named as ‘Geographical settings’. Upon click of this button ‘frmManageRegion’ opens. From this form user should be able to add ‘Region_Code’ and ‘Region_Name’ to the table ‘tblRegion’. Please refer to the tables created in ‘ForecastToolDatabase’ which is attached.

Please note that ‘Region Code’ should always be in the series of ‘R1, R2, R3, R4 and so on……’ and upon click of ‘New’ button the system should automatically generate the ‘Region Code’ based upon the values which is already there in the table and the user can give any name for that region. Once done the user should be able to update these details to database by clicking on ‘Add’ Button. It would be of great help and support if we can build this functionality. Please do the needful.

Question by:pg1533
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
LVL 85
ID: 39218683
While you may find one of the Experts willing to do this, please understand that the purpose of EE is to help YOU do these things - not for you to post your Project Requirements and have the volunteer Experts do your work for you.

Exactly what are you having troubles with? It's a fairly simple matter to add values to a database. The basics are this:

1) Open a Connection to the database. This can be done with ADO or DAO.
2) Create your SQL statements to insert data.
3) Run those statements against your connection.

For example:

Dim con As New ADODB.Connection
con.OPen "Your connect string here"

con.Execute "INSERT INTO SomeTable(Col1, Col2) VALUES(Val1, Val2)"

This would insert a new record in SomeTable. You would of course have to change this to suit your needs.

Here's information on Access connections:

Author Comment

ID: 39219001
I understand the purpose of EE. I know how to connect to a database. But where I am finding difficulty is this logic "'Region Code’ should always be in the series of ‘R1, R2, R3, R4 and so on……’ and upon click of ‘New’ button the system should automatically generate the ‘Region Code’ based upon the values which is already there in the table "

It would be helpful if you could provide an logic or a solution? Attached is the actual project which I have worked upon till date for better understanding. I just created another basic files to ease out your work. Please advise.
LVL 85
ID: 39219064
You'll have to give a bit more explanation for this:
system should automatically generate the ‘Region Code’ based upon the values which is already there in the table
How would that work? In other words, how is the Region code related to the values already in the table?

Another way to ask is: How would you create a Region Code on paper, for example?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Author Comment

ID: 39219174
Fine. This is how it works.

I am maintaining a unique value for any region name which is created by an user. The current logic is that the user would enter region code which is R1, R2, R3 and so on for each region name he creates.

Instead of the user creating the region code the system should assign a unique value to the region name which he tries to create.

There are two text boxes in a form. One for Region code and Region name. User is manually entering the region code now. Instead the system should give the region code when he tries to add the region name to the table.


In a given table there are region code from R1 to R6 for each 6 region name the user has created. If the user tries to add another region name to the database then the system should pop up 'R7' in the region code text box.

LVL 85
ID: 39219646
So referring to your .mdb file, I see a table named "Region". In that table are several rows, for example:

Region_Code   Region_Name
R7                    USA
R8                    China
R9                    Japan

So if the user were to enter a new value for Region_Name, you'd want to enter a value of "R10", REGARDLESS OF THE VALUE THE USER ENTERS FOR REGION_NAME?

Author Comment

ID: 39219944
That's correct.

Author Comment

ID: 39220451
Hi LSMConsulting,

Please advise and provide me an update.

Thank you
LVL 85
ID: 39220507
Please advise and provide me an update.
Please try to be patient. We're all volunteer here, and we all have jobs and family that take precedence over this.

Author Comment

ID: 39220762

Accepted Solution

pg1533 earned 0 total points
ID: 39220978
Hi LSMConsulting,

I was able to figure out the solution. Thank you for your support and time. Have a great day!!!


Author Comment

ID: 39220983
Solution is given below:

Public Function SaveUpdateRegionName()

    Dim CodeCnt As Integer
    Call clsadodb.ConnecttoDatabase
    objRSet.CursorType = adOpenStatic
    objRSet.Open "SELECT TOP 1 slno FROM ftd_region ORDER BY slno DESC", clsadodb.objCon
    'objRSet.Open "SELECT count(*) FROM ftd_region", clsadodb.objCon
    'Call CountRow.numRowsInSpecificTable
    CodeCnt = objRSet![slno]
    'mycnt = Right(CodeCnt, Len(CodeCnt) - 1)
    Dim rgnName As String
    rgnName = frmRegionManage.txtRegionName.Text
    'MsgBox objRSet.GetRows
    'CodeCnt = objRSet.GetRows
    'CodeCnt = 12
    Dim newRgnCode As String
    newRgnCode = "R" & CodeCnt + 1
    objRSet.Open "INSERT INTO ftd_region(Region_Code,Region_Name)VALUES('" & newRgnCode & "','" & rgnName & "')", clsadodb.objCon
    If clsadodb.objCon.State = adStateOpen Then clsadodb.objCon.Close
    If objRSet.State = adStateOpen Then objRSet.Close

End Function

Author Closing Comment

ID: 39232770
I was able to figure out the solution. Hence, closing this request.


Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question