Forms - Allowing user to choose between two different item lookupsfor data entry

[mylissamurry]

I'm trying to set up a form where the user can either enter data using
an Item Number or a SKU Number. I've created a basic invoicing
database where I have my Products table (with Item Number and SKU
Number), an Orders table and a Purchase Orders table. Then I created
a Purchase Orders form with an Orders subform. It works great. The
user can enter in the Item Number in the Orders subform and can enter
orders that way; however, I have now been charged with allowing the
user to be able to enter orders using either the Item Number or the
SKU Number (both of which are unique to each record in the products
table). How do I allow users to enter either one? Is this possible?

I really appreciate the help!!!

 

[Ken Sheridan]

In the subform you'd need two controls, one bound to the item Number column,
the other to the SKU column. In the AfterUpdate event procedure of each you
can look up the corresponding value for the other and assign it to the other
control, e.g. in the Item Number control's AfterUpdate event procedure:

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
strCriteria = "[Item Number] = " & ctrl
Me.SKU = DLookup("SKU", "Products", strCriteria)
Else
Me.Undo
End If

Similarly in the SKU control's AfterUpdate event procedure:

Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
strCriteria = "SKU = " & ctrl
Me.SKU = DLookup("[Item Number]", "Products", strCriteria)
Else
Me.Undo
End If

This assumes both Item Number and SKU are number data types. If text data
type you'd need to wrap the values in quotes characters like so:

strCriteria = "[Item Number] = """ & ctrl & """"

strCriteria = "SKU = """ & ctrl & """"

I should point out that by having both columns in the Orders table you
should protect against inconsistencies by creating a single relationship
between Order and Products on both columns and enforcing referential
integrity.

Ken Sheridan
Stafford, England