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

Here is my ComboBox question again.

P: 35
Hello,
Here I try to reform my question from the thread below
which was closed as it got too complicated.
http://www.thescripts.com/forum/thre...2674-4-10.html

The Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).

However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. whs    0    1    ggggggggg
  3.     0    2    qqqqqqq
  4.     0    3    mmmmm
  5.     0    4    nnnnnnnnn
  6.     0    5    ooooooo
  7.     0    6    pppppp
  8. Kickstart    0    8    xyz
  9.     0    9    898989898
  10.     0    12    mmp
  11.     0    13    qty
  12.  
This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)

Now the question is about getting the synatx right for the
code below to copy the Name value to Cust_ID value for each new record.

+++++++++++
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.     MyRS.AddNew      ' Create new record.
  32.     MyRS("Name") = Screen.ActiveControl
  33.     MyRS("Cust_ID") = MyRS("Name")
  34.          MyRS.Update      ' Save changes.
  35.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  36.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  37.  
  38.     'Me.Bookmark = MyRS.Bookmark
  39.  
  40.    Dim recNo As Long
  41.  
  42. ' for this to work there cannot be any RecordNumber with a value of 0
  43. ' it finds the highest record number for the name in the combo box
  44. ' and returns 0 and exits if no record found.
  45. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  46. Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
  47. If recNo = 0 Then
  48. Exit Sub
  49. End If
  50. Me.Text90.SetFocus
  51. DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  52.  
  53.  
  54.    End If
  55. Endsub:
  56.    MyRS.Close
  57.  
  58.  
  59. End Sub
+++++++++++++++++++++++

I hope this is clearer now.
Any help is greatly appreciated as always.
Nov 17 '06 #1
Share this Question
Share on Google+
30 Replies


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

I will be taking a look at this sometime - probably over the weekend now as time is short and this will probably take a fair amount.
If you've heard nothing more from me by Monday (then I've probably died or something) then bounce this thread as I've probably dropped the ball.
But expect an answer tomorrow.

-NeoPa.
Nov 17 '06 #2

NeoPa
Expert Mod 15k+
P: 31,660
I haven't died, but I was knocked off my motorcycle late last night.
I'm home from hospital, but it may involve me in a lot of forms and extra work so my time will be restricted.
Please bear with me.
Nov 18 '06 #3

PEB
Expert 100+
P: 1,418
PEB
However i don't think that Cust_ID and Name are the same data type...
MyRS("Cust_ID") = MyRS("Name")

So on those lines there should be conversion or Error as you want ;)
Nov 18 '06 #4

P: 35
However i don't think that Cust_ID and Name are the same data type...
MyRS("Cust_ID") = MyRS("Name")

So on those lines there should be conversion or Error as you want ;)
Hello,

Many thanks for the help, that was a good observation.
I have now changed the data type of Cust_ID to Text
and tweaked the code a little.

So I get the results as below

++++++++++
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. Customer        1    
  3. New Customer        2    
  4. Oldcustomer        3    
  5. Different Customer        4    
  6.     Oldcustomer    5    aaaaaaa
  7.     Oldcustomer    6    bbbbbbbbbb
  8.     Oldcustomer    7    cccccccccc
  9.     Oldcustomer    8    
  10.     New Customer    9    ddddddddddd
  11.     New Customer    10    eeeeeeeeeeee
  12.     New Customer    11    
  13.     Different Customer    12    fffffffffffffffff
  14.     Customer    13    gggggggggg
  15.     Customer    14    fffffffffffffffffff
  16.         15    jjjjjjjjjjjjjjj
  17.     Customer    16    
  18.     Oldcustomer    17    
  19.     New Customer    18    
  20.     Different Customer    19    
  21.     Customer    20    
  22.     New Customer    21    
  23.     Oldcustomer    22    
  24.     New Customer    23    
  25.     Different Customer    24    
  26.     Customer    25
+++++++++++++++++++++++
Iget the following in the DEBUG window

