Datasheets in subforms

[mlopes1]

I am a new user so please bare with me. I have form with a subform as a datasheet. One of the columns in the subform is combo box of Item #'s. Another column is combo box of Product #'s. The reason I have it like this is that one time I may only know the item # and another only the product #. Also, every Product has both an Item # and Product #. How do I get the Item # to automatically fill in when I select a Product # or vice versa? The combo boxes are pulling their data from a Products table.

Thank you very much.

 

[Dave Eyley]

I would try using DLookUp in both combos in the following way -

In the AFTERUPDATE property of the combo (Item#) use this code -

If IsNull(Prod#) Then
DLookUp("[Prod#]","[Products]","[Item#]=Forms!MainForm!SubForm.Form.Item#")
End If


In the AFTERUPDATE property of the combo (Item#) use this code -


If IsNull(Item#) Then
DLookUp("[Item#]","[Products]","[Prod#]=Forms!MainForm!SubForm.Form.Prod#")
End If


Each combo looks up the corresponding field for the other, but only if the other field is null.

It should work...

If you've used the DLookUp function before you'll be fine. If not, then the line means -

LookUp the field [Field2] from table [Tablename] where the table field [TableField] equals the field pointed to by Forms!Mainform!SubForm.Form.Field1.

It looks a bit long because the action is taking place on a subform and so the path to it is longer. If the combos had been on a mainform then the function would have read -

DLookUp("[Field2]","[Tablename]","[Tablefield]=Forms!Mainform!Field1")

Make sure there's a value in the lookup combo first or you will get an error. You could set the 'Limit to List' property to help with this.

I hope this makes sense...

Dave E