1. I would like to build simple invoicing system for the company I work in.
2. I have four tables:
OrderItem (OrderItemId, OrderItemName, OrderItemQuantity, OrderItemUnitPrice)
Invoice (InvoiceId, InvoiceNo, InvoiceDate, CompanyId)
InvoiceItem (InvoiceItemId, InvoiceId, OrderItemId, InvoicetemNo, InvoiceItemName, InvoiceItemQuantity, InvoiceItemUnitPrice)
Company (CompanyId, CompanyName, CompanyAddress).
3. First I enter the orders in the OrderItem table.
4. Then I have the invoice form (screenshot attached): masterform is Invoice and subform is InvoiceItem.
In master form Invoice I enter just general data of the invoice and in subform InvoiceItem I enter items to be on the invoice.
In a tablegrid InvoiceItem I have a list box showing ordered items from table OrderItem
Code: Select all
SELECT `OrderItemName`, `OrderItemId` FROM `OrderItem`
5. Then I have a invoice designed in Sun Report Builder.
Simple way is just to execute a report based on query
Code: Select all
SELECT `Invoice`.`InvoiceNo`, `Invoice`.`InvoiceDate`, `Company`.`CompanyName`, `InvoiceItem`.`InvoiceItemNo`, `InvoiceItem`.`InvoiceItemDescription`, `InvoiceItem`.`InvoiceItemQuantity`, `InvoiceItem`.`InvoiceItemUnitPrice` FROM `InvoiceItem`, `Invoice`, `Company`, `OrderItem` WHERE `InvoiceItem`.`InvoiceId` = `Invoice`.`InvoiceId` AND `Invoice`.`CompanyId` = `Company`.`CompanyId` AND `OrderItem`.`OrderItemId` = `InvoiceItem`.`OrderItemId`
6. But it is not he way I would like to do this.
I would like the user to be able to modify or add new items when issuing an invoice.
When entering data in InvoiceItem subform, when choosing from the list box showing ordered items from table OrderItem I would need a macro linked to the listbox just to suggest data and to fill the data into other textboxes InvoiceItemName, InvoiceItemQuantity, InvoiceItemUnitPrice, which are analog to the ones in OrderItem table. InvoiceItemName = OrderItemName etc. Then user could make modifications.
I think this macro should first read data (name of the ordered item etc. ) from database using sql query for the current OrderItemId
Code: Select all
SELECT `OrderItemId`, `OrderItemName`, `OrderItemPrice`, `OrderItemQuantity` FROM `OrderItem`
I found the below macro to calculate value and update the textbox in a form, but I do not know how to write a macro to read data with sql and then enter them into the from.
Code: Select all
Sub calcTotal(Event As Object)
Dim Form As Object
Dim Quantity As Object
Dim UnitPrice As Object
Dim Total As Double
Form=Event.Source.Model.Parent
Quantity=Form.GetByName("QUANTITY") REM text box w/ quantity
UnitPrice=Form.GetByName("UNITPRICE") REM text box with price
Total=CInt(Quanity.Text)*CDbl(UnitPrice.Text)
Form.Columns.getByName("TOTAL").updateDouble(Total)
End Sub
Thank you!