?
Solved

Record Cloning complete procedure

Posted on 2006-06-07
116
Medium Priority
?
670 Views
Last Modified: 2010-05-18
I reviewed many of the questions/solutions about record cloning; however, i get lost because the answers are fragmented with extra questions within the main question.  Is there a SUMMARY of the Record cloning code and which "ON XYZ" line to place it ?

I would like to be on a Main Form record which uses FormulaID as a primary key.  this field is input and not autonumber unless you tell me to make it autonumber for the clone.

there is a subform which shows RAWID records; typically about 10 subform raw material records showing the amount of pounds per rawid.  Pretty much a bill of materials form.

FormulaID and RawID are text fields.

So how can i take an existing record, hit a command button, and duplicate the entire record except the need to either input a NEW FormulaID or have FormulaID as an autonumber.  I prefer inputting a new formulaID but i can go either way.

thanks alot.
0
Comment
Question by:glen9
  • 57
  • 56
113 Comments
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 16853501
I hope I can help....

Main Form Records            Sub Form Records
FormulaID ------------------> FormulaID
Data1                                 Data1
Data2                                 Data2
Data3                                 Data3 etc...

For most operations where you have a primary index number such as order number, invoice number or anything similar...these numbers ARE created based on a seed.

A seed is a numeric value based on a starting number. Each time a new order, or invoice is created, the seed is incremented by one and the new value goes on the new order. That way you don't run into duplicate invoice numbers.

Cloning or duplicating would be no different, when you hit your button, it will create a recordset of your main form data AND your sub form data. It will create brand new entries in the tables underneath your form with the NEW order number.

this new record would then be called to be displayed on your form for editing and submission.

the code for this varies...there are lots of ways of copying a set of records and depending on the structure of your data, can be very complex.....but all use the same principal as I've described.

The code that duplicates the order can be placed in a module of it's own and passed the variables needed to complete it, or can be used directly in the On_Click event of your button. best practice says that you should create a function in a module, vs the button simply for re-use.

Tell us what the underlying table and field names are, and we can help you better.
J
0
 

Author Comment

by:glen9
ID: 16853744
Thanks for the comforting approach.
I will give you just a few fields and after I get your gist, i will do the rest later.

On the Mainform called frmRndFormulaMain:
=============================
Name:  txtFormulaID              Control Source:FormulaID  (text Input field)
Name: txtRNDformulaName     Control Source: FormulaName (text Input field)
Name: txtDescription               Control Source: Description (text Input field)

On the Subform called sfrmRNDFormulaDetail
=============================
Name: RawID               Control Source:  RawID
Name: UOMusage         Control Source:  UOMusage
Name:  UsageFactor      Control source:  UsageFactor
Name: UnitRawFrtAct    Control Source: UnitRawFrtAct

I have the subform on a TAB called Formula.

I hope this helps.  Thanks alot.
0
 

Author Comment

by:glen9
ID: 16853831
I forgot to add the following that you asked for:

the frmRndMain uses QryRndMain which uses tblRndFormulaMain.

the sfrmRndFormulaDetail uses QryRndFormulaDetail which uses tblRndFormulaDetail, tblRawItem and tblFreight.  The tblRndformulaDetail contains just 3 fields:
FormulaID (text), RawID (text) and UOMusage (text).

The earlier mentioned UsageFactor is a calculated subform field.  The UnitRawFrtAct comes from tblRawItem.

thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:jefftwilley
ID: 16854119
This is a good start, and before we start coding, let's organize what needs to be done.

For starters, let's define what the Header(or primary) data is, and what out of it needs to be stored as part of a new record. this needs to be done so that we can accurately duplicate the base data for the Main Form part of this.

So....Main form
the frmRndMain uses QryRndMain
which uses tblRndFormulaMain
which looks like this
Name:  txtFormulaID         Control Source: FormulaID  (text Input field)
Name: txtRNDformulaName     Control Source: FormulaName (text Input field)
Name: txtDescription        Control Source: Description (text Input field)

Subform is linked this way - Where FormulaID = RawID


sfrmRndFormulaDetail uses QryRndFormulaDetail which uses
tblRawItem
tblFreight
tblRndFormulaDetail,
which contains FormulaID (text), RawID (text) and UOMusage (text).

Name: RawID            Control Source:  RawID
Name: UOMusage         Control Source:  UOMusage
Name: UsageFactor      Control source:  UsageFactor
Name: UnitRawFrtAct    Control Source:  UnitRawFrtAct

so our first recordset will be for the main form Source...because we'll have to write to it

set rsMain = select * from tblRndFormulaMain
set rsMainClone = me.frmRndMain.RecordsetClone

Now the recordset for the subform

Set rsSub = Select * from tblRndFormulaDetail
set rsSubClone = Subform.RecordsetClone
NOTE - If you have related data that needs to go into the other tables in the query, we have to create a recordset for each of those

Now we can insert the current record for each recordset into the tables with a new ID.
 
Here's where your main problem is. Because you don't use the seed method, how are you going to assign the next number?

Let me know what you think so far.
J
0
 

Author Comment

by:glen9
ID: 16854698
So far i am following you.  well, maybe 80% but you are patiently explaining well.
you are correct that i do not need a second rsSub because i store the UnitRawFrtAct  in tblRawitem; it is the Unit raw material cost for a RawID.

by the way, if i mistakenly did not add txt as a preface to a Name row, yell and i will fix my file.

I think you may have the following statement wrong:  Subform is linked this way - Where FormulaID = RawID

i have in my subform:
Name:  sfrmRNDFormulaDetail
source object:  sfrmRNDFormulaDetail
link child fields:  FormulaID
link master fields:  FormulaID

thanks
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16854782
That makes it better....so there IS a formulaID in the Sub recordset.

Let's talk about the FormulaID. You say it's a text field and you enter it manually. Where exactly do you do that? If you want to create a new record on your Main form, do you key this in manually? Is there a rhyme or reason you follow so that you don't end up with all kinds of values or duplicates? If there is a method to the way you do it....do you see a way to increment it? I'm asking YOU this because it's your database....and it's your decision to make. Are there a lot of records already that would have to be changed if you decided to make that a numeric field?
J
0
 

Author Comment

by:glen9
ID: 16854928
good quesitons.  yes.  formulaID is in both the main and subform via their respective tables tblRndFormulaMain and tblRndformulaDetail.

when on frmRndMain, i enter formulaID in a field which has Name: txtFormulaID and a Control source: FormulaID.  It is a primary key field in tblRndFormulaMain.

I originally wanted to use autonumber for formulaID for tblRndFormulaMain.  However the Research & Development boss wants to vary the formulaID when making samples.  Eg Pecan489 and Pecan489B and Pecan489C.  At some point, he will release 1 of these 3 examples to Production but wants to keep the R&D processes not used remaining in the table.  Hence, he adds a letter to the end of the usual formulaID.

(fyi: If he picked Pecan489C, then that code is the only one ever shown in the Production depts table. I have a button that transfers the record to a Production table.)


Regarding the subform and the related table.  The formulaID and RawID fields are combo primary keys in tblRndFormulaDetail.
thanks
0
 

Author Comment

by:glen9
ID: 16854981
more clarification.  Pecan489, pecan489B and pecan489C would all remain in tblRndformulaMain and tblRndformulaDetail for eternity, so to speak.  But the r&d boss would go to Pcan489C record and hit a button t0 append to the two Production tables.  i keep the R&D people's work divorced from the Production people's need.

thanks
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16855043
Interesting....So the decision to make this a numeric field is kind of out of your hands.
So give me an idea, if I were to open the tblRndFormulaMain, what values I would see in the FormulaID field...
these?
Pecan489
Pecan489B
Pecan489C

Is "Pecan" a constant? or does that also vary? Do the numbers increment? Like "Pecan490" "Pecan491"?

Without figuring out how to increment this field, because it's your primary index AND your link to your subform data, we might as well quit here. Automation can only go so far. Logic has to come into play when assigning the new ID. I guess if we had to, because you've yet to release into production, come up with our own "Seed" and that would be the "Production" method?
We can always allow this for example....Make the TEXT part of the ID variable, but make the Numeric part incremental. Then concatenate the two to make the new ID for the Duplicated Record.
thoughts?
J
0
 

