Still Struggling with Module

[DBarker]

Below is the code. I thought that it was working and the
only issue I needed to address was to get the JournalDate
to go before the Journal it corresponded to. However now
I am getting the following error:
"Run Time Error 3061 , Too few parameters expected 1"

Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The above error occurs at the following line, when the
cursor is over the snapshot portion it shows = 4. That
table only has 3 fields, CaseID, JournalDate & Journals.

Let me explain what I am trying to do: I have two tables
the To table STi_Table has all the fields and this is
where I want to concatenate the multiple journals with
their dates. The STi-Multiple table is where there are
duplicated CASEIDs with different journal entries and
this is where the data should be pulled from.

Any Help would be greatly appreciated:

Below is the entire code I am using.
Sub Combine()
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & ";" _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub

 

[Roger Carlson]

The only thing that I can possibly imagine that would cause this error is if
you do not have a field called CaseID in the STi_Multiples table or it is
spelled slightly different. Check everything again very carefully.

The 4 you get when you hover over dbOpenSnapshot simply means that that is
the actual value of the intrinsic constant. You could in fact replace
dbOpenSnapshot with the number 4.

 

[Rodrigo]

try adding the ; at the end of the :

strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID ; "

I found this gave me errors before. when i worked with dao.

Rodrigo.

Below is the code. I thought that it was working and the
only issue I needed to address was to get the JournalDate
to go before the Journal it corresponded to. However now
I am getting the following error:
"Run Time Error 3061 , Too few parameters expected 1"

Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The above error occurs at the following line, when the
cursor is over the snapshot portion it shows = 4. That
table only has 3 fields, CaseID, JournalDate & Journals.

Let me explain what I am trying to do: I have two tables
the To table STi_Table has all the fields and this is
where I want to concatenate the multiple journals with
their dates. The STi-Multiple table is where there are
duplicated CASEIDs with different journal entries and
this is where the data should be pulled from.

Any Help would be greatly appreciated:

Below is the entire code I am using.
Sub Combine()
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & ";" _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub

 

[DBarker]

Well I did find where the Case ID had a space and when I
changed that it fixed the issue. But it is not adding
the journal entries it is just adding a ; to the
beginning of the existing journal entry in the
STi_Table. So I am getting closer. So I though in the
from that I would have to specifically tell it to select
the journalDate and Journals but am having syntax issues
now. I want to add that in the StrSQL so that at the end
where I tell it to put the ; I can also tell it to put
the date and journals and hope that works.

Here is what I changed:
strSQL = "SELECT STi_Multiples.JournalDate" & _
"FROM STi_Multiples " & _
"ORDER BY CaseID"

-----Original Message-----
The only thing that I can possibly imagine that would cause this error is if
you do not have a field called CaseID in the STi_Multiples table or it is
spelled slightly different. Check everything again very carefully.

The 4 you get when you hover over dbOpenSnapshot simply means that that is
the actual value of the intrinsic constant. You could in fact replace
dbOpenSnapshot with the number 4.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Below is the code. I thought that it was working and the
only issue I needed to address was to get the JournalDate
to go before the Journal it corresponded to. However now
I am getting the following error:
"Run Time Error 3061 , Too few parameters expected 1"

Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The above error occurs at the following line, when the
cursor is over the snapshot portion it shows = 4. That
table only has 3 fields, CaseID, JournalDate & Journals.

Let me explain what I am trying to do: I have two tables
the To table STi_Table has all the fields and this is
where I want to concatenate the multiple journals with
their dates. The STi-Multiple table is where there are
duplicated CASEIDs with different journal entries and
this is where the data should be pulled from.

Any Help would be greatly appreciated:

Below is the entire code I am using.
Sub Combine()
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & ";" _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub

.