Inserting records into subform - Take 2

  • Thread starter Thread starter Jasonm
  • Start date Start date
J

Jasonm

I have isolated the records that I need in a recordset in vb I now want to
insert those records into the subform to give the users a head start on
entering the data that we need to store. What I would like to know is this:
Is an SQL "INSERT INTO" statement the easiest way to go - using dbExecute?

Or should I be using a For Each Loop? II have been trying this and am very
close, but cannot seem to reference the correct element to reference in the
recordset to make the loop run the correct number of times or to increment
through each record (I can only insert the same record 4 or 5 times!)

Any thought that you can offer on this would be greatly appreciated.

TIA, Jm
 
Jasonm said:
I have isolated the records that I need in a recordset in vb I now want to
insert those records into the subform to give the users a head start on
entering the data that we need to store. What I would like to know is this:
Is an SQL "INSERT INTO" statement the easiest way to go - using dbExecute?

Or should I be using a For Each Loop? II have been trying this and am very
close, but cannot seem to reference the correct element to reference in the
recordset to make the loop run the correct number of times or to increment
through each record (I can only insert the same record 4 or 5 times!)
Click to expand...


Neither of those ideas will work.

You can loop through a recordset using this kind of logic:

With nameofyourrecordset
.MoveFirst
Do Until .EOF
strSQL = "appropriate Insert Into SQL statement"
db.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
 
* Could you explain what your objective is?

Jasonm said:
I have isolated the records that I need in a recordset in vb I now want to
Click to expand...

If you can isolate a set of records there *MUST* mathematically
be a logical query that is equivalent to what ever logic you are using
in VBA code.

That being the case you can base the subform on that recordsource.

Job done.
insert those records into the subform to give the users a head start on
entering the data that we need to store. What I would like to know is this:
Is an SQL "INSERT INTO" statement the easiest way to go - using dbExecute?
Click to expand...

INSERT INTO subform_Recordsource
SELECT <columns>
FROM <tables>
WHERE <if you know this condition then you can base the subform on it
anyway>

* So what is your objective?

Ready to help - John
 
Marshall,

Thanks for the quick reply. I will work on this code and see if I can get it
to work. I have done a bit of programming in VB.Net, but VBA has just enough
differences to make it difficult, and I don't have nearly the time I would
like to take to play around with it!

Thanks again, I really appreciate the help.

Jm
 
John,

I am am building a database for tracking water quality testing. There are
numerous "groups" of tests and numerous contaminants for each group. I would
like to create a record in a "results" table (the subform) for each
contaminant in a group when a new "sample" record is created on the main
form and a contaminant group is selectd from a combo box.

I think that Marshall is on the right track in the post above this one, but
if you can suggest an easier method I would love to look at it. As I replied
to Marshall I do not get to spend teh time I would like to sit and code...
things like life and work always get in the way.

I hope that helps explain what I am trying to do.
 
if i understand you correctly: you have a table where each record
identifies a specific water sample and the "group" of tests performed on
that sample. this tblSamples has a one-to-many relationship with a
tblTestResults, where each record contains a value showing the level of a
specific contaminant that is present in the water sample of the related
parent record in tblSamples. correct?

if the above is correct, i'm also guessing that you have a table listing all
contaminents that can be tested for. and a table listing all test groups.
and perhaps a third (linking) table that lists all the contaminents tested
for in each specific test group.

if this discribes your setup pretty closely, i believe i understand what
you're getting at: when the user enters a Sample record, and selects a test
group from the combo box, you want child records to be created for each
contaminant covered in that test group, and you want those child records to
show up in the subform so all the user has to do is enter the value for each
contaminent.

i modeled the above setup for another newsgroup "post-er" a few months ago,
using the following code, as

Private Sub ChildContaminents_Enter()

