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
> User Input To Determine How Many Rows To Insert, Access 2016    
 
   
kjohnson
post Apr 21 2020, 02:13 PM
Post#1



Posts: 368
Joined: 2-May 12
From: South Central Texas


Hello all!

I think I'm on the right track with this code, but I just can't figure out the best way to complete it.

I'm importing sample testing results from a testing facility's report emailed to me. I can get all data into my table but I need to associate "owners" (Primary Investigators or "PIs") with the samples. For the most part, I have the database search my PIs table for a name that matches what was imported and write into tblSamplePI that Sample ID and the PI ID - if it gets it wrong, it's easy to fix those few. For some samples, there are multiple PIs so I need to associate each PI with that one SampleID. This way I can build a report for one specific PI and get all of their samples. I have an "add" button on the form that will add a duplicate row to the SamplePI table with the PI field blank (to be filled in by the user with a combobox on the form). easy peasy. However, I have about 20 or so samples that have 3 or more PIs and it gets tedious to click the "add" button that many times on that number of samples. I want to make an "Add +" button where the user can input how many new rows they want and ta dah, that many rows are added. For a sample that has 5 PIs associated, the user would click "Add +", a prompt box would open and they could type "4" which would then add 4 new rows to tblSamplePI with the same SampleID and blank PI ID field to be chosen by the user next.

My code for the basic "Add" button is:
CODE
Private Sub cmdAdd_Click()
DoCmd.RunSQL "INSERT INTO tblSamplePI (SampleID) SELECT tblSamplePI.SampleID FROM tblSamplePI WHERE tblSamplePI.ResultsPIID = " & Forms!frmResultsPI!ResultsPIID
Me.Requery
End Sub


For the "Add +" button, I'm thinking I will need an input box, which a loop code would use to determine how many times to loop that same Insert code. Something along these lines?

CODE
Private Sub cmdAddPlus_Click()
Dim strSQL As String
Dim a As Integer
Dim i As Long

a = InputBox("Please enter the number of rows you want to add.")
strSQL = "INSERT INTO tblSamplePI (SampleID) SELECT tblSamplePI.SampleID FROM tblSamplePI WHERE tblSamplePI.ResultsPIID = " & Forms!frmResultsPI!ResultsPIID

For i = 1 To a
    DoCmd.RunSQL strSQL
Next i
Me.Requery
End Sub
Go to the top of the page
 
GroverParkGeorge
post Apr 21 2020, 02:30 PM
Post#2


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


That should work. What happens when you try it? Of course, I'm assuming you have a development copy of the data to test on.

I would recommend, though, that you change the DoCmd.RunSQL syntax to use CurrentDB.Excecute syntax instead.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kjohnson
post Apr 22 2020, 12:09 PM
Post#3



Posts: 368
Joined: 2-May 12
From: South Central Texas


It works but when it refreshes the page it pops me back up to the top. In order to refresh the page and put me back at the line I was, would I use a bookmark?

Also, when I click the "Add" button, a dialogue box pops up saying "You are about to append 1 row(s)... are you sure? Yes/no" is there a way to skirt around that confirmation box? Just click "Add" and it just adds it?
This post has been edited by kjohnson: Apr 22 2020, 12:11 PM
Go to the top of the page
 
Jeff B.
post Apr 22 2020, 12:21 PM
Post#4


UtterAccess VIP
Posts: 10,490
Joined: 30-April 10
From: Pacific NorthWet


I may be interpreting (?mis-interpreting) your description too literally (I get told that a lot!)…

It sounds like you are trying to insert 'placeholder' records/rows, for subsequent 'filling'. If so, why? It generally isn't considered a good practice to add "empty" rows in Access tables, to be filled in later.

… or have I missed?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
kjohnson
post Apr 22 2020, 12:36 PM
Post#5



Posts: 368
Joined: 2-May 12
From: South Central Texas


Sort of? So after I get all the sample results in, I have a form that reviews all samples with PI names. When we physically submit samples, we have some that have multiple PIs, so when the PI name is imported, it may be "Johnson/Smith" so the database doesn't know which one to associate with that sample. In tblSamplePI, I need the same sample to be associated with each PI so I can generate a report for that specific PI only.

