Here is my ComboBox question again.

[valley]

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.

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
 

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.

+++++++++++

Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
   ' If the matching record is found, the procedure goes to it.
   ' If the record isn't found, the focus stays on the current record.
 
   Dim Criteria As String       ' This is the argument to the FindFirst method.
   Dim MyRS As Recordset        ' Recordset used to search.
   Dim ComboName As String    ' The name of the company to search for.
   Const IDYES = 6
 
   Set MyRS = Me.RecordsetClone
   ' Build the criteria.
   ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
   Criteria = "[Name]=" & ComboName
   ' Perform the search.
 
   MyRS.FindLast Criteria
   If MyRS.NoMatch Then
 
   Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
   If Response = IDYES Then
         MyRS.AddNew      ' Create new record.
         MyRS("Name") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
   Else
         GoTo Endsub
   End If
   Else
    MyRS.AddNew      ' Create new record.
    MyRS("Name") = Screen.ActiveControl
    MyRS("Cust_ID") = MyRS("Name")
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
 
    'Me.Bookmark = MyRS.Bookmark
 
   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", "[Cust_ID]='" & Me.Cust_ID & "'"), 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
 
 
   End If
Endsub:
   MyRS.Close
 
 
End Sub

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

I hope this is clearer now.
Any help is greatly appreciated as always.

[NeoPa]

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.

[NeoPa]

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.

[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 ;)

[valley]

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

++++++++++

Name    Cust_ID    RecordNum    Address
Customer        1    
New Customer        2    
Oldcustomer        3    
Different Customer        4    
    Oldcustomer    5    aaaaaaa
    Oldcustomer    6    bbbbbbbbbb
    Oldcustomer    7    cccccccccc
    Oldcustomer    8    
    New Customer    9    ddddddddddd
    New Customer    10    eeeeeeeeeeee
    New Customer    11    
    Different Customer    12    fffffffffffffffff
    Customer    13    gggggggggg
    Customer    14    fffffffffffffffffff
        15    jjjjjjjjjjjjjjj
    Customer    16    
    Oldcustomer    17    
    New Customer    18    
    Different Customer    19    
    Customer    20    
    New Customer    21    
    Oldcustomer    22    
    New Customer    23    
    Different Customer    24    
    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

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.

[valley]

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

++++++++++

Name    Cust_ID    RecordNum    Address
Customer        1    
New Customer        2    
Oldcustomer        3    
Different Customer        4    
    Oldcustomer    5    aaaaaaa
    Oldcustomer    6    bbbbbbbbbb
    Oldcustomer    7    cccccccccc
    Oldcustomer    8    
    New Customer    9    ddddddddddd
    New Customer    10    eeeeeeeeeeee
    New Customer    11    
    Different Customer    12    fffffffffffffffff
    Customer    13    gggggggggg
    Customer    14    fffffffffffffffffff
        15    jjjjjjjjjjjjjjj
    Customer    16    
    Oldcustomer    17    
    New Customer    18    
    Different Customer    19    
    Customer    20    
    New Customer    21    
    Oldcustomer    22    
    New Customer    23    
    Different Customer    24    
    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

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

Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
   ' If the matching record is found, the procedure goes to it.
   ' If the record isn't found, the focus stays on the current record.
 
   Dim Criteria As String       ' This is the argument to the FindFirst method.
   Dim MyRS As Recordset        ' Recordset used to search.
   Dim ComboName As String    ' The name of the company to search for.
   Const IDYES = 6
 
   Set MyRS = Me.RecordsetClone
   ' Build the criteria.
   ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
   Criteria = "[Name]=" & ComboName
   ' Perform the search.
 
   MyRS.FindLast Criteria
   If MyRS.NoMatch Then
 
   Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
   If Response = IDYES Then
         MyRS.AddNew      ' Create new record.
         MyRS("Name") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
   Else
         GoTo Endsub
   End If
   Else
    MyRS.AddNew      ' Create new record.
    'MyRS("Name") = Screen.ActiveControl
    MyRS("Cust_ID") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
 
    'Me.Bookmark = MyRS.Bookmark
 
   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", "[Cust_ID]='" & Me.Cust_ID & "'"), 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
 
 
   End If
Endsub:
   MyRS.Close
 
 
End Sub

