Help needed with Automatic record generation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to develop a procedure/macro that I can attach to a single command
button that will automatically create records based on the active record. I
have two fields, CardCnt1 (value of 1) and CardCnt2 (value of 3), that
represents "1 of 3". The first number, CardCnt1, says that this is the first
instance, while the second number, CardCnt2, states the total number of
instances is 3. Each record in the set will be exactly alike except for the
CardCnt1 value, which will increment until it matches the CardCnt2 value.
Basically I want to create the first record completely including the "1 of
3", click the Submit button, and have the database create the other two
records "2 of 3" and "3 of 3". How on earth can this be done?

floprock
 
My question would be - Why? Why are you creating three or more identical
records in a database? I can't think of any cases where this would make
much sense.
 
I'm creating a kanban (inventory control) card database. Basically, these
kanban cards often have multiple copies that are printed out. When stock
gets to a certain point, a card is pulled and sent to purchasing for
replenishment.

Technically they're not exactly alike because of the CardCnt1 field
(iteration). I just want to be able to present a database that will take the
grunt work out of making cards. I want them to be able to fill in only one
card and be presented with the correct number of multiple cards for the
kanban system.

I'm sure this is possible, but probably only through VBA, in which not only
can I not swim, but I'm afraid of the water as well. :)

Thanks!
floprock
 
Create a table KanbanCardCount with a single field of type integer.
Create 3 records.
Put 1 in the first record.
Put 2 in the second record.
Put 3 in the third record.

In the query editor add the KanbanCardCount table to the query but do not
join it the Inventory table.

run the query to see what happens - smile.

use the number from KanbanCardCount in one text box/label and "3" in an
unbound textbox/label - smile

To gain extra points
Color code each card according to importance.
Associate each card with a stock level not forgetting time to replenish
incorporating in house work (ordering) external work (supply) and possibly
third party work (delivery services).

or

Press the report button 3 times - John
 
John,

I can see where this would work for three of each card, but (I failed to
mention) there are not always three cards per group. There could be from one
to ten (or maybe more) of each card. Is there not a way to write an IF
statement in an append query that is based on the numbers in the CardCnt1 and
CardCnt2 fields? Maybe something like:

IF CardCnt1 < CardCnt2 THEN
add record to table and then increment field by 1
ELSE
stop
END IF

I'm not a programmer, but I'm sure this can be done...somehow. Maybe it can
be done with just macros, but that's something else that I'm very new at.
I'll be playing with this for the next few days.

Thanks for your help!
floprock
 
1) Delete existing records
DELETE * FROM KanbanCardCount ;

2) Change integer field CardCnt to autonumber
Add 2nd Field Dummy Text 1

3) Insert a single record using table view

4) run a query like this as many times as necessary (size doubles each
time).
INSERT INTO KanbanCardCount ( Dummy )
SELECT "1"
FROM KanbanCardCount ;

5) Create a query like so and save as qryKanbanCardCount
SELECT CardCnt, [MAXRECORD]
FROM KanbanCardCount

5) you can then add the KanbanCardCount table to your query/report,
do not link to main query (write and test the main query first).
You can add to the where clause
WHERE KanbanCardCount.CardCnt <= [MAXRECORD]

John
 
John,

I know it's been a while since you posted this, but hopefully you are still
watching.

I'm not quite understanding how this is supposed to work. Can you please
explain it again to me? Thanks!

floprock


John Griffiths said:
1) Delete existing records
DELETE * FROM KanbanCardCount ;

2) Change integer field CardCnt to autonumber
Add 2nd Field Dummy Text 1

3) Insert a single record using table view

4) run a query like this as many times as necessary (size doubles each
time).
INSERT INTO KanbanCardCount ( Dummy )
SELECT "1"
FROM KanbanCardCount ;

5) Create a query like so and save as qryKanbanCardCount
SELECT CardCnt, [MAXRECORD]
FROM KanbanCardCount

5) you can then add the KanbanCardCount table to your query/report,
do not link to main query (write and test the main query first).
You can add to the where clause
WHERE KanbanCardCount.CardCnt <= [MAXRECORD]

John

floprock said:
John,

I can see where this would work for three of each card, but (I failed to
mention) there are not always three cards per group. There could be from one
to ten (or maybe more) of each card. Is there not a way to write an IF
statement in an append query that is based on the numbers in the CardCnt1 and
CardCnt2 fields? Maybe something like:

IF CardCnt1 < CardCnt2 THEN
add record to table and then increment field by 1
ELSE
stop
END IF

I'm not a programmer, but I'm sure this can be done...somehow. Maybe it can
be done with just macros, but that's something else that I'm very new at.
I'll be playing with this for the next few days.

Thanks for your help!
floprock
Click to expand...
Click to expand...
 
Back
Top