Default value on a form of previous record of certain customer

**ADezii** · Apr 21 '10, 12:32 AM

Originally posted by DanicaDear

I have been peeking around the internet looking for a solution that I can't find. So I'm back. :-) I'll try to lay out everything initially in hopes it saves time in the long run.

I will search for [CUST_NUM] on frmCustomers. When I find the customer, I click a button btnShowOrders and it opens frmOrders with subfrmOrderDeta ils2. (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 subfrmOrderDeta ils2 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 subfrmOrderDeta ils2. 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.

Thanks in advance to all the smart people. LOL.

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** · Apr 21 '10, 11:45 AM

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 subfrmOrderDeta ils2 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** · Apr 21 '10, 12:34 PM

Originally posted by 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 subfrmOrderDeta ils2 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.

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** · Apr 21 '10, 01:22 PM

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** · Apr 21 '10, 08:49 PM

@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 (subfrmOrderDet ails2) 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 :-)

Attached Files

GloveProgram042110.zip (173.5 KB, 203 views)

**ADezii** · Apr 21 '10, 10:25 PM

Originally posted by 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 (subfrmOrderDet ails2) 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 :-)

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

**topher23** · Apr 21 '10, 11:12 PM

Hmmmm... I've got a solution for how you could "Automagica lly" 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:

Code:

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** · Apr 21 '10, 11:39 PM

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:

Code:

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** · Apr 21 '10, 11:46 PM

Originally posted by DanicaDear

@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** · Apr 21 '10, 11:50 PM

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** · Apr 22 '10, 12:00 PM

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

Attached Files

2003GloveProgram042110.zip (170.8 KB, 225 views)

**topher23** · Apr 22 '10, 12:51 PM

Originally posted by 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.

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** · Apr 22 '10, 12:54 PM

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

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

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** · Apr 22 '10, 01:25 PM

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.

Originally posted by Topher

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.

Default value on a form of previous record of certain customer