Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Add value to database

Posted on 2013-06-04
Medium Priority
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?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

719 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