Saving multiple entries using a range

[Birky]

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

[PianoMan64]

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.

[Birky]

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.

[PianoMan64]

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.

[Birky]

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

[PianoMan64]

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:

Sub OpenDatabaseEmployee() 
Dim MyDB as DAO.Database
Dim MyRS as DAO.Recordset
 
Set MyDB = CurrentDB() ' This is to set the current open database as the database you're going to connect too.
 
Set MyRS = MyDB.OpenRecordset("SELECT * FROM EMPLOYEE",dbOpenSnapshot)
'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.
'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.
 
If Not MyRS.EOF Then
'You put in Code that you want to do under the condition
End if
 
'Now you also have the option to move the record point to the record you want in any number of ways.
 
MyRS.MoveFirst 'Move record pointer to top of table
MyRS.MoveLast 'Move record pointer to bottom of table
MyRS.MoveNext 'Move record pointer to next record in table
MyRS.MovePrevious 'Move record pointer to previous record in table
 
'You also have find and Seek options within the recordset
 
MyRS.Find "[EmployeeID] = 1"
MyRS.Seek "[EmployeeFirstName] = 'John'"
 
'When complete you need to close all Recordsets and Connections to the table. and then set the values to Nothing.
 
MyRS.Close
MyDB.Close
 
Set MyRS = Nothing
Set MyDB = nothing
 
'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.
 
End Sub
 
 

Play around with that and let me know of you have any questions.

Joe P.

[Birky]

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….

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim db As Database
Dim recEntry As DAO.Recordset
Dim strCount As String
Dim intAnswer As Integer
 
Set db = CurrentDb
strCount = Me.test1
 
    ' Check to see if there is an entry in the Range object on the form.
    If Len(Trim(Nz(Me!test2))) > 0 Then    ' If there is an entry then set up for Range update
        MsgBox "Yes there is a range for this entry!"
            If Me!test2 <= Me!test1 Then    ' Check to see if the value in the Range object is greater than the starting Version object.
                MsgBox "The Range value must be greater than the Version object. Please reassess your values and try again. "
                Exit Sub
            Else
                Set recEntry = db.OpenRecordset("Loop", dbOpenDynaset)
                intAnswer = MsgBox("Are you sure you want to create " & Me.test2 - Me.test1 & " new version entries within the database?", vbQuestion + vbYesNo, "Want to Proceed")
                If intAnswer = vbNo Then
                    MsgBox "No changes have been made to the database. Please reevaluate you input and save again."
                    Exit Sub
                End If
                Do While strCount <= Me.test2                ' make copies of the current data
                    recEntry.AddNew
                    recEntry!test1 = Me.test1
                    recEntry!test2 = Me.test2
                    recEntry!test3 = Me.test3
                    recEntry!test4 = strCount
                    recEntry!test5 = "automated test entry"
                    recEntry.Update
                    strCount = strCount + 1
                Loop
                recEntry.Close
                Set recEntry = Nothing
            End If
    Else    ' If there is not entry in the range object then carry on as normal.
        MsgBox "there is no range to worry about!"
    End If
    DoCmd.GoToRecord , , acNext
 
Exit_Command10_Click:
    Exit Sub
 
Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
 
End Sub

[PianoMan64]

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….

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim db As Database
Dim recEntry As DAO.Recordset
Dim strCount As String
Dim intAnswer As Integer
 
Set db = CurrentDb
strCount = Me.test1
 
' Check to see if there is an entry in the Range object on the form.
If Len(Trim(Nz(Me!test2))) > 0 Then ' If there is an entry then set up for Range update
MsgBox "Yes there is a range for this entry!"
If Me!test2 <= Me!test1 Then ' Check to see if the value in the Range object is greater than the starting Version object.
MsgBox "The Range value must be greater than the Version object. Please reassess your values and try again. "
Exit Sub
Else
Set recEntry = db.OpenRecordset("Loop", dbOpenDynaset)
intAnswer = MsgBox("Are you sure you want to create " & Me.test2 - Me.test1 & " new version entries within the database?", vbQuestion + vbYesNo, "Want to Proceed")
If intAnswer = vbNo Then
MsgBox "No changes have been made to the database. Please reevaluate you input and save again."
Exit Sub
End If
Do While strCount <= Me.test2 ' make copies of the current data
recEntry.AddNew
recEntry!test1 = Me.test1
recEntry!test2 = Me.test2
recEntry!test3 = Me.test3
recEntry!test4 = strCount
recEntry!test5 = "automated test entry"
recEntry.Update
strCount = strCount + 1
Loop
recEntry.Close
Set recEntry = Nothing
End If
Else ' If there is not entry in the range object then carry on as normal.
MsgBox "there is no range to worry about!"
End If
DoCmd.GoToRecord , , acNext
 
Exit_Command10_Click:
Exit Sub
 
Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click
 
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.