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
  • 8
  • 4
LVL 84
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 84
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?

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 84
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.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 39220451
Hi LSMConsulting,

Please advise and provide me an update.

Thank you
LVL 84
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now