Author Comment

by:glen9
ID: 16855186
i think i follow you.  you are saying that you could take the BASE or initial form's FormulaID (input value) eg Pecan489 and when making a duplicate clone, you could take the Base formulaID Pecan489 and make it Pecan489-2.  is this what you mean?  I could go for that.

Now the next formula input might be Maple123 and maybe we never make a second formulazation.

Now the boss has formulaID's which are eight digit Numbers only and some which are combined text and number.  eg  11020604 is for Orange Flavor  and CASP300 is for Green Tea.

thanks
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16855499
We probably don't want to include the "-" but yes, you get the idea. We need to take into account all of the "ID's". You always have the option here of creating a pivot table of sorts, that would store things like Pecan489 and CASP300 and 11020604, and give them an alias like
1000 = Pecan489 - Base Number
2000 = CASP300 - Base Number
3000 = 11020604 - Base Number.

Then when you create a new order, you start with 1000 and increment to 1001....or 1000 + 001 (gives you more numbers)
each time you create a pecan order, you grab the last incremented number. and replace it with the next. So the value for Pecan489 would remain the same, but the ID would be 10002, or 10003. Do you see where I'm going with it?
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16855517
Let me think on this some more...the more I mull it over, the more I think you need a new field that's just for OrderNum.
0
 

Author Comment

by:glen9
ID: 16855991
One thing.  the R&D lab might create say 3 different formula detail sets.  in other words Pecan1 might use milk as a rawID but Pecan2 might use Whey instead of Milk and maybe a 3rd formulazation uses Powdered Milk.

After customer approval, let's pretend the cust picks the 2nd formulaization of using Whey as a raw ingredient.  But R&D wants to keep formulaizations #1 and #3 hanging around in the file so as to see test results just in case some year, the cust goes away from Whey and asks for a Milk base formulaization eg Pecan1.

but i see your point of using a new autofield to handle the records best.
but can we use a better name than OrderID.  It is more like an RndID.

G
0
 

Author Comment

by:glen9
ID: 16856078
FYI.  when building the first formula Pecan1, i may use RawID's as follows (simplied codes  for our purpose her):
RawID     UomUsage
101              30             Salt
188              24             Sugar
291              46              Milk

So i want to clone Pecan1 and make a Pecan2 recordset initially with the same 3 RawID's above.
When i am on Pecan2, i will manually change rawID 291 into rawID 341 Whey and the uom usages too.  So Pecan 2 now has:

101              30             Salt
188              36             Sugar   (notice the usage change)
341              34             Whey

Then i might use my combo box to search and call up Pecan1 again.  and then do a clone thing and make Pecan3 with the same Pecan1 rawID's and UomUsage's.  And then maybe change Milk to Powder Milk.

RawID     UomUsage
101              30             Salt
188              24             Sugar
409              46              Powdered Milk

thanks
0
 

Author Comment

by:glen9
ID: 16856447
You have me thinking about something.  What if i used your new field as FormulaID with autonumber.  and then change my current FormulaID input field into RndID ?  Production only cares about the final formulaID which could become the autonumbered field.

On the frmRndFormulaMain main form, i could have two combo boxes.  one to search for a formulaId (the new autonumber deal) and a second combo box which looks up the RNDID's like pecan1 or pecan 2 or pecan 3.

Here is why i think of this now.   For some other readers use.  not me.   this application theory could be used in a different Scenario project.  EG the main form could use a unique autonumbered formulaID but use the RndID for a scenario.  For example.  the formulaId could be automatic and therefore unique.  the RndId field could be Jones1 with a set of assumptions to buy a company.  Then a cloned version #2 could use RndID Jones2 with a different set of assumptions.

Therefore, i think you are onto something.  it sounds like i need FormulaID as a primary key to be an autonumbered field on the main form.  I need an RndID on the main form but it would not be a primary key.  

the subform would continue to have a link child fields thing using the autonumbered FormulaID.

I think this is what you are also saying.
G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856455
It sounds like you're thinking like Spock now....
and here's what you're after. The ID gets added to the main table as a number field. it's linked to the subform which is also a numeric field. The way a new number is introduced into the main table is when you go to add a record or duplicate one.  

ID               FormulaID      FormulaName      Formula Description                    ID      FormulaID   RawID     UomUsage
00001         Pecan1            PecanSurprise     Pecan with milk and sugar ---->  00001  Pecan1        101              30             Salt
                                                                                                                 00001  Pecan1        188              24             Sugar
                                                                                                                 00001  Pecan1        291              46              Milk

So i want to clone Pecan1 and make a Pecan2 recordset initially with the same 3 RawID's above.
It's here that I can decide if I want the change or not, and cancel to delete this record, or modify it and save it.

When i am on Pecan2, i will manually change rawID 291 into rawID 341 Whey and the uom usages too.  So Pecan 2 now has:

ID               FormulaID      FormulaName      Formula Description                    ID      FormulaID   RawID     UomUsage
00002         Pecan1            PecanSurprise     Pecan with milk and sugar ---->  00002  Pecan1        101              30             Salt
                                                                                                                 00002  Pecan1        188              24             Sugar
                                                                                                                 00002  Pecan1        291              46              Milk

When i am on Pecan2, i will manually change rawID 291 into rawID 341 Whey and the uom usages too.  So Pecan 2 now has:

ID               FormulaID      FormulaName      Formula Description                    ID      FormulaID   RawID     UomUsage
00002         Pecan2            PecanSurprise     Pecan with milk and sugar ---->  00002  Pecan2        101              30             Salt
                                                                                                                 00002  Pecan2        188              36             Sugar
                                                                                                                 00002  Pecan2        341              34             Whey
Then save the record.


0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856505
So here's what you need to do....in order to make all this work right.
you need to add a field to these tables. Make it numeric, call it whatever you want.

I'm going to show you how to create a seed based numbering function that will create the ID number for you when you Add a new record, or Duplicate one.

You're going to need to make a table.
All you really need in it is one numeric field.format it as long integer.
now you need to decide, based on how many records you think you might have over say a 5 year period...and come up with the first number that will go in there. I suggest starting with at least 000001. That leaves you a lot of records to go before you have to start at 0 again. The records that you already have in the table we'll have to populate by hand. once we do that, then the number right after the last record we do manually will be the next seed. so if we have 50 records, the next seed will be ?????
J

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856528
"On the frmRndFormulaMain main form, i could have two combo boxes.  one to search for a formulaId (the new autonumber deal) and a second combo box which looks up the RNDID's like pecan1 or pecan 2 or pecan 3."

You can do this easy enough once we're done with just one combo box....your user doesn't have to even see our autonumber. We'll make it so that all he sees is Pecans.
J
0
 

Author Comment

by:glen9
ID: 16856627
Let me see if i follow.

Wouldn't I to to my tblRndFormulaMain and make FormulaID into an autonumber primary key?
and then add a new field RndID as a number input field NOT as a primary key?

and then add the field RndID into the QryRndMain and frmRndMain main form?

Luckily there are zero existing RndID's to deal with.  The old one's will stay in excel and we will just go forward with new codes.  If you agree that RndID can be an input field, then the code can be anything they like.

As for FormulaID, i would need to ask what value to start with.  we mostly use 8 digit numbers and are already in the 9xxxxxxx range.  and then there is 10% codes that use text and number.  So i wonder if i should use a 9 digit autonumber field starting with 1xxxxxxxx.

am i making sense here??
thanks
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856656
You are....but I don't think I am. All I want you to do is add a field into your Main Table called MainID and another in your Sub Table called SubID. Make them numeric. We'll leave all the other fields and formats in your tables alone.
J
0
 

Author Comment

by:glen9
ID: 16856700
So I should go to tblRndFormulaMain and add a field called MainID as numeric.  i thought it would be autonumber but i will make it plain numeric and i think you said long integer.

and then i will go to tblRndformulaDetail and add a field SubID also as numeric and long integer.

right?

thanks  
0
 

Author Comment

by:glen9
ID: 16856718
shall i take off the primary key on formulaID in tblRndformulaMain and also take off 2 primary keys FormulaID and RawID in tblRndFormulaDetail  ?

