adding new records overwrites existing record 1

[StuartBombay]

My client wants all customer/order information on one form - they do now want to open a new form to add customers or orders.
I created an unbound form for customers, I used unbound because I found that when using a bound form adding a new customer would overwrite existing ones. I have this working by using an 'ad new' and 'save' button, and lots of code behind it.
The customer form has a sub-form for orders. A customer can have more than one order. I made a continuous bound form for the orders sub form. The orders show up correctly but again, when I go to add a new order it overwrites an existing order.
Do I have to make the sub form unbound also? If so, how? I'm having trouble with that because of the one-to-many relationship. I can grab the recordset in code, but how do I indicate on the form that there is more than one order for the selected customer?
Hope I'm making sense here, or at least enough sense to get some help with this!
Thanks-

 

[PHV]

I found that when using a bound form adding a new customer would overwrite existing ones
Really ?
Have a look at the Northwind sample database shipped with access and/or follow the form wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Do your forms have Allow Additions set to yes? Do you have your navigation buttons? Does it have the new record button? I don't see why new data would overwrite any old data. When you have a form/subform situation, when you go to a new record for the form, the subform follows.

 

[StuartBombay]

Thanks, I started over with a new main form. I think the root of my problem is I'm using 2 combo boxes to find the customer I need to display. The first combo box is "Company" and the second is "Customer", which is filtered by "Company".
So, if I select a company, then select a customer, then select another company, the customers' company changes to the second selected customer. Not unexplainable behavior if you think about it.
So, I would need to use 2 unbound combo boxes in order to select my record? How would that work? Once a company and a customer are selected, do I use an after update event to populate the form? What would that code look like?
Thanks, I appreciate any help I can get in figuring this out.

 

[StuartBombay]

I'll answer myself - yes, ubound combo controls. And After Update code that looks like this:

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

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SponsorID] =  " & Me![cboSelectSponsor]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I had to change it a little bit from what the wizard created, but it works. I think this may be where I got stuck the first time and moved to an unbound form.
I'll continue in this vein, but if anyone had ideas for me, please post them!

Thank you-

 

[TheAceMan1]

How are ya StuartBombay . . .

Excellent self analyis. However, you should change:

[blue]If Not rs.[purple][b]EOF[/b][/purple] Then Me.Bookmark = rs.Bookmark
    [purple][b]to[/b][/purple]
If Not rs.[purple][b]NoMatch[/b][/purple] Then Me.Bookmark = rs.Bookmark[/blue]

[blue]Not rs.EOF[/blue] will always be true as long as one record exist!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[StuartBombay]

Thanks AceMan1, good call.

 

[PHV]

Bottom line, any control used as a navigation tool should be unbound.