By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,099 Members | 2,156 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,099 IT Pros & Developers. It's quick & easy.

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

P: 35
Hello,
I am new to access. I need to have the Default value in a field for a record as the SUM of all the values in another field where the value of a thirdr field is same. All fields have numeric values.

MC RefillTotal = sum of MC Refill for the same customer till the New record entry.

I try to put this as the default value for the MCRefTot field in the table , but not accepted as the synatax being not correct.

= Sum [MCRef] where [Cust_ID]=[Me]![Cust_ID]

Is this possible in Access with a correct synatx ?
I have searched for an example but couldn't find one. Can someone please help ?
Nov 12 '06 #1
Share this Question
Share on Google+
34 Replies


P: 35
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.
Nov 12 '06 #2

100+
P: 1,646
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

Thanks
Nov 12 '06 #3

P: 35
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
The entries I am trying to make are on the FORM and not on the table itself

I hope this explains
Many thanks again
Nov 12 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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])
Nov 12 '06 #5

P: 35
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 supplier
from the combobox the FIRST record data for that supplier loads in the form and the Total doesnot apper
The code Inserted by the wizard while making the Combo is:

Private Sub Combo84_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo84], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Is there a FindLast function? I can substitute for the FindFirst ?
Nov 12 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534

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])
If Name is a text field you will need to enclose in single quotes.

=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 ?
Just change rs.FindFirst to rs.FindLast
Nov 12 '06 #7

P: 35
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
Many thanks for your help and replies.

I have corrected the code as it is a text field. Thanks.

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.

