I have a rather simple but perplexing problem. I am creating a way for the use to enter and print invoices. To do so, I have an invoice form (InvoiceEntry) and an invoice details subform (InvoiceDetails) and they seem to work well except for one thing; when an invoice detail line is entered in the form, and an item number is chosen from the combo box, the result is just the item name. I also want to have the item description and unit price populate the line. However, after researching this site and Access Help, I just cannot see how to do this. I looked at MajP's "FindByCombo" demo file and see that this works well, but after several tries, I cannot reproduce this. To do that, I tried making another subform called ItemDetails which would display the Item Name, Item Description and Unit Price after the Item Name was chosen in the combo box on the InvoiceDetails subform. However this ItemDetails subform does not even show on the InvoiceDetails form. It does show up in Design View however. Please help me with this problem. Thank you.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Combo box does not produce all the fields needed 2
- Thread starter gbuchman1
- Start date
hjgoldstein
Programmer
Have you got the column count and column widths properties set correctly?
Might be as simple as that.
It is time for pacifists to stand up and fight for their beliefs.
Might be as simple as that.
It is time for pacifists to stand up and fight for their beliefs.
- Thread starter
- #3
I guess the first question is must I use a subform to get the Item Description and Unit Price to populate when I select the Item Name?
Ive tried doing this two ways:
A - two forms: InvoiceEntry and subfmInvoiceDetails
B - three forms: InvoiceEntry, subfmInvoiceDetails, subfmItemDetails (nested inside subfmInvoiceDetails).
If I correct the column numbers and col widths in option A, nothing changes.
Option B - I could not even get the subfmItemDetails to show up within the other forms.
Ive tried doing this two ways:
A - two forms: InvoiceEntry and subfmInvoiceDetails
B - three forms: InvoiceEntry, subfmInvoiceDetails, subfmItemDetails (nested inside subfmInvoiceDetails).
If I correct the column numbers and col widths in option A, nothing changes.
Option B - I could not even get the subfmItemDetails to show up within the other forms.
-
1
- #4
provide the sql for your subform query.
I would assume you have a table of "items" with fields like: name, unitPrice, and itemDescription.
Then you have a table of "invoice details" with foreign keys to the invoice table and the the item table.
Your subform should be based on a query that links the item table to the invoice detail table by item ID. When you add an item id to this table you would see all corresponding information for that item.
I would assume you have a table of "items" with fields like: name, unitPrice, and itemDescription.
Then you have a table of "invoice details" with foreign keys to the invoice table and the the item table.
Your subform should be based on a query that links the item table to the invoice detail table by item ID. When you add an item id to this table you would see all corresponding information for that item.
- Thread starter
- #5
Yes, that is correct. I have all these tables and have linked them as you said. I have corresponding forms for these tables also. If I open the forms or the tables, I do see the data.
Also my combo box showing the ItemName does display the items, their descriptions and unit prices.
The InvoiceEntry form links to the InvoiceDetails subform via the Invoice Number. When you say "Your subform should be based on a query ..." do mean that this query must in the Recordsource of the InvoiceDetails subform?
Also my combo box showing the ItemName does display the items, their descriptions and unit prices.
The InvoiceEntry form links to the InvoiceDetails subform via the Invoice Number. When you say "Your subform should be based on a query ..." do mean that this query must in the Recordsource of the InvoiceDetails subform?
- Thread starter
- #7
I tried your suggestion of basing the InvoiceDetails subform on a query and it worked beautifully! Thank you MajP! Now the last step is the extended price field. This field is a simple calculation of Quantity * Unit Price. I created a seperate field apart from the query with this formula: =[InvoiceDetails Query]!Quantity*[InvoiceDetails Query]!UnitPrice. However I just get #Name? when I run the main form. So then I changed it to reference the fields from the InvoiceDetails table so that it was this:
=InvoiceDetails!Quantity*InvoiceDetails!UnitPrice This however only yields the extended price for the first line of the query. I need an extended price for each line in the query, and the number of lines will vary per invoice. How could I accomplish this?
=InvoiceDetails!Quantity*InvoiceDetails!UnitPrice This however only yields the extended price for the first line of the query. I need an extended price for each line in the query, and the number of lines will vary per invoice. How could I accomplish this?
- Thread starter
- #8
- Thread starter
- #9
The query did not allow for entry of new items, so I have instead made an event procedure which works, except for one thing. The ItemDescription and UnitPrice that it pulls is are the ones on the first record in the Items table, regardless of if the item is elsewhere in the table. The script is as follows:
Private Sub ItemName_AfterUpdate()
On Error GoTo Err_ItemName_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = Me!ItemName
' Look up item's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Items", strFilter)
' Look up item's description and assign it to ItemDescription control.
Me!ItemDescription = DLookup("ItemDescription", "Items", strFilter)
Exit_ItemName_AfterUpdate:
Exit Sub
How can I make this select the values from the correct record?
Private Sub ItemName_AfterUpdate()
On Error GoTo Err_ItemName_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = Me!ItemName
' Look up item's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Items", strFilter)
' Look up item's description and assign it to ItemDescription control.
Me!ItemDescription = DLookup("ItemDescription", "Items", strFilter)
Exit_ItemName_AfterUpdate:
Exit Sub
How can I make this select the values from the correct record?
-
1
- #10
to add a calculated field in the query you would write in the query builder in a new field:
ExtendedPrice:[Quantity]*[UnitPrice]
if you look in the sql view this would give you something like
Select field1,field2,...[Quantity]*[UnitPrice] as ExtendedPrice from InvoiceDetails
in a calculated control on the form it would be
=[Quantity]*[UnitPrice]
without the query name(assuming these fields are on the form).
I prefer to do the calculation in the query. It is easier to troubleshoot and not get #Name.
ExtendedPrice:[Quantity]*[UnitPrice]
if you look in the sql view this would give you something like
Select field1,field2,...[Quantity]*[UnitPrice] as ExtendedPrice from InvoiceDetails
in a calculated control on the form it would be
=[Quantity]*[UnitPrice]
without the query name(assuming these fields are on the form).
I prefer to do the calculation in the query. It is easier to troubleshoot and not get #Name.
- Thread starter
- #12
PHV, thanks for the answer but after substituting the line strFilter = "ItemName=' & Me!ItemName & "'" for this, strFilter = Me!ItemName , I get no item description or unit price.
I also tried strFilter = "ItemName=' & Me!ItemName & '"
and again got nothing for item description and unit price.
I also tried strFilter = "ItemName=' & Me!ItemName & '"
and again got nothing for item description and unit price.
- Thread starter
- #14
- Thread starter
- #15
MajP, I see in the Northwinds db (aka Demo db) that there is no extended price field in the Order Details table. There are also no calculated fields in the Orders table. Therefore, my question is, are you advocating that I create an ExtendedPrice field in the OrderDetails table, as well as have the ExtendedPrice field on the form? I have been able to successfully put the ExtendedPrice field on the InvoiceEntry form, and it calculates great, but should I also have an ExtendedPrice field in the table? I'm thinking no here. I will be creating a query for the purposes of exporting my invoices to another application, but even so, the query itself will be able to recreate the calculated fields, so therefore no need to store these in tables, correct?
No. My post just said that you can do calculations in a form's query and bind that field to a control. Or you can do a calculation in an unbound control. I said nothing about storing calculations in a field.
I normally do this in a form's query because I find it a lot easier. However, there may be efficiency to worry about. Assume you have 1million records. If you have a calculated control in a query it must do 1 million calculations. If your form is in single form view and you do the calculation on the form then it only does one calculation at a time.
I normally do this in a form's query because I find it a lot easier. However, there may be efficiency to worry about. Assume you have 1million records. If you have a calculated control in a query it must do 1 million calculations. If your form is in single form view and you do the calculation on the form then it only does one calculation at a time.
- Thread starter
- #17
- Status
Similar threads