RecordNo: 22 and Name: 'Oldcustomer'
RecordNo: 23 and Name: 'New Customer'
RecordNo: 24 and Name: 'Different Customer'
RecordNo: 25 and Name: 'Customer'

++++++++++++++++++++++

There are two issues

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
actually goes to the NEWLY created record insted of the PREVIOUS (Last) record.

If I enter a NEW data a second time (Or subsequent entries) the value doesn't get entered in the Cust_ID field
(Same behaviour as Combo)

So we need a means to ensure there are no empty fields in Cust_ID column.
Nov 19 '06 #5

P: 35
Hello,

Many thanks for the help, that was a good observation.
I have now changed the data type of Cust_ID to Text
and tweaked the code a little.

So I get the results as below

++++++++++
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. Customer        1    
  3. New Customer        2    
  4. Oldcustomer        3    
  5. Different Customer        4    
  6.     Oldcustomer    5    aaaaaaa
  7.     Oldcustomer    6    bbbbbbbbbb
  8.     Oldcustomer    7    cccccccccc
  9.     Oldcustomer    8    
  10.     New Customer    9    ddddddddddd
  11.     New Customer    10    eeeeeeeeeeee
  12.     New Customer    11    
  13.     Different Customer    12    fffffffffffffffff
  14.     Customer    13    gggggggggg
  15.     Customer    14    fffffffffffffffffff
  16.         15    jjjjjjjjjjjjjjj
  17.     Customer    16    
  18.     Oldcustomer    17    
  19.     New Customer    18    
  20.     Different Customer    19    
  21.     Customer    20    
  22.     New Customer    21    
  23.     Oldcustomer    22    
  24.     New Customer    23    
  25.     Different Customer    24    
  26.     Customer    25
+++++++++++++++++++++++
Iget the following in the DEBUG window

RecordNo: 22 and Name: 'Oldcustomer'
RecordNo: 23 and Name: 'New Customer'
RecordNo: 24 and Name: 'Different Customer'
RecordNo: 25 and Name: 'Customer'

++++++++++++++++++++++

There are two issues

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
actually goes to the NEWLY created record insted of the PREVIOUS (Last) record.

If I enter a NEW data a second time (Or subsequent entries) the value doesn't get entered in the Cust_ID field
(Same behaviour as Combo)

So we need a means to ensure there are no empty fields in Cust_ID column.

Sorry, I forgot to post my CURRENT code

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.     MyRS.AddNew      ' Create new record.
  32.     'MyRS("Name") = Screen.ActiveControl
  33.     MyRS("Cust_ID") = Screen.ActiveControl
  34.          MyRS.Update      ' Save changes.
  35.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  36.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  37.  
  38.     'Me.Bookmark = MyRS.Bookmark
  39.  
  40.    Dim recNo As Long
  41.  
  42. ' for this to work there cannot be any RecordNumber with a value of 0
  43. ' it finds the highest record number for the name in the combo box
  44. ' and returns 0 and exits if no record found.
  45. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  46. Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
  47. If recNo = 0 Then
  48. Exit Sub
  49. End If
  50. Me.Text90.SetFocus
  51. DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  52.  
  53.  
  54.    End If
  55. Endsub:
  56.    MyRS.Close
  57.  
  58.  
  59. End Sub
Nov 19 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Hi

Have you tried in the condition of this line to include a criteria that excludes the record that you are editing? Just here:

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)

If you have in the record a primary Key like ID it should be:

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [ID]<>" & str(Me![ID])), 0)

For the second one issue haven't tought yet!

Sorry, I forgot to post my CURRENT code

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.     MyRS.AddNew      ' Create new record.
  32.     'MyRS("Name") = Screen.ActiveControl
  33.     MyRS("Cust_ID") = Screen.ActiveControl
  34.          MyRS.Update      ' Save changes.
  35.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  36.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  37.  
  38.     'Me.Bookmark = MyRS.Bookmark
  39.  
  40.    Dim recNo As Long
  41.  
  42. ' for this to work there cannot be any RecordNumber with a value of 0
  43. ' it finds the highest record number for the name in the combo box
  44. ' and returns 0 and exits if no record found.
  45. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  46. Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
  47. If recNo = 0 Then
  48. Exit Sub
  49. End If
  50. Me.Text90.SetFocus
  51. DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  52.  
  53.  
  54.    End If
  55. Endsub:
  56.    MyRS.Close
  57.  
  58.  
  59. End Sub