I guess some change is required in the After Update code below
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo84_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.  
  5.     Set rs = Me.RecordsetClone
  6.     rs.FindLast "[RecordNum] = " & Str(Nz(Me![Combo84], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
  9.  
  10. Private Sub Combo84_NotInList(NewData As String, Response As Integer)
  11. Dim Db As Database, rs As Recordset
  12. Dim strmsg As String
  13. strmsg = "'" & NewData & "' is not in Current List"
  14. strmsg = strmsg & "@You Must Add it to Continue"
  15. strmsg = strmsg & "@Click Yes to link or No to Cancel."
  16. If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new Description?") = vbNo Then
  17. Response = acDataErrContinue
  18. Else
  19. Set Db = CurrentDb
  20. Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
  21. On Error Resume Next
  22. rs.AddNew
  23. rs!Name = NewData
  24. rs.Update
  25. rs.Close
  26.  
  27. Set rs = Nothing
  28. If Err Then
  29. MsgBox "An error occurred. Please try again."
  30. Response = acDataErrContinue
  31. Else
  32. Response = acDataErrAdded
  33. End If
  34. End If
  35.  
  36. End Sub
  37. Private Sub Save_Record_Click()
  38. On Error GoTo Err_Save_Record_Click
  39.  
  40.  
  41.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  42.  
  43. Exit_Save_Record_Click:
  44.     Exit Sub
  45.  
  46. Err_Save_Record_Click:
  47.     MsgBox Err.Description
  48.     Resume Exit_Save_Record_Click
  49.  
  50. End Sub
  51. Private Sub NEWRECORD_Click()
  52. On Error GoTo Err_NEWRECORD_Click
  53.  
  54.  
  55.     DoCmd.GoToRecord , , acNewRec
  56.  
  57. Exit_NEWRECORD_Click:
  58.     Exit Sub
  59.  
  60. Err_NEWRECORD_Click:
  61.     MsgBox Err.Description
  62.     Resume Exit_NEWRECORD_Click
  63.  
  64. End Sub
  65. Private Sub Command88_Click()
  66. On Error GoTo Err_Command88_Click
  67.  
  68.  
  69.     DoCmd.GoToRecord , , acNewRec
  70.  
  71. Exit_Command88_Click:
  72.     Exit Sub
  73.  
  74. Err_Command88_Click:
  75.     MsgBox Err.Description
  76.     Resume Exit_Command88_Click
  77.  
  78. End Sub
  79. Private Sub Command89_Click()
  80. On Error GoTo Err_Command89_Click
  81.  
  82.  
  83.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  84.  
  85. Exit_Command89_Click:
  86.     Exit Sub
  87.  
  88. Err_Command89_Click:
  89.     MsgBox Err.Description
  90.     Resume Exit_Command89_Click
  91.  
  92. End Sub
Nov 12 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534

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.
Nov 12 '06 #9

P: 35
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
Nov 13 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
try

rs.FindPrevious

see if that will work

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
Nov 13 '06 #11

P: 35
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.
Nov 14 '06 #12

P: 35
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.
I assume the problem is because the Name combobox is UNBOUND to the NAME field of the table.

Does anyone know how to save the Information
to UNBOUND field ?

Many thanks for any suggestions.
Nov 14 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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. Private Sub Combo84_AfterUpdate()
  3.  
  4.   Me.RecordNum.SetFocus 
  5.   DoCmd.FindRecord Me.Combo84, acAnywhere, , acSearchAll, , acCurrent
  6.  
  7. End Sub
  8.  
  9.  
You may have to designate Me.Combo84 as a string. If so put:

DoCmd.FindRecord "'" & Me.Combo84 & "'", acAnywhere, , acSearchAll, , acCurrent
Nov 14 '06 #14

P: 35
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 !
Nov 14 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
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.


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 !
Nov 14 '06 #16

P: 35
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.
Adress is not a unique field. RecordNumber has been set as an Auto Number and Primary Key and the only unique field. I showed address only as an example to
show the First/last issue.

Here is the ROW source property of the Combo

SELECT [Name] FROM Miracle_Cloth_Main;
Nov 14 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
OK try this instead:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Name_Combo_AfterUpdate()
  3. ' This procedure tries to find the matching product's record.
  4.    ' If the matching record is found, the procedure goes to it.
  5.    ' If the record isn't found, the focus stays on the current record.
  6. Dim recNo As Long
  7.  
  8.   ' for this to work there cannot be any RecordNumber with a value of 0
  9.   ' it finds the highest record number for the name in the combo box
  10.   ' and returns 0 and exits if no record found.
  11.   recNo = nz(DMax("[RecordNumber]", "Query or Table behind form","[Name]='" & Me.Name_Combo & "'"), 0)
  12.   If recNo = 0 Then
  13.     Exit Sub
  14.   End If 
  15.   Me.RecordNumber.SetFocus 
  16.   DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  17.  
  18. End Sub
  19.  
Nov 14 '06 #18

P: 35
OK try this instead:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Name_Combo_AfterUpdate()
  3. ' This procedure tries to find the matching product's record.
  4.    ' If the matching record is found, the procedure goes to it.
  5.    ' If the record isn't found, the focus stays on the current record.
  6. Dim recNo As Long
  7.  
  8.   ' for this to work there cannot be any RecordNumber with a value of 0
  9.   ' it finds the highest record number for the name in the combo box
  10.   ' and returns 0 and exits if no record found.
  11.   recNo = nz(DMax("[RecordNumber]", "Query or Table behind form","[Name]='" & Me.Name_Combo & "'"), 0)
  12.   If recNo = 0 Then
  13.     Exit Sub
  14.   End If 
  15.   Me.RecordNumber.SetFocus 
  16.   DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  17.  
  18. End Sub
  19.  

I have tried appending the code below . No change in results but No error messages
Expand|Select|Wrap|Line Numbers
  1.   Dim recNo As Long
  2.  
  3.   ' for this to work there cannot be any RecordNumber with a value of 0
  4.   ' it finds the highest record number for the name in the combo box
  5.   ' and returns 0 and exits if no record found.
  6.   recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Name]='" & Me.Name_Combo & "'"), 0)
  7.   If recNo = 0 Then
  8.     Exit Sub
  9.   End If
  10.   Me.Text90.SetFocus
  11.   DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
Nov 14 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
Add the line in bold and tell me exactly what appears on the screen. Is it what you expect?


Dim recNo As Long

' for this to work there cannot be any RecordNumber with a value of 0
' it finds the highest record number for the name in the combo box
' and returns 0 and exits if no record found.
recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Name]='" & Me.Name_Combo & "'"), 0)
Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
If recNo = 0 Then
Exit Sub
End If
Me.Text90.SetFocus
DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
Nov 14 '06 #20

