Static data in a form

[Aaron Howe]

Hi guys,

Little problem I hope you can help with. I have a form
which is to be used for mass data entry, using Acess
rather than Excel so that many of the variables in the
form can be updated easier. Certain fields in the form
need completing for only one record and can be carried
across, and some need to be completed for each record.
For example; I might start with a date, an invoice number
and a client name - I would then proceed to enter line by
line (record by record) data to go on that invoice.

I have managed to keep some of the data static by using a
combo box which does not clear after each record is
created, however I can't seem to do that with the date
field. I tried and failed miserably to send Ctrl + with
Sendkeys but didn't get anywhere. Is it at all possible
to make these fields remain in place throughout all
records of the data set? Should I be looking to use a
subform for this? And can a subform return the static
data for each entry along with the input data?

 

[Wayne Morgan]

By the description you've given, it appears that you may want a subform.
Place the "static" data on the main form and the individual entries in the
subform. These should also be two different tables with a one-to-many
relationship between them.

One way to "carry forward" the previous entry is to set the Default Value of
the control in the form's BeforeUpdate event. Once this is done, when you go
to the next new record, the default value of the control will be entered for
you.

Example:
Me.txtMyTextbox.DefaultValue = Me.txtMyTextbox

 

[Guest]

Hi Wayne,

Thanks for your reply. Yes I think it is a subform I
need, the problem I seemed to be having was making the
static data carry forward with the data I was entering.
For example, I could enter several lines for the invoice
but they were just invoice lines with none of the static
data intact. To illustrate:
Example Agency Ltd | 13/12/04 | Inv0002
2 Boxes on evelopes @ £5
3 Boxes of paper @ @6
4 Boxes of paperclips @ 3

Total £40

The result would be in my table:
2 | Envelopes | 10
3 | Paper | 18
4 | Paperclips | 12

When it should be

Example Ltd | 13/12/04 | Inv0002 | 2 | Envelopes | 10
Example Ltd | 13/12/04 | Inv0002 | 3 | Paper | 18
Example Ltd | 13/12/04 | Inv0002 | 4 | Paperclips | 12

Sounds long winded I know, but I intend to export as CSV
for upload to another system, so each line has to be
detailed this way. What I seem unable to do is update
each line of "Data Entry" with the static info. I have
been playing about with the event procedures and copying
info, and can do this within the form but can't get the
subform to co-operate. The static info is incidentally
pulled from many places and may change from time to time
so I can't use default value, but that's something I can
work around. What I guess I'm missing really is the
syntax for copying across that static text line by line...?

 

[Wayne Morgan]

The mention of the Default Value was just to carry forward to the next new
entry in the form. It won't work for exporting the data out as text.

To export the data out as text, I would recommend a query with both (all) of
the tables involved added to the query. Add the fields from each table that
you need in the query's output. Use this query to export the data to text.
The query will repeat the data in the "static data" table (main form's
record source) for each entry in the "dynamic data" table (subform's record
source) that the query outputs.

Example:
SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field2,
Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1;

In the query, Table1 would supply the data for the main form and Table2
would supply the data for the subform. The output of the query would look
like:

T1.F1 T1.F2 T1.F3 T2.F2 T2.F3
1 abc aaa 50 27
1 abc aaa 51 33
1 abc aaa 56 90
2 abb bbb 75 21
2 abb bbb 77 30
etc.

As you can see, the "static" information from Table1 is in each line.

--
Wayne Morgan
MS Access MVP


Hi Wayne,

Thanks for your reply. Yes I think it is a subform I
need, the problem I seemed to be having was making the
static data carry forward with the data I was entering.
For example, I could enter several lines for the invoice
but they were just invoice lines with none of the static
data intact. To illustrate:
Example Agency Ltd | 13/12/04 | Inv0002
2 Boxes on evelopes @ £5
3 Boxes of paper @ @6
4 Boxes of paperclips @ 3

Total £40

The result would be in my table:
2 | Envelopes | 10
3 | Paper | 18
4 | Paperclips | 12

When it should be

Example Ltd | 13/12/04 | Inv0002 | 2 | Envelopes | 10
Example Ltd | 13/12/04 | Inv0002 | 3 | Paper | 18
Example Ltd | 13/12/04 | Inv0002 | 4 | Paperclips | 12

Sounds long winded I know, but I intend to export as CSV
for upload to another system, so each line has to be
detailed this way. What I seem unable to do is update
each line of "Data Entry" with the static info. I have
been playing about with the event procedures and copying
info, and can do this within the form but can't get the
subform to co-operate. The static info is incidentally
pulled from many places and may change from time to time
so I can't use default value, but that's something I can
work around. What I guess I'm missing really is the
syntax for copying across that static text line by line...?

 

[Aaron Howe]

And this would automatically add static data for each
piece of added data? I'm pretty sure it does and I can
test it, but thought it best to ask for anyone else with
the same problem.

Thanks very much for this help by the way