Form with a entry box having more than one entry (?)

[Guest]

Okay, I am creating a database, etc for recording DHL waybills and their
Invoices (for us it is an SO #).
Anyway, many times one waybill references 2-5 Invoices.
I want to create a form where I don't have to enter the main information 2-5
times to record all invoices on that waybill.
Can I create a entry box that will allow me to enter all of the invoices for
that waybill and still expect it to display as separate entities on reports
and such?
I am brand new to access, literally, so please be simple in your instructions.

 

[Guest]

You need to normalize your data a bit. You should have two tables. A
Waybill table and an Invoice table. Creating a new record for each invoice
with repeating data is not a relational database, it is really a spreadsheet.
You Waybill table should have all the information about the waybill, and no
information regarding invoices.
You Invoice table should have the information about the invoice. The only
piece of data from the Waybill you want in the invoice table is the primary
key field of the Waybill record the invoice belongs to.

Then, you will want to use a form/subform with the Waybill table as the
record source of the form and the Invoice table as the record source of the
subform.

When you want to use the data for reports, use queries to associate the data
from the two tables.

You first reaction may be "Yeah, but I have to........"
No you don't. This is the correct way to do what you are doing in a
relational database.

 

[Carl Rapson]

Okay, I am creating a database, etc for recording DHL waybills and their
Invoices (for us it is an SO #).
Anyway, many times one waybill references 2-5 Invoices.
I want to create a form where I don't have to enter the main information
2-5
times to record all invoices on that waybill.
Can I create a entry box that will allow me to enter all of the invoices
for
that waybill and still expect it to display as separate entities on
reports
and such?
I am brand new to access, literally, so please be simple in your
instructions.

What you need is what is called a junction table, with two fields: the
waybill id and the invoice id. You can then add this table (or a query based
on it) to your waybill form as a subform, joined on the waybill id, and then
each invoice added will automatically be associated with the waybill. For
reports, just link your invoice table to the junction table through the
invoice id.

Carl Rapson

 

[missinglinq via AccessMonster.com]

No! You really need to have a separate record for each invoice with the
common waybill data joining them. You can facilitate data entry by setting
the Default value of any repeated fields to the last dtat entered. In other
words, if you have a waybill number, you enter that number, then assign that
number to the default value for the waybill number field. The next new record
you create will now have that number automatically filled in. So will each
new record until you manually change the waybill number.
Here's some generic code for doing this:

If the field is defined as text:

Private Sub YourTextControlName_AfterUpdate()
If Not IsNull(Me.YourTextControlName.Value) Then
YourTextControlName.DefaultValue = """" & Me.YourTextControlName.Value &
""""
End If
End Sub

If the field is defined as numeric:

Private Sub YourNumericControlName_AfterUpdate()
If Not IsNull(Me.YourNumericControlName.Value) Then
YourNumericControlName.DefaultValue = Me.YourNumericControlName.Value
End If
End Sub

Good Luck!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Whoa! Slow your horses! : )
First remember I said I am a newby to Access! - I have only been using it
for 2 days!
Secondly, I am not entering all the information from the waybill and all the
information from the invoice so I don't need two separate databases.

This is only for internal tracking purposes for one employee and one of her
customers.

My form looks like this:

Location: (A box containing 4 choices)
Ship Date: (12/17/2007)
DHL Waybill: (1234567890)
SO Number: (123456)

That is the sum total of what information I am collecting.
However, more often than not, the waybill is for more than one SO number.
So - all I want to do is make it so that I can enter all the SO numbers into
that one box (one under the other under the other) and not have to reenter
all the other information which with multiple SO's will not change.
This is what I want it to look like:

Location: (A box containing 4 choices)
Ship Date: (12/17/2007)
DHL Waybill: (1234567890)
SO Number: (123456)
(234567)
(345678)
(456789)
(567890)

Then I want to be able to still be able to run reports and queries with the
individual SO numbers.
Does that make it clearer to understand what I am looking for?
Thanks for your help.

 

[Rick Brandt]

Whoa! Slow your horses! : )
First remember I said I am a newby to Access! - I have only been
using it for 2 days!
Secondly, I am not entering all the information from the waybill and
all the information from the invoice so I don't need two separate
databases.

This is only for internal tracking purposes for one employee and one
of her customers.

My form looks like this:

Location: (A box containing 4 choices)
Ship Date: (12/17/2007)
DHL Waybill: (1234567890)
SO Number: (123456)

That is the sum total of what information I am collecting.
However, more often than not, the waybill is for more than one SO
number. So - all I want to do is make it so that I can enter all the
SO numbers into that one box (one under the other under the other)
and not have to reenter all the other information which with multiple
SO's will not change.
This is what I want it to look like:

Location: (A box containing 4 choices)
Ship Date: (12/17/2007)
DHL Waybill: (1234567890)
SO Number: (123456)
(234567)
(345678)
(456789)
(567890)

Then I want to be able to still be able to run reports and queries
with the individual SO numbers.
Does that make it clearer to understand what I am looking for?
Thanks for your help.

Yes, you still need two tables. Otherwise you might as well use Excel or Word.

 

[Guest]

Thanks for the help. I think I leave it as is and just enter the information
as often as I need to.
It seems like way too much work to enter so much information.
I'm trying to help not hinder.
But thank you for your time and efforts.
Have a wonderful day! : )