NeoPa
Expert Mod 15k+
P: 31,494
NB.
Before you try this make sure you've type Ctrl-G to open the Debug window.
What Mary's after should appear in there - be ready to Copy and Paste it back in here as a post.
Good luck.
Nov 14 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
NB.
Before you try this make sure you've type Ctrl-G to open the Debug window.
What Mary's after should appear in there - be ready to Copy and Paste it back in here as a post.
Good luck.
If you can't figure out how to do that you can use a message box instead:

Msgbox "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
Nov 14 '06 #22

P: 35
If you can't figure out how to do that you can use a message box instead:

Msgbox "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"

Many thanks for the help again

Here is what I get in the DEBUG window

RecordNo: 1 and Name: 'whs'
RecordNo: 8 and Name: 'Kickstart'
+++++++++++++++++

Below is actual TABLE

Name Cust_ID RecordNum Address
whs 0 1 ggggggggg
0 2 qqqqqqq
0 3 mmmmm
0 4 nnnnnnnnn
0 5 ooooooo
0 6 pppppp
Kickstart 0 8 xyz
0 9 898989898
0 12 mmp
0 13 qty
Nov 15 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Many thanks for the help again

Here is what I get in the DEBUG window

RecordNo: 1 and Name: 'whs'
RecordNo: 8 and Name: 'Kickstart'
+++++++++++++++++

Below is actual TABLE

Name Cust_ID RecordNum Address
whs 0 1 ggggggggg
0 2 qqqqqqq
0 3 mmmmm
0 4 nnnnnnnnn
0 5 ooooooo
0 6 pppppp
Kickstart 0 8 xyz
0 9 898989898
0 12 mmp
0 13 qty
I think your problem is that you've only entered the [Name] once. Therefore there is no way to relate the other records to the [Name].

Copy the [Name] value down as below and you should find my last code will work.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Name    Cust_ID RecordNum Address
  3. whs      0     1     ggggggggg
  4. whs      0     2     qqqqqqq
  5. whs      0     3     mmmmm
  6. whs      0     4     nnnnnnnnn
  7. whs      0     5     ooooooo
  8. whs      0     6     pppppp
  9. Kickstart 0     8     xyz
  10. Kickstart 0     9     898989898
  11. Kickstart 0     12     mmp
  12. Kickstart 0     13     qty
  13.  
  14.  
  15.  
Nov 15 '06 #24

P: 35
Many thanks for the help again

Here is what I get in the DEBUG window

RecordNo: 1 and Name: 'whs'
RecordNo: 8 and Name: 'Kickstart'
+++++++++++++++++

Below is actual TABLE

Name Cust_ID RecordNum Address
whs 0 1 ggggggggg
0 2 qqqqqqq
0 3 mmmmm
0 4 nnnnnnnnn
0 5 ooooooo
0 6 pppppp
Kickstart 0 8 xyz
0 9 898989898
0 12 mmp
0 13 qty
+++++++++++++++++++++++++
Looking through the code, I find the above result is LOGICAL as no values are being entered in to the
Name field from the Combo after the initial NEW Entry.
The code is looking for a higher Record number where
the NAME is the chosen name. There is all but ONE record for each name which is the FIRST record which satisfies the criteria.

Though I found and tried a routine to do this, I have abandoned it as it populates the pull down box with
same Name making it useless to select a customer quickly. I have since used the SORT DESC method to get the empty ones to the bottom.

I guess One way is to automatically Enter a Customer ID (a short name for example) at the same time as the ORIGINAL Name in a second field (not dependant on the Combo) and use the search routine you have proposed to find the highest record number.

What do you think ?
Nov 15 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
If the customerID corresponds uniquely to the name you can use that instead. Where [NAME] appears in the queries in my code replace with CustID. Change combobox to SELECT DISTINCT CustID, Name .. etc. Set the combobox field widths to 0cm; 3cm which will hide the CustID from the user. Keep the bound field to 1 and it should work. Any problems let me know.

Mary