if so, that leaves me with no primary keys at this juncture.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856785
Let me explain why autonumeric wouldn't work....

when you open up a table with an autonumber...go to new record (last line) and start typing in values...the autonumber doesn't populate until you save the record. That's ok if you are just doing data entry, but that's not what we're doing. We're creating a recordset in memory and inserting this into the tables THEN saving. Doing it that way and your subform data won't get the proper ID unless we close our Main recordset and open it up to the new record. Does that make sense?  by using the seed method, you are grabbing the next number in a sequence anyway, just like autonumber, but it's a value we can use to add to the recordset BEFORE we save. That way the data in both tables stay related.

Once you've added the numeric field, you need to go down through the main table and using the numbering scheme we discussed above, start with 000001 and number your lines.

Once that's done, then you can re-index using the new number field AND your FormulaID if you want....or you can just use the number field. That way you can guarantee no duplicates.
J

PS are you having fun yet? :o)
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16856812
Once you renumber the data in the Sub table, then the index in your Sub table can consist of three fields. the SubID, FormulaID and RawID.
0
 

Author Comment

by:glen9
ID: 16857280
yes.  i did all you said so far.  I see what you mean about the Saving sequence.  i had no idea about the timing.

what do i do next?  yes this is exciting.  i like learning something new with a purpose.
G
0
 

Author Comment

by:glen9
ID: 16857436
Here is the contents of my two tables.  I think the way that you anticipate.

-----------------------------------------
tblRndFormulaMain      
number             Text
Long Integer      

MainID              FormulaID
1             Pecan1
2             Pecan2
---------------------------------------------



tblRndFormulaDetail                  
Number      Text      Text      Number
Long Integer            Single
SubID      FormulaID      RawID      UOMusage
1      Pecan1      11020604      30.00
1      Pecan1      11031200      24.00
1      Pecan1      11122400      46.00
                  
2      Pecan2      11020604      30.00
2      Pecan2      11031200      36.00
3      Pecan2      11195000      34.00
===========================

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16858103
You're doing great!
The last entry up there has a subID of 3 - it should be 2. We don't have a Main 3 yet.

Make a table, call it Seed
One field is all you need, call it SeedNumber
Save it.
Open it in data view and put 3 in the SeedNumber field.
That will be the next seed according to your data.

You'll need this now to lookup the seed number. We'll use it when we add new or Duplicate

Dim lngSeed as Long
lngSeed = dlookup("SeedNumber", "Seed")

Here's a piece of code you're going to use to get the next seed....it's a function so you can paste it into any module and we'll call it from the form when we add a new record or duplicate an existing one and we need to increment the seed by 1 for the next time we need it.

Function UpdateSeed()
 
    'On Error GoTo UpdateSeed_Err
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Seeds")
    rs.FindFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.Close
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.Close
    Exit Function
   
UpdateSeed_Err:
    rs.Close
    UpdateSeed = 0
    Exit Function
 
End Function

Let me know how you're doing. Tomorrow....we code.
J
0
 

Author Comment

by:glen9
ID: 16858135
oops.  i fixed the 3 in tblrndformulaDetail to 2.

i made the table and called it tblSeedNumber.  yell if you want a better name.  I made the field Seednumber ask you said.  it is a number field with field size Single.  yes?

i did not copy paste any code yet because i don't know where to put it.  I assume that you mean to worry about this part tomorrow.

thanks for being so patient.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16858144
You're going to have to modify your form's underlying queries now to include the new ID fields. Once you do this then...

Go out to your forms. Open them in design view.

You should see a field list for each form based on the underlying table/query. The new ID fields should be there now.
You want to drag the new fields to their respective forms. You can delete the label parts, you only need the textboxes. On their properties, make them invisible.  Now you're going to relate the forms master/child using these fields. Let me know if you don't know how to do that.

Now your forms are ready except for the buttons we're going to make.

We're cooking with gas now!
J

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16858165
Name the table Seed. That's what the code refers to. Name the field SeedNumber < caps and stuff

IF you're looking at your database now, press Alt/F11 at the same time. It will open the VBA window. This window is your new friend. Up at the top, select Insert>Module

A blank screen will open up. Paste the function I gave you in there. On the menu, select debug. IT should work just fine. If not, we will probably need to add references...I'll help you.

if it passes debug, then save. Name this module MiscFunctions. We'll use this module again to store other functions in.

J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16858181
The number field in the seed table needs to be a long integer...not single
J
0
 

Author Comment

by:glen9
ID: 16860713
Here is what i put in my subform sfrmRndFormulaDetail:
Name:  sfrmRNDFormulaDetail
Source object:  sfrmRNDFormulaDetail
Link Child fields:  SubID
Link Master fields:  MainID
============================

Seed table name:  i made the table tblSeed.  is this okay or not?

tblSeed field:  SeedNumber as number single.
================================

I made the module MiscFunctions.  I hit Debug, Step Into.  was this correct?  it gave me an error.
compile error:   db As Database.  also, i see a name SEEDS plural?

Option Compare Database

Function UpdateSeed()
 
    'On Error GoTo UpdateSeed_Err
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Seeds")
    rs.FindFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.CLOSE
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.CLOSE
    Exit Function
   
UpdateSeed_Err:
    rs.CLOSE
    UpdateSeed = 0
    Exit Function
 
End Function
==============================================

Looks like we are close.  please correct me on anything done wrong.
G


0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16861486
You're getting there....We'll work on the compile error in a second.
You DO need to change the "Seeds" to tblSeed in the UpdateSeed function...otherwise it won't find the table. Do that then try to debug again.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16861497
When you're in your VBA window, go up to the Tools>References. It should open a window up and show you a list of things. Tell me what you have checked there.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16861528
You also need to change the SeeNumber from Single to Long Integer.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16862671
Here's what I've been working on over here....
go throough it and see if you understand what it's doing....check all the table names, field names and such.

Function DuplicateOrder(strID As Long) As Long

    'This function duplicates an existing order

    On Error GoTo ExitDuplicate

    Dim rsMain As Recordset
    Dim rsSub As Recordset
    Dim rsMainClone As Recordset
    Dim rsSubClone As Recordset
    Dim NewID As Long
    Dim SubsToCopy As Integer
    Dim ws As Workspace

    NewID = UpdateSeed

    Set ws = DBEngine.Workspaces(0)

    Set rsMain = CurrentDb.OpenRecordset("Select * From tblRndFormulaMain Where MainID=" & strID)
    Set rsSub = CurrentDb.OpenRecordset("Select * From tblRndFormulaDetail Where SubID=" & strID)
    ws.BeginTrans

    rsMain.MoveLast
    rsMain.MoveFirst
    rsSub.MoveLast
    rsSub.MoveFirst
    If rsMain.RecordCount < 1 Or rsSub.RecordCount < 1 Then
        DuplicateOrder = 0
        Exit Function
    End If

    Set rsMainClone = rsMain.Clone
    Set rsSubClone = rsSub.Clone

    rsMainClone.MoveFirst
    rsSubClone.MoveFirst

    'Copy Main Data from the Clone to the Recordset
    Dim I As Integer
    rsMain.AddNew
        For I = 1 To rsMainClone.Fields.count - 1
            Select Case rsMainClone.Fields(I).Name
                Case "MainID"
                    rsMain.Fields("MainID") = NewID
                Case Else
                    rsMain.Fields(I) = rsMainClone.Fields(I)
            End Select
        Next
    rsMain.Update

    'Copy Sub Data from the Clone to the Recordset
    Dim J As Integer
    rsSub.AddNew
        For J = 1 To rsSubClone.Fields.count - 1
            Select Case rsSubClone.Fields(I).Name
                Case "SubID"
                    rsSub.Fields("MainID") = NewID
                Case Else
                    rsSub.Fields(I) = rsSubClone.Fields(I)
            End Select
        Next
    rsSubClone.Update
 
    ws.CommitTrans
    rsSubClone.Close
    rsMainClone.Close
    rsMain.Close
    rsSub.Close
    Close
    DuplicateOrder = NewID
    Exit Function

ExitDuplicate:
    ws.Rollback
    DuplicateOrder = 0

