M
mjones
Hi All,
With help for you kind folks, I’m almost at the top of, what I feel is
like, Mount Everest. I’ve been able to create a course certificate,
receipt and course booking confirmation. Now to reach the top, I need
to finish an invoice.
fInv form works great except units sold quantity won't let me change
the amount from the default of 1.
rInv report works so far with the exception of unitprice and
productdescription, which are on the form, but not the report. Can I
ask your help again to get this part working? This is the first time
I've used a subform and I'm trying to use the packing slip as an
example because it's very similar except without pricing.
Here’s what there is so far:
FORM (works except units sold quantity)
fInv form has a subform called fInvDtl where one chooses the products
to be invoiced from the tProduct table. This fInvDtl subform has four
columns:
1 - ProductCode with row source:
SELECT tProduct.ProductCode, tProduct.ProductDescription,
tProduct.Type FROM tProduct WHERE (((tProduct.Type)<>"X")) ORDER BY
tProduct.ProductCode;
2 – TextProductDescription with control source:
=DLookUp("ProductDescription","tProduct","ProductCode='" &
[cboProduct] & "'")
3 – UnitPrice with control source:
=DLookUp("UnitPrice","tProduct","ProductCode='" & [cboProduct] & "'")
4 – UnitsSold with control source UnitsSold, which I now realize won’t
let me change the default value from 1 to anything else although it is
enabled and not locked - a new problem to solve
fInv form record source is SELECT tInv.* FROM tInv INNER JOIN tClass
ON tInv.ClassCode = tClass.ClassCode;
fInvDtl subform record source is SELECT tInventoryTransactions.* FROM
tInventoryTransactions; This subform contains the object InvDtlNo
with control source InvoiceID (which is a number in
tInventoryTransactions table).
The tInventoryTransactions table makes a record for each product
invoiced and reduces inventory (this works okay).
The detail section of fInv contains object InvNo with control source
InvNo.
REPORT (need help getting UnitPrice and Description to show up)
rInv report has a group 0 with a similar table to that in the form.
Except I’ll need this table to do some math with extended price and
such, which I think I can figure out – I only I could get the price in
there.
Columns are:
1 – UnitsSold with control source UnitsSold (see above for problem
with changing default 1 value)
2 – Description with control source
=[tInventoryTransactions.ProductCode] & " - " & [ProductDescription]
This partially works in that the ProductCode dash part shows without
the description on the end. And previewing the report asks for
ProductDescription input.
3 – UnitPrice with control source UnitPrice (value doesn’t show on
report)
4 – ExtPrice with control source =[UnitsSold]*[UnitPrice] (previewing
report asks for ExtPrice input; I’m guessing because it doesn’t have
UnitPrice)
rInv report record source is:
SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec AS
Expr1, tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode,
tClient.*, tInv.PaymentMethod, tInv.ClassDates, tInv.Terms,
tInv.DueDate, tInventoryTransactions.*
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]));
Prices are currency and numbers are number formats.
fInv form Code is:
Option Compare Database
Private Sub ClassCode_AfterUpdate()
Me!ClassDates = DLookup("ClassDate", "tClass", "ClassCode = '" & Me!
ClassCode & "'")
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myEmpCode As Variant
myEmpCode = DLookup("EmpCode", "tEmployee", "[EmpFirstName]='" &
CurrentUser() & "'")
If IsNull(myEmpCode) Then
MsgBox "Warning :Employee code missing from employee table"
Else
Me!EmpCode = myEmpCode
End If
End Sub
Private Sub PreviewInvoice_Click()
If (Me!InvNo = 0) Then
Me!InvNo = Nz(DMax("[InvNo]", "tInv")) + 2
End If
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
Private Sub CommandSave_Click()
On Error GoTo Err_CommandSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Exit_CommandSave_Click:
Exit Sub
Err_CommandSave_Click:
MsgBox Err.Description
Resume Exit_CommandSave_Click
End Sub
fInvDtl subform Code is:
Option Compare Database
Option Explicit
Private Sub cboProduct_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Me!cboProduct.Column(3) = "X" Then
MsgBox "Product is discontinued"
Cancel = True
Exit Sub
End If
End If
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me!UnitsSold) Then
MsgBox "Enter a quantity"
Cancel = True
Exit Sub
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!TransactionDescription = [Forms]![fInv]![PayerID].Column(1) & "
- " & "Inv" & " " & [Forms]![fInv]![InvDtlNo]
Me!UnitPrice = DLookup("UnitPrice", "tProduct", "ProductCode = '"
& Me!ProductCode & "'")
Me!ProductDescription = DLookup("ProductDescription", "tProduct",
"ProductCode = '" & Me!ProductCode & "'")
End Sub
Any ideas where I should look would be hugely appreciated as well as
how to keep all this information organized. A person can only stare
at this for so long before they start to go crazy.
Sorry for the long post and thanks again for reading this,
Michele
With help for you kind folks, I’m almost at the top of, what I feel is
like, Mount Everest. I’ve been able to create a course certificate,
receipt and course booking confirmation. Now to reach the top, I need
to finish an invoice.
fInv form works great except units sold quantity won't let me change
the amount from the default of 1.
rInv report works so far with the exception of unitprice and
productdescription, which are on the form, but not the report. Can I
ask your help again to get this part working? This is the first time
I've used a subform and I'm trying to use the packing slip as an
example because it's very similar except without pricing.
Here’s what there is so far:
FORM (works except units sold quantity)
fInv form has a subform called fInvDtl where one chooses the products
to be invoiced from the tProduct table. This fInvDtl subform has four
columns:
1 - ProductCode with row source:
SELECT tProduct.ProductCode, tProduct.ProductDescription,
tProduct.Type FROM tProduct WHERE (((tProduct.Type)<>"X")) ORDER BY
tProduct.ProductCode;
2 – TextProductDescription with control source:
=DLookUp("ProductDescription","tProduct","ProductCode='" &
[cboProduct] & "'")
3 – UnitPrice with control source:
=DLookUp("UnitPrice","tProduct","ProductCode='" & [cboProduct] & "'")
4 – UnitsSold with control source UnitsSold, which I now realize won’t
let me change the default value from 1 to anything else although it is
enabled and not locked - a new problem to solve
fInv form record source is SELECT tInv.* FROM tInv INNER JOIN tClass
ON tInv.ClassCode = tClass.ClassCode;
fInvDtl subform record source is SELECT tInventoryTransactions.* FROM
tInventoryTransactions; This subform contains the object InvDtlNo
with control source InvoiceID (which is a number in
tInventoryTransactions table).
The tInventoryTransactions table makes a record for each product
invoiced and reduces inventory (this works okay).
The detail section of fInv contains object InvNo with control source
InvNo.
REPORT (need help getting UnitPrice and Description to show up)
rInv report has a group 0 with a similar table to that in the form.
Except I’ll need this table to do some math with extended price and
such, which I think I can figure out – I only I could get the price in
there.
Columns are:
1 – UnitsSold with control source UnitsSold (see above for problem
with changing default 1 value)
2 – Description with control source
=[tInventoryTransactions.ProductCode] & " - " & [ProductDescription]
This partially works in that the ProductCode dash part shows without
the description on the end. And previewing the report asks for
ProductDescription input.
3 – UnitPrice with control source UnitPrice (value doesn’t show on
report)
4 – ExtPrice with control source =[UnitsSold]*[UnitPrice] (previewing
report asks for ExtPrice input; I’m guessing because it doesn’t have
UnitPrice)
rInv report record source is:
SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec AS
Expr1, tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode,
tClient.*, tInv.PaymentMethod, tInv.ClassDates, tInv.Terms,
tInv.DueDate, tInventoryTransactions.*
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]));
Prices are currency and numbers are number formats.
fInv form Code is:
Option Compare Database
Private Sub ClassCode_AfterUpdate()
Me!ClassDates = DLookup("ClassDate", "tClass", "ClassCode = '" & Me!
ClassCode & "'")
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myEmpCode As Variant
myEmpCode = DLookup("EmpCode", "tEmployee", "[EmpFirstName]='" &
CurrentUser() & "'")
If IsNull(myEmpCode) Then
MsgBox "Warning :Employee code missing from employee table"
Else
Me!EmpCode = myEmpCode
End If
End Sub
Private Sub PreviewInvoice_Click()
If (Me!InvNo = 0) Then
Me!InvNo = Nz(DMax("[InvNo]", "tInv")) + 2
End If
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub
Private Sub CommandSave_Click()
On Error GoTo Err_CommandSave_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Exit_CommandSave_Click:
Exit Sub
Err_CommandSave_Click:
MsgBox Err.Description
Resume Exit_CommandSave_Click
End Sub
fInvDtl subform Code is:
Option Compare Database
Option Explicit
Private Sub cboProduct_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If Me!cboProduct.Column(3) = "X" Then
MsgBox "Product is discontinued"
Cancel = True
Exit Sub
End If
End If
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me!UnitsSold) Then
MsgBox "Enter a quantity"
Cancel = True
Exit Sub
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!TransactionDescription = [Forms]![fInv]![PayerID].Column(1) & "
- " & "Inv" & " " & [Forms]![fInv]![InvDtlNo]
Me!UnitPrice = DLookup("UnitPrice", "tProduct", "ProductCode = '"
& Me!ProductCode & "'")
Me!ProductDescription = DLookup("ProductDescription", "tProduct",
"ProductCode = '" & Me!ProductCode & "'")
End Sub
Any ideas where I should look would be hugely appreciated as well as
how to keep all this information organized. A person can only stare
at this for so long before they start to go crazy.
Sorry for the long post and thanks again for reading this,
Michele