+++++++++++++++++++++++++
Looking through the code, I find the above result is LOGICAL as no values are being entered in to the
Name field from the Combo after the initial NEW Entry.
The code is looking for a higher Record number where
the NAME is the chosen name. There is all but ONE record for each name which is the FIRST record which satisfies the criteria.

Though I found and tried a routine to do this, I have abandoned it as it populates the pull down box with
same Name making it useless to select a customer quickly. I have since used the SORT DESC method to get the empty ones to the bottom.

I guess One way is to automatically Enter a Customer ID (a short name for example) at the same time as the ORIGINAL Name in a second field (not dependant on the Combo) and use the search routine you have proposed to find the highest record number.

What do you think ?
Nov 15 '06 #26

P: 35
If the customerID corresponds uniquely to the name you can use that instead. Where [NAME] appears in the queries in my code replace with CustID. Change combobox to SELECT DISTINCT CustID, Name .. etc. Set the combobox field widths to 0cm; 3cm which will hide the CustID from the user. Keep the bound field to 1 and it should work. Any problems let me know.

Mary
Is there a way to enter a customer ID derived from the Name automatically everytime a name is selected from the combo for a new record ?
Nov 15 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
Is there a way to enter a customer ID derived from the Name automatically everytime a name is selected from the combo for a new record ?
Use the DLookup function:

DLookup("[CustID]","Miracle_Cloth_Main","[Name]='" & Me.Name_Combo & "'")
Nov 15 '06 #28

P: 35
Is there a way to enter a customer ID derived from the Name automatically everytime a name is selected from the combo for a new record ?
For Example
Expand|Select|Wrap|Line Numbers
  1.  MyRS.AddNew      ' Create new record.
  2.          MyRS("Name") = Screen.ActiveControl
  3.     MYRS("Cust_ID")= An AUTONumber+ SHORT MyRS("Name")
  4.          MyRS.Update      ' Save changes.
  5.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  6.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  7.  
Nov 15 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry, I understand now.

You're making life difficult for yourself trying to create an alpha numeric CustID. My advice is just add an Autonumber primary key and call it CustomerID. It will populate itself for all new records. Then use that as the foreign key in any table that currently has the [Name] as the foreign key.

Using the kind of combination you're suggesting is very difficult. How do you find the max number already entered to add 1 to (which is how you code an autonumber in VBA). You would have to break it apart each time to remove the shortname portion. It's a lot more trouble than it's worth.


Expand|Select|Wrap|Line Numbers
  1. MyRS.AddNew ' Create new record.
  2. MyRS("Name") = Screen.ActiveControl
  3. MYRS("Cust_ID")= An AUTONumber+ SHORT MyRS("Name")
  4. MyRS.Update ' Save changes.
  5. MyRS.Move 0, MyRS.LastModified ' Go to new record
  6. Me.Bookmark = MyRS.Bookmark ' Go to new record
  7.  
[/quote]
Nov 15 '06 #30

P: 35
Sorry, I understand now.

You're making life difficult for yourself trying to create an alpha numeric CustID. My advice is just add an Autonumber primary key and call it CustomerID. It will populate itself for all new records. Then use that as the foreign key in any table that currently has the [Name] as the foreign key.

Using the kind of combination you're suggesting is very difficult. How do you find the max number already entered to add 1 to (which is how you code an autonumber in VBA). You would have to break it apart each time to remove the shortname portion. It's a lot more trouble than it's worth.


Expand|Select|Wrap|Line Numbers
  1. MyRS.AddNew ' Create new record.
  2. MyRS("Name") = Screen.ActiveControl
  3. MYRS("Cust_ID")= An AUTONumber+ SHORT MyRS("Name")
  4. MyRS.Update ' Save changes.
  5. MyRS.Move 0, MyRS.LastModified ' Go to new record
  6. Me.Bookmark = MyRS.Bookmark ' Go to new record
  7.  
[/quote]

OK I understand. So the Simple solution will be just to copy the NAME to the CUST ID Field

Expand|Select|Wrap|Line Numbers
  1. MyRS.AddNew      ' Create new record.
  2.          MyRS("Name") = Screen.ActiveControl
  3.     MYRS("Cust_ID")=  MyRS("Name")
  4.          MyRS.Update      ' Save changes.
  5.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  6.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