End Function
Function UpdateSeed() As String
 ' This function returns the seed value in the seed table then increments it for next time
    'On Error GoTo UpdateSeed_Err
    Dim db As DataBase, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_Seed")
    rs.MoveFirst
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot create seed.  Please contact administrator.", vbCritical, "Warning"
        rs.Close
        Exit Function
    Else
    UpdateSeed = rs!SeedNumber
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.Close
    Exit Function
   
UpdateSeed_Err:
    rs.Close
    UpdateSeed = 0
    Exit Function
 
End Function
0
 

Author Comment

by:glen9
ID: 16862905
I made the changes in tblSeed.

I went to the Module section and hit design on MiscFunctions.  then i went to the Function UpdateSeed() section.  Here is what i now have.  When i hit Debug, Step Into, i get the same error message:
Compile error - user-defined type not found.  highlighted in blue is:
db As Database which is the first line in the function.

==================================
Function UpdateSeed()
 
    'On Error GoTo UpdateSeed_Err
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSeed")
    rs.FindFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.CLOSE
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.CLOSE
    Exit Function
   
UpdateSeed_Err:
    rs.CLOSE
    UpdateSeed = 0
    Exit Function
 
End Function
====================


G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16863086
When you're in your VBA window, go up to the Tools>References. It should open a window up and show you a list of things. Tell me what you have checked there.
J
0
 

Author Comment

by:glen9
ID: 16864530
I see that i have to go into Design first with MiscFunctions.  Then I hit Tools, reference.

the 4 items checkmarked are:
visual basic for applications
microsoft access 9.0 object library
ole automation
microsoft activex data objects 2.1 library


G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16864739
Try this

==================================
Function UpdateSeed()
 
    'On Error GoTo UpdateSeed_Err
    Dim rs As Recordset

    Set rs = CurrentDB.OpenRecordset("Select * from tblSeed")
    rs.FindFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.CLOSE
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.CLOSE
    Exit Function
   
UpdateSeed_Err:
    rs.CLOSE
    UpdateSeed = 0
    Exit Function
 
End Function
====================
0
 

Author Comment

by:glen9
ID: 16864784
i replaced the module Miscfunctions contents with the data above.  in the design mode, i did Debug, Step Into.  I get a new error:

Compile Error:  method or data member not found.  

it is blued at:  Findfirst
which is the 3rd row.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16864831
Try replacing Findfirst to MoveFirst.
Also...there should only EVER be one record in that seed table. the only thing that updates it is the UpdateSeed function.
J
0
 

Author Comment

by:glen9
ID: 16865247
I made the change.  I ran debug again.  it goes further down.  it stops at:

compile error:  method or data member not found

LockEdits


also, i only have one record in tblSeed which contains the number 3.

G

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16865596
comment that line and debug again....that control is for databases that are updating the seed from multiple functions....(my database that I support here!!)
J
0
 

Author Comment

by:glen9
ID: 16865637
I remmed the Lockedits line and ran debug.  it barked at rs.Edit.  so i remmed that too.  then ran debug.  i did not get anymore errors.  i do not see anything change so i do not know if it worked.  but i got no error message.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16865889
well....we kinda need the edit part. That's what allows us to change the seed. I'm going to take off here in a few minutes. I'll look at this some more in the morning. I recommend you add the
Microsoft DAO library (mine is 3.6) to your references. You'll need to add the edit part back (uncomment). don't worry, we'll get this.
J
0
 

Author Comment

by:glen9
ID: 16865962
no problem.  no rush either.  thanks for everything so far.
G
0
 

Author Comment

by:glen9
ID: 16865966
I just added Microsoft DAO 3.6 object library.  that one?
G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16870075
I had a thought, go to the tblSeed table and open it in design view. Create an index on that column in there. Set the "Required" option to YES. Save it.

then paste this over what you have for the UpdateSeed function and debug it once more.

Function UpdateSeed()
 
    On Error GoTo UpdateSeed_Err
    Dim db As DataBase, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSeed")
    rs.MoveFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.Close
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.Close
    Exit Function
   
UpdateSeed_Err:
    rs.Close
    UpdateSeed = 0
    Exit Function
 
End Function

Should compile.
J
Once this is working, I think all that's left is to make the buttons for your form. Let me know how you're doing.
J
0
 

Author Comment

by:glen9
ID: 16870568
hi.  i made the changes.  i get a debug error at:
rs.LockEdits

by the way, the module is named MiscFunctions.  So you prefer UpdateSeed or maybe RndClone ?

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16871019
It doesn't really matter what the module name is....it's the functions and their names that get called from the forms.

Comment LockEdits out and try to debug once more. We're almost there. The Reference I had you check in your module should have allowed the LockEdits, so I'm going to have to look at that...but like I explained earlier, that's used when the table might be accessed from more than one function at a time, like a distributed system. We'll put that on the back burner for now.
Let me know if it debugs ok now.
J

0
 

Author Comment

by:glen9
ID: 16871589
i renamed module MiscFunctions into Rndclone.

I remmed out the lockEdits.  ran the debug.  it stops at rs.Edit

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16871707
Is your seed table indexed now?
0
 

Author Comment

by:glen9
ID: 16871729
yes
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16871850
visual basic for applications
microsoft access 9.0 object library
ole automation
microsoft activex data objects 2.1 library
MS DAO 3.6 Obj Lib

Besides those, add these for me to your references

Utility
MS Common Dialog control 6.0
MS Forms 2.0 Obj Lib
MS Windows Common Controls
Active Setup Control
MS ActiveX Data Objects

then try to debug again.
I'm still curious about why it stops where it does.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16872072
I think it's the ActiveX data object you need...I removed all my references then added them all back one by one, and that one allowed the rs method. Let me know.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16872173
ok....I had to reference the DAO that I had you load as a reference.....and I created a new little database to test the references that you had to make this thing compile. Here's what I have

Function UpdateSeed()
 
    'On Error GoTo UpdateSeed_Err
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From tbl_Seed")

    'Set rs = db.OpenRecordset("tblSeed")
    rs.MoveFirst
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.Close
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.Close
    Exit Function
   
UpdateSeed_Err:
    rs.Close
    UpdateSeed = 0
    Exit Function
 
End Function
0
 

Author Comment

by:glen9
ID: 16874340
hi.  sorry for the delay.  Yes, i added the ActiveX object 2.8 library.  i use access 2000.  is that what you wanted?

i added your new RndClone module above.  it ran fine.  i received no error.  nothing seemed to happen yet but i guess that is okay?

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16874681
IF you ran it when your form wasn't open it wouldnt have done anything. What you need to do now is add a couple of buttons to your form. One for duplicate and one for add new....we'll probably make one for cancel too just in case. With these buttons you're going to code them to use the functions we created. The Duplicate button will go first, because that's what your original question was about.

Create a button. Go to properties and we're going to put a call to our duplicate function on the On_Click event.

DuplicateOrder(strID As Long) <-------
This is the function and the strID is what we're passing to it. strID in this case is the MainID number. so the code will look something like this.

private sub CommandDup_On_Click()
strMainID = me.MainID.value
DuplicateOrder(strMainID)
end sub

Give that a try and we'll go from there.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16874690
Also I noticed you're referring to the updateseed function as a module. That isn't correct. A module is basically a folder that contains functions. Updateseed and duplicateorder are both functions inside a module. If you already understand that then we're all good.

So you should have a module named???
and two functions named????
J
0
 

Author Comment

by:glen9
ID: 16874770
I have a module named RndClone.  Inside is Function UpdateSeed() and all the lines you noted last.

when you say 2 functions, i guess i do not understand.  i see the UpdateSeed function.  what is the other one?

i made a button on frmRndMain;  on the On Click, I have:
Private Sub CommandDup_Click()
strMainID = Me.MainID.Value
DuplicateOrder (strMainID)
End Sub


Is the button the second function as you refer to?
G
0
 

Author Comment

by:glen9
ID: 16874778
i see the other function now.  it was in the far earlier messages.  i now have it in Rndclone module.
i am glad you said that Rndclone is a "folder".  i did not know it.  so now i have 2 functions in RndClone.
G
0
 

Author Comment

by:glen9
ID: 16874789
i ran debug on rndclone module.  it stops at ws.Rollback


