UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form To Add Multiple Records Based Of Single Record, Access 2016    
 
   
e60_pa
post Jun 13 2019, 10:49 PM
Post#1



Posts: 6
Joined: 11-May 14



Hi. I am trying to find some code that allows me to add multiple records to a collection table based of a checklist table record. Each card in my collection has a single record even if the card is a duplicate, because of grading attributes.

Form Example (basic):
pkPrStID (tblSetChecklist)
txtCardDescription (display) (tblSetChecklist)
lkpCollectionBucket (user-select) (cboColType,-Keep,Have,Want,Trade) (unbound)
numQty (user-input) (unbound)

ok cancel


So as an example, the user would select the collection type value and enter a quantity. Clicking OK would insert the the # of records into the Collection table with the following values.

INSERT INTO (tblCollectionCards) (this was just example code that I used to loop through a list box)
rs!fkStCkCdID = ctl.ItemData(varItem)
rs!fkPrStID = Me.pkPrStID.Value
rs!lkpCardColType = Me.cboColType.Value
rs!lkpCardLocation = "On-Site"
rs!lkpCardCond ="Nrmt"

Plan to add other user choice options at later time.
This post has been edited by e60_pa: Jun 13 2019, 11:01 PM
Go to the top of the page
 
theDBguy
post Jun 13 2019, 10:57 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
After a reading and updating I was able to figure it out.

Hi. Not sure I understand your post. Are you saying you no longer need any help?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
e60_pa
post Jun 13 2019, 10:59 PM
Post#3



Posts: 6
Joined: 11-May 14



Sorry I took it out. Was trying to show that I could understand and dissect the code to make it work for my use. Now it is another story if it is clean. LOL
This post has been edited by e60_pa: Jun 13 2019, 11:00 PM
Go to the top of the page
 
MadPiet
post Jun 13 2019, 11:00 PM
Post#4



Posts: 3,120
Joined: 27-February 09



Create a table of numbers or a tally table...

CODE
CREATE TABLE Tally (N int PRIMARY KEY);
GO


then insert values from 1 to however many duplicates you need in your query.
CODE
INSERT INTO Tally (N) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);


If you have a Quantity in your Cards table, then it's something like

CODE
INSERT INTO tblCollection (CardID, DuplicateNumber)
SELECT c.CardID, t.N
FROM Cards c INNER JOIN Tally t
ON t.N<=c.Quantity;


The non-equijoin will cause there to be N records per CardID. Best part? No unnecessary VBA, and a whole lot faster.
This post has been edited by MadPiet: Jun 13 2019, 11:03 PM
Go to the top of the page
 
e60_pa
post Jun 13 2019, 11:03 PM
Post#5



Posts: 6
Joined: 11-May 14



Ok I think this makes some some sense. Can I use a value list in the form for my number selection? And then =Forms!ForName!numQty in the query, right?
This post has been edited by e60_pa: Jun 13 2019, 11:04 PM
Go to the top of the page
 
MadPiet
post Jun 13 2019, 11:06 PM
Post#6



Posts: 3,120
Joined: 27-February 09



Yep,

SELECT N
FROM tally
WHERE tally.N <= Forms!MyOpenForm!cboQuantity
Go to the top of the page
 
e60_pa
post Jun 13 2019, 11:07 PM
Post#7



Posts: 6
Joined: 11-May 14



Thanks! I am going to give this a try tomorrow when I am fresh. Will be hard to sleep. Everything is coming together and with the help of inspirational thoughts and advanced knowledge like I get here. Much appreciated! -Pat

Edit, right "cbo".
This post has been edited by e60_pa: Jun 13 2019, 11:08 PM
Go to the top of the page
 
MadPiet
post Jun 13 2019, 11:30 PM
Post#8



Posts: 3,120
Joined: 27-February 09



Here's a quick database example with just two tables and a couple of queries. If you open the form, you can specify how many duplicates you want (that one will create the same number of duplicates for every record in the table). The query "ExplodedQuantity" will just get the quantity from the table "People". There's nothing up my sleeves. It's all in the queries. And NO CODE. =)

(I don't have anything against VBA, but simpler is better!)

If you want to understand how it works, just tear it apart...
Attached File(s)
Attached File  CreateDuplicates.zip ( 34.22K )Number of downloads: 4
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 09:56 PM