Solved

Add value to database

Posted on 2013-06-04
12
161 Views
Last Modified: 2013-06-09
Hi,

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.

Regards,
Prashanth
ForecastToolDatabase.mdb
New-Forecasting-Tool.xls
0
Comment
Question by:pg1533
  • 8
  • 4
12 Comments
 
LVL 84
Comment Utility
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:
http://www.connectionstrings.com/access
0
 

Author Comment

by:pg1533
Comment Utility
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.
New-Forecasting-Tool.xls
ForecastToolDatabase.mdb
0
 
LVL 84
Comment Utility
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?
0
 

Author Comment

by:pg1533
Comment Utility
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.

Example:

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.


Regards,
Prashanth
0
 
LVL 84
Comment Utility
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?
0
 

Author Comment

by:pg1533
Comment Utility
That's correct.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:pg1533
Comment Utility
Hi LSMConsulting,

Please advise and provide me an update.

Thank you
0
 
LVL 84
Comment Utility
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.
0
 

Author Comment

by:pg1533
Comment Utility
Ok
0
 

Accepted Solution

by:
pg1533 earned 0 total points
Comment Utility
Hi LSMConsulting,

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

Regards,
Prashanth
0
 

Author Comment

by:pg1533
Comment Utility
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)
    objRSet.Close
   
    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
0
 

Author Closing Comment

by:pg1533
Comment Utility
I was able to figure out the solution. Hence, closing this request.

Regards,
Prashanth
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

763 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

16 Experts available now in Live!

Get 1:1 Help Now