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'?

"WDSnews" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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

(please reply to the newsgroup)

thank you.


"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "WDSnews" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>