Append only current record in a form to a table

[AFuturePrez]

I have searched for this topic and have not found exactly what I
need.
Scenario: I need to use the label wizard to create multiple labels
from one record on a form. To do this, First, I need to know how to
create a table of JUST the current forms record data. Example: Job,
Line, Name, Number
Please assist.

 

[Arvin Meyer [MVP]]

I have searched for this topic and have not found exactly what I
need.
Scenario: I need to use the label wizard to create multiple labels
from one record on a form. To do this, First, I need to know how to
create a table of JUST the current forms record data. Example: Job,
Line, Name, Number
Please assist.

Use an Append query:

INSERT INTO MyTable(Job, Line, Name, Number) VALUES ('" & Me.txtJob & "', '"
& Me.txtLine & "', " & Me.txtName & ", " & Me.txtNumber & ");"

As a Note, you need to change the names of fields and controls with Name,
Line, Number. These are reserved words.

 

[Clifford Bass]

Hi,

Instead of creating a separate table for your label data, just use a
query that does what is called a cartesian join with a numbers table.
Borrowing this from Dale Fye.

Create another table (tblNumbers) with a single field (lngNumber) of
type long integer. Put 10 records in it (the numbers 0 to 9).

Create another query that pulls your data from your form's table and
uses the tblNumbers table to produce multiple copies of the same label:

select Job, Line, [Name], [Number]
from tblYourTable, tblNumbers
where Job = [Forms]![frmYourForm]![txtJob]
and lngNumber < 5;

Several notes:

1) This will produce five copies of the same label.

2) "Name" and "Number" are reserved words in Access. It is better not
to use those for column and control names as doing so can produce unexpected
results. Better to use something like JobName, SomeNumber. Search for
"reserved words" in online help for a list of reserved words.

3) If you need more than 10 labels, create a query that uses the
tblNumbers and use the query instead of the table. This will provide the
numbers from 0 to 99:

select Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
from tblNumbers as Tens, tblNumbers as Ones;

4) The [Forms]![frmYourForm]![txtJob], in case you do not know, is how
you tell the query to get the value from the "txtJob" control (presumed name
of the control that holds the Job) of the currently displayed row in your
from named "frmYourForm". The form must remain open for the query to work.

Use the first query as the data source when you run the create label
report wizard.

Hopes that helps,

Clifford Bass

 

[Ken Sheridan]

There is no need to append records to a separate table to create multiple
labels. Create another table LabelNumbers with a single column of number
data type LabelNumber and fill it with values from 1 to 100 say (or whatever
is the maximum possible number of labels you might want to print per record;
you can go as high as you wish).

Create a query which includes your current table and the LabelNumbers table,
but don't join the tables. This will return what's known as the 'Cartesian
product' of the two tables, i.e. each row in one is joined to each row in the
other, so if you have 1000 rows in your current table and 100 rows in
LabelNumbers it would return100,000 rows. Add the necessary fields from your
current table to the query, including its primary key column, which I'll call
MyID and will assume is a number data type. Also add the LabelNumber column,
but uncheck its 'show' check box in query design view and in its 'criteria'
row put [Number of labels to print:]

Design your label report using the wizard, basing it on the query you've
created above. On your form add a button to open the report with code like
this in the button's Click event procedure:

Dim strCriteria As String

strCriteria = "[MyID] = " & Me.[MyID]

' ensure current record is saved
Me.Dirty = False
' print labels from current record
DoCmd.OpenReport "YourLabelReportName", WhereCondition:=strCriteria

when you click the button you'll be prompted with: Number of labels to
print: and however many labels you specify will then be printed.

If you want a default number printed if no value is entered at the prompt
change the parameter in the criteria row of the query to, if the default
number to be printed is 24 say:

Nz([Number of labels to print:],24)

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Correction: the parameter should be:

<=[Number of labels to print:]

or if a default is required:

<=Nz([Number of labels to print:],24)

Ken Sheridan
Stafford, England