ExitDuplicate:
    ws.Rollback
    DuplicateOrder = 0

g
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16874875
this is the other one

Function DuplicateOrder(strID As Long) As Long

    'This function duplicates an existing order

    On Error GoTo ExitDuplicate

    Dim rsMain As Recordset
    Dim rsSub As Recordset
    Dim rsMainClone As Recordset
    Dim rsSubClone As Recordset
    Dim NewID As Long
    Dim SubsToCopy As Integer
    Dim ws As Workspace

    NewID = UpdateSeed

    Set ws = DBEngine.Workspaces(0)

    Set rsMain = CurrentDb.OpenRecordset("Select * From tblRndFormulaMain Where MainID=" & strID)
    Set rsSub = CurrentDb.OpenRecordset("Select * From tblRndFormulaDetail Where SubID=" & strID)
    ws.BeginTrans

    rsMain.MoveLast
    rsMain.MoveFirst
    rsSub.MoveLast
    rsSub.MoveFirst
    If rsMain.RecordCount < 1 Or rsSub.RecordCount < 1 Then
        DuplicateOrder = 0
        Exit Function
    End If

    Set rsMainClone = rsMain.Clone
    Set rsSubClone = rsSub.Clone

    rsMainClone.MoveFirst
    rsSubClone.MoveFirst

    'Copy Main Data from the Clone to the Recordset
    Dim I As Integer
    rsMain.AddNew
        For I = 1 To rsMainClone.Fields.count - 1
            Select Case rsMainClone.Fields(I).Name
                Case "MainID"
                    rsMain.Fields("MainID") = NewID
                Case Else
                    rsMain.Fields(I) = rsMainClone.Fields(I)
            End Select
        Next
    rsMain.Update

    'Copy Sub Data from the Clone to the Recordset
    Dim J As Integer
    rsSub.AddNew
        For J = 1 To rsSubClone.Fields.count - 1
            Select Case rsSubClone.Fields(I).Name
                Case "SubID"
                    rsSub.Fields("MainID") = NewID
                Case Else
                    rsSub.Fields(I) = rsSubClone.Fields(I)
            End Select
        Next
    rsSubClone.Update
 
    ws.CommitTrans
    rsSubClone.Close
    rsMainClone.Close
    rsMain.Close
    rsSub.Close
    Close
    DuplicateOrder = NewID
    Exit Function

ExitDuplicate:
    ws.Rollback
    DuplicateOrder = 0

End Function



you have two functions. The button calls this one....this one calls update seed.

You can comment out the we.rollback for now. It's part of error handling that you won't use at this time.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16874903
I think once all this is debugged...saved...and the button on the form with

Private Sub CommandDup_Click()   '<----CommandDup is just what I called the button.
strMainID = Me.MainID.Value
DuplicateOrder (strMainID)
End Sub

When you go into design mode on your form, and click on your new button and open properties...what is the name of your button? IF you look at the events tab on the properties window, you'll see the on_click event...if you click on the "..." it will open a window that lets you select "Code". If you select that it will take you to the right place to put this

strMainID = Me.MainID.Value
DuplicateOrder (strMainID)
0
 

Author Comment

by:glen9
ID: 16874911
Okay.  i made the changes and rem.  the debug runs fine.  there are no results but there are no error messages.

i hit the button and the same.  nothing seems to happen but at least no errors.
G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16874920
running the debugger only checks syntax and verbage...it means there are no formatting errors or missing objects.

The button might not be called the same as the code I put in...you have to use the button's name.

here's a place where you can put this database as is...so I can have a look

http://www.ee-stuff.com/
0
 

Author Comment

by:glen9
ID: 16874997
Jeff - i went to the http mentioned.  it is the Experts Exchange Community News screen.  where do i upload my file?  i reduced the size for you and then zipped it up to about 3.5 megs.  okay?

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16875014
yes.....that's fine...if you don't see a link to upload a file...try this

www.savefile.com

it will let you upload for free

J
0
 

Author Comment

by:glen9
ID: 16875074
Jeff - i looked everywhere on the EE site but finally sent the file on the Savefile.com.  first time i ever heard of that service.  I will gradually get used to the EE site.  I appreciate everything that you are doing for me.  When we are done, maybe you can explain how to close the topic and also how to donate more for this difficult topic.

do you need to need to know the Savefile code to find my file?

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16875208
When you put the upload on the savefile site...it should have given you a URL  ex: http:\\......something

Did you save that?
 paste it here if you did.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16875528
ok...I had some tweeking to do but here's the stuff you need to paste what's below over the old stuff.
Something you'll notice is the form disappears momentarily while the duplicate process runs. I had to do this because the form was holding the tables open and wouldn't let me update them with the new lines. You can create a pop-up form that says something informative that opens when the main form closes then closes when it reopens if you think it's necessary. Also, the FormulaID field is locked on your form. So Pecan1 stays in the new record and you can't change it to Pecan2 or whatever. It looks like we also need to work on your search box. It's pulling unique formulaID's and not looking at the MainID at all. Just something to think about. This works now. Take a look at the changes I made and if you have any questions please let me know.
-----------------------------------------------
Private Sub CommandDup_Click()
strMainID = Me.MainID.Value
DuplicateOrder (strMainID)
End Sub
-----------------------------------------------
Function UpdateSeed() As Long
 
    'On Error GoTo UpdateSeed_Err
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From tblSeed")

    'Set rs = db.OpenRecordset("tblSeed")
    rs.MoveFirst
    lnSeed = rs.Fields("SeedNumber").Value
    'Stop document number increment at 99,999.
    If rs!SeedNumber = 99999 Then
        MsgBox "Document seed has reached limit. Cannot update seed.  Please contact Administrator.", vbCritical, "Warning"
        rs.CLOSE
        Exit Function
    End If
    'Pessimistic record lock begins
        rs.LockEdits = True
        rs.Edit
        rs!SeedNumber = rs!SeedNumber + 1
        rs.Update
    'Record lock ends
    rs.CLOSE
    UpdateSeed = lnSeed
    Exit Function
   
UpdateSeed_Err:
    rs.CLOSE
    UpdateSeed = 0
    Exit Function
 
End Function



-------------------------------------------------------------------------------------------------------------------------------------
Function DuplicateOrder(strID As Long) As Long
DoCmd.CLOSE acForm, "frmRNDmain"
'This function duplicates an existing order
'The clone button on frmRndMain calls this function DuplicateOrder....this one calls update seed.


    On Error GoTo ExitDuplicate

    Dim rsMain As DAO.Recordset
    Dim rsSub As DAO.Recordset
    Dim rsMainClone As DAO.Recordset
    Dim rsSubClone As DAO.Recordset
    Dim NewID As Long
    Dim SubsToCopy As Integer
    Dim ws As Workspace

    NewID = UpdateSeed

    Set ws = DBEngine.Workspaces(0)

    Set rsMain = CurrentDb.OpenRecordset("Select * From tblRndFormulaMain Where (tblRndFormulaMain.MainID) = " & strID)
    Set rsSub = CurrentDb.OpenRecordset("Select * From tblRndFormulaDetail Where (tblRndFormulaDetail.SubID) = " & strID)
    ws.BeginTrans

    rsMain.MoveLast
    rsMain.MoveFirst
    rsSub.MoveLast
    rsSub.MoveFirst
    If rsMain.RecordCount < 1 Or rsSub.RecordCount < 1 Then
        DuplicateOrder = 0
        Exit Function
    End If

'Do the Main form data now
    Set rsMainClone = rsMain.Clone
    rsMainClone.MoveFirst
    Dim I As Integer
    rsMain.AddNew
        For I = 0 To rsMainClone.Fields.Count - 1
            Select Case rsMainClone.Fields(I).Name
                Case "MainID"
                    rsMain.Fields("MainID").Value = NewID
                Case Else
                    rsMain.Fields(I).Value = rsMainClone.Fields(I).Value
            End Select
        Next
    rsMain.Update
   