In the report, my "add" button duplicates the row and then in the duplicate row, I can choose which other PI. If that makes sense? So it's adding a duplicate but within 2 seconds I'm filling in the PI name. Here's a screenshot. When I import, it will try to match up the imported PI names with names in the PI table, but again, it can't tell what PI to choose when there are multiple named for a single sample.
Attached File(s)
Attached File  Example.png ( 28.03K )Number of downloads: 2
 
Go to the top of the page
 
kjohnson
post Apr 22 2020, 12:40 PM
Post#6



Posts: 368
Joined: 2-May 12
From: South Central Texas


Here is the same form after duplicating sample ID 1065 so that I can choose C for one and S for the other.
Attached File(s)
Attached File  Example2.png ( 33.1K )Number of downloads: 3
 
Go to the top of the page
 
ADezii
post Apr 22 2020, 01:12 PM
Post#7



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


I also do not think that it is a good idea to Insert 'Filler Rows', but that being said, here is how you can accomplish what you requested.
  1. Add a Primary Key to tblSamplePI (see below).
    SIDSampleIDResultsPIID
    1120
    2230
    3340
    4450
    5560
    6670
    7890
    89100
    910200
  2. Prompt the User on how many Rows to Insert.
  3. Perform minimal Validation on the entry.
  4. Define the SQL Statement (I used a CONSTANT for the WHERE Clause).
  5. Append x Records into tblSamplePI ([SampleID] Field only).
  6. Set a Variable to the Value in the Primary Key Field (used Me![txtSID] for this Demo).
  7. Requery the Form, set Focus to the PK Field (Me![txtSID]), go back to the Original Record). I do not think that a Bookmark would work after the Requery.
  8. Code Definition:
    CODE
    Dim strSQL As String
    Dim a As String
    Dim i As Long
    Dim lngPK As Long
    Const conRESULTS_PIID = 100

    a = InputBox("Please enter the number of rows you want to add.")

    'Minimal Validation
    If a = "" Or Not IsNumeric(a) Then Exit Sub
              
    strSQL = "INSERT INTO tblSamplePI ([SampleID]) SELECT tblSamplePI.[SampleID] " & _
             "FROM tblSamplePI WHERE tblSamplePI.[ResultsPIID] = " & _
              conRESULTS_PIID

    For i = 1 To Val(a)
      CurrentDb.Execute strSQL, dbFailOnError
    Next

    lngPK = Me![txtSID]

    With Me
      .Requery
      ![txtSID].SetFocus
      DoCmd.FindRecord lngPK
    End With
  9. The Code has been fully tested and is operational, if you would like a Demo I would be happy to provide one.

This post has been edited by ADezii: Apr 22 2020, 01:12 PM
Go to the top of the page
 
kjohnson
post Apr 22 2020, 01:57 PM
Post#8



Posts: 368
Joined: 2-May 12
From: South Central Texas


What is the difference or purpose behind using a constant for the where clause? How does it change from the where clause I had? (asking so I can learn)

Also FYI - I do have a primary key for tblSamplePI :-) that table is ResultsPIID, SampleID, and PIID

For number 6 - I would then use Me![txtResultsPIID] and then set focus to that field on the form as well, right?

Go to the top of the page
 
ADezii
post Apr 22 2020, 02:35 PM
Post#9



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


I removed the CONSTANT and used an Absolute Reference to the Text Box on the Form. I also uploaded a Demo which hopefully will say it all.
Attached File(s)
Attached File  Insert_Rows.zip ( 26.48K )Number of downloads: 4
 
Go to the top of the page
 
projecttoday
post Apr 22 2020, 02:46 PM
Post#10


UtterAccess VIP
Posts: 12,397
Joined: 10-February 04
From: South Charleston, WV


How is your method different from k's, ADezii?

Another approach, not that you need one, would be to have a dummy table of numbered blank records. Then insert where the record number is less than or equal to the user's input number, eliminating the loop.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 07:05 AM