By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Saving multiple entries using a range

P: 52
All,

I have scrubbed the archives to see if someone else has requested this type of help but I have not been able to find anything that fits this scenario. I have a form (which is working perfectly at the moment) where my users want me to insert functionality that would allow them to save multiple entries into the database that would automatedly change one value (in our case the Version number). I currently have one object for the Version Number which forces them to insert multiple entries into the database where they are only changing the version number. (We do have cases where we install 5 to 6 version of the same project into test.) They were hoping that I could insert a new Object for the End-of-Range and use the existing object as the Starting-Range. My question is, is there an easy way to make copies of the same record, changing only one value (the Version) using the range values they inserted in the two objects on the form? I’m thinking this will need to be done on my “Save Record” command object within the On Click declaration. I would hate to have to create multiple variables for all the objects on the form and use them to produce the records. Any suggestion on how best to proceed would be greatly appreciated.

Thanks
Birky
Mar 28 '08 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 374
All,

I have scrubbed the archives to see if someone else has requested this type of help but I have not been able to find anything that fits this scenario. I have a form (which is working perfectly at the moment) where my users want me to insert functionality that would allow them to save multiple entries into the database that would automatedly change one value (in our case the Version number). I currently have one object for the Version Number which forces them to insert multiple entries into the database where they are only changing the version number. (We do have cases where we install 5 to 6 version of the same project into test.) They were hoping that I could insert a new Object for the End-of-Range and use the existing object as the Starting-Range. My question is, is there an easy way to make copies of the same record, changing only one value (the Version) using the range values they inserted in the two objects on the form? I’m thinking this will need to be done on my “Save Record” command object within the On Click declaration. I would hate to have to create multiple variables for all the objects on the form and use them to produce the records. Any suggestion on how best to proceed would be greatly appreciated.

Thanks
Birky
Without knowing your table structure in any more detail, I'll try to answer the question by simplying saying that you would have to do a lookup based the the value entered into the form, and then after that has been found, then allow them to change the version number, and then complete it by simply inserting that record into the database.

But that would create a lot of duplicated data.

How I would prefer to do it, is simply have the record that has the information that is going to be static, in one parent table, with a Primary Key associated with it. Then create a table that is referenced by the parent primary key, and have the child table have the version number or any other information that you need to store for the parent record in the child table.

Example:
ParentID -> Autonumber
Value1
value2
.... etc for each value that is going to be static

in the child table you'd create the following structure

ChildID -> Autonumber
ParentID ->Double
VersionNo

They way you can create any number of child records as you need for each parent record.

If you need more details as to how to do this, please include table and query structures that you already have.

Hope that helps,

Joe P.
Mar 29 '08 #2

P: 52
Thanks Joe… I do find myself in-between a rock and hard place for I have inherited the database and we don’t have the time for me to restructure (although it truly could use some). Anyhow, what I have is a very basic table that records the version of an application that has been deployed to a certain environment.

The table includes only: AppName, Version, DateInstalled, and Environment.

The form itself is relatively just as basic, providing a simple display for the user to enter the above values and storing it to the table.

It appears we are bringing up some new environments and therefore deploying multiple versions of the same App to those environments. They want me to insert a new object within the form so they would be able to enter a range of Versions (for the same project) that are being deployed to a specific Environment. That means I would need to enter a single entry within the database for each version within the range. Yes, you are correct, there is a lot of duplicate information within the table but the version is what defines the records distinctions.

This sounds simple enough but I don’t really now the best way to attack this one? Would it be via records set or individual variable collection and storing with a loop? And if so how do I go about doing so? This one truly has my brain cell in a head-lock and I can’t seem to get past it. Again, any help would be greatly appreciated.
Mar 31 '08 #3

Expert 100+
P: 374
Thanks Joe… I do find myself in-between a rock and hard place for I have inherited the database and we don’t have the time for me to restructure (although it truly could use some). Anyhow, what I have is a very basic table that records the version of an application that has been deployed to a certain environment.

The table includes only: AppName, Version, DateInstalled, and Environment.

The form itself is relatively just as basic, providing a simple display for the user to enter the above values and storing it to the table.

It appears we are bringing up some new environments and therefore deploying multiple versions of the same App to those environments. They want me to insert a new object within the form so they would be able to enter a range of Versions (for the same project) that are being deployed to a specific Environment. That means I would need to enter a single entry within the database for each version within the range. Yes, you are correct, there is a lot of duplicate information within the table but the version is what defines the records distinctions.

This sounds simple enough but I don’t really now the best way to attack this one? Would it be via records set or individual variable collection and storing with a loop? And if so how do I go about doing so? This one truly has my brain cell in a head-lock and I can’t seem to get past it. Again, any help would be greatly appreciated.

OK Bricky,

My first question before we take on this moster of yours is, do you know how to program DAO or ADO Recordsets?