' Do the subform data now
    Set rsSubClone = rsSub.Clone
    rsSubClone.MoveFirst
    Dim S As Integer
    For S = 0 To rsSubClone.RecordCount - 1
        'Copy Sub Data from the Clone to the Recordset
        Dim J As Integer
        rsSub.AddNew
            For J = 0 To rsSubClone.Fields.Count - 1
                Select Case rsSubClone.Fields(J).Name
                    Case "SubID"
                        rsSub.Fields("SubID") = NewID
                    Case Else
                        rsSub.Fields(J) = rsSubClone.Fields(J)
                End Select
            Next
        rsSub.Update
    Next
 
    ws.CommitTrans
    rsSubClone.CLOSE
    rsMainClone.CLOSE
    rsMain.CLOSE
    rsSub.CLOSE
    Close
    DuplicateOrder = NewID
    Dim criteria As String
    criteria = "MainID = " & NewID

    DoCmd.OpenForm "frmRNdMain", , , criteria
    'DoCmd.OpenForm "frmRndMain", acNormal, , "me.MainID.value = " & NewID
    Exit Function

ExitDuplicate:
    'ws.Rollback
    DuplicateOrder = 0
MsgBox "error message is " & err.Description
End Function
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16875537
I also think we'll have to work on the delete button, because it delete's the Main data but not the Sub data. You should also create an ADD button, to put a new record in now it will have to grab a seed number. Just some things to think about. Overall I think the form and all looks great. Very creative and quite functional.
J
0
 

Author Comment

by:glen9
ID: 16876830
Wow! is all i can say.  I made the paste changes.  i ran debug and had no errors.  i did not hit the button on frmRndMain.  Should I yet?  i am afraid that i might create tblseed records that do not match yours.

Issues:
1 -I want to think a couple of hours about the idea of making my FormulaID search not to essentially use tblRndFormulaMain and maybe use tblFormulaMain.  I am only hesitant because i want to think through how the process works.  i want to call my boss.  EG i was orig thinking of keeping the Production tblFormulaMain file pristine and only touched by R&D dept for New items.  did you see my button on frmRndMain to the middle area right side called "Production Release".  i planned to improve the button, with the idea of appending the screen to tblFormulaMain and tblFormulaDetail when R&D signed off on a formula for authorized Production use.   The procedure is important for health reasons so as not to have a bad formula used by Production since this is foods stuff.  i will let you know in a few hrs how i feel.  i am leaning to your idea for this yellow lookup button (i use yellow borders for lookups and purple borders to mean doubleclick into another file.)

2 - maybe you can help me on the delete button.  i did not do much work in it yet.  i also did not do any relationships yet because our data is still being built.  This is a new database for us.  I am emailing 3 people in other states to build the formula detail and other stuff.  so i felt best to put on the relationships when we are done getting the struction and data done first.  i am probably 2 months away from that.

3 - Is my "New Sheet" button the same as your "ADD" button idea?  it is in the upper left corner.  I have not "prettied up" the frmRndMain yet because of the fluid situation with my 3 co workers who are always thinking of some new fields.    I will change the button back to ADD.  can you take a look at New Sheet button and see if that is what you would have done?

thanks for the nice comments.  Little by little, the file is coming around.

Let me know what to do next.

G



0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16877383
Feel free to push the button all you want to test it. ....just remember that if you want to "clean" your good data, delete all records with an id > 2 in both the main and sub...then go into the seed table and set it to 3 again...voila!! back where you were.

You really only need to make two mods to your delete key. It already deletes the record from Main, but it uses the Formula ID vs the ID field...so  just add a delete statement something like this:

Delete * from tblRndFormulaDetail Where (tblRndFormulaDetail.SubID) = " & strID
Delete * From tblRndFormulaMain Where (tblRndFormulaMain.MainID) = " & strID
Then just do what you're already doing with the refresh.

It's good practice to delete detail records before you delete Main simply because of the relationship.

For the combo box....try going into design mode on your form. Drop in a new Unbound combo box and step through the wizard. Make it select MainID, FormulaID and description(Three Columns) to help distinguish which FormulaID you want to see. Let it find records that match the Main ID (Bound Column) and let it display just the FormulaID and Description in the dropdown. This should get you what you want as far as navigation and selection.

The add button will take you to a new record in the Main table and make a call to the UpdateSeed function to grab the next Seed number. This number will be written into the MainID field and as you enter each new SubID record, you'll grab the MainID value and put it into the SubID field.

Congratulations, you've successfully learned how to duplicate records!!
J




0
 

Author Comment

by:glen9
ID: 16878176
Hi Jeff.  I just got back.  I mulled over all day something you said and i want to do it.  It occurs to me that if I add a text yes/no field called "Rnd Release" into tblFormulaMain, and allow Rnd people to code the new field.

Then on frmRndMain, i can make the formulaID source tblFormulaMain as you suggested.  Rnd would be allowed to see all yes/no "Rnd release" which is both things available for sale and things in testing.  Later, if i ever make a form for production, i can filter their work with just the "yes" "rnd release" product to sell.

that also means that i could put the formula detail into tblFormulaDetail.

therefore, as you already thought out, i could eliminate tblRndFormulaMain and tblFormulaDetail.
I think adding that new field "Rnd release" yes/no make things simpler.

therefore, i also need to change my Yellow bordered combo boxes to use the qry for tblFormulaMain and not use the Qry for the future deleted tblRndMain.

I can flow this all though in any module stuff and let you know how i made out.

Regarding the DELETE toilet bowel button:  i remmed out everything between the Private and the end sub.  i sandwiched your new above 2 lines;  they turn RED.  I wonder why?

I am also beginning to think i need some kind of protection that the Rnd person does not wipe out a formula or formula detail.  maybe that is a a question for a future post?

Can you tell me if you like the approach that i am about to do?  i thinmk it is what you preferred too?
i will wait for you answer before i go on.
thanks.  
G



0
 

Author Comment

by:glen9
ID: 16878209
Jeff - i sent a word file to you.

http://www.savefile.com/files/7174030

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16879012
Reading over what you wrote, and I have a few thoughts. It's always good to keep development and production separate. If you aren't facing some kind of space problems, then I would keep them separated, however. I see that you may want to duplicate production data, and make it RND until you're ready to release it to production once again. A flag could be used for this (your yes/no) field. As soon as a record is duplicated or any new records would automatically have this flag set. And the button on your form you've already created could simply set the flag so that it becomes production. IF you wanted to secure this, then when the "move to production" button is pressed, pop a small password screen to have the RND person release the formula. It's a good approach to managing the environments and I Assume your program will be smart enough to know who is logging on in order to put them into the right space..ie RND vs Production.

As far as the two lines I pasted for deleting the record, you need to create two SQL statements for them....  

dim strSQL as string
strSQL = "Delete * from table where id =" & strID
set warnings = false
DoCmd.runSql strSQL

You might want to set warnings false first as you don't want the pop-up message to appear every time telling you that you're about to delete X number of rows.

I'll look over your word doc when I get a chance. Enjoy!
J

0
 

Author Comment

by:glen9
ID: 16879700
Thanks for the encouraging remarks.  I was worried that you would be mad at me for changing horses in mid stream.  i always like the Pinto one anyway.

I think i have the file back in shape.  I went back to my old premise which you sensed immediately as stronger.  i have combined some steps for Rnd and production.

my main goal now was to avoid having formula details for both rnd and production.  I now made tblFormulaDetail the main focus along with tblFormulaMain.  I got rid of the 2 rnd tables.  i would have had thousand of duplicate formula detail records in the system that i first showed you.  so i am relieved now.  you spurred me on to reevaluate it.  I still have one input issue but maybe that will get solved too.

i sent the file again to savefile.  it is named Jeff2Ny2.zip
http://www.savefile.com/files/5081641

==========================
Because of some of our new fields like subid, i have a calc problem in the calculated field in QrySampleDetail.  It needs to look at both SubID and formulaID records which match up and then do the DSUM of UomUsage based on common subId and formulaID.  

below is my old code which is now obsolete and wrong.  the Dsum needs adjustment for the subid/formulaID.

UomUsagePercent: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblFormulaDetail]"),4)*100))

=========

my next problem is in sfrmSampleDetail  (formerly called sfrmRndFormulaDetail).

you can see the labe Ufactor and the field UsageFactor.  I had to make the field for now Input.
this will be a spirited topic on Monday with the boss.  i will have them retype the answer calculated in the field to the left which is UomUsagePercent.  they will not enjoy that.  it would be neat if their were a vba way to populate the field yet allow UsageFactor to remain an Input field.