Is that a correct Syntax ?
Nov 15 '06 #31

MMcCarthy
Expert Mod 10K+
P: 14,534

OK I understand. So the Simple solution will be just to copy the NAME to the CUST ID Field
NO. The simple solution is to go to the table that MyRS is based on. Add a new field. Call it CustID (without the underscore to distinguish it) and set it's data type to Autonumber. Ideally you want to delete the existing Cust_ID field if it's not going to mess up your data too much.

Then in your code below you can ignore it completely as it is populated automatically every time a new record is added.

Expand|Select|Wrap|Line Numbers
  1. MyRS.AddNew ' Create new record.
  2. MyRS("Name") = Screen.ActiveControl
  3. MyRS.Update ' Save changes.
  4. MyRS.Move 0, MyRS.LastModified ' Go to new record
  5. Me.Bookmark = MyRS.Bookmark ' Go to new record
Nov 15 '06 #32

P: 35
NO. The simple solution is to go to the table that MyRS is based on. Add a new field. Call it CustID (without the underscore to distinguish it) and set it's data type to Autonumber. Ideally you want to delete the existing Cust_ID field if it's not going to mess up your data too much.

Then in your code below you can ignore it completely as it is populated automatically every time a new record is added.

Expand|Select|Wrap|Line Numbers
  1.  
  2. .
  3.  
  4.  
  5.  
Hello Mary,

I think I haven't made it clear.
To find the latest record you need the UNIQUE customer NAME to search and compare.
The Combo doesn't allow you to have a NAme associated with each record as technically it is not bound and doesn't fill the field.

If I then use the same NAME to bind to Customer ID (or any other field) I will have a searchable Unique VALE (Name) to find the latest record

Expand|Select|Wrap|Line Numbers
  1. I have tried the code below but get a Syntax error on 
  2.  
  3.  MyRS("Cust_ID") = MyRS("Name")
  4.  
  5.  
  6. MyRS.AddNew      ' Create new record.
  7.     MyRS("Name") = Screen.ActiveControl
  8.     MyRS("Cust_ID") = MyRS("Name")
  9.          MyRS.Update      ' Save changes.
  10.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  11.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
Nov 15 '06 #33

Expert 5K+
P: 8,434
Hi all.

Can I get an update on where this thread has reached? I have just spent some minutes reading through it, but it seems to have changed direction at least once or twice.

I believe the original question was how to have a field SUM'd across all prior records for the same ID, and provided on the form as default input value for another field. This seems quite straightforward, and should present no great difficulty for the Access masterminds here.

But now we seem to be buried in a maze of unique names and autonumbers and bound versus unbound fields, and so on. Could someone please summarise the outstanding problem, just to show that someone understands what the issue actually is? I suspect (or should I say hope :)) that I'm not the only one to have lost the plot here.
Nov 16 '06 #34

NeoPa
Expert Mod 15k+
P: 31,494
Valley,

I'm sorry to say that this thread has become so complicated that most of us (that I've spoken with) find it difficult even to understand the question any more.
It is a complicated question (or series of them) where the information is not as clear as it could be.
It has certainly reached the stage where trying to give answers is too heavy a workload for a single thread. I personally tried another shot at it tonight and I felt myself getting quite depressed at only about half way through.
I do appreciate that you've tried to provide information where requested and so this is not really about criticism - just , in the real world, it has got too complicated to deal with.

I therefore don't think it is reasonable to expect any more interaction on this thread.

Having said all that, if you would like to formulate (very carefully as this is quite a complicated problem) a new thread, with all the relevant information laid out clearly in a single place, then I'm sure we would give it another go.
We would expect :
1. You to follow the guidelines laid out herePOSTING GUIDELINES: Please read carefully before posting to a forum .
2. You to follow each response in order and deal with one thing at a time. If a problem is complicated, it nevertheless needs dealing with before progressing to another, as this makes it doubly difficult for an expert to find a sensible solution.
Nov 16 '06 #35

Post your reply

Sign in to post your reply or Sign up for a free account.