RecordCount isn't accurate until you've read all of the records in the
recordset.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Jan" <JanetMcDonald67@hotmail.com> wrote in message
news:d3lih8$vjq$1@newsg1.svr.pol.co.uk...[color=blue]
> Hiya
>
> What you described is what I had originally! I had added the matchcount as
> .recordcount only returns 1! Reading on the net it was suggested that
> there is some timing issues and matchcount was recomended, i didnt realise
> that would only return one record! :(
>
> I have changed my code back to the original as shown below.
>
> I now get told it will process 1 record, but it then processes 52! There
> is still a difference between the number of records QryMassICTDist5
> returns when I put Select * FROM QryMassICTDist5 in an SQL view and then
> view the records returned i get 345!!!!!
>
> I appreciate your help :)
>
> I now have:
> rivate Sub Command0_Click()
>
> Dim cnxn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Set cnxn = CurrentProject.Connection
> Set rs = New ADODB.Recordset
> Set linktable = New ADODB.Recordset
>
> rs.Open "Select * FROM QryMassICTDist5", cnxn
> linktable.Open "TblSchoolId_Correspondence", cnxn, adOpenKeyset,
> adLockOptimistic
>
> rs.MoveFirst
> MsgBox "you are about to process " & rs.RecordCount & " records"
> While Not rs.EOF
> linktable.AddNew
> linktable.Fields("SchoolId") = rs.Fields("SchoolId")
> linktable.Fields("CorespondenceId") = Text5.Value
> linktable.Update
> rs.MoveNext
> Wend
> rs.Close
> Set rs = Nothing
> linktable.Close
> End Sub
>
>
>
> "Rob Oldfield" <blah@blah.com> wrote in message
> news:eJo77iFQFHA.2520@tk2msftngp13.phx.gbl...[color=green]
>> (apologies for accidentally mailing this to your reply address)
>>
>> I'm afraid that whatever code you have that is working, it's
>> fundamentally
>> different from this code.
>>
>> If you take the SQL statement Select Count(*) As MatchCount FROM
>> QryICTMassDistribution3 and paste it into the SQL window of a query, and
>> then switch to the datasheet, then you'll get a single record, single
>> field
>> display showing you how many records you have.
>>
>> You'd be better off just replacing that with Select * FROM
>> QryICTMassDistribution3... that will then give you a loop that works.
>> You'll be able to use rs.recordcount to return the number of records
>> returned for your message.
>>
>> On the .update question then yes, you should have it run inside the loop
>> as
>> you suggest.
>>
>> And regarding the idea to use an append query, you should be appending on
>> to
>> a table - ensuring that the values you're writing into foreign key fields
>> are correct. That would get around the recordset not being updatable.
>>
>>
>>
>> "Jan" <JanetMcDonald67@hotmail.com> wrote in message
>> news:d3ik4e$lkm$1@newsg2.svr.pol.co.uk...[color=darkred]
>>> Hi
>>>
>>> Thanks for the previous comments.
>>>
>>> As you may guess programming in VBA isn't my strong point!
>>>
>>> I'm not sure I understand why you are saying rs.open only returns one
>>> record? I have actually used this code in the past with different
>>> queries
>>> that return 50 to 60 records and it has worked.
>>>
>>> How do I make it return all the records?
>>>
>>> I cant use an append query as the relationships are too complex and the
>>> query ends up saying 'record set is not updateable'. Hence I decided the
>>> easiest way would be to simple loop through the records in the query and[/color]
>> add[color=darkred]
>>> their ID together with a correspondence to a new table.
>>>
>>> I have got a .update at the end of the code, are you saying it should be[/color]
>> in[color=darkred]
>>> the loop with the .addnew?
>>>
>>> Many thanks for the help
>>> JM
>>>
>>> "Rob Oldfield" <blah@blah.com> wrote in message
>>> news:O8Rj1B6PFHA.2652@TK2MSFTNGP10.phx.gbl...
>>> > To be honest, I can't see how it does anything at all.
>>> >
>>> > The rs.open will return a recordset with a single record containing a
>>> > count
>>> > value. Looping through that recordset will therefore loop just once.
>>> > The .addnew needs a corresponding .update in order to tell the
>>> > linktable
>>> > recordset to actually retain the record you've added.
>>> >
>>> > Have you tried dropping a breakpoint in there and following your logic
>>> > through?
>>> >
>>> > Why not just use an append query? It'd be a great deal easier.
>>> >
>>> >
>>> > "Jan" <JanetMcDonald67@hotmail.com> wrote in message
>>> > news:d3gus2$71j$1@news8.svr.pol.co.uk...
>>> >> Hi
>>> >>
>>> >> I have a database that I use to keep track of the sales promotions
>>> >> that
>>> >> we
>>> >> send to companies.
>>> >>
>>> >> I normally send a mailing based on a subset of the companies in the
>>> > database
>>> >> (found using the query: QryICTMassDistribution3) , I then use a form[/color]
>> and[color=darkred]
>>> >> the code below to create a new record in the corrispondence table to[/color]
>> show[color=darkred]
>>> >> what corrispondence has been sent to various companies.
>>> >>
>>> >> However, when I tried this for our latest mailing it only worked for
>>> >> 52
>>> >> of
>>> >> 348 the records.
>>> >> The QryICTMassDistribution3 query, links several tables, and returns[/color]
>> 348[color=darkred]
>>> >> records when run normally but in the code below the recordset only[/color]
>> seems[color=darkred]
>>> > to
>>> >> have 52 records.
>>> >>
>>> >> Can anyone suggest where I have gone wrong?
>>> >>
>>> >> Many thanks
>>> >> JM
>>> >>
>>> >> the code:
>>> >>
>>> >> Private Sub Command0_Click()
>>> >>
>>> >> Dim cnxn As ADODB.Connection
>>> >> Dim rs As ADODB.Recordset
>>> >> Set cnxn = CurrentProject.Connection
>>> >> Set rs = New ADODB.Recordset
>>> >> Set linktable = New ADODB.Recordset
>>> >> Dim count As Integer
>>> >>
>>> >>
>>> >> rs.Open "Select Count(*) As MatchCount FROM QryICTMassDistribution3",
>>> >> cnxn
>>> >> linktable.Open "TblCompanyId_Correspondence", cnxn, adOpenKeyset,
>>> >> adLockOptimistic
>>> >> rs.MoveFirst
>>> >> MsgBox "you are about to process " & rs!MatchCount & " records"
>>> >> count = 0
>>> >> While Not rs.EOF
>>> >> count = count + 1
>>> >> linktable.AddNew
>>> >> linktable.Fields("CompanyId") = rs.Fields("CompanyId")
>>> >> linktable.Fields("CorespondenceId") = Text5.Value
>>> >> rs.MoveNext
>>> >> Wend
>>> >> rs.Close
>>> >> MsgBox count & " records added to corrispondence table"
>>> >>
>>> >> Set rs = Nothing
>>> >> linktable.Update
>>> >> linktable.Close
>>> >> Rem Set linktable = Nothing
>>> >> End Sub
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]