Nov 19 '06 #7

P: 35
Hi

Have you tried in the condition of this line to include a criteria that excludes the record that you are editing? Just here:

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)

If you have in the record a primary Key like ID it should be:

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [ID]<>" & str(Me![ID])), 0)

For the second one issue haven't tought yet!
Many thanks

I have RecordNum as primary key

I have tried

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)
But It doesn't work.
I now have

Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. customer    customer    1    aaaaaaaaaa
  3.         2    bbbbbbbbbbb
  4.         3    cccccccccc
  5. NEWcustomer    NEWcustomer    4    ddddddddddd
  6.         5    hhhhhhhhhh
  7.         6    jjjjjjjjjjjjjjjjj
  8. OLDcustomer    OLDcustomer    7    kkkkkkkkkkk
  9.         8    rrrrrrrrrrrrrrrr
  10.     customer    9    mmmmmmmm
  11. Goodcustomer    Goodcustomer    10    pppppppp
  12.         11    mmmmmm
  13.     NEWcustomer    12    ttttttttttttttttt
  14.         13    kkkkkkkkkkllllllllllll
  15. ANYcustomer    ANYcustomer    14    
and in the DEBUG window
RecordNo: 1 and Name: 'customer'
RecordNo: 1 and CustID: 'customer'
RecordNo: 4 and Name: 'NEWcustomer'
RecordNo: 4 and CustID: 'NEWcustomer'

Here is the NEW CODE

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("Cust_ID") = Screen.ActiveControl
  25.          MyRS.Update      ' Save changes.
  26.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  27.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  28.    Else
  29.          GoTo Endsub
  30.    End If
  31.    Else
  32.     MyRS.AddNew      ' Create new record.
  33.     'MyRS("Name") = Screen.ActiveControl
  34.     MyRS("Cust_ID") = Screen.ActiveControl
  35.          MyRS.Update      ' Save changes.
  36.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  37.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  38.  
  39.     'Me.Bookmark = MyRS.Bookmark
  40.  
  41.    Dim recNo As Long
  42.  
  43. ' for this to work there cannot be any RecordNumber with a value of 0
  44. ' it finds the highest record number for the name in the combo box
  45. ' and returns 0 and exits if no record found.
  46. 'recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  47.  
  48. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)
  49.  
  50. 'MyRS.FindLast "[RecordNum] = " Me.Cust_ID, 0)
  51.  
  52. Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
  53.  
  54. Debug.Print "RecordNo: " & recNo & " and CustID: '" & Me.Cust_ID & "'"
  55.  
  56.  
  57. If recNo = 0 Then
  58. Exit Sub
  59. End If
  60. Me.Text90.SetFocus
  61. DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  62.  
  63.  
  64.    End If
  65. Endsub:
  66.    MyRS.Close
  67.  
  68.  
  69. End Sub
Nov 19 '06 #8

P: 35
I wonder if there is a way to modify and get this code
actually created by the WIZARD for the Combo by
using the FindLast built in function
Expand|Select|Wrap|Line Numbers
  1.  ' Find the record that matches the control.
  2.     Dim rs As Object
  3.  
  4.     Set rs = Me.Recordset.Clone
  5.     rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo107], 0))
  6.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Nov 19 '06 #9

PEB
Expert 100+
P: 1,418
PEB
About

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)

What is the error that is shown?

In fact i've lost what was the goal of all this code:

What i know for the moment:

-You have a form with a combho with After Update Function
-When a value is introduced the combo shold place the form on the respective record
-In same time some information should be updated i didn't understand for what goals.. - maybe this concerns only the new records???

