How to find a customer and go to a new order

Topic

New Reply

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

Reply | Quote

Replies

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…

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

Reply | Quote

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:

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

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.

Reply | Quote

Oops – sorry about that…

I meant to suggest BeforeInsert instead of AfterInsert.
I must have been asleep at the keyboard…

My apologies,

Reply | Quote