you see, the UomUsage field in inputted by Rnd in say Ounces.  but for production needs, i need the usage percents which therefore can use any kind of unit of measure (uom).  so i have a dilemma.
the tblFormulaDetail requires the UsageFactor percents in order to do production and costing.  but R&D like to mimick a customer request in the UomUsage field with oz or grahams or widgets etc.
if you do not have time to consider this latter new request about how to move the usagePercent column values into the UsageFactor field, don't worry.  you will that both are expecting inputs per tblformulaDetail.

================

i need to go over your delete button comments again.

G


0
 

Author Comment

by:glen9
ID: 16880520
J:  i bet i have the revised delete button incorrect.  Here is what i interpreted form you.
Did i do right?  on top of main form frmSamplesMain.

Private Sub DeleteRecord_Click()
'Jeff Willey style.  delete sub records before the main records.
Dim strSQL As String
strSQL = "Delete * from table where id =" & strID
Set warnings = False
DoCmd.RunSQL strSQL
End sub


G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16880882
that's about right, you needed to identify the strID and the TABLE name though.

Private Sub DeleteRecord_Click()
'Jeff Willey style.  delete sub records before the main records.
Dim strSQL As String
dim strID as long
strID = me.MainID.value
strSQL = "Delete * from table where id =" & strID
Set warnings = False        
DoCmd.RunSQL strSQL
End sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16880889
You should probably close this question at this point. The original intent has been resolved and it's working right? IF you need to tweek some other things, you should open a new question...that way others can assist as well. I am enjoying helping, but I think others would enjoy helping you as well. Just a thought!
J
You can always e-mail me at the address on my profile. Just click on my name.
0
 

Author Comment

by:glen9
ID: 16880895
Sorry to have deviated from the question; but your structural discovery lead me back to my original design.  I got off tract because the boss once worked in an SAP environment that kept the R&D data divorced from the Production group.  the negative was that both groups could enter the formulanames anyway they felt.  and other data too.  that should be a no no.  So now, as you pointed put, I have restructured the frmSamplesMain to focus on the underlying tblFormulaMain and tblFormulaDetail.  And then use the "Approved" field act as the tie breaker of access.  I appreciate how quickly you saw that.  That was worth even more than the Clone question.  And will be rewarded.

So now, before going back to the clone question, i have to figure out how to make the qrySampleDetail calc field below use SubID and RawID into the Dsum mix in order to sum correctly by subid-rawid.

UomUsagePercent: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblFormulaDetail]"),4)*100))

Do you know how to write this where i show the Dsum in the Qry?

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16880941
is this the same query as the qryRNDFormulaDetail in your database?
J
0
 

Author Comment

by:glen9
ID: 16880991
Sorry for forgetting to send a newer file.

http://www.savefile.com/files/5595978

yes, the qry is in qrySampleDetail and the 2 field in question is

UomUsageSum  (i just use this field as a visual.  the necessary calc are in the next field)
and
UomUsagePercent  (the real important field)

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16881132
I guess I don't understand your question. Can you break it down so that I understand what the recordsource is for this field, and what you're trying to total? It seems you already have a value in the percent field. If I understand, you want to show the initial value of "Dsum"?
J
0
 

Author Comment

by:glen9
ID: 16881229
i am trying to use tblFormulaDetail records.  this involves the divisor in the calc.  i am trying to dsum up all like SubID's UomUsage records into UomUsagePercent.  in other words, using the Pecan1 that uses SubID 2490 on 3 records stored in tblFormulaDetail,  i want to take the 3 records amounts (30, 24, 46) UomUsage for SubID 2490.  the amounts add up to 100.

so somehow, i need the Qry to use the SubID like a criteria in the Dsum.

so in the Qry, using subID 2490 as the example, the UomUsagePercent calc would take the 30 amount and divide by 100.  the next comparable 2490 record would take 24 divided by 100 and the 3rd record takes 46/100.

my boss wanted the percentage calcs to show so that we do not need to use the calculator.  for now, the inputter would take the UomUsageSum calculated value and Manually (kind of bad) enter them into the UsageFactor input field.

so i need the UomUsageSum qry field to look to it's subID and dsum common subid"s for the divisor.  Usually, they will add up to 100 for a group of rawID's.

is this any better explaination?  i can try again if you want me to.

G




0
 

Author Comment

by:glen9
ID: 16881258
I said the following wrong:
"for now, the inputter would take the UomUsageSum calculated value and Manually (kind of bad) enter them into the UsageFactor input field. "

i meant to say, that the inputter would take the calculated UomUsagePercent and type the answer for each row, into the UsageFactor input field.

i think i am making this unclear because the test data for Pecan1 uses bad values as examples.  a better example would have been my entering data in sfrmSalesDetail for say 3 rawID records using the same Subid's.  let's say i used Grahams.  i might enter 5, 15 and 20 grahams on the 3 records.  they add to 40 graham's.

i need these 3 graham values to be converted or calculated into percents.  the Dsum should add up UomUsage as 40 but i don't know how to make my UomUsagePercent field look at SubID in the dsum portion of the calc.  if we get it right, then the first row would take 5 divided by 40 for an answer of 12.5.   the 2nd row would be 15/40 or 37.5  and the 3rd record 20/40 for 50.0.

So the 3 records for subid 2490 (if using this grahams example) would show in UomUsagePercent:
12.5
37.5
50.0

i changed the data in my tblFormulaDetail to be as follows.  this is better data example.

SubID      FormulaID      RawID      UOMusage      UsageFactor
2490      Pecan1      11020604      5.00      12.50
2490      Pecan1      11031200      15.00      37.50
2490      Pecan1      11122400      20.00      50.00

2491      Pecan2      11020604      5.00      12.50
2491      Pecan2      11031200      17.00      42.50
2491      Pecan2      11195000      18.00      45.00


sorry for the confusion.
G
0
 

Author Comment

by:glen9
ID: 16881961
I figured out the latest problem.  I spent the last 6 hours looking at people's posts and gleaned some ideas.  i now have a criteria in the calc.

UomUsagePercent: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblFormulaDetail]","[SubID] =  " & [SubID] & " "),4)*100))

so now i am ready to return to the Clone deal.

i will send you a file again, if you don't mind.

http://www.savefile.com/files/3643939

thanks for the structural problems advise.  now back to the clone.

G

0
 

Author Comment

by:glen9
ID: 16882502
sun 6-11-06  8:47 pm eastern
Jeff
i am sending a short access file with just 1 form and 1 subform that you need to patch in the earlier Jeff4 file.  overwrite the earlier frmSamplesMain and sfrmSamplesDetail.

I could no longer add a new rawID with our new setup.  then i realized that i need to fix the subform's master and child links.

http://www.savefile.com/files/5359061

so now i think i am ready for you to test out the cloning.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16882675
Hey Glen,
I noticed a few things with the Jeff4 database. The delete record button is trying to delete records from a table called "Table".

This is what it is in JEff 4.......
Dim strSQL As String
strSQL = "Delete * from table where id =" & strID <----this isn't right. Also, strID isn't defined.
Set warnings = False
DoCmd.RunSQL strSQL

Here's what I pasted earlier that you need to change it to
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub DeleteRecord_Click()
'Jeff Willey style.  delete sub records before the main records.
Dim strSQL As String
Dim strSQL1 as string
dim strID as long
strID = me.MainID.value
strSQL = "Delete * from table where id =" & strID  '<-----this "table" need to be your Main table name
strSQL1 = "Delete * from subtable where id =" & strID  '<-----This needs to be the Sub table name
Set warnings = False        
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL1
End sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16882695
Also, the new form has a slightly different name than the DuplicateOrder function has in it at the end.

Follow the code down until you see where the form gets opened again at the end of the code and rename it to frmSamplesMain instead of frmSampleMain.

Still testing.
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16882756
Here's a working delete code. Once the record is deleted I move you to the first record in the main table.

Private Sub DeleteRecord_Click()

