Hello guys. I need a help for my new project. I'm new to this forum and new to MS Access. Not much Idea about VB codes and all.
I have three different tables with different number format and sales details with values. From those three table details I need to raise invoices. I combined three tables by union query. Now I need to generate invoice numbers for those union query. Can you please help me on this.
Welcome Sandaruwan,
We'd love to help you but you haven't given us enough information to take a stab at what you want. What do the tables look like? What does the invoice look like? What have you tried and what problem do you have?
Also, we help YOU to solve a problem rather than building applications for you.
A union query implies that the three tables are the same format and contain the same type of data. This is indicative of a design flaw. Why have three tables when all the data can be placed in a single table perhaps with a new column to identify what type of data a row contains.
Welcome Sandaruwan,
We'd love to help you but you haven't given us enough information to take a stab at what you want. What do the tables look like? What does the invoice look like? What have you tried and what problem do you have?
Also, we help YOU to solve a problem rather than building applications for you.
A union query implies that the three tables are the same format and contain the same type of data. This is indicative of a design flaw. Why have three tables when all the data can be placed in a single table perhaps with a new column to identify what type of data a row contains.
Sorry for the less information. Those tables are for different three sales department. Tables has Log numbers, Client Names and Sales value. Each three table has different Log Number format. I need to create a sales report by those tables so I need to keep separate tables for separate sales department.
Even if I create a single table for all sales department I don't know how to add auto generate number format to invoices. In invoices I will mention the Log Number, Sales Value and all the details I update in the Tables apart from that I need to add auto generate number format also.
I need to build it my self not give it to anyone else.
Are these three Tables identical...i.e. do that have the same exact Fields? If so, you do, indeed, need to combine them into one Table, simply adding another Field to designate which sales department a given Record comes under.
As for auto-generating of an invoice number...this is a common subject on any Access forum. Here's a typical Auto incrementing Number hack from my own archives that should point you in the right direction.
The first code here would be for an InvoiceNumber that is defined in the table as Text Datatype. "Number" fields that aren't used for math are often defined as Text.
Code:
[B]Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.InvoiceNumber = "1"
Else
Me.InvoiceNumber = DMax("val([InvoiceNumber])", "YourTableName") + 1
End If
End If
End Sub
[/B]
Here's the same code for an InvoiceNumber defined as Numerical:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.InvoiceNumber = 1
Else
Me.InvoiceNumber = DMax("[InvoiceNumber]", "YourTableName") + 1
End If
End If
End Sub
Note that if you're entering a Record and want to print out an invoice immediately, you need to Save the Record first, before attempting to do so, in order for the InvoiceNumber to be assigned. If you need to do this, let us know if you need help with that.
This code being in the Form_BeforeUpdate event, which is the last event to fire before the Record is Saved, pretty much insures that there will be no duplicates assigned. The only possible drawback is that the number doesn't appear on the Record until the Record is Saved.
