Error 3061 After Transfer of Module

[tlitman09]

So I finally got this function working with the help of another member, then I transferred it to another database to do the same exact thing and I get error 3061 too few parameters expected 1. I have checked and double checked the table and field names to no avail, but still no luck. If anybody has an idea as to how I could fix this, I would really appreciate the help?

Here is my code:
(Both functions return the same error)

Public Function patientAdmit(patientName As String, startDate As Date) As Date
    Dim dbs As dao.Database
    Dim rst As dao.recordset
    Dim StrSQl As String
    Dim Startdates(10) As Date 'create a matrix of 10 positions
    Dim enddates(10) As Date
    Dim i As Integer
    
    On Error GoTo ErrorAndExit
    StrSQl = "SELECT patientName, startDate, endDate " & _
             "FROM MyRecords " & _
             "WHERE (((patientName)= '" & patientName & "') " & _
             "AND ((startDate)<= #" & Format(startDate, "mm-dd-yyyy") & "#) ) " & _
             "ORDER BY MyRecords.startDate DESC;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(StrSQl)
 
    'check recordset
    If Not rst.EOF And Not rst.BOF Then
        rst.MoveLast
        rst.MoveFirst
    End If
    i = 0 'initialise counter
    
    While Not rst.EOF And Not rst.BOF
        If rst.RecordCount = 1 Then         'if theres only one entry no furter testing
            patientAdmit = rst!startDate
        Else
            Startdates(i) = rst!startDate       'dump dates in an array
            enddates(i) = Nz(rst!endDate, Date) 'if no enddate entered us today
            If i > 0 Then                       'if more the one date check for
                If enddates(i) + 1 = Startdates(i - 1) Then
                    patientAdmit = rst!startDate
                Else
                patientAdmit = Startdates(i - 1)
                GoTo Exitfunction                'no need for further looping
                End If
            End If
        End If
    rst.MoveNext
    i = i + 1
    Wend
GoTo Exitfunction
ErrorAndExit:
    MsgBox "Error: " & Err.Description & vbNewLine & "Errornumber: " & Err.Number, vbOKOnly, "Error"
Exitfunction:
Set dbs = Nothing
Set rst = Nothing
End Function

Public Function patientDischarge(patientName As String, endDate As Date) As Date
    Dim dbs As dao.Database
    Dim rst As dao.recordset
    Dim StrSQl As String
    Dim Startdates(10) As Date 'create a matrix of 10 positions
    Dim enddates(10) As Date
    Dim i As Integer
    
    On Error GoTo ErrorAndExit
    StrSQl = "SELECT patientName, startDate, endDate " & _
             "FROM MyRecords " & _
             "WHERE (((patientName)= '" & patientName & "') " & _
             "AND ((endDate)>= #" & Format(endDate, "mm-dd-yyyy") & "#) ) " & _
             "ORDER BY MyRecords.endDate ASC;"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(StrSQl)
 
    'check recordset
    If Not rst.EOF And Not rst.BOF Then
        rst.MoveLast
        rst.MoveFirst
    End If
    i = 0 'initialise counter
    
    Do While Not rst.EOF And Not rst.BOF
        If rst.RecordCount = 1 Then         'if theres only one entry no furter testing
            patientDischarge = rst!endDate
        Else
            Startdates(i) = rst!startDate       'dump dates in an array
            enddates(i) = Nz(rst!endDate, Date) 'if no enddate entered us today
            If i > 0 Then                       'if more the one date check for
                If enddates(i - 1) + 1 = Startdates(i) Then
                    patientDischarge = enddates(i)
                Else
                    patientDischarge = enddates(i - 1)
                    GoTo Exitfunction           'no need for further looping
                End If
            End If
        End If
    rst.MoveNext
    i = i + 1
    Loop
GoTo Exitfunction
ErrorAndExit:
    MsgBox "Error: " & Err.Description & vbNewLine & "Errornumber: " & Err.Number, vbOKOnly, "Error"
Exitfunction:
Set dbs = Nothing
Set rst = Nothing

End Function

 

[pbaldy]

Add this:

Debug.Print StrSQl

after the string is built, and run the code. That will print the finished SQL string out to the Immediate window. You may spot the problem there, or you can copy/paste the SQL into the SQL view of a query and try to run it, which will often yield a more descriptive error.

 

[DCrake]

One thing you might consider looking at the ambiguous naming convention between arguments and field names. For example you have an argument named endDate in your patientAdmit function and also a field name called endDate in the sql that is not enclose in square brackets. This can lead to confusion in some cases. Would suggest giving argument names different ones to dispell any possible clashes.

David

 

[gemma-the-husky]

do you know which line is erroring out

i would use a breakpoint, and step through this, to see what is happening.

out of interest, if there are no dates found by your sql statement then i think the function value is undefined? you just drop through the while/wend to the exit function. could this be the problem

one other thing - i dont quite see the logic of iterating a record set . surely you can get the admission or discharge date for a patient with a single dlookup.

 

[tlitman09]

I tried to paste the SQL into a query and it works fine, so I guess that is not the issue.

As far as using dlookup, I had tried. My problem was that I am looking for the start date and end date of a continuous length of stay. In otherwords, I want the function to return the min and max where the dates are continuous for a patient name. This function did that in the last database, but now I am receiving an error.

I will also try to change the double names now and see if that works.

I have tried to put in breakpoints, but I do not know what to do with them. Do I just run the query after putting the break point in?

Thanks,
Tyler

 

[tlitman09]

I just tried to change the names of the doubled variables and unfortunately that did not work.

Thanks for the suggestion,
Tyler

 

[tlitman09]

Ok, I did the tried the breakpoint and it seems to error out when I do the currentDB.(just after the strsql assignment)

Set dbs = CurrentDb

 

[pbaldy]

Really? That wouldn't be where I would expect it. That isn't the error I would expect either, but make sure the DAO library is checked in Tools/References.

Dave didn't read the problem any better than I did the first time.

 

[tlitman09]

The DAO library is checked, and it doesn't work. The odd thing is that it is on the same computer. The only difference is the database. Could it be because of how I transferred the module(draging and dropping from one db to another)?

Thanks,
Tyler

 

[tlitman09]

Sorry, I totally screwed up. since both functions were calculating, I forgot to put the breakpoint in the other one. when I put a breakpoint on the line

If Not rst.EOF And Not rst.BOF Then

it has the error.

sorry,
Tyler

 

[pbaldy]

Funny, because that's not where I expected it either (that error is normally thrown at the Set rst line). Can you post the db in its current state?

 

[tlitman09]

I would post it, but unfortunately it is 50 mb.

 

[tlitman09]

ok, it turns out that if I join tables in a query before carrying out the function, it errors out. If I do a make table query, and then make a query only pulling from the table, then the function works. Any idea why?

Is it possible the make my code create a table to do this?

Thanks,
Tyler

 

[gemma-the-husky]

just to be sure

the breakpoint isnt the error

the breakpoint just stops program execution

then you can press F8 to advance a line at a time

while this is happening you can hover over any variable to see the value.

----------
maybe you have some irregular corruption. as things that should work seem to be going wrong

its a pain, but you could open a new dbs, and try to import everything form the old one.

copy your dbs before you start doing stuff like this, to ensure you have an adequate safe backup

 

[DCrake]

The renaiming of the the variables was not intended to be a fix for the problem it was mearly an indicator on good programming skills.

David