If so, then we can handle the problem by querying one side of the tables, and then the other, and then see what we get.

After that, you can make a decision as to how you want to proceed.

Sounds Good?

Let me know.

Joe P.
Apr 1 '08 #4

P: 52
I must admit that I do not know how to program DAO or ADO Recordsets but I am reading up on them as I type. Everything I have learned thus far within VB, VBA has been from a book or the help of others such as your self. My motto is, if you’re willing to teach I am willing to learn. I believe it is my lack of knowledge which is hampering my ability top attack this monster. My goal is to get the suggestion on best to proceed and then I will learn all I can to try and implement.

btw – I truly appreciate you time and help

Birky
Apr 1 '08 #5

Expert 100+
P: 374
I must admit that I do not know how to program DAO or ADO Recordsets but I am reading up on them as I type. Everything I have learned thus far within VB, VBA has been from a book or the help of others such as your self. My motto is, if you’re willing to teach I am willing to learn. I believe it is my lack of knowledge which is hampering my ability top attack this monster. My goal is to get the suggestion on best to proceed and then I will learn all I can to try and implement.

btw – I truly appreciate you time and help

Birky
Ok, Lesson 1:

Creating a connection with DAO. this is the simplest way to learn it.

WHen creating a connection and openning a database, there are obviously two parts. If you've done any serious programming in VB, you'll already know about connection objects as well as Recordset and/or Datasets in VB.

In DAO, you have to create a connection first and then Open a record set.

Example:

Expand|Select|Wrap|Line Numbers
  1. Sub OpenDatabaseEmployee() 
  2. Dim MyDB as DAO.Database
  3. Dim MyRS as DAO.Recordset
  4.  
  5. Set MyDB = CurrentDB() ' This is to set the current open database as the database you're going to connect too.
  6.  
  7. Set MyRS = MyDB.OpenRecordset("SELECT * FROM EMPLOYEE",dbOpenSnapshot)
  8. 'This will open the table EMPLOYEE and SELECT all the records in the table. If you know how to write SQL Statement, then you can simply put the sql statement for searching what it is that you want to find.
  9. 'NOTE: If you build it in Query Builder and then copy and paste it into the VBA code, and format so that it follows the format that I've layed out, It should work for you.
  10.  
  11. If Not MyRS.EOF Then
  12. 'You put in Code that you want to do under the condition
  13. End if
  14.  
  15. 'Now you also have the option to move the record point to the record you want in any number of ways.
  16.  
  17. MyRS.MoveFirst 'Move record pointer to top of table
  18. MyRS.MoveLast 'Move record pointer to bottom of table
  19. MyRS.MoveNext 'Move record pointer to next record in table
  20. MyRS.MovePrevious 'Move record pointer to previous record in table
  21.  
  22. 'You also have find and Seek options within the recordset
  23.  
  24. MyRS.Find "[EmployeeID] = 1"
  25. MyRS.Seek "[EmployeeFirstName] = 'John'"
  26.  
  27. 'When complete you need to close all Recordsets and Connections to the table. and then set the values to Nothing.
  28.  
  29. MyRS.Close
  30. MyDB.Close
  31.  
  32. Set MyRS = Nothing
  33. Set MyDB = nothing
  34.  
  35. 'Please note the order, LIFO (Last In First Out) You have to close the last one you create before you close the first one you create. this way, it will free up resources on your computer. Especially if it is a big recordset.
  36.  
  37. End Sub
  38.  
  39.  
Play around with that and let me know of you have any questions.

Joe P.
Apr 1 '08 #6

P: 52
Okay Joe,

Here is what I have come up with and it seems to be working... Again I would like to thank you for you time and patience. GOD knows I will take any schooling you are willing to give..

