Replacing Excel form with Access so we can capture the data (1 Viewer)

SpeedyA

New member
Local time
Today, 12:05
Joined
Sep 24, 2021
Messages
3
Hi All,

I am hoping that you can help me. I've been using access for years but after a long break I'm having trouble breaking back into it.

What I'm trying to achieve is create a database whereby an 'Invoice' document will be produced but also the information is saved in an invoice table.

Currently we use the attached Excel file for the invoicing template which uses macros to hide/show if certain options are selected and auto populate default prices by using VLOOKUPs.

Can someone point me in the right direction on where to begin to

1. Get access to produce the excel document
2. Create the form so that the document can be produced and hold the data in an invoice table

All help is greatly appreciated.

Thanks in advance!
 

Attachments

  • New Invoice Template - Copy.zip
    37.4 KB · Views: 270

plog

Banishment Pending
Local time
Today, 07:05
Joined
May 11, 2011
Messages
11,611
The first step of any database is getting your tables properly structured. That process is called normalization:


Read up on that, work a few tutorials and then create a blank database and attempt it with your data. When done, set up the Reationship Tool in Access and take a screenshot and post it back here (or you can just upload the database too). Then we can review it, ask some questions of your data and get the tables properly set up.

Once you have that, then you work on Reports/Queries and finally forms.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Feb 19, 2002
Messages
42,970
An Invoicing application requires a number of tables.
tblCustomers:
CustID (autonumber PK)
CustName
ContactName
ContactPhone
BillingAddr1
BillingAddr2
BillingCity
BillintST
BillingZip
ShippingAddr1
ShippingAddr2
ShippingCity
ShippingST
ShippingZip
CreditLimit
TaxExemptYN
etc.

tblProducts
ProdID (autonumber PK)
ProdName
UnitPrice
QtyOnHand
ShippingWeight
TaxExemptYN
etc.

tblOrders
OrderID (autonumber PK)
CustID (FK to tblCustomers)
OrderDT
etc.

tblOrderDetails
DetailID (autonumber PK)
OrderID (FK to tblOrders)
ProdID (FK to tblProducts)
Qty
Discount%
etc.

tblInvoicse
InvoiceID (autonumber PK)
OrderID (FK to tblOrders)
InvoiceDT
PmtDueDT
PaidDT
PaidAmt
etc.

and a few more such as tax rates for whatever taxing authorities you need to deal with.

I don't like the MS samples but someone might have a sample that can get you started. or just start with these as tables and go from there.

The assumption this model makes is that you have an invoice separate from the order. That is a useful assumption if you have to rebill the same order if the customer doesn't pay promptly.

Without knowing ALL your business rules, all we can do is guess and suggest generally useful schemas.
 

Isaac

Lifelong Learner
Local time
Today, 05:05
Joined
Mar 14, 2017
Messages
8,738
As Pat said, a database application starts with tables and entities and modeling the data properly.

This forum works better when a person asks specific questions about one specific thing they're having trouble with, or want feedback or insight on.
In your case you're interested in building an entire application and that's about all you know. You need to come up with your own specific set of requirements and probably hire a developer to build this for you.

An entire program isn't built from a single post asking for free help. And you wouldn't want that - then all you'd have is a weapon you didn't understand to shoot yourself in the foot with.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:05
Joined
Jul 9, 2003
Messages
16,244
Have you seen the northwind sample database provided by Microsoft? It demonstrates an invoicing solution.

It is a sample database, not designed for use in production. It does have a few problems which I highlight in my blog, and I also show several solutions of varying complexity.

See here:-

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:05
Joined
Jul 9, 2003
Messages
16,244
Both versions of the Northwind database are available for download here:-

 

SpeedyA

New member
Local time
Today, 12:05
Joined
Sep 24, 2021
Messages
3
Thanks everyone.

The area that I am struggling with is getting the invoice line items to automatically populate.

Invoice header contains a Product Code:

I then want to populate the invoice line item table automatically ready to verification.

Eg,

Product A is selected on the Invoice Header

Form the auto populates the line items table with

Item Codes
Service A
Travel Hours
Mileage
 

SpeedyA

New member
Local time
Today, 12:05
Joined
Sep 24, 2021
Messages
3
Yes the line items table is displayed as a sub form on the main form.

Which i want to auto populate the line items based on the product field selected on the main form.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:05
Joined
Jul 9, 2003
Messages
16,244
Is the Subform linked to the main form via a child parent link?

For more info on this see video number 3 here:- (time index 1:38)

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Feb 19, 2002
Messages
42,970
If the Invoice is tied to an order, you don't need to duplicate the order details as invoice details. If you want to print the details on the invoice, get them from the orderdetails table.
 

Users who are viewing this thread

Top Bottom