If Not IsNull(Me!SampleID) And Not IsNull(Me!fkTestGroupID) Then
With Me!ChildContaminents.Form
If .Recordset.RecordCount = 0 Then
CurrentDb.Execute "INSERT INTO tblSampleContaminents " _
& "( fkSampleID, fkContaminantID ) " _
& "SELECT " & Me!SampleID _
& ", tblTestGroupContaminants.fkContaminantID " _
& "FROM tblTestGroupContaminants WHERE " _
& "fkTestGroupID=" & Me.Parent!fkTestGroupID, _
dbFailOnError
.Requery
End If
End With
Me!ChildContaminents.Locked = False
End If

End Sub

Private Sub Form_Current()

Me!ChildContaminents.Locked = Me.NewRecord
Me!fkTestGroupID.Locked = Not Me.NewRecord

End Sub

i still have the model db. if you want to see it, post back and i'll email
it to you.

hth
 
Tina,

That's it almost perfectly. I think I can modify your code to do what I
need. How many years does it take to finally get the hang of all this
stuff?!
Thank-you very much for your input. I will try the code out right now!
hopefully I'll be in fat heaven in an hour or so!

Thanks again, Jm
 
How many years does it take to finally get the hang of all this
stuff?!

well, i've been writing databases since 1998, and using VBA since 2000. if i
ever get the hang of "all of it", i'll let you know! ;)

glad my suggested code looks like it may help. i'll be monitoring this
thread for several days, so if you decide you need to see the demo db, or
have questions about the code, just post back. good luck!

hth
 
Gee, all I did was answer the question. John skipped the
question to get the thread onto the right track. Then Tina
deduced the problem and provided a solution.

You guys make a good team, keep up the excellent work.
 
JasonM said:
John,

I am am building a database for tracking water quality testing. There are
numerous "groups" of tests and numerous contaminants for each group. I would
like to create a record in a "results" table (the subform) for each
Click to expand...

tblSample (sample of water, identified by SampleID)
tblSampleTest (list of tests linked by SampleID, SampleID, TestID, Result)
tblGroup (list of groups, identified by GroupID)
tblTestInGroup (list of tests members of a group GroupID, TestID)
tblTest (list of different tests that can be applied to a sample, identified
by TestID)

*** WARNING NOT TESTED ***

To show all tests in a group is trivial
SELECT TestID
FROM tblTestInGroup
WHERE GroupID = "Heavy Metal"

coping to tblTestInGroup for the subform
INSERT INTO tblSampleTest ( SampleID, TestID )
SELECT forms![Sample]![SampleID], tig.TestID
FROM tblTestInGroup tig
WHERE tig.GroupID = "Heavy Metal"

to avoid adding tests more than once
INSERT INTO tblSampleTest ( SampleID, TestID )
SELECT forms![Sample]![SampleID], tig.TestID
FROM tblTestInGroup tig
WHERE tig.GroupID = "Heavy Metal"
AND tig.TestID NOT IN (
SELECT st.TestID
FROM tblSampleTest st
WHERE st.SampleID = forms![Sample]![SampleID]
)

which has the effect that adding more than 1 group will not duplicate any
tests.
if test 21 "Does sample fluoresce" is in Group 1 and group 100 then applying
both groups to a sample will show test 21 only once.

On the right lines? - John
 
luckily, your dialogue (and John's) with Jason gave me a clear enough
picture to recognize the similarity with the solution i had worked out in
April. it's pretty cool how all of us working together were able to help
Jason meet his data entry goal - another example of the power of many minds
meeting in the newsgroups! :)
 
And it must be said that I Really, Really, Really appreciate the help. I
love to program, however most of my programming involves getting pieces of
machinery to operate in a certain fashion and then to design operator
interfaces that allow plant operations staff to interface with that
equipment.

You all pointed me on the right trail, and I had to fuss a bit to get
everthing just right (there was a need for some single qoutes in the WHERE
clause...) but after that It is working great!

Thanks very much to all of you for your help.

Jason

tina said:
luckily, your dialogue (and John's) with Jason gave me a clear enough
picture to recognize the similarity with the solution i had worked out in
April. it's pretty cool how all of us working together were able to help
Jason meet his data entry goal - another example of the power of many
minds
meeting in the newsgroups! :)
Click to expand...
 
Back
Top