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

On Oct 21, 8:49*pm, mjones <mich...@quality-computing.com> wrote:
> 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

P.S. = UnitsSold now works by removing the lines

Me!UnitPrice = DLookup("UnitPrice", "tProduct", "ProductCode = '"
& Me!ProductCode & "'")
Me!ProductDescription = DLookup("ProductDescription", "tProduct",
"ProductCode = '" & Me!ProductCode & "'")

On Oct 21, 11:05*pm, mjones <mich...@quality-computing.com> wrote:
> On Oct 21, 8:49*pm, mjones <mich...@quality-computing.com> wrote:
>
>
>
>
>
>
>
>
>
> > 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
>
> P.S. = UnitsSold now works by removing the lines
>
> * * Me!UnitPrice = DLookup("UnitPrice", "tProduct", "ProductCode = '"
> & Me!ProductCode & "'")
> * * Me!ProductDescription = DLookup("ProductDescription", "tProduct",
> "ProductCode = '" & Me!ProductCode & "'")

Just letting everyone know that I figured out the 3 problems with
units sold, description and unit price. Thanks.