Query form question

[Nancy]

Hello -

I am a bit confused as to how to pursue this and was wondering if you had
some ideas....

I am working with two tables A and B.
Table A contains itemid, item and Quanity.
Table B contains item, desc, UOM.

Item is the key between the tables. A sample of the data would be:

Table A
itemid item quanity
123 0123 2
123 0124 6

Table B
item Desc UOM
0123 testing EA
0124 testing too EA

My user would like to have a query screen where they will enter a itemid
(table A), press a button and the data associated with it would display (such
as below).

Itemid:xxxxx (TblA) desc:xxxxxxxxxxxxxxxxxxxxxxxxxxx (tblB)

Item Desc Quanity UOM etc.
xxxx (TblA) xxxx(tblb) xxx(tbl a) xxx(tbl b)
.....

Okay so here is where I am at:
I created a form with all of the fields in the detail.

I am trying to create a sub form with idem, desc, quanity, uom) but am
having difficultites because there is data from multiple tables.

I am in the process of creating a button where I can clear the form and
populate the subform (but am a a little lost on populating the subform).

Am I on the right track with this? Any suggestions on how to do this or
would be appricated. If for some reason it can not be done, could you please
let me know what suggestions I need to make to the user.

Thanks,

Nancy

 

[Damon Heron]

I really can't see the need for your two tables in this instance. How about
something like this instead?

tblItems
ItemID
Name
Description
UOM
***********************
tblTransactions
TransID
TransDate
ItemID
PurchaseOrderID
AmtOrdered
AmtReceived
Used
.....etc. any other fields that may be applicable.
***********************
I know this is a bit more complicated, but it will handle the receipt and
disposition of items much better. Notice that the quantity on hand is not
in the table, but calculated on the form from the Amt Received- Used. The
main form would be from the Item table, and the subform would be from the
Transaction table.

At the very least, you should combine your two tables into one and thus have
no need for a subform.

 

[Nancy]

Damon -

I am a bit confused by your response. The tables have been created. I have
interited the design and do not want to change the table structure yet
because I am still learning it and do not know what problems it causes.

There are no calculated fields here, they users enter the information.

So, in addressing my question without changing the tables, do I still need
the subform? or am I going about this wrong?

Thanks,

Nancy

 

[Damon Heron]

Okay then, create a form and enter the Record Source by clicking on the
(...) in the properties box. Use the query builder to add all the fields
from both Table A & B. Then go back to the form and add textboxes for all
the fields except the itemID. For the itemID, just insert a combobox on the
main form with the ROW Source as tableA, itemID. The combobox afterupdate
event will look like this:

Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ItemID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

There is a wizard that does this automatically, if you add the combo box as
the first object on the form before adding the other fields.

The user selects an itemID in the combobox and all the other fields are
filled in with the record data. If this does not work, then you may need to
add a relationship link between the two tables when you build the form
query.

Any questions?
Damon