time and Billing access database template - reporting help

[akechag]

Hi
I have downloaded and started using the time and billing template.
I have two issues that i do not know how to address.
1. Because i have some projects that run through more than one month but at the same time i need to raise and invoice for each one of these projects at the end of each month, i need to modify the database so that i can issue an invoice depending on the month i need the invoice for.
for example: i have a project running from September 2021....i will have to issue an invoice on September 30, October 31st, November 30th and so on....And each invoice should contain only the manhours and fees spend on this particular month, i.e. the October 31st invoice should contain only the manhours for October. But should also show any outstanding amounts from the previous month(s).
2. there is now way (or at least i cannot find how to do it), to have a unique number allocated (and subsequently stored in the database) and printed on each invoice i issue. Then, when at a later time i need to reissue this invoice, the database should somehow return the same invoice with the same number.

Any help is really appreciated and welcome. i am not really experienced with programming but i do understand some basic and intermediate Access concepts.
I am using the template as it was and i have made no changes to the structure.



Thank you very much in advance for your time to look into this post and your help if you can help

SK

[ranman256]

make a form with 2 text boxes; txtStartDate, txtEndDate


query=:
select * from table where [DateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate


you wont have to keep entering dates, and they can pick from calendar in the textbox, and it only gather the records for that range. Report on that query.

[akechag]

make a form with 2 text boxes; txtStartDate, txtEndDate


query=:
select * from table where [DateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate


you wont have to keep entering dates, and they can pick from calendar in the textbox, and it only gather the records for that range. Report on that query.

Thank you ranman256
I have created the form, that was easy.
then i created the query named "query1" but when i try to save it it gives me an error highlighting the word "table" in the SQL code line. I suppose the query searches for a table, and because it cannot find the table, it gives the error code.
Any ideas how to move forward?

[Welshgasman]

Thank you ranman256
I have created the form, that was easy.
then i created the query named "query1" but when i try to save it it gives me an error highlighting the word "table" in the SQL code line. I suppose the query searches for a table, and because it cannot find the table, it gives the error code.
Any ideas how to move forward?

You have to supply the correct table name as well as the correct field names?

That is just an example?

[davegri]

I made a modified version of the time and billing template a few months ago that includes invoice billings. On demand, a new invoice for a project is created with amounts due since last invoice. Each invoice has an unique number. You cannot issue duplicate invoice numbers with different figures for each month. If you want to pursue this, PM me.


The ProjectPayments table is replaced by the ProjectInvoices table:

[akechag]

Hi
That can definitely work for me. unfortunately i cannot find the PM function in the forum

[davegri]

Hi
That can definitely work for me. unfortunately i cannot find the PM function in the forum

Ah, yes. I think you have to have a certain minimum number of posts before PM privilege is granted.

I had to pare down some functionality requested by a former patron, but hope core functionality is still intact.
Project compiles with no errors, but runtimes may occur. My modifications were implemented against a DB with data already present. This DB is empty, and may have runtime errors when data entry initially starts. If you run into problems, help is always here.



And the DB:

akechag-davegri-v01.zip

[akechag]

Hi davegri
Really appreciate your help.
This worked out really well. I only have a problem with the projects form. I debugged and got this when i click on the project id link to open an existing project (one i input as test) or when i click on the "new" link to enter a new project
Any ideas?
This is a screenshot of the debug


I am now trying to create a report for a single invoice per customer, containing all projects for a given period.

SK

[Welshgasman]

You have to say what the error is?

[akechag]

it says runtime error 3075 missing operator

[Micron]

Me.ID holds text data type and you are missing delimiting quotes for text? I know that missing delimiters will raise that error.

[davegri]

I don't get that error. I added one customer, one employee, then added new project. Clicked on project and got project details:

[davegri]

With further testing I did encounter the error.



You can avoid the error with this code change:

Private Sub ID_Click()
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (Me.ID & "") = "" Then Exit Sub
    DoCmd.OpenForm "ProjectDetails", , , "Projects_PK=" & Me.ID, , acDialog
    
    Me.Requery
    On Error Resume Next
    DoCmd.SearchForRecord , "", acFirst, "[Projects_PK]=" & Me.ID
End Sub

[akechag]

That did the JOB ! Thank you!!!

With further testing I did encounter the error.



You can avoid the error with this code change:

Private Sub ID_Click()
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (Me.ID & "") = "" Then Exit Sub
    DoCmd.OpenForm "ProjectDetails", , , "Projects_PK=" & Me.ID, , acDialog
    
    Me.Requery
    On Error Resume Next
    DoCmd.SearchForRecord , "", acFirst, "[Projects_PK]=" & Me.ID
End Sub

[ssanfu]

You are using

If (Form.Dirty) Then
      DoCmd.RunCommand acCmdSaveRecord
End If

I would use

If (Me.Dirty) Then
      Me.Dirty = False
End If

See Docmd.Runcommand accmdsaverecord vs Me.Dirty = False