Dlookup, and direct table edits

[eskybel]

I have a form, and a subform (frmTrans, and sformTrans). frmTrans is unbound, using dlookup in the txtITEMID text box for displaying information to the user about that item. Ex: user scans barcode, and the form dlookup for that ITEMID, displays type, make, model, user, location, etc. This works fine.



sformTrans is currently bound to a table (tblTrans) that holds all the transactions against all ITEMIDs. Ex: this monitor moves from here to there, or that PC was discarded, and this table holds the records on these moves for historical purposes.

I have the main form in single form view. I have the subform in datasheet view. I want to be able to scan (or enter) a number in the ITEMID field in the main form, and have it filter the subform based on the transactions of that ITEMID, but to allow me to enter information into that table directly in the subform, based on that ITEMID.

I looked at the filter property on the subform, trying to point it to the txtITEMID field above, but this didn't seem to work. I also tried to force a "requery" as part of the dlookup process. Couldn't get this right either.

I then thought, binding the subform to a temp table, having the user enter in the transaction information, then using an append/clear query to the main table. This may be fine, but wanted to see if I could make the above work, because then a user can see the historical information while entering the new transaction at the same time, deal with less queries and tables. Probably not best practice to edit the table directly each time...

I'm sure this is a lot easier than I'm making it out to be, but I can't seem to find what I want to do.

[June7]

Temp table not needed and poor design.

If the main form is unbound, I would not use form/subform. Suggest a single form in Continuous view with unbound combobox to select ITEMID in the form header section. Controls in continuous form can be arranged to look like datasheet.

Also don't see need for DLookup. Code in the combobox AfterUpdate event like:

Me.Filter = "ItemID=" & Me.comboboxname
Me.FilterOn = True

[eskybel]

My apologies, I misstated my first line. My frmTrans (main form) is bound to my tblInventory table. The dlookup is used to retrieve inventory information for display. The subform is bound to tblTrans, for putting transactions against that item. Since there are two separate tables holding different information, I assume there isn't an easy way to have one form in continuous view.

[June7]

Main form must be in single view, the subform can be in single, continuous, datasheet (don't know about pivot).

If ItemID is the primary/foreign key that records are related by, code is not needed. Set the Master/Child links properties of the subform container control and the records will automatically synchronize.