Add value to database

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
pg1533Asked:
Who is Participating?
 
pg1533Author Commented:
Hi LSMConsulting,

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

Regards,
Prashanth
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pg1533Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pg1533Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pg1533Author Commented:
That's correct.
0
 
pg1533Author Commented:
Hi LSMConsulting,

Please advise and provide me an update.

Thank you
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pg1533Author Commented:
Ok
0
 
pg1533Author Commented:
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
 
pg1533Author Commented:
I was able to figure out the solution. Hence, closing this request.

Regards,
Prashanth
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.