General Table/Input Data Question

have a bit of a problem for a small little project i wish to finish and i am
stuck on a minor detail.

Basically this little project has to keep track of receipts

Company Name
Vat Number
Date of Receipt
Amount of Transaction

the easy way of doing it of course is just straight input it into the table
(either directly or via the use of a form to mask the entry into the table in
a pretty way)

I have gone the form route and well i have discovered that i have several
receipts from the same company which means i have to enter the company name
and Vat number over and over again.

which is time consuming as you can imagine

i could create a companies table and have the form pull the info from this
table. BUT if there is a new company i have to go to the company table input
it there and then go to the form to fill out the rest

is there a way (im sure there is.. i just cant get my head around it right
now)

to have the form pull up the company and vat data from a table, if that
particular data does not exist allow you to input it on the spot and then,
let you fill in the rest date and amount of transaction ?

thanks in advance..

Sid

Sid

Check Access HELP NotInList and LimitToList. HELP has syntax and
examples you can adapt.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sid" wrote in message
...
have a bit of a problem for a small little project i wish to finish and i
am
stuck on a minor detail.

Basically this little project has to keep track of receipts

Company Name
Vat Number
Date of Receipt
Amount of Transaction

the easy way of doing it of course is just straight input it into the
table
(either directly or via the use of a form to mask the entry into the table
in
a pretty way)

I have gone the form route and well i have discovered that i have several
receipts from the same company which means i have to enter the company
name
and Vat number over and over again.

which is time consuming as you can imagine

i could create a companies table and have the form pull the info from this
table. BUT if there is a new company i have to go to the company table
input
it there and then go to the form to fill out the rest

is there a way (im sure there is.. i just cant get my head around it right
now)

to have the form pull up the company and vat data from a table, if that
particular data does not exist allow you to input it on the spot and then,
let you fill in the rest date and amount of transaction ?

thanks in advance..

Sid

Sid:

You really do need a Companies table to avoid redundancy in the receipts
table. A table which stores data redundantly is not correctly normalized and
at risk of inconsistent data. The Companies table would be like this:

Companies
….CompanyID (autonumber primary key)
….CompanyName
….VATNumber
….more columns for address etc

In the Receipts table just include a CompanyID column as a foreign key of
long integer number data type (not an autonumber this time), removing the
redundant CompanyName and VATNumber columns, and create a relationship from
Companies to Receipts, enforcing referential integrity.

In the Receipts form use a combo box as the control for the CompanyID field
and set up the combo box as follows:

Name: cboCompany

ControlSource: CmpanyID

RowSource: SELECT [CompanyID], [VATNumber], [CompanyName] FROM [Companies]
ORDER BY [CompanyName];

BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first two
dimensions are zero to hide the first two columns and that the third is at
least as wide as the combo box.

Add an unbound text box to the form, with a ControlSource property of:

=[cboCompany].Column(1)

This will show the VAT number for the selected company. The Column property
is zero-based, so Column(1) is the second column, VATNumber.

You can add a new company directly via the combo box by typing in the new
company name. To facilitate this you include the following code in the
combo box's NotInList event procedu

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCompanies", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCompanies closed
DoCmd.Close acForm, "frmCompanies"
' ensure company has been added
If Not IsNull(DLookup("[CompanyID]", "[Companies]", _
"[CompanyName] = """ & NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Companies table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If


The above code opens a form frmCompanies, which you'll need to create, bound
to the Companies table, and passes the new company name to it. In the form
you can then enter the VAT number, address details etc for the new company,
and when you close the form you'll be returned to the receipts form with the
new company added to the combo box's list.

In the Open event procedure of frmCompanies put the following code:

If Not IsNull(Me.OpenArgs) Then
Me.[CompanyNamee].DefaultValue = """" & Me.OpenArgs & """"
End If

This sets the default value of the CompanyName control to the value passed to
the form as its OpenArgs property.

Ken Sheridan
Stafford, England

Sid wrote:
have a bit of a problem for a small little project i wish to finish and i am
stuck on a minor detail.

Basically this little project has to keep track of receipts

Company Name
Vat Number
Date of Receipt
Amount of Transaction

the easy way of doing it of course is just straight input it into the table
(either directly or via the use of a form to mask the entry into the table in
a pretty way)

I have gone the form route and well i have discovered that i have several
receipts from the same company which means i have to enter the company name
and Vat number over and over again.

which is time consuming as you can imagine

i could create a companies table and have the form pull the info from this
table. BUT if there is a new company i have to go to the company table input
it there and then go to the form to fill out the rest

is there a way (im sure there is.. i just cant get my head around it right
now)

to have the form pull up the company and vat data from a table, if that
particular data does not exist allow you to input it on the spot and then,
let you fill in the rest date and amount of transaction ?

thanks in advance..

Sid

--
Message posted via http://www.accessmonster.com

"Sid" wrote in message
...
have a bit of a problem for a small little project i wish to finish and i
am
stuck on a minor detail.

Basically this little project has to keep track of receipts

Company Name
Vat Number
Date of Receipt
Amount of Transaction

the easy way of doing it of course is just straight input it into the
table
(either directly or via the use of a form to mask the entry into the table
in
a pretty way)

I have gone the form route and well i have discovered that i have several
receipts from the same company which means i have to enter the company
name
and Vat number over and over again.

which is time consuming as you can imagine

i could create a companies table and have the form pull the info from this
table. BUT if there is a new company i have to go to the company table
input
it there and then go to the form to fill out the rest

is there a way (im sure there is.. i just cant get my head around it right
now)

to have the form pull up the company and vat data from a table, if that
particular data does not exist allow you to input it on the spot and then,
let you fill in the rest date and amount of transaction ?

thanks in advance..

Sid

gggggggg
"Sid" a écrit dans le message de groupe de
discussion : ...
have a bit of a problem for a small little project i wish to finish and i
am
stuck on a minor detail.

Basically this little project has to keep track of receipts

Company Name
Vat Number
Date of Receipt
Amount of Transaction

the easy way of doing it of course is just straight input it into the
table
(either directly or via the use of a form to mask the entry into the table
in
a pretty way)

I have gone the form route and well i have discovered that i have several
receipts from the same company which means i have to enter the company
name
and Vat number over and over again.

which is time consuming as you can imagine

i could create a companies table and have the form pull the info from this
table. BUT if there is a new company i have to go to the company table
input
it there and then go to the form to fill out the rest

is there a way (im sure there is.. i just cant get my head around it right
now)

to have the form pull up the company and vat data from a table, if that
particular data does not exist allow you to input it on the spot and then,
let you fill in the rest date and amount of transaction ?

thanks in advance..

Sid