I have created a dummy table named “Loop” which contains only five columns (test1 thru test5). I have also created a dummy form with five corresponding text fields where I will be using “test1” as the start of the range and “test2” as the end of the range (to mimic the versions range). I then created a command button to launch all the code below. Note this all seems to be working for the range of records are getting populated in the table. Note I have also included some basic checksums to ensure the correct path is being taken as I worked through this. Any suggestions on how I can better do this would truly be appreciated. And thanks again for taking the time to help me out….

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2. On Error GoTo Err_Command10_Click
  3. Dim db As Database
  4. Dim recEntry As DAO.Recordset
  5. Dim strCount As String
  6. Dim intAnswer As Integer
  7.  
  8. Set db = CurrentDb
  9. strCount = Me.test1
  10.  
  11.     ' Check to see if there is an entry in the Range object on the form.
  12.     If Len(Trim(Nz(Me!test2))) > 0 Then    ' If there is an entry then set up for Range update
  13.         MsgBox "Yes there is a range for this entry!"
  14.             If Me!test2 <= Me!test1 Then    ' Check to see if the value in the Range object is greater than the starting Version object.
  15.                 MsgBox "The Range value must be greater than the Version object. Please reassess your values and try again. "
  16.                 Exit Sub
  17.             Else
  18.                 Set recEntry = db.OpenRecordset("Loop", dbOpenDynaset)
  19.                 intAnswer = MsgBox("Are you sure you want to create " & Me.test2 - Me.test1 & " new version entries within the database?", vbQuestion + vbYesNo, "Want to Proceed")
  20.                 If intAnswer = vbNo Then
  21.                     MsgBox "No changes have been made to the database. Please reevaluate you input and save again."
  22.                     Exit Sub
  23.                 End If
  24.                 Do While strCount <= Me.test2                ' make copies of the current data
  25.                     recEntry.AddNew
  26.                     recEntry!test1 = Me.test1
  27.                     recEntry!test2 = Me.test2
  28.                     recEntry!test3 = Me.test3
  29.                     recEntry!test4 = strCount
  30.                     recEntry!test5 = "automated test entry"
  31.                     recEntry.Update
  32.                     strCount = strCount + 1
  33.                 Loop
  34.                 recEntry.Close
  35.                 Set recEntry = Nothing
  36.             End If
  37.     Else    ' If there is not entry in the range object then carry on as normal.
  38.         MsgBox "there is no range to worry about!"
  39.     End If
  40.     DoCmd.GoToRecord , , acNext
  41.  
  42. Exit_Command10_Click:
  43.     Exit Sub
  44.  
  45. Err_Command10_Click:
  46.     MsgBox Err.Description
  47.     Resume Exit_Command10_Click
  48.  
  49. End Sub
Apr 2 '08 #7

Expert 100+
P: 374
Okay Joe,

Here is what I have come up with and it seems to be working... Again I would like to thank you for you time and patience. GOD knows I will take any schooling you are willing to give..

I have created a dummy table named “Loop” which contains only five columns (test1 thru test5). I have also created a dummy form with five corresponding text fields where I will be using “test1” as the start of the range and “test2” as the end of the range (to mimic the versions range). I then created a command button to launch all the code below. Note this all seems to be working for the range of records are getting populated in the table. Note I have also included some basic checksums to ensure the correct path is being taken as I worked through this. Any suggestions on how I can better do this would truly be appreciated. And thanks again for taking the time to help me out….

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2. On Error GoTo Err_Command10_Click
  3. Dim db As Database
  4. Dim recEntry As DAO.Recordset
  5. Dim strCount As String
  6. Dim intAnswer As Integer
  7.  
  8. Set db = CurrentDb
  9. strCount = Me.test1
  10.  
  11. ' Check to see if there is an entry in the Range object on the form.
  12. If Len(Trim(Nz(Me!test2))) > 0 Then ' If there is an entry then set up for Range update
  13. MsgBox "Yes there is a range for this entry!"
  14. If Me!test2 <= Me!test1 Then ' Check to see if the value in the Range object is greater than the starting Version object.
  15. MsgBox "The Range value must be greater than the Version object. Please reassess your values and try again. "
  16. Exit Sub
  17. Else
  18. Set recEntry = db.OpenRecordset("Loop", dbOpenDynaset)
  19. intAnswer = MsgBox("Are you sure you want to create " & Me.test2 - Me.test1 & " new version entries within the database?", vbQuestion + vbYesNo, "Want to Proceed")
  20. If intAnswer = vbNo Then
  21. MsgBox "No changes have been made to the database. Please reevaluate you input and save again."
  22. Exit Sub
  23. End If
  24. Do While strCount <= Me.test2 ' make copies of the current data
  25. recEntry.AddNew
  26. recEntry!test1 = Me.test1
  27. recEntry!test2 = Me.test2
  28. recEntry!test3 = Me.test3
  29. recEntry!test4 = strCount
  30. recEntry!test5 = "automated test entry"
  31. recEntry.Update
  32. strCount = strCount + 1
  33. Loop
  34. recEntry.Close
  35. Set recEntry = Nothing
  36. End If
  37. Else ' If there is not entry in the range object then carry on as normal.
  38. MsgBox "there is no range to worry about!"
  39. End If
  40. DoCmd.GoToRecord , , acNext
  41.  
  42. Exit_Command10_Click:
  43. Exit Sub
  44.  
  45. Err_Command10_Click:
  46. MsgBox Err.Description
  47. Resume Exit_Command10_Click
  48.  
  49. End Sub
from the looks of it, it sounds like you have a good grasp of what you're doing.

One thing to keep in mind, that there is a very very powerful language in the MS Access dialect of SQL. You can do a great deal of looping and suming real easily when it comes to searching database for particual entries and stuff.

Just keep that in mind.

Other than that, if you have any more questions, please let me know.

Joe P.
Apr 3 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.