Entering data using ComboBox for search.

[valley]

Hello,

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

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.

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)


Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down .
(I have tried a mehod which entered the values for
all records but this clutter up the Pull down with SAME
Name for repeated records making the PULL DOWN unusable.)

I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.

+++++++++++
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") = 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

+++++++++++++++++++++++
The question is is there an easier way to
achieve the summing function ?

Any help is greatly appreciated as always.

--------------------------------------------------------------------------------

 

[valley]

Hello,

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

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.

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)


Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down .
(I have tried a mehod which entered the values for
all records but this clutter up the Pull down with SAME
Name for repeated records making the PULL DOWN unusable.)

I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.

+++++++++++
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") = 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

+++++++++++++++++++++++
The question is is there an easier way to
achieve the summing function ?

Any help is greatly appreciated as always.

--------------------------------------------------------------------------------

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 ?

 

[uplate]

Can you explain why the name field is blank? Shouldn't each record contain the name of the shop, since that is who the data applies to? It looks like you've kept the name field blank to increase readability. If so, that sort of formatting should be done in a query or report based on the table, not in the table itself.

I think you've made the right choice in using a combo box to select names, and I don't believe the choice of a combo box is causing your problem. I suspect the problem is due to ... wait a second. Are you using the main table as the source for the combo box? If so, that's your problem. Let me know if that's the case, and I'll tell you how to fix it. If not, then how are you populating the choices in the combo box?

 

[valley]

Many thanks for your help.

THe COMBO has only one purpose, to Enter a NEW Customer record and choose the CUSTOMER you are visiting. So far it is working. It is not the MAIN field for the whole table. So keeping it empty except for a new entry doesn't matter and help to keep the pull down neat.

The CUST_ID field has the name of the CUSTOMER visited which is the MAIN value for all searches.

I try to keep it simple as it has to work on a PDA.
There is only ONE table and the DATA is organised by VISITS

I agree that the NAME COPY code is in the wrong place NOW, will be moved just before SAVE later on

Here is a sample.

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

As the search doesn't seem to work , is it possible to adapt the ACCESS native combo code
below to achive the same using CUST_ID values instead of the COMBO values.?

Code:
--------------------------------------------------------------------------------

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

 

[gemma-the-husky]

I don't quite follow what you are trying to do. can you explain again please?

it sounds like for a given customer, you are trying to create a total of all the stock they have purchased.

It is awkward to do this with a single form, as you only see items one at a time.

Instead of doing what you are doing, try a continuous form with a few selected columns.(you can change this form into a continuous form, but the layout will look awkward). You will need to filter the form for each customer.

select the form type in forms properties

With a continuous form, you will see all the visits you have made together, and you can easily produce a total. You can get a similar effect with a datasheet layout, but its hard to get at the totals.

 

[uplate]

Ignoring the simplification of summing for the moment...

If you're trying to simplify the combo box, then simply set its record source to something like:

SELECT DISTINCT Cust_ID FROM <YourTableNameHere> ORDER BY Cust_ID;

Then you can actually delete the Name field from the table.

It seems like you've been thinking that the combo box will essentially be a column from the table - and therefore have as many values as there are rows in the main table. This has been creating one of two problems - either the problem of a sparsely populated combo box (using the Name field) or the problem of a combo box with lots of duplications (using the Cust_ID field).

