Finishing Touches

[Elvaar]

I am almost done with a database i have been working on, there are just a few things i would like to "polish up"

First off, thanks to Pat and Jon K. most of my questions have already been answered - either by me asking or by searching through all the information already here.

The last couple issues i am having are these:

1) Limiting data entry in a subform. I have a form/subform based on a query that joins 2 tables (Invoices and Transactions). In this case the invoices table has a 1-many relationship with the transaction table. There can be any number of transactions per 1 invoice. These tables are related through "InvoiceID" What i want to do is limit the number of transactions per invoiceID to 10. I have seen a couple of examples in searching but cant get any of them to work, one was proposed by Rich...the other used DCount.

What is the best way to set up this code? Do i need to code this into a before update event? or is there a better place?

2) One of my reports is going to be multiple pages long, is there a way i can make it so preview report shows the LAST PAGE first, or shows only the last page? Keep in mind i do not want to change order of my records, just the pages.

3) Is there a way to limit the viewable results of queries/reports by how old they are? Like say i want to only view results from the last 60 days. However i do not want to exclude the earlier results because they are used in calculations/running totals, I just dont want to SEE them.

Thanks again for all your help, Even though Access is mostly just a hobby i have really learned alot over the last 4 years or so and I owe most of that to this forum and all of you. (Now onto understanding SQL and VBA better )

 

[dt01pqt]

2) One of my reports is going to be multiple pages long, is there a way i can make it so preview report shows the LAST PAGE first, or shows only the last page? Keep in mind i do not want to change order of my records, just the pages.

In the OrderBy property of the report put [key field] DESC, and set the order by on to yes.

 

[dt01pqt]

1) Limiting data entry in a subform. I have a form/subform based on a query that joins 2 tables (Invoices and Transactions). In this case the invoices table has a 1-many relationship with the transaction table. There can be any number of transactions per 1 invoice. These tables are related through "InvoiceID" What i want to do is limit the number of transactions per invoiceID to 10. I have seen a couple of examples in searching but cant get any of them to work, one was proposed by Rich...the other used DCount.

In the before update of the sub form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[InvoiceID]", "Transactions", "[InvoiceID] = " & Forms![I]MainForm[/I]![InvoiceID])  > 10 Then
MsgBox "You can't have more than 10 transactions per Invoice"
Cancel = True
End If
End Sub

 

[Elvaar]

i get "Data Type Mismatch" errors every time i try to implement that code. Here is my exact version:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("invoicenumber", "Transactions", "invoicenumber = " & Forms!Invoices![Invoicenumber]) >= 10 Then
MsgBox "You can't have more than 10 transactions per Invoice"
Cancel = True
End If
End Sub

where [invoicenumber] is the primary key a table called "Invoices" and the foreign key in a table called "Transactions". I have implemented this code into the Before Update section of the subform, the subform is called [Transactions Subform] and the main form is called [Invoices]

i am at an impass

 

[Elvaar]

my invoicenumber field was a text field so changing the code to:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("invoicenumber", "Transactions", "invoicenumber = """ & Forms![invoices]![InvoiceNumber] & """") >= 10 Then
MsgBox "You can't have more than 10 transactions per Invoice"
Cancel = True
Me.Undo
End If
End Sub

has worked perfect, thanks again for the help on this matter

 

[dt01pqt]

has worked perfect, thanks again for the help on this matter

No problem, I will look into your other problems after I go to the barbers. Incidentally =>10 to cancel will give you nine invoices.

 

[dt01pqt]

3) Is there a way to limit the viewable results of queries/reports by how old they are? Like say i want to only view results from the last 60 days. However i do not want to exclude the earlier results because they are used in calculations/running totals, I just dont want to SEE them.

Try this:

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "[[I]Sample Date[/I]] > #" & Format(DateAdd("d", -60, Date), "mm-dd-yy") & "#"
Me.FilterOn = True
End Sub