Sum of one field as a Default value for a second field

Sorry, I forgot to mention that I need this entry everytime I select a customer through a Combo Box.
So I would imagine that the Code will go in to AFterUpdate prperty of the Combo Box for selecting the customer.

Hi. As far as I am aware this is not possible in any database. The default value is assigned when the table is designed. It is the value that will be assigned to this field by the database in the event that you create a new record and do not assign any value to the field.

I suspect, however, that you are thinking about something different from default values. Please explain something about your app and what you are trying to achieve with this database field

Many thanks for the reply.
Perhaps I must clarify. Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold.
the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.

The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return

Thanks

You need to use the DSum function as you are not just referring to the current record.

= DSum("[MCRef]","Table/Query Name","[Cust_ID]=" & [Cust_ID])

Many thanks for the Help. I have now this code as the Default Property fot the MCRTot field
=DSum("[MCRef]","Miracle_Cloth_Main","[Name]=" & [Name])

However I notice that on selecting the Name of a supplierfrom the combobox the FIRST record data for that supplier loads in the form and the Total does not appear

The code Inserted by the wizard while making the Combo is:

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub Combo84_AfterUpdate()
3. ' Find the record that matches the control.
4. Dim rs As Object 
5.   Set rs = Me.RecordsetClone
6.   rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo84], 0))
7.   If Not rs.EOF Then 
8.     Me.Bookmark = rs.Bookmark
9.   End Sub 
10.  

Is there a FindLast function? I can substitute for the FindFirst ?

If Name is a text field you will need to enclose in single quotes.

=DSum("[MCRef]","Miracle_Cloth_Main","[Name]='" & [Name] & "'")



Just change rs.FindFirst to rs.FindLast

I thought it is and changed to Rs.FindLast. However
it still doesn't show the total. On checking the table
I find that the Combo field entry doesn't get entered in the table though other data are getting saved when save button is pressed.

The problem I think is that the control source property of your combo box is not bound to the appropriate field in the table.

In the properties of the combo box under the data tab change the control source to the field that you want the value stored in. It is probably blank at the moment.

Many thanks for the reply.
I guess the Cobo cannot be bound to a control dource for the selection or NotInList properties to work enabling entry of NEW data using the Combo.

I have now some code attached to the SAVe button which creates a NEW Record with the chosen Customer Name. However The FindLast still doesn't work as the
Name is not getting written from the Combo to the table
though other data is being saved.
Here is the New Code;
++++++++++++++++++

Expand|Select|Wrap|Line Numbers

1. Private Sub Name_Combo_AfterUpdate()
2. ' Find the record that matches the control.
3. Dim rs As Object
4.  
5. Set rs = Me.Recordset.Clone
6. rs.FindLast "[RecordNum] = " & Str(Nz(Me![Name_Combo], 0))
7. If Not rs.EOF Then Me.Bookmark = rs.Bookmark
8. End Sub
9.  
10.  
11. Private Sub Name_Combo_NotInList(NewData As String, Response As Integer)
12.  
13. Dim Db As Database, rs As Recordset
14. Dim strmsg As String
15. strmsg = "'" & NewData & "' is not in Current List"
16. strmsg = strmsg & "@You Must Add it to Continue"
17. strmsg = strmsg & "@Click Yes to link or No to Cancel."
18. If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new Description?") = vbNo Then
19. Response = acDataErrContinue
20. Else
21. Set Db = CurrentDb
22. Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
23. On Error Resume Next
24. rs.AddNew
25. rs!Name = NewData
26. rs.Update
27. rs.Close
28.  
29. Set rs = Nothing
30. If Err Then
31. MsgBox "An error occurred. Please try again."
32. Response = acDataErrContinue
33. Else
34. Response = acDataErrAdded
35. End If
36. End If
37.  
38. End Sub
39.  
40. Private Sub Save_Record_Click()
41. Dim Db As Database, rs As Recordset
42. Set Db = CurrentDb
43. Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
44.  
45. rs.AddNew
46. rs.Close
47.  
48.  
49. On Error GoTo Err_Save_Record_Click
50.  
51.  
52. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
53.  
54. Exit_Save_Record_Click:
55. Exit Sub
56.  
57. Err_Save_Record_Click:
58. MsgBox Err.Description
59. Resume Exit_Save_Record_Click
60.  
61. End Sub

