How to auto-post editable lookups?

[WDSnews]

When using an invoice form, it should be possible to look up all the line
item data and duplicate it into the detail table. but how?

I know how to build a combo box to reveal data from a related table, but I
don't know how to copy multiple fields from its record to a different record
of a different table. It should be possible to select an 'Apple' detail
item on the Invoice subform and let Access fill-in the line-item details,
including the description and price. Then it should be possible on the
Invoice's subform to manually change the price and description if desired.

I'm assuming three tables would be used: 'Invoice', 'Invoice Detail', and
'Items'. The 'Invoice Detail' is the child table of 'Invoice'. The 'Items'
table would hold all the lookup data for the 'Invoice Detail'. What do I
need to do in my tables and on my form to enable automatic data entry of
'Invoice Detail'?

 

[Dirk Goldgar]

When using an invoice form, it should be possible to look up all the line
item data and duplicate it into the detail table. but how?

I know how to build a combo box to reveal data from a related table, but I
don't know how to copy multiple fields from its record to a different
record of a different table. It should be possible to select an 'Apple'
detail item on the Invoice subform and let Access fill-in the line-item
details, including the description and price. Then it should be possible
on the Invoice's subform to manually change the price and description if
desired.

I'm assuming three tables would be used: 'Invoice', 'Invoice Detail', and
'Items'. The 'Invoice Detail' is the child table of 'Invoice'. The
'Items' table would hold all the lookup data for the 'Invoice Detail'.
What do I need to do in my tables and on my form to enable automatic data
entry of 'Invoice Detail'?

One of the easiest ways to do this is to use a combo box with multiple
columns. The description and price columns may or may not be visible when
the list is dropped down, and of course you can only see one column -- the
first visible column -- when the list is collapsed. But you can still copy
information from the invisible columns into the other fields in the subform
record, in the combo box's AfterUpdate event.

Suppose your subform (based on ItemDetails) has a combo box ItemCode and
text boxes ItemDescription and Price. Suppose that the ItemCode combo box
has 3 columns, with a rowsource like this:

SELECT ItemCode, ItemDescription, Price FROM Items;

Then you could have an AfterUpdate event procedure for the combo box like
this:

'----- start of example code -----
Private Sub ItemCode_AfterUpdate()

With Me.ItemCode

If IsNull(.Value) Then
Me.ItemDescription = Null
Me.Price = Null
Else
Me.ItemDescription = .Column(1)
Me.Price = .Column(2)
End If

End With

End Sub
'----- end of example code -----

Notice that, in VBA code, the second column of the combo box is .Column(1),
and the third column is .Column(2). A combo box's Column property is
0-based.

 

[WDSnews]

thank you.

One of the easiest ways to do this is to use a combo box with multiple
columns. The description and price columns may or may not be visible when
the list is dropped down, and of course you can only see one column -- the
first visible column -- when the list is collapsed. But you can still
copy information from the invisible columns into the other fields in the
subform record, in the combo box's AfterUpdate event.

Suppose your subform (based on ItemDetails) has a combo box ItemCode and
text boxes ItemDescription and Price. Suppose that the ItemCode combo box
has 3 columns, with a rowsource like this:

SELECT ItemCode, ItemDescription, Price FROM Items;

Then you could have an AfterUpdate event procedure for the combo box like
this:

'----- start of example code -----
Private Sub ItemCode_AfterUpdate()

With Me.ItemCode

If IsNull(.Value) Then
Me.ItemDescription = Null
Me.Price = Null
Else
Me.ItemDescription = .Column(1)
Me.Price = .Column(2)
End If

End With

End Sub
'----- end of example code -----

Notice that, in VBA code, the second column of the combo box is
.Column(1), and the third column is .Column(2). A combo box's Column
property is 0-based.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)