I'm affraid to tel you but this info can't organize something that can help you... My previous interventions were due to general checking of your code and general ideas about what you can want, but i wan to know:

-this combo what functions has... Where get data from and where put this data to /new record edited record/ and why???

Sorry to be so curious but to be helpfull i must understand what i'm doing...

And one suggestion... The concept of the database is 70% of the work! The next 30% are technical work!

So work about the concept!
Nov 19 '06 #10

P: 35
About

recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)

What is the error that is shown?

In fact i've lost what was the goal of all this code:

What i know for the moment:




-You have a form with a combho with After Update Function
-When a value is introduced the combo shold place the form on the respective record
-In same time some information should be updated i didn't understand for what goals.. - maybe this concerns only the new records???

I'm affraid to tel you but this info can't organize something that can help you... My previous interventions were due to general checking of your code and general ideas about what you can want, but i wan to know:

-this combo what functions has... Where get data from and where put this data to /new record edited record/ and why???

Sorry to be so curious but to be helpfull i must understand what i'm doing...

And one suggestion... The concept of the database is 70% of the work! The next 30% are technical work!

So work about the concept!
Many thanks for your time and attention.

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

I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total

Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).

However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty

This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)

That explains the purpose of all this.
Nov 19 '06 #11

NeoPa
Expert Mod 15k+
P: 31,660
Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).

However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty

This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)

That explains the purpose of all this.
Valley,

I know you've had a torrid time on here trying to get resolutions to your problems, but I think you need to do as PEB suggests and take another look at your concept/design. From what you've posted (quoted above) it seems clear that your system really needs a rethink as this is not how to do things in an RDBMS.

I suggest you take a look here (How to structure your tables). This is found in our Links to useful sites sticky at the top of the Access forum.
Another possibly helpful one is Tutorials and General Help.
Nov 19 '06 #12

PEB
Expert 100+
P: 1,418
PEB
You want to say that from your shops your data arrives with first record filled in and for the next records there is no information and for theu it's assumed that it's the value of the first record of the respective shop?


But your data is imported from Excel or this is the introduction metod in Access?

I don't think this as normal..

Instaed doing this it should be better to correct this and fill the field Name for all records..

If it is the problem sure there is better solutions than your combo!

I think this is you major problem if you resolve it you can do what you want on your records!

PLS confirm that you want to work on this problem?
Nov 19 '06 #13

P: 35
You want to say that from your shops your data arrives with first record filled in and for the next records there is no information and for theu it's assumed that it's the value of the first record of the respective shop?


But your data is imported from Excel or this is the introduction metod in Access?

I don't think this as normal..

Instaed doing this it should be better to correct this and fill the field Name for all records..

If it is the problem sure there is better solutions than your combo!

I think this is you major problem if you resolve it you can do what you want on your records!

PLS confirm that you want to work on this problem?
Hello PEB and other experts of this FORUM,

What I am trying to do is simple and use just one TABLE and ONE FORM . Nothing complicated. I am really amazed how a simple thing can become complicated like this.
I mentioned that our sales people go to a new shop with a PDA and if it is the first visit, simply pull down and enter the shop and stock details in to a combo box and a few other MUMERIC fields like stock and prices.
They go for a second visit a month later, pull down the data by selecting the customer NAME from the COMBOlist with the data from the LAST visit, Update it with NEW data from the current visit and SAVE the record.
Can any RDBS be simpler than this ?
Nov 19 '06 #14

NeoPa
Expert Mod 15k+
P: 31,660
Just to log that I've read your post and I'm not just ignoring it.
Clearly you have little confidence in my responses, which is absolutely your right and I have no problem with that.
However, I don't think I can be of any further help in the circumstances.
Nov 19 '06 #15

PEB
Expert 100+
P: 1,418
PEB
So

FIRST
In RDBS in each record there is assumed to have uniques values! Say for your table that RecordNo can be considered like a primary key with unique value!
However i don't find for normal to have customer Name and Customer Id reapeted each time in your table... They should be in a table a part with primary keyCustID. Only the CustID should participate in your table with this information. And it should be OBLIGATORY FILLED IN IF WE SPEEK FOR RDBS! All details about your Customer like Name and detailed information should be in the table with your Customers...