'Jeff Twilley style.  delete sub records before the main records.
Dim strSQL As String
Dim strSQL1 As String
Dim strID As Long
strID = Me.MainID.Value
strSQL = "Delete * from tblFormulaMain where MainID =" & strID  '<-----this "table" need to be your Main table name
strSQL1 = "Delete * from tblFormulaDetail where SubID =" & strID  '<-----This needs to be the Sub table name
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL1
DoCmd.GoToRecord , , acFirst
Me.Requery
End Sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16882787
Im looking at the Add record button now. You're going to have to introduce the MainID and the SubID when you go to the new record. Because the recordsource for the subform is a query, you're going to have to set the mainID then requery. I'll let you know what I find.
J
0
 

Author Comment

by:glen9
ID: 16884376
Jeff - i made the changes and sent a new file.  Jeff5.zip

http://www.savefile.com/files/1266249

you are doing some terrific work.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16885252
In looking at adding a new record....what I'm trying to do logically is this..
I set the MainID field to the next Seed value

Private Sub Command54_Click()
Dim strNewID
strNewID = UpdateSeed
DoCmd.GoToRecord , , acNewRec
Me.MainID.Value = strNewID
End Sub

When I do this, the main form now has a MainID. It's at this point I'm not really sure how you enter a new record, so I did this
I entered the FormulaID Test1
I entered a name Testing Add New Record
I entered a description Testing Add New Record
I then went to the subform and selected the first RawID from the dropdown.
The MainID was then populated for that record which linked the two records MainID-SubID successfully.
I saved the record and went to check the tables and the entries were both there.

Let me know where you are on all of this...this should complete the changes to the buttons...unless I didn't get the sequence right with the Add button.

J
0
 

Author Comment

by:glen9
ID: 16886141
I just pasted your new ADD stuff.

I think you are correct.  I would hit the ADD button to make a brand new never before Sample.  The boss people prefer to enter their own formulaID and not use autonumber.  they would fill out the fields that use a White Background with Red fore font.

on the subform, they would build the new formuladetail.

I assume that if they want to build a second kind of version of the formula, they would hit your new Clone Button.

then on the clone version, they have the chance to change any formuladetail  for the 2nd version.

G
0
 

Author Comment

by:glen9
ID: 16886210
I see a new error.  when i am on my frmRndMenu and try to use button #401, i get an error message.  I never had it before.  it appears to think i want to add a new record on open.

i presently have in button 401:

Private Sub LblButton1_Click()
DoCmd.OpenForm "frmSamplesMain"
DoCmd.Maximize
End Sub

Instead, i am thrown to the ADD button's code.
can you check out?  thanks

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16886434
I don't get an error. What I do get though is the frmRndMenu opening up maximized on my screen. The frmSamplesMain is behind it. Is that what you want it to do? Or do you want to close the menu when you open the samplesMain form?
What add button do you appear to be getting thrown to? Can you step through the code, starting at your button401 and see what it's doing?

Can you paste in the error you're getting?
J
0
 

Author Comment

by:glen9
ID: 16886700
I haven't had good success with modal Yes and popup Yes.  maybe something going on with them?
anyway, yes, i want the Menu to go away after i hit 401.  i guess i then have to change my Close button in frmSamplesMain to have an open frmRndMenu when i am done?  i ran into a problem when i want a datasheet view of say a query when the model/popup is set to yes.  so i took off all my model popup stuff.

Never mind on that error.  i made a mistake when i pasted the ADD stuff.  i accidently duplicated the Private and the end sub.  i am okay now.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16886775
If you want the menu to come back after you close the SamplesMain form then yes, a simple formopen command will work. And you might want to do a close when on the menu form, or make it invisible or something when you open your other forms from it. Not a lot of work really...just good practice. As far as problems with modal and pop-up, it's a good way to keep users from doing things they shouldn't. It keeps the focus on the form in question until the form is closed. If you're having problems navigating between forms, just make sure you aren't doing the same thing twice, like the maximize command. check your form_Open events against the buttons that call them. Make sure you're only doing things once.
J
0
 

Author Comment

by:glen9
ID: 16887496
I've been using maximize all the time.  maybe that is my error.
G
0
 

Author Comment

by:glen9
ID: 16888084
jeff - i ran into a problem.  on frmFormulaBuild.  i can no longer make a new formula.  i narrowed it down to the need for mainid and subid interaction.  probably on the ADD button in frmFormulaBuild.

===================================
which leds me to a question.
i am used to in a mainform having an autonumber say JunctionID.  and lets say i made a JunctionID field in tblFormulaMain and tlbFormulaDetail.

and then i go to the sfrmBom and added subid and formulaid.  i placed it way to the right since it is not input.

And then i would go to the subform on the link master and child fields and use JunctionID, FormulaID.

that way, the JunctionID and FormulaID are automatically created (i never know how access does this) in tblFormulaDetail.  So the JunctionID field in both tblFormulaMain and tblFormulaDetail link everything.
===================================

So i am trying to figure out how our new style mainId and SubID method cannot use my old approach.  is it because of the need to Clone does something to my old JunctionID method?
just wondering.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16888737
I'm not sure really. I was unaware you created new formulas anywhere except in the RND form we were working on. Is this another form off of that menu I saw? Is it similar to the RND form?

I did just what you're talking about on the ADD button, only I called UpdateSeed to populate the MainID and you would manually put a formulaID in there, and the name and description. As soon as you move down into the subform, the SubID field gets populated with the MainID. I hope this is the same kind of thing. Look at the ADD button's code on your RND form that we worked on. If there are further links down to another level of data and that's linked to the formulaID, then I'll have to look at it.
J
0
 

Author Comment

by:glen9
ID: 16889665
jeff - here is a new file.

http://www.savefile.com/files/8314234

please look at tblformulaDetail.  i sorted it in SubID ascend so that you will see missing subid's.
it is because we added formula detail records using frmFormulaBuild.  i suspect that i need to fix some Vba code in the ADD button there too??  do i simply copy from the ADD button on frmSamplesMain?

also, i seem to have a new problem.  but maybe it relates to the subid.  not sure.

when i am in frmFormulaBuild, and go to the subform, i can doubleclick on RawID which already exists and get to the RawID build form.  but if i am doubleclicking a new one, i get a debug error and eventually get thrown out of access.  maybe this will work if the earlier mentioned blank subid's work again.

G
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16889737
Try mapping out where each of these relationships might exist. Seems you have more than one way to add data for these tables.
things to Remember,

If you're going to add a formulaID, then you have to grab the next seed.
If you want a sub record then it HAS to relate to the formulaID, each sub record has to have the same seed number as the FormulaID
If you are adding related data underneath the Sub Record level, then only the sub and that underneath record need to relate.

It cascades, and if you keep that in mind, and you know all the places it does that, then you're good to go.

I never tried to add a formula using the frmFormulaBuild form. the only way I did it was through the form that we coded already.

The ADD button code simply calls the UpdateSeed function and puts it into the New Record MainID field. I typed in a new FormulaID and name and description and your form was populating the SubID field the moment you clicked on the first field on it. It does that through the relationship. It sounds like the form you're working on now needs to do the same thing.
J
0
 

Author Comment

by:glen9
ID: 16890029
unrelated question.  i did some work today in the frmSamplesMain.  i changed the Status field.  when i try to change a value, i get a weird message that my "field is too small to accept the amount of data to add.  try inserting less or paste less data."  i have never seen that message before.

could you show me what i did wrong?

thanks
g
0
 

Author Comment

by:glen9
ID: 16890906
jeff - figured out one of my problems.  the reason that i got an error message when doubleclicking in the rawId field of the subform is because i needed to change "AS double" into "as String".  i forgot that the boss changed the rawID from strict numbers to text.

g
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16891336
if it's a numeric field, go into the source in design mode and change the type to long. Otherwise, you either have a text field that you need to add bytes to...255 is the limit for a text box...or if it's yes or no field, you may want to create an new interface, like a check box vs a field.
J
0
 

Author Comment

by:glen9
ID: 17096269
7-12-2006
I am swamped and cannot continue the record clone thought.  i may resume in 2 or 3 weeks.  but it is okay if you now close the subject.
glen9
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17096362
I thought we got all this working G?
0
 

Author Comment

by:glen9
ID: 17119436
7-16-06
hi - yes you can close this out now.
glen9
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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