If that's what you've been thinking then there is good news. (If you weren't thinking that then I apologize for misunderstanding.) The combo box is only used to pick a customer, so it should be populated by a simple unique listing of customer names, not by a column from the table. You really don't need the Name field at all - just use the DISTINCT clause on the Cust_ID field and this will prevent duplicates in the Cust_ID field from repeating when presented in the combo box record source.

There's another possibility - it's possible that you have constraints imposed by the PDA target platform that I'm unaware of. The problem seems quite basic for a full version of Access, but I'm not knowledgeable about PDA development. Are you familiar with development for PC-based Access? If so, what constraints does developing for a PDA target impose on this problem that don't exist on a PC platform? Knowing that would help me understand why this situation is problematic.

 

[valley]

Hello,
No problem, I shall explain again.
The application needs to run on PDAS(Handheld) and be synchronised with a desktop.
So I keep everything simple, just one table and one form.
I use a ComboBox with some extra code (other than generated by the wizard) to enter a NEW Customer Name and later pull down the Customer Name to present the latest customer record (in all fields of the form)
So when I make a return visit I just pull down the customer and can see what happened during the last visit.
I have the COMBO Name entry working as I need.

I simply have a routine to copy the NAME to a second field CUST_ID in the form so that all records can be referenced to a customer Name.(Cust_ID)
This also work correctly.
I now need a function to go to that last record by searching the CUST_ID field with the NAME value from the combo when I pull down a Name from the Combo.

The Wizard creates this code to enable that using the combo.
I just need to modify the code to use the CUST_ID field insted.


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

Is that too much to ask for ?
Best regards and many thanks in advance.

 

[valley]

Hello Uplate,

Many thanks for your expert guidance. If someone would have told me about DISTINCT clause. I could have saved a week trying to copy to a second field.
The main reason I wanted to do this was because of cluttering of the Combo pull down.
Your suggestion should make the search function easy also because I can just leave the On Update code for the combo created by the wizard.

I shall try and let you know how it goes.

Many thanks

 

[valley]

Hello Uplate,

I have one or two issues which forced me to copy theNamer value.
The first was that the Wizard somehow included the RecordNum field (Autonumber and Primary Key) in the source properties of the Combo bringing up the RecordNum value in the pull down as DEFAULT making it difficult to use the code for entering NEW data in to the combo.(It comes up with an error message asking you to select a name from the list)

SELECT [Miracle_Cloth_Main].[RecordNum], [Miracle_Cloth_Main].[Name] FROM [Miracle_Cloth_Main]

Even when you simply copy this bit of code created by the Wizard , it comes up with a mismatch error

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo114], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

However by force editing the source property to
SELECT DISTINCT [Name] FROM Miracle_Cloth_Main ORDER BY [Name] DESC;
I can get it to work to enter New Names

I think I will have to settle for two combos, one for entering New Names and the second for Pull Down

 

[valley]

I have tried with the DISTINCT directive in the source property of the combo to eliminate the repeat entries. The problem is the combo doesn't add the new values to the field leaving empty fiels. I can condense the list by sorting DESCENDING.
This makes it necessary to copy values to a second field.
However I can use the Combo Built in function to go to the last Record.
Now thhe question is how I can edit the fields and then save the record as a NEW record ?

 

[uplate]

I think if you look at the problem from an "event-based" perspective, it might help. For example, there are two important combo box events that occur, upon which you will want to take action.

The first event is the AfterUpdate event, which you're already using to search for the specified customer, find the appropriate record, etc.

The second event that would help you is the NotInList event. This occurs when you put something in the combo box that wasn't part of the record source. In your case, this is exactly what you want - to be able to enter a new customer from the same combo box that you use to select existing customers.

In your NotInList sub, you can put the prompt asking if they want to create a new supplier.

If they want to create a new supplier, then you set "Response = acDataErrAdded" and insert your code to add the new record, copy the NewData value (passed into the NotInList sub) into the new Cust_ID field. Setting Response to acDataErrAdded will tell Access that the new value is not an error, and you can respond to it however you want (i.e with code to create the new record, fill in Cust_ID, etc).

If they don't want to create a new supplier, you set "Response = acDataErrContinue", and then the user can correct the value in the combo box (this would happen, for example, if the user made a typo).

Other events are useful too. Do a search for the BeforeUpdate and Unload events and you will see lots of discussion (and confusion) about how and when to validate user input. Once you trap the proper events, your application code can do whatever is appropriate.

 

[valley]

Using NotInList Property.

Many thanks for your suggestions about exploiting the events capabilities of the powerful ACCESS.