Second
Once when you do this, you should correct your combo box Source row: Now it doesnt displays the latest records by product and customer, it displays all!

U can do the following SQL for your combo:

SELECT TOP1 CustID, ProductID
FROM MYTABLE
GROUP BY CustID, ProductID
ORDER RecNo DESC;

Than you can set for Control Source of your Combo CustID and Set Column Count and column width properties

If you want to update any fields in your form with information of your combo box use in after Update code like this:

Me![MyField]=Me![MyCombo].Column(1)

And I can say that it's all the solution of your problem! It is easy when you express clear what do you need!

In Bg there was a woman that had the ability to read minds... But here we don't have such abilities..

Thanks for your posts ans pls begin your post with what you tell us at the end!

Hello PEB and other experts of this FORUM,

What I am trying to do is simple and use just one TABLE and ONE FORM . Nothing complicated. I am really amazed how a simple thing can become complicated like this.
I mentioned that our sales people go to a new shop with a PDA and if it is the first visit, simply pull down and enter the shop and stock details in to a combo box and a few other MUMERIC fields like stock and prices.
They go for a second visit a month later, pull down the data by selecting the customer NAME from the COMBOlist with the data from the LAST visit, Update it with NEW data from the current visit and SAVE the record.
Can any RDBS be simpler than this ?
Nov 19 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
If you want the previous record then move this statement up the code to before the addnew record event

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  2.  
Nov 19 '06 #17

P: 35
If you want the previous record then move this statement up the code to before the addnew record event

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
  2.  
Hello Mary,

Many thanks for your reply.

You are right. The code to COPY the NAME field is in the
wrong place now. But it works. It will be moved to just before SAVE somewhere later.

You code seemed to work before and Navigate to the LAST record but doesn't NOW.
I have changed it like this now . The form doesn't give an error, but doesn't go to the correct record.

Expand|Select|Wrap|Line Numbers
  1. MyRS.FindLast Criteria
  2.    If MyRS.NoMatch Then
  3.  
  4.    Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
  5.    If Response = IDYES Then
  6.          MyRS.AddNew      ' Create new record.
  7.          MyRS("Name") = Screen.ActiveControl
  8.          MyRS("Cust_ID") = Screen.ActiveControl
  9.          MyRS.Update      ' Save changes.
  10.          MyRS.Move 0, MyRS.LastModified    ' Go to new record
  11.          Me.Bookmark = MyRS.Bookmark    ' Go to new record
  12.    Else
  13.          GoTo Endsub
  14.    End If
  15.    Else
  16.    'recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "'"), 0)
  17.    recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)
  18.  
  19.     Me.Text90.SetFocus
  20.     DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
  21.     Me.Text90.SetFocus
  22.  
  23.     'MyRS.AddNew      ' Create new record.
  24.     'MyRS("Name") = Screen.ActiveControl
  25.     'MyRS("Cust_ID") = Screen.ActiveControl
  26.     'MyRS.Update      ' Save changes.
  27.     'MyRS.Move 0, MyRS.LastModified    ' Go to new record
  28.     'Me.Bookmark = MyRS.Bookmark    ' Go to new record
I have manually populated the TABLE for trial now.
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. Customer    Customer    1    AAAAAAAA
  3.     Customer    2    BBBBBBBBB
  4.     Customer    3    CCCCCCCCCC
  5.     Customer    4    DDDDDDDD
  6. NEWcustomer    NEWcustomer    5    pppppp
  7.     NEWcustomer    16    qqqqqqqq
  8.     NEWcustomer    17    rrrrrrrrrrrrr
  9.     NEWcustomer    18    ssssssssss
  10. SAMEcustomer    SAMEcustomer    19    ttttttttttttt
  11.     SAMEcustomer    20    uuuuuuuuu
  12.     SAMEcustomer    21    vvvvvvvvv
  13.     SAMEcustomer    22    wwwwwwww
  14. ANYcustomer    ANYcustomer    23    xxxxxxx
  15.     ANYcustomer    24    yyyyyyyy
  16.     ANYcustomer    25    zzzzzzzzz
  17. Somecustomer    Somecustomer    26    mmmmmm
  18.     Somecustomer    27    nnnnnn
  19.     Customer    28    ooooo
  20.     NEWcustomer    29    kkkkkkkkkkk