try

rs.FindPrevious

see if that will work

Hello,
Many thanks again for the reply.

The problem is that any record saved based on the pulled down customer name gets saved with an empty customer name field. So there is only ONE record
which is called up by FindFIRST, LAST OR PREVIOUS.

The way to use the data is to call up the last saved record (From the last customer visit) change it with new
data and save it during the visit.

So what is required is some code to include the name field in the saved Record Set.

Hope you can help.

OK I've been rereading through the posts and I think I got the wrong impression somewhere about what you were trying to do. When you used Me.RecordsetClone and rs.FindFirst this was for searching a recordset to find a value or set of values and preform some action.

If all you want to do is to get the form to go to the record corresponding to the value selected in the combo box then you just need to use the following code:

Expand|Select|Wrap|Line Numbers

1.  
2.  
3.  
4.  
5.  
6. Private Sub Combo84_AfterUpdate()
7.  
8.   Me.RecordNum.SetFocus 
9.   DoCmd.FindRecord Me.Combo84, acAnywhere, , acSearchAll, , acCurrent
10.  
11. End Sub
12.  
13.  

You may have to designate Me.Combo84 as a string. If so put:

DoCmd.FindRecord "'" & Me.Combo84 & "'", acAnywhere, , acSearchAll, , acCurrent

Many thanks.

Many thanks

All I am trying to achieve is the following

Name Cust_ID RecordNum Address
whs 0 1 ggggggggg
0 2 qqqqqqq
0 3 mmmmm
0 4 nnnnnnnnn
0 5 ooooooo
0 6 pppppp

When I select the Name from the Combo drop down
Iget the Record No1 with address gggggggggg as default
whereas what I need the record No 6 with address ppppp
(The last visit record which can be modified during the current visit)

My latest code is here :

Expand|Select|Wrap|Line Numbers

1. Private Sub Name_Combo_AfterUpdate()
2. ' This procedure tries to find the matching product's record.
3. ' If the matching record is found, the procedure goes to it.
4. ' If the record isn't found, the focus stays on the current record.
5.  
6. Dim Criteria As String ' This is the argument to the FindFirst method.
7. Dim MyRS As Recordset ' Recordset used to search.
8. Dim ComboName As String ' The name of the company to search for.
9. Const IDYES = 6
10.  
11. Set MyRS = Me.RecordsetClone
12. ' Build the criteria.
13. ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
14. Criteria = "[Name]=" & ComboName
15. ' Perform the search.
16.  
17. MyRS.FindLast Criteria
18. If MyRS.NoMatch Then
19.  
20. Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
21. If Response = IDYES Then
22. MyRS.AddNew ' Create new record.
23. MyRS("Name") = Screen.ActiveControl
24. MyRS.Update ' Save changes.
25. MyRS.Move 0, MyRS.LastModified ' Go to new record
26. Me.Bookmark = MyRS.Bookmark ' Go to new record
27. Else
28. GoTo Endsub
29. End If
30. Else
31. 'Synchronize the form's record to the dynaset's record.
32. Me.Bookmark = MyRS.Bookmark
33.  
34. End If
35. Endsub:
36. MyRS.Close
37.  
38.  
39. End Sub

This code seems to be entering the name in the COMBO
correctly. On doing trials I have found that getting the values entered in each record is not a good idea since it fills up the combo with the same customer name .

So to summarise the current issue:
I NEED the last record to load when a combo name is
pulled down and not the FIRST record.

I am sorry to be a bother but do realise that I am treading on the higher levels of ACCESS without a clue !

Is the Address a unique field? And is it a number or a string?

Can you post the exact SQL statement you are using for the 'row source' of the combo box. This will be either a table, saved query or a SELECT statement and can be found in the properties under the data tab.