Lookup company invoices (pull down by company ID)

[kevin]

I created a sales order invoice tracking database based on the
NORTHWIND database.

I have keep everything as close to default as possible and modified a
few field names in the forms to track sales order id/date due
date.....

Anyway, now my issue,I would like to create a lookup / Combo box based
on company ID that would then populate the form with company name and
the order ID(s) and detail information.

What is the easiest way to do this?

THANKS

Kevin

 

[Graham Mandeno]

Hi Kevin

Let's assume you have a main form bound to your Customers table. You should
create a combo box on your main form with the following properties:
Name: cboLookupCustomer
ControlSource: (leave it blank [unbound])
RowSourceType: Table/Query
RowSource: Select CustomerID, CustomerName from Customers order by
CustomerName;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0
AfterUpdate: [Event Procedure]

And for your event procedure code:

Private Sub cboLookupCustomer_AfterUpdate()
If Not IsNull(cboLookupCustomer) Then
With Me.RecordsetClone
.FindFirst "CustomerID=" & cboLookupCustomer
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

If CustomerID is a text field, you need quotes:

.FindFirst "CustomerID='" & cboLookupCustomer & "'"

Now, selecting a customer from the combo will navigate to that customer's
record.

For your orders and order details, I suggest two subforms:

One a continuous form displaying just the order number and date and linked
to the main form by CustomerID. Name the subform control sfmListOrders.

The other can be just your order entry form based on Orders and
OrderDetails. Name the subform control sfmCurrentOrder.

Create a textbox on your main form:
Name: txtCurrentOrder
ControlSource: =[sfmListOrders].[Form]![OrderNumber]
Visible: No

Set the following properties for sfmCurrentOrder:
LinkMasterFields: txtCurrentOrder
LinkChildFields: OrderNumber

Obviously you will need to change table and field names to suit but this
should give you the general idea.

 

[Ken Sheridan]

Kevin:

You can do this very easily by modifying the existing Customer Orders form
and saving it under a new name. The modifications required are as follows:

1. Delete the ControlSource of the CompanyName text box.

2. Change the text box to a combo box.

3. Rename it cboCustomer

4. Set up the combo box's properties like so:

RowSource: SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

5. In the AfterUpdate event procedure of the combo box put:

Dim rst As Object

Set rst = Me.Recordset.Clone

With rst
.FindFirst "CustomerID = """ & cboCustomer & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

6. In the form's Current event procedure put:

Me.cboCustomer = Me.CustomerID

7. Set the form's AllowEdits property to True (Yes in the properties sheet)
so that you can select from the combo box. If you want the form to be read
only set the Country control's Locked property to True (Yes) and its Enabled
property to False (No)

8. If you wish you can of course add controls bound to other columns from
the Customers table to the form and , if necessary, lock/disable them as
above.

9. The combo box will show the company names, although its value is the
hidden CustomerID. The CustomerID column is really of little intrinsic
meaning, essentially being just to provide a set of unique values as the
table's primary key, so would not normally be shown on a form of this type,
but if you do want to see it you can add a text box (locked and disabled
again) to the form with a ControlSource property of:

CustomerID

You'll find that when you select a customer from the combo box the form will
move to that customer record and the first subform will show that customer's
orders. When you click on an order in the first subform the second subform
will show the details for that order.

Ken Sheridan
Stafford, England

 

[kevin]

Kevin:

You can do this very easily by modifying the existing Customer Orders form
and saving it under a new name.  The modifications required are as follows:

1.  Delete the ControlSource of the CompanyName text box.

2.  Change the text box to a combo box.

3.  Rename it cboCustomer

4.  Set up the combo box's properties like so:

RowSource:     SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;

BoundColum:   1
ColumnCount:  2
ColumnWidths  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one.  The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

5.  In the AfterUpdate event procedure of the combo box put:

    Dim rst As Object

    Set rst = Me.Recordset.Clone

    With rst
        .FindFirst "CustomerID = """ & cboCustomer & """"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

6.  In the form's Current event procedure put:

    Me.cboCustomer = Me.CustomerID

7.  Set the form's AllowEdits property to True (Yes in the properties sheet)
so that you can select from the combo box.  If you want the form to be read
only set the Country control's Locked property to True (Yes) and its Enabled
property to False (No)

8.  If you wish you can of course add controls bound to other columns from
the Customers table to the form and , if necessary, lock/disable them as
above.

9.  The combo box will show the company names, although its value is the
hidden CustomerID.  The CustomerID column is really of little intrinsic
meaning, essentially being just to provide a set of unique values as the
table's primary key, so would not normally be shown on a form of this type,
but if you do want to see it you can add a text box (locked and disabled
again) to the form with a ControlSource property of:

CustomerID

You'll find that when you select a customer from the combo box the form will
move to that customer record and the first subform will show that customer's
orders.   When you click on an order in the first subform the second subform
will show the details for that order.

Ken Sheridan
Stafford, England








- Show quoted text -

Thank you for your quick replies......

I will let you know how I make out.

 

[kevin]

Kevin:

You can do this very easily by modifying the existing Customer Orders form
and saving it under a new name.  The modifications required are as follows:

1.  Delete the ControlSource of the CompanyName text box.

2.  Change the text box to a combo box.

3.  Rename it cboCustomer

4.  Set up the combo box's properties like so:

RowSource:     SELECT CustomerID, CompanyName FROM Customers ORDER BY
CompanyName;

BoundColum:   1
ColumnCount:  2
ColumnWidths  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one.  The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

5.  In the AfterUpdate event procedure of the combo box put:

    Dim rst As Object

    Set rst = Me.Recordset.Clone

    With rst
        .FindFirst "CustomerID = """ & cboCustomer & """"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

6.  In the form's Current event procedure put:

    Me.cboCustomer = Me.CustomerID

7.  Set the form's AllowEdits property to True (Yes in the properties sheet)
so that you can select from the combo box.  If you want the form to be read
only set the Country control's Locked property to True (Yes) and its Enabled
property to False (No)

8.  If you wish you can of course add controls bound to other columns from
the Customers table to the form and , if necessary, lock/disable them as
above.

9.  The combo box will show the company names, although its value is the
hidden CustomerID.  The CustomerID column is really of little intrinsic
meaning, essentially being just to provide a set of unique values as the
table's primary key, so would not normally be shown on a form of this type,
but if you do want to see it you can add a text box (locked and disabled
again) to the form with a ControlSource property of:

CustomerID

You'll find that when you select a customer from the combo box the form will
move to that customer record and the first subform will show that customer's
orders.   When you click on an order in the first subform the second subform
will show the details for that order.

Ken Sheridan
Stafford, England








- Show quoted text -

Thank you Ken..... Worked Perfect......

You are the man!!!!

 

[kevin]

Thank you Ken..... Worked Perfect......

You are the man!!!!- Hide quoted text -

- Show quoted text -

Reading your reply Graham.... Your the man as well.....

Thank you