Many thanks again for your help.
Nov 20 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>'" & CStr(Me![RecordNum])), 0) & "'"
recNo is a string variable?
Nov 20 '06 #19

P: 35
Try this ...

Expand|Select|Wrap|Line Numbers
  1. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>'" & CStr(Me![RecordNum])), 0) & "'"
recNo is a string variable?
Many thanks.

RecNo is defined as Long
I get the Error 3075
Syntax Error in Querry Expression
'[Cust_Id]'='Samecustomer'AND [recordNum]<>1
Nov 20 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
Many thanks.

RecNo is defined as Long
I get the Error 3075
Syntax Error in Querry Expression
'[Cust_Id]'='Samecustomer'AND [recordNum]<>1
Did you copy it in exactly, it looks like you're missing a space before AND and that you've put single quotes around cust_id as well?
Nov 20 '06 #21

P: 35
Did you copy it in exactly, it looks like you're missing a space before AND and that you've put single quotes around cust_id as well?
I had to type in the ERROR as it is in a message Box
Nov 20 '06 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
I had to type in the ERROR as it is in a message Box
I understand but you had quotes around the '[Cust_ID]'. Was that just a typing error or do they show in the error. It's important

Mary
Nov 20 '06 #23

P: 35
I had to type in the ERROR as it is in a message Box
Syntax Error in Querry Expression'[Cust_Id]'='Samecustomer'AND [recordNum]<>'1'

NOte the single quote around 1
Nov 20 '06 #24

P: 35
Syntax Error in Querry Expression'[Cust_Id]'='Samecustomer'AND [recordNum]<>'1'

NOte the single quote around 1
Here is the EXACT error

Syntax Error in Querry Expression'[Cust_Id]='Samecustomer' AND [recordNum]<>'1'
Nov 20 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
Here is the EXACT error

Syntax Error in Querry Expression'[Cust_Id]='Samecustomer' AND [recordNum]<>'1'
Ok two things:

Is [Cust_Id] a string/text value?
Is recordNum a string/text value?

Mary
Nov 20 '06 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok two things:

Is [Cust_Id] a string/text value?
Is recordNum a string/text value?

Mary
BTW, I wasn't trying to be pedantic. The clue to the problem in these cases is almost always found by examining the exact output of the error.

Mary
Nov 20 '06 #27

P: 35
BTW, I wasn't trying to be pedantic. The clue to the problem in these cases is almost always found by examining the exact output of the error.

Mary

By all means. U R welcome Mary to ask any info you need.

CUSt_ID is text
RecorNum is Number
Nov 20 '06 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
By all means. U R welcome Mary to ask any info you need.

CUSt_ID is text
RecorNum is Number
OK

Try this ....

Expand|Select|Wrap|Line Numbers
  1.  recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>" & Me.RecordNum), 0)
Nov 20 '06 #29

P: 35
OK

Try this ....

Expand|Select|Wrap|Line Numbers
  1.  recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>" & Me.RecordNum), 0)

Hello Mary,
Looking at the DEBUG results and the table it seems to be getting the search right and find the last record.
However it doesn't update the fields in the Form to the found record. The CUST_ID stays on 1and Brings up
CustID as customer

Here is the DEBUG results

