Default value on a form of previous record of *certain customer*

[DanicaDear]

I will search for [CUST_NUM] on frmCustomers. When I find the customer, I click a button btnShowOrders and it opens frmOrders with subfrmOrderDetails2. (I'm having trouble grasping the previous sentence myself, so here's the paraphrase: I open a form for a customer, click SHOW ORDER button, and the order form opens showing order details.) In frmOrders, the [CUST_NUM] is automagically filled in since I used the button to open it. Additionally, an [ORDER_NUM] is filled in automatically by adding 1 to the previous max [ORDER_NUM].

Our customers order 4 times each year, and 90% of the time they have the exact same order. What I desire is to have the subfrmOrderDetails2 roll over the default value from the previous order *for that customer*. Note that is not the previous record. It would be the previous max [ORDER_NUM] pertaining to that particular [CUST_NUM].

It's probably worth noting that an [ORDER_NUM] and [CUST_NUM] are required before one enters data in the subform (Referential integrity) but both of these are supplied when one opens the form by the button. However, the form has be to dirty (I think I'm using correct terminology) before one can enter anything into the subfrmOrderDetails2. I don't really want the form to have to be dirty...I thought with those two controls filled in it was dirty enough. I'm not sure if that makes a difference in the solution, but wanted to mention it just in case.

[ADezii]

@DanicaDear
I'm a little hazy on your Request, but it appears as though you want to retrieve the Highest Order Number for a given Customer as depicted in a Field on frmOrders, then increment it by 1:
DMax("[ORDER_NUM]", "<Table Name>", "[CUST_NUM] = " & Forms!frmOrders![txtCustNum]) + 1

[DanicaDear]

ADezii,
You're partly right. I want to retrive the previous highest order number for a given customer (because when I open the form for the new order the max order number is going to be on the form already...I already have it incrementing by one), but I want to take the *order details (items ordered)* linked to that order number and have them default into the newest order. In want each customer's unique order to roll over when I open a new order form.
Mainform frmOrders contains [order_num] and [cust_num]
Subform subfrmOrderDetails2 contains the items I want to default into all new orders for that customer.
My question is how do I get defaults set for order details for each individual customer? Hope this clarifies it.
PS. Let me know if I'm still not clear. Thank you very very very much.

[ADezii]

@DanicaDear
The same logic would apply, but the Criteria would specify the largest Order Number + 1 in the Order Details Table for the given Order Number already specified. Worse case scenario, can you Upload your Database, or a subset of it?

[NeoPa]

Danica,

This might be complicated if the OrderDetails data is stored in separate records (A record per OrderDetails line).

Fundamentally though, the concept of setting the defaults rather than setting up the values directly is what ensures that the record buffer remains un-dirtied. From what you say already, it seems you understand all this. What I'm having trouble seeing is what you need from us exactly. Are you having difficulty referring to the .DefaultValue property? The specific items on the subform perhaps (See Referring to Items on a Sub-Form)?

[DanicaDear]

@ADezii Thanks for offering to look at the file. It is attached. Don't comment on ugliness. I'm not there yet. LOL. Here are directions to get to what you need to see:
1. Open frmCustomers
2. Navigate to the second customer on the list. (CUST_NUM 10001)
3. Click "Show Orders" button at the the top. The frmOrders opens. Note this customer's last order was ORDER_NUM was 20090014. Note on the subform (subfrmOrderDetails2) he has 5 items in his order, of different sizes, class, and quantity. ("Commodity Number" is the PK.) Now, I want to give him a new order.
4. ORDER_NUM 20090015, ...16, ...17 have already been used on other customers. Click the main form (frmDetails) navigation button one to the right to get to the next order for this customer. ORDER_NUM 20090018 automatically fills in, along with CUST_NUM 10001. I want those 5 items on the subform from the last order for *this customer* (ORDER_NUM 20090014) to roll over also. My question isn't about increments; it's about defaults. Each customer number will have a unique set of defaults, defined on their initial order. After that, I want the order details to roll over on new orders. The order details very rarely change.
5. We will enter data into the 10 empty controls, unlabeled on the form at this point. That will dirty our form and allow us to save the record.

@NeoPa Yes, the order details are stored one per line. You're scaring me. LOL. Yes, I am hoping this can be done with the default property. Please don't laugh but this is what I was thinking. (Remember, don't laugh. LOL).
Code that says this: Look at the customer number on my form. Take the DMax of the order number linked to the customer number currently open. Use default property or a slick line of code that grabs the order details and pastes them into the new subform. :-) Inexperienced users always oversimplify, don't we? haha

I hope the attachment of my DB and instructions above help clear up my question. If not, reply back. This is pretty important for me.

Thanks as always! Danica :-)

[ADezii]

@DanicaDear
My fault Danica, I'm not running Access 2007. Can you convert the DB to 2003 and resend it?