[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

Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
   ' If the matching record is found, the procedure goes to it.
   ' If the record isn't found, the focus stays on the current record.
 
   Dim Criteria As String       ' This is the argument to the FindFirst method.
   Dim MyRS As Recordset        ' Recordset used to search.
   Dim ComboName As String    ' The name of the company to search for.
   Const IDYES = 6
 
   Set MyRS = Me.RecordsetClone
   ' Build the criteria.
   ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
   Criteria = "[Name]=" & ComboName
   ' Perform the search.
 
   MyRS.FindLast Criteria
   If MyRS.NoMatch Then
 
   Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
   If Response = IDYES Then
         MyRS.AddNew      ' Create new record.
         MyRS("Name") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
   Else
         GoTo Endsub
   End If
   Else
    MyRS.AddNew      ' Create new record.
    'MyRS("Name") = Screen.ActiveControl
    MyRS("Cust_ID") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
 
    'Me.Bookmark = MyRS.Bookmark
 
   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", "[Cust_ID]='" & Me.Cust_ID & "'"), 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
 
 
   End If
Endsub:
   MyRS.Close
 
 
End Sub

[valley]

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

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

Name    Cust_ID    RecordNum    Address
customer    customer    1    aaaaaaaaaa
        2    bbbbbbbbbbb
        3    cccccccccc
NEWcustomer    NEWcustomer    4    ddddddddddd
        5    hhhhhhhhhh
        6    jjjjjjjjjjjjjjjjj
OLDcustomer    OLDcustomer    7    kkkkkkkkkkk
        8    rrrrrrrrrrrrrrrr
    customer    9    mmmmmmmm
Goodcustomer    Goodcustomer    10    pppppppp
        11    mmmmmm
    NEWcustomer    12    ttttttttttttttttt
        13    kkkkkkkkkkllllllllllll
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

Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
   ' If the matching record is found, the procedure goes to it.
   ' If the record isn't found, the focus stays on the current record.
 
   Dim Criteria As String       ' This is the argument to the FindFirst method.
   Dim MyRS As Recordset        ' Recordset used to search.
   Dim ComboName As String    ' The name of the company to search for.
   Const IDYES = 6
 
   Set MyRS = Me.RecordsetClone
   ' Build the criteria.
   ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
   Criteria = "[Name]=" & ComboName
   ' Perform the search.
 
   MyRS.FindLast Criteria
   If MyRS.NoMatch Then
 
   Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
   If Response = IDYES Then
         MyRS.AddNew      ' Create new record.
         MyRS("Name") = Screen.ActiveControl
         MyRS("Cust_ID") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
   Else
         GoTo Endsub
   End If
   Else
    MyRS.AddNew      ' Create new record.
    'MyRS("Name") = Screen.ActiveControl
    MyRS("Cust_ID") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
 
    'Me.Bookmark = MyRS.Bookmark
 
   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", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
 
recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)
 
'MyRS.FindLast "[RecordNum] = " Me.Cust_ID, 0)
 
Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
 
Debug.Print "RecordNo: " & recNo & " and CustID: '" & Me.Cust_ID & "'"
 
 
If recNo = 0 Then
Exit Sub
End If
Me.Text90.SetFocus
DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
 
 
   End If
Endsub:
   MyRS.Close
 
 
End Sub

[valley]

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

 ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo107], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[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!

[valley]

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.

[NeoPa]

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.

[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?

[valley]

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 ?

[NeoPa]

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.

[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 ?

[MMcCarthy]

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

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

[valley]

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

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

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.

MyRS.FindLast Criteria
   If MyRS.NoMatch Then
 
   Response = MsgBox("Could not find the Supplier Name: " & ComboName & "  Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
   If Response = IDYES Then
         MyRS.AddNew      ' Create new record.
         MyRS("Name") = Screen.ActiveControl
         MyRS("Cust_ID") = Screen.ActiveControl
         MyRS.Update      ' Save changes.
         MyRS.Move 0, MyRS.LastModified    ' Go to new record
         Me.Bookmark = MyRS.Bookmark    ' Go to new record
   Else
         GoTo Endsub
   End If
   Else
   'recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "'"), 0)
   recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Screen.ActiveControl & "' AND [RecordNum]<>" & Str(Me![RecordNum])), 0)
 
    Me.Text90.SetFocus
    DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
    Me.Text90.SetFocus
 
    'MyRS.AddNew      ' Create new record.
    'MyRS("Name") = Screen.ActiveControl
    'MyRS("Cust_ID") = Screen.ActiveControl
    'MyRS.Update      ' Save changes.
    'MyRS.Move 0, MyRS.LastModified    ' Go to new record
    'Me.Bookmark = MyRS.Bookmark    ' Go to new record

I have manually populated the TABLE for trial now.

Name    Cust_ID    RecordNum    Address
Customer    Customer    1    AAAAAAAA
    Customer    2    BBBBBBBBB
    Customer    3    CCCCCCCCCC
    Customer    4    DDDDDDDD
NEWcustomer    NEWcustomer    5    pppppp
    NEWcustomer    16    qqqqqqqq
    NEWcustomer    17    rrrrrrrrrrrrr
    NEWcustomer    18    ssssssssss
SAMEcustomer    SAMEcustomer    19    ttttttttttttt
    SAMEcustomer    20    uuuuuuuuu
    SAMEcustomer    21    vvvvvvvvv
    SAMEcustomer    22    wwwwwwww
ANYcustomer    ANYcustomer    23    xxxxxxx
    ANYcustomer    24    yyyyyyyy
    ANYcustomer    25    zzzzzzzzz
Somecustomer    Somecustomer    26    mmmmmm
    Somecustomer    27    nnnnnn
    Customer    28    ooooo
    NEWcustomer    29    kkkkkkkkkkk

Many thanks again for your help.

[MMcCarthy]

Try this ...

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

recNo is a string variable?

[valley]

Try this ...

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

[MMcCarthy]

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?

[valley]

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

[MMcCarthy]

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

[valley]

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

[valley]

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'

[MMcCarthy]

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

[MMcCarthy]

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

[valley]

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

[MMcCarthy]

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

CUSt_ID is text
RecorNum is Number

OK

Try this ....

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