RecordNo: 22 and Name: 'SAMEcustomer'
RecordNo: 22 and CustID: 'Customer'
RecordNo: 25 and Name: 'ANYcustomer'
RecordNo: 25 and CustID: 'Customer'
RecordNo: 29 and Name: 'NEWcustomer'
RecordNo: 29 and CustID: 'Customer'
RecordNo: 27 and Name: 'Somecustomer'
RecordNo: 27 and CustID: 'Customer'
RecordNo: 28 and Name: 'Customer'
RecordNo: 28 and CustID: 'Customer'
RecordNo: 25 and Name: 'ANYcustomer'
RecordNo: 25 and CustID: 'Customer'

Here is the table
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. Customer    Customer    1    AAAAAAAA
  3.     Customer    2    BBBBBBBBB
  4.     Customer    3    CCCCCCCCCC
  5.     Customer    4    DDDDDDDD
  6. NEWcustomer    NEWcustomer    5    pppppp
  7.     NEWcustomer    16    qqqqqqqq
  8.     NEWcustomer    17    rrrrrrrrrrrrr
  9.     NEWcustomer    18    ssssssssss
  10. SAMEcustomer    SAMEcustomer    19    ttttttttttttt
  11.     SAMEcustomer    20    uuuuuuuuu
  12.     SAMEcustomer    21    vvvvvvvvv
  13.     SAMEcustomer    22    wwwwwwww
  14. ANYcustomer    ANYcustomer    23    xxxxxxx
  15.     ANYcustomer    24    yyyyyyyy
  16.     ANYcustomer    25    zzzzzzzzz
  17. Somecustomer    Somecustomer    26    mmmmmm
  18.     Somecustomer    27    nnnnnn
  19.     Customer    28    ooooo
  20.     NEWcustomer    29    kkkkkkkkkkk
Nov 20 '06 #30

MMcCarthy
Expert Mod 10K+
P: 14,534
Check ...

Text90 is the control that has its control source set to RecordNum

If not, that is the control you need to set focus to before the DoCmd.FindRecord.


Hello Mary,
Looking at the DEBUG results and the table it seems to be getting the search right and find the last record.
However it doesn't update the fields in the Form to the found record. The CUST_ID stays on 1and Brings up
CustID as customer

Here is the DEBUG results

RecordNo: 22 and Name: 'SAMEcustomer'
RecordNo: 22 and CustID: 'Customer'
RecordNo: 25 and Name: 'ANYcustomer'
RecordNo: 25 and CustID: 'Customer'
RecordNo: 29 and Name: 'NEWcustomer'
RecordNo: 29 and CustID: 'Customer'
RecordNo: 27 and Name: 'Somecustomer'
RecordNo: 27 and CustID: 'Customer'
RecordNo: 28 and Name: 'Customer'
RecordNo: 28 and CustID: 'Customer'
RecordNo: 25 and Name: 'ANYcustomer'
RecordNo: 25 and CustID: 'Customer'

Here is the table
Expand|Select|Wrap|Line Numbers
  1. Name    Cust_ID    RecordNum    Address
  2. Customer    Customer    1    AAAAAAAA
  3.     Customer    2    BBBBBBBBB
  4.     Customer    3    CCCCCCCCCC
  5.     Customer    4    DDDDDDDD
  6. NEWcustomer    NEWcustomer    5    pppppp
  7.     NEWcustomer    16    qqqqqqqq
  8.     NEWcustomer    17    rrrrrrrrrrrrr
  9.     NEWcustomer    18    ssssssssss
  10. SAMEcustomer    SAMEcustomer    19    ttttttttttttt
  11.     SAMEcustomer    20    uuuuuuuuu
  12.     SAMEcustomer    21    vvvvvvvvv
  13.     SAMEcustomer    22    wwwwwwww
  14. ANYcustomer    ANYcustomer    23    xxxxxxx
  15.     ANYcustomer    24    yyyyyyyy
  16.     ANYcustomer    25    zzzzzzzzz
  17. Somecustomer    Somecustomer    26    mmmmmm
  18.     Somecustomer    27    nnnnnn
  19.     Customer    28    ooooo
  20.     NEWcustomer    29    kkkkkkkkkkk
Nov 20 '06 #31

Post your reply

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