Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to find a customer and go to a new order

    How to find a customer and go to a new order number.


    I have a Form called frmOrders. for issuing orders and invoices. How can i find a customer from a

    combo box, and then issue a new order with this customer? I have a form called frmCustomers, and

    there i use the followin expresion to find the customer:
    Private Sub CboCompany_AfterUpdate()
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ClientID] = " & CboCompany.Value
    If rs.NoMatch Then
    MsgBox "Client Not Found"
    Else
    Me.Bookmark = rs.Bookmark
    End If
    End Sub
    It works excellent.This code is very convenient because the client appears after i write one or two

    letters. However i cannot use this code in the form frmOrders.The form frmOrders has a different

    recordset, based on the OrderID. This codes finds only the first customer,a customer only with an

    existent order. And i want to go to a new orderid.If i use the expression DoCmd.GoToRecord , ,

    acNewRec, then the customer chosen disappears.What i want is to choose a customer from a combo box

    and then go to a new order.The source of my mistake is that i want to go to anew record fr an

    order, and not a new record for a new customer. My database is similar to that of Northwind traders

    shipped with Microsoft.
    Will be grateful for any help

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find a customer and go to a new order

    There are many ways to accomplish your goal. I would suggest removing the ControlSource of the ClientID combo box. When you click your button to create a new record, the selected customer will stay.
    You will also need to add a line of code that adds the selected ClientID to the newly created record. I would add this to the AfterInsert event of the form...
    <img src=/w3timages/redline.gif width=33% height=2>
    Dim rs as Recordset
    Dim fld as Field
    set rs = Me.RecordsetClone
    Set fld = rs.Fields("ClientID")
    rs.Edit
    fld.Value = ClientID.Value
    rs.Update
    rs.Close

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to find a customer and go to a new order

    The simplest way to handle this is to have a control bound to the clientID field but with its visible property set to false if you wish. Your combobox should be unbound, since you use it to select the client for whom you're adding a record. When you add a record, it isn't really there until you insert data, so change the afterupdate event of the combobox to this:

    <pre>Private Sub CboCompany_AfterUpdate()
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ClientID] = " & CboCompany.Value
    If rs.NoMatch Then
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me![txtClientID]= cboCompany.Value
    Else
    Me.Bookmark = rs.Bookmark
    End If
    End Sub</pre>


    This assumes you have a ClientID field on your Orders form and that you've called it txtClientID. AfterInsert occurs after the new record has been inserted. If the value you want added is a required field, which it appears to be, using AfterInsert won't work because it's too late.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find a customer and go to a new order

    Oops - sorry about that...[img]/w3timages/icons/blush.gif[/img]

    I meant to suggest BeforeInsert instead of AfterInsert.
    I must have been asleep at the keyboard...[img]/w3timages/icons/doze.gif[/img]

    My apologies,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •