Still Struggling with Module

  • Thread starter Thread starter DBarker
  • Start date Start date
D

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
 
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.
 
try adding the ; at the end of the :
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID ; "
Click to expand...

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

Rodrigo.

DBarker said:
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
Click to expand...
 
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
Click to expand...


.
Click to expand...
 
Back
Top