[topher23]

Hmmmm... I've got a solution for how you could "Automagically" have all of the details you want filled in with the data from the last order for that customer, if that's what you're looking for. You could use a code-generated recordset like so:

Private Sub subPopulateFields()
Dim rs As DAO.Recordset
 
    Set rs = Me.RecordsetClone 'provided the form's recordsource will contain the old data you want
 
    rs.FindLast "CUST_ID=" & Me.CUST_ID 'Note that, since the current record hasn't
                                        'been saved yet, it's not saved in the recordset.
    If rs.RecordCount > 0 Then 'only run this if there was a previous order for this customer
 
        Me.FillField1 = rs!FillField1 'populate your form with the associated
        Me.FillField2 = rs!FillField2 'fields from the recordset
 
    End If
 
    rs.Close: Set rs = Nothing
 
End Sub
 

This would fill in the fields you wanted, then the user could change them if necessary.
Edit: it looks like a recordsetclone probably isn't the way to go here, but rather an SQL statement joining the main order table and the details table. The principle should basically be the same, though.

Sorry if I stomped you, ADezii. I read the initial question and felt like this might be what Danica was looking for.

[topher23]

And, now that I look over all of the posts on here for the second time, I realize something that might even work better. Since your details are a subform, and thus more than likely in a different table, you could use something like this:

Private Sub subPopulateFields()
Dim LastOrder As Long
 
    LastOrder = Nz(DMax("Order_ID", "tblOrders", "CUST_ID=" & Me.CUST_ID), 0)
    If LastOrder <> 0 Then
        'I'm assuming tblOrderDetails links to tblOrders on Order_ID here
        DoCmd.RunSQL "INSERT INTO tblOrderDetails ( Order_ID, Field1, Field2 ) " & _
        "SELECT " & Me.Order_ID & " AS CurrentOrder, tblOrderDetails.Field1, " & _
        "tblOrderDetails.Field2 FROM tblOrderDetails WHERE Order_ID=" & LastOrder
    End If
    Me.subfrmOrderDetails2.Requery
 
End Sub
 

This will take multiple lines of order details and plunk them all into the new order in the table, then requery the subform to show the newly-added details. Is that the "slick line of code" you were mentioning?

[NeoPa]