Though I already visited the NotIn List property, in the beginning,I left it for the reason of empty fields in the Name column, I guess. (Not sure!)

I have now a nearly satisfactory solution by using
---- Built in Combo after_update and FindLast function to
go to the last record for a customer.

----- The NotInList Property for adding a New Name to the list.

Private Sub Combo114_AfterUpdate()


    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindLast "[RecordNum] = " & Str(Nz(Me![Combo114], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo114_NotInList(NewData As String, Response As Integer)

Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@You Must Add it to Continue"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new Description?") = vbNo Then
Response = acDataErrContinue
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Name = NewData
rs.Update
rs.Close

Set rs = Nothing
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

End Sub

I still have the empty Name field when a Customer already in the listed is selected for a new Record.

This makes it necessary to have a second field like
Cust_ID to copy the name so you can correctly track each record or search by date etc.

I have the table as below now. The enties in the CUST_ID field are previously entered.

Please see the last records where NEW Names havee been added by NotInList and not copied and the empty
Name fields for repeat entries. (For which customer ?)

Name	Cust_ID	RecordNum	Address
New Customer	New Customer	1	
Main Customer	Main Customer	2	
Good Customer	Good Customer	3	
Any Customer	Any Customer	4	
All customers	All customers	5	
	New Customer	6	
	Main Customer	7	
	Good Customer	8	
	Main Customer	9	
	New Customer	10	
	Main Customer	11	
	Good Customer	12	
	New Customer	13	
	Main Customer	14	
	Good Customer	15	
	Main Customer	16	
	New Customer	17	
	Good Customer	18	
Finest Customer	Finest Customer	19	
Newest Customer	Newest Customer	20	
Thiscistomer		21	
Othercustomer		22	xxxxx
		23	ccccccccc
		24	mmmmmmm

In conclusion

I need to copy the Name to a second field Cust_ID
for the combo to work as a data entry as well as select
mechanism, leaving the Cust_ID as the basis for each record.

Currently the combo search and find the last record from the Name field. So is it possible to force the
search to search the Cust_ID field insted of the Combo114 field modifying the code below :

Private Sub Combo114_AfterUpdate()


    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindLast "[RecordNum] = " & Str(Nz(Me![Combo114], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[uplate]

I don't understand why you're still using the Name field and why you're updating it instead of Cust_ID (or instead of both).

I think if you get rid of it, your design will be better, you'll reduce complexity, you'll simplify maintenance, you'll save space, and I think you'll get to a good solution much faster. I believe space constraints were the reason you didn't want to create a separate customers table, and yet by keeping a sparsely populated column in the "big" table, you've essentially created a second table within a table. Keeping a second column of names probably takes more space than a second table would take because all the fields with empty names take the same space as fields with names, so instead of using only as many name fields as customers, you're using as many name fields as visits.

I can't see any reason to keep the second, sparsely populated, name field - could you explain how it contributes? If I understand this, I could probably help you better.

 

[valley]

Ah, I am so glad you are back Uplate !

I had been thinking the same thing and tinkering all day
but hasn't go any further.
I agree with you fully but First of all I can't get the DISTINCT clause working to only select the value once from repeated entries.
Here is my Souce property for the combo
SELECT DISTINCT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name, Miracle_Cloth_Main.Cust_ID FROM Miracle_Cloth_Main ORDER BY [Cust_ID] DESC;

Could you please tell me What I am doing wrong ?

Let me take stock:

I Need a Combo to Enter New Names ---- I can do this by NotInList property

I Need the same combo to pull down a Name ---- Yes

I Need the same Combo to Go to the Last record ---- I can do this by After_update (Native code)

I Need to edit and save the record as a new Record ---- Could do with some help and suggestions

I need to Populate the Name field with the Name selected in the
Combo when a New record is saved ------------- Could do with some help and suggestions

I Need to eliminate repeat entries of Names in the Combo pull down ---

I have since found out that this is possible with a simple query like
SELECT DISTINCT [Name] FROM Miracle_Cloth_Main ORDER BY [Name] DESC;

However ACCESS needs A primary Key included in the querry
for the FindLast function to work.
SELECT DISTINCT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name, Miracle_Cloth_Main.Cust_ID FROM Miracle_Cloth_Main ORDER BY [Cust_ID] DESC;

Is there a compromise where I can get the Filtering by DISTINCT clause
to work with the above ?




I think that is a good start or midway point!

 

[uplate]

Ok, now I think we're making progress. By the way, I'm assuming that RecordNum is your PK - correct me if that's incorrect.

SELECT DISTINCT was my suggestion to simply return each unique company name. The SQL I suggested (SELECT DISTINCT Cust_ID FROM Miracle_Cloth_Main ORDER BY Cust_ID) will yield a single record for each customer, but doesn't address the issue of finding the last record number. To kill both birds with one stone, try something like:

SELECT Cust_ID, Max(RecordNum) AS MaxRec FROM Miracle_Cloth_Main GROUP BY Cust_ID ORDER BY Cust_ID;

This will select only one record per customer - specifically the record with the maximum record number. I recommend using Max() instead of Last() because you want the maximum number which isn't guaranteed to be the last record. You may wish to do some research on aggregate functions such as Max(), Last(), Count(), Sum(), etc and how to use them. Incidentally, when you use aggregate functions, the GROUP BY clause removes duplicates, so DISTINCT is not necessary.

Also, you can set the combo box column width to zero for the MaxRec column (i.e. the column widths property would be something like 2";0") so you just see a simple list of unique customer names (the user really shouldn't care about record numbers). Even though it is hidden, it will still be accessible to your code - see the Column property.

Try that and let me know how it goes.

 

[valley]

Many thanks for the reply.

RecordNum is my Primary Key.

I have tried
SELECT Cust_ID, Max(RecordNum) AS MaxRec FROM Miracle_Cloth_Main GROUP BY Cust_ID ORDER BY Cust_ID;

as the source property of the Combo which works fine and brings up a sorted list of Cst_ID without duplicates.

Could you please tell me what do I include in the After_Update code as it is no more required to search for the Last Record, but just a matter of updating the form for the selected record.

 

[valley]

Hello Uplate and everyone,

Iam pleased to say that against all odds I have a SUPERCOMBO which does all these with a single table.

Enter New Cust_ID
Pulldown a list of UNIQUE Cust_ID to select the Cust_ID
Go to the Last record for the selected Cust_ID

I have the code below for the Combo;

Private Sub Combo114_AfterUpdate()
 Dim rs As Object

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

Private Sub Combo114_NotInList(NewData As String, Response As Integer)

Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@You Must Add it to Continue"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new Description?") = vbNo Then
Response = acDataErrContinue
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Miracle_Cloth_Main", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Name = NewData
rs!Cust_ID = NewData
rs.Update
rs.Close
Set rs = Nothing
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

End Sub

Now when I pull down a customers last record
and make some changes the new information is saved.
When I select another record and then reselct the
edited record, the new entries are there.

How Do I save this as a NEW record for the customer?
Could you suggest where I need to insert some code?

When I try to create a new record from the form navigation at the bottom and fill in some data
it gets saved without the Cust_ID of the combo.

Best regards

 

[valley]

I have since found this code for a Button to create
a New record from recordset clone
and created a button.

The button creates a new record without any entries exceptthe auto number

Could you please suggest how to complete this and make it work

Private Sub Save_Record_Click()
'Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the record in the form.
Dim NewKey As String

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
'If Me.NEWRECORD Then
'MsgBox "Select the record to duplicate."
'Else
'Get the new key
'NewKey = InputBox("Enter the new Policy No")
'You need to test there was an entry, and of a suitable type.

With Me.RecordsetClone
.AddNew
'!KeyFieldName = NewKey
'!OrderDate = Date
'etc for other fields.
.Update

'Show the new record
Me.Bookmark = .LastModified
End With
'End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "Save_Record_Click"
Resume Exit_Handler
End Sub