@NeoPa Yes, the order details are stored one per line. You're scaring me. LOL. Yes, I am hoping this can be done with the default property. Please don't laugh but this is what I was thinking. (Remember, don't laugh. LOL).

No need to be scared Danica. Soft cooing noise - followed by ...
BOOO!!!

Defaults, however, can only be set up at a record level. It is not possible in you circumstances to set all up at once. That said, with a more complicated bit of code, you could set up the defaults to match the first record, then continually replace these with subsequent record data untill all the previous records had been duplicated. As I say, this would need to be done in code. It would also require some way of accessing, or referring to, the previous records after the initial main record had been created.

If this is not scary, then it may suit you. It's not too trivial though, for a novice coder.

[NeoPa]

Topher,

One of the points of the request was for the earlier data to be prompted, or defaulted. This wouldn't necessarily mean that data would be accepted in all cases. Duplicating the records as you've posted, may be an alternative worth considering, but bear in mind it's a solution for a slightly different question.

[DanicaDear]

Here is my DB in 03 format. I am looking at topher23's and NeoPa's recommendations now.
Thanks!!

[topher23]

@NeoPa
Ummm... I didn't see anywhere where Danica asked for a prompt to the user. Danica said: "Use default property or a slick line of code that grabs the order details and pastes them into the new subform. :-)" I looked at the "or" clause and provided a solution - something that grabs the order details and puts them into the new subform. I think you may be hung up on the word "default" rather than thinking "what is the most efficient way to accomplish what needs to be done."

[DanicaDear]

@NeoPa I'm not sure I understand your point to Topher23. If the data gets into the subform and it's no longer correct, then can't we just manually change it?

I'd like to get Topher23's suggestion into my DB and see what it does. I certainly have nothing to lose and nothing else to go on at this point.

Based on past experiences I understand that this could evolve significantly before I'm finished, so I'm reposting topher's code with my actual table and field names. I think this will save me trouble later. Here it is, with nothing else changed.

Private Sub subPopulateFields() 
Dim LastOrder As Long 
 
    LastOrder = Nz(DMax("ORDER_NUM", "tblOrders", "CUST_NUM=" & Me.CUST_NUM), 0) 
    If LastOrder <> 0 Then 
        'I'm assuming tblOrder_Details links to tblOrders on ORDER_NUM here 
        DoCmd.RunSQL "INSERT INTO tblOrder_Details ( ORDER_NUM, QTY, CLASS ) " & _ 
        "SELECT " & Me.ORDER_NUM & " AS CurrentOrder, tblOrder_Details.QTY, " & _ 
        "tblOrder_Details.CLASS FROM tblOrder_Details WHERE ORDER_NUM=" & LastOrder 
    End If 
    Me.subfrmOrder_Details2.Requery 
 
End Sub
 

Now, because Topher's code had Field1 and Field2, and I have Field3 and Field 4, I have made an attempt to continue what he was doing by adding my additional fields. Topher, if you look at this, please look at it very closely. I can be very dangerous.
QTY is a number field
CLASS and SIZE are text fields
SPECIAL is a yes/no field (a checkmark on the form)
I should probably mention here as well that I have another field COMMODITY_NUM that automatically fills itself in the table (and the form) based on the size and class. I'm hoping this will continue to work without having to address it in the code.

Private Sub subPopulateFields() 
Dim LastOrder As Long 
 
    LastOrder = Nz(DMax("ORDER_NUM", "tblOrders", "CUST_NUM=" & Me.CUST_NUM), 0) 
    If LastOrder <> 0 Then 
        'I'm CONFIRMING tblOrderDetails links to tblOrders on Order_ID here 
        DoCmd.RunSQL "INSERT INTO tblOrder_Details ( ORDER_NUM, QTY, CLASS, SIZE, SPECIAL ) " & _ 
        "SELECT " & Me.Order_NUM & " AS CurrentOrder, tblOrder_Details.QTY, " & _ 
        "tblOrder_Details.CLASS, " & "tblOrder_Details.SIZE, " & _ 
        "tblOrder_Details.SPECIAL FROM tblOrder_Details WHERE Order_NUM=" & LastOrder 
    End If 
    Me.subfrmOrder_Details2.Requery 
 
End Sub

I'll be putting this in my DB after submitting this post to see if it works. I don't think code I've manipulated has ever worked on the first try...so go ahead and tell me what's wrong with it. LOL. (I'm concerned with mixing the number, text, and yes/no fields as recently I've learned you have to be careful with ' or ". I will further study that now.)

[NeoPa]

I was simply drawing attention to the fact that the solution presented didn't match the stated requirement. I was not suggesting (was careful not to suggest) that it was therefore useless. Such offerings often result in the OP looking at their actual situation with new eyes (as opposed to their posted question) and prove very useful. Unfortunately, they can also cause the OP to get frustrated when they feel their question is not properly understood, especially when they don't come with an explanation that the poster understands the suggestion is a little off base from the question but may nevertheless be helpful. Some OPs (Not true for Danica I'm sure) don't even notice the difference as they struggle to comprehend what the solution actually does. We have many members that fit that category.

What is the most efficient way to accomplish what needs to be done?

As I see this is defined by the OP (what needs to be done), I'm very careful around such issues (as illustrated here). I could guess what I think they really mean of course, but I prefer to keep to what's stated, as I know from experience the problems that occur when communication problems get in the way.

Lastly, I'm sorry if my post seemed like a criticism. I was hoping merely to point out the situation, so that all could understand exactly what you were offering and no-one got confused.

[DanicaDear]

I'm not confused. I'm happy. :-)
LOL.

[NeoPa]

@NeoPa I'm not sure I understand your point to Topher23. If the data gets into the subform and it's no longer correct, then can't we just manually change it?

Certainly you can. As the OP you are in a good position to judge what is appropriate for you of course, and that could certainly be got to work pretty straightforwardly.

From your reaction I can only imagine that Topher's suggestion is a perfect solution for you. Certainly much easier than the originally suggested way.

[DanicaDear]

I have tried to place my code in the mainform and the subform. Neither are working. (I fully understand the last code I posted may not be correct and causing it not to work. I don't get any error messages, it just pretends the code isn't there.) I opened VBA code builder and pasted the code directly in it while I had the corresponding forms open in design view. Is it more complicated than that? (I'm more familiar with attaching code to events, like On Load, On Click, etc)
Thanks!
Danica

[topher23]

Danica, you're sure right about the referential integrity issues. I've been messing around with the database you attached for the better part of an hour and haven't been able to get around that. This subroutine can't be called until after the new order has been added, but I've been unable to get any event to work that calls it properly.

The changes that I see need to be made to the code relate to the use of fields that say NUM but are actually text fields. Oh, and COMMODITY_NUM will need to be added. I also added a check to the DMax just to make sure the order is less than the current order (because of that referential integrity). So the latest version of that subroutine looks like:

Private Sub subPopulateFields()  
Dim LastOrder As Long  
 
    LastOrder = Nz(DMax("ORDER_NUM", "tblOrders", "CUST_NUM='" & Me.CUST_NUM & "' AND ORDER_NUM < '" & Me.ORDER_NUM & "'"), 0)  
    If LastOrder <> 0 Then  
        'I'm CONFIRMING tblOrderDetails links to tblOrders on Order_ID here  
        DoCmd.RunSQL "INSERT INTO tblOrder_Details ( ORDER_NUM, QTY, CLASS, SIZE, COMMODITY_NUM, SPECIAL ) " & _  
        "SELECT '" & Me.Order_NUM & "' AS CurrentOrder, tblOrder_Details.QTY, tblOrder_Details.CLASS, " & _  
        "tblOrder_Details.SIZE, tblOrder_Details.COMMODITY_NUM," & _  
        "tblOrder_Details.SPECIAL FROM tblOrder_Details WHERE Order_NUM='" & LastOrder & "'"  
    End If  
    Me.subfrmOrder_Details2.Requery  
 
End Sub 
 

[DanicaDear]

Ok. So maybe if we remove the default date on the main form (DATE_SHIP), type in the DATE_SHIP (which dirties the form), add a save button and save the record, the order will be saved, the referential integrity issue should disappear, and your code should work. I'll make those changes and see what happens. If the code still won't work, I can repost my DB with the other changes I just descirbed, if needed. I'll let you ask for an updated version if you'd like it. Thanks for all your help!!!

[topher23]

Eureka! I had to create a "New Order" button in order to do it, but there is now a working event in your database. Have a look.

Ha! I just saw your last reply. Looks like we had similar ideas. :)

[DanicaDear]

I love seeing the word Eureka on my Bytes posts! LOL.

Yes, we were thinking exactly the same. I appreciate your time and ideas greatly. Bytes is sooooooooo awesome!
Thank you thank you thank you.

[NeoPa]

I have tried to place my code in the mainform and the subform. Neither are working. (I fully understand the last code I posted may not be correct and causing it not to work. I don't get any error messages, it just pretends the code isn't there.) I opened VBA code builder and pasted the code directly in it while I had the corresponding forms open in design view. Is it more complicated than that? (I'm more familiar with attaching code to events, like On Load, On Click, etc)
Thanks!
Danica

The last code you posted is a Subroutine procedure. Other common types of procedure are Function procedures and Event procedures. Functions return values whereas Subs don't. Event procedures are the only ones that are triggered automatically by various predefined events. Thus it is that code in an OnClick event procedure is run whenever a particular control is clicked. Subs and functions, on the other hand, will only ever execute when they are called by other code. If you have no code that calls your Sub procedure then it will never run.

[NeoPa]

Well, you guys have been quite busy since I last looked. Great work Topher.

[DanicaDear]

Yes, this is the fastest solution I think I've ever gotten. I had planned for a week. LOL. Hats off to topher! (and NeoPa and ADezii too!)

[ADezii]

Here is something else to keep you busy, Danica. Download the Revised Attachment and:

1. Navigate to Record #2 or [CUST_NUM] 10001.
2. Click on Show Orders.
3. Add a New Order - a Save & Rollover Order Details Command Button now appears.
4. Add a Ship Date and at least 1 value in either of the 10 Text Boxes (critical).
5. Click Save & Rollover Order Details Command Button.
6. A New Order for [CUST_NUM] 10001 will be added, Order Details, if any, for the Last/Max Order Number for Customer #10001 will be carried over for the newly created Order, and tblBOX_NUM will be populated with the appropriate data.

BTW, nice work topher23!

[DanicaDear]

You are right ADezii. That will keep me busy for a WHILE. I looked at the code and gasped. I'm going to weigh differences between the 2 items provided for me. This will be a great learning experience. You and topher23 both put in a lot of time on this and I want you to know I appreciate it.

I have another question about this form. It's of a different nature so I'm going to create a new post. I had intended to handle this later but now I have 2 or 3 people on my trail who are probably more familiar with my DB than they'd like to be, so it's probably a time savings to address it now. Take a look if you'd like. Otherwise hopefully somone else is brave enough to take on DanicaDear. LOL.

[DanicaDear]

ADezii,
I've printed off this code and I'm studying. Question: Why do I have to type in one of the 10 empty controls? It can't be to dirty the form because the date will dirty the form. (I resist because we will be scanning numbers into those controls once the order is filled. That's part of the question I'll be posting later.)

EDITED BY DANICADEAR: Nevermind ADezii, I see on the program you left the date defaulted to Date(). I can remove that default and then the BOX_NUM control is no longer requried to save record. Input of date will dirty form. Sorry about missing that.

[ADezii]

@DanicaDear

1. Rem out the Validation Code that checks for at least 1 Value in the Empty Controls.
2. Add another Order and Rollover the Details exactly as described in Post #26
3. Close the Details Form, and go back to the Orders Form.
4. Show Orders/Order Details for [CUST_NUM] 10001 again.
5. Do you see the newly added Order and Order Details?

[DanicaDear]

Yes, ADezii, this works. Upon studying your code I thought this would be a possibility. I had already tried it out before you even asked. Thanks again. :-)

[ADezii]

You are quite welcome.