Urgent Linked table problem

[cyberbiker]

I have an ACCESS 97 application. The front end is an MDE on the clients while the backend is an MDB on the server.
When I link to a test backend on my machine all works well. When I link to the server, I receive an error "too few parameters, One expected" when I attempt to open a recordset against one particular table.
This app has run for about 9 months now with minor improvements. Each time, I test against my test data on my machine then relink to the server with no difficulties.
Obviously, something is different, but I am not certain what. Permissions have not changed.
I have tried relinking the tables, but the results are the same.

I cannot figure why it works against my test data and not the live. The test data is the table as it existed in Febuary. The table schema is unchanged since then.

I have a time issue here big time so any ideas no matter what will be appreciated.

Code follows:

Private Sub Form_Load()
DoCmd.SetWarnings 0 'turn off goofy access messages
If Not boolPWD Then
disabletext 'don't permit edits
End If
Set BSISDB = DBEngine(0)(0)
Set technicianRS = BSISDB.OpenRecordset("Technicians", dbOpenDynaset)
On Error Resume Next 'if customer form not open we got here another way


strCustomerID = Forms!Customers.CustomerID

strSQL = "SELECT PhoneLog.CustomerID, PhoneLog.DateofCall, " & _
"PhoneLog.Technician, PhoneLog.MemoField, Customers.Customer, " & _
"phonelog.printthis " & _
"FROM PhoneLog Inner join Customers on Customers.CustomerID = " & _
"PhoneLog.CustomerID "
On Error GoTo fail
If Not strCustomerID = "" Then
If Forms!frmphonelogprompt.optAll.Value = True Then
strSQL = strSQL & _
"WHERE (((PhoneLog.CustomerID)=" & Chr(34) & strCustomerID & _
Chr(34) & &quot) ORDER BY PhoneLog.DateofCall;"
ElseIf Forms!frmphonelogprompt.optDate.Value = True Then
strSQL = strSQL & "WHERE (((PhoneLog.CustomerID) =" & _
Chr(34) & strCustomerID & Chr(34) & _
&quot) and phonelog.dateofcall between #" & _
Forms!frmphonelogprompt.txtDateFrom & "# and #" & _
Forms!frmphonelogprompt.txtDateTo & _
"# ORDER BY PhoneLog.DateofCall,PhoneLog.CustomerID;"
' "# Group By PhoneLog.CustomerID " &
ElseIf Forms!frmphonelogprompt.optWild.Value = True Then
strSQL = strSQL & "WHERE (((PhoneLog.CustomerID) = " & _
Chr(34) & strCustomerID & _
Chr(34) & &quot) and phonelog.memofield like '*" & _
Forms!frmphonelogprompt.txtPhrase & _
"*' ORDER BY PhoneLog.DateofCall;"
Else
Error.Raise 911, "Cannot open Phone log"
DoCmd.Close acForm, "frmphonelogprompt"
Exit Sub
End If
Else 'we got here from a general search not by customer
On Error GoTo fail
If Forms!frmphonelogprompt.optDate.Value = True Then
strSQL = strSQL & _
"Where phonelog.dateofcall between #" & _
Forms!frmphonelogprompt.txtDateFrom & "# and #" & _
Forms!frmphonelogprompt.txtDateTo & _
"# ORDER BY PhoneLog.DateofCall"
ElseIf Forms!frmphonelogprompt.optWild.Value = True Then
strSQL = strSQL & _
"WHERE phonelog.memofield like '*" & _
Forms!frmphonelogprompt.txtPhrase & _
"*' ORDER BY PhoneLog.DateofCall;"
Else
Error.Raise 911, "Cannot open Phone log"
DoCmd.Close acForm, "frmphonelogprompt"
Exit Sub
End If
End If

'+tah 10/02/02 end add
Set phoneRS = BSISDB.OpenRecordset(strSQL, dbOpenDynaset)
'Me.CustomerID = Form_Customers.CustomerID
'Me.Customer = Form_Customers.Customer
Me.CustomerID = phoneRS!CustomerID
Me.Customer = phoneRS!Customer
DoCmd.Close acForm, "frmphonelogprompt" '+tah 10/02/02
If Not technicianRS.EOF Then
technicianRS.MoveFirst
Do Until technicianRS.EOF
strTechnician = technicianRS!Technician
Me!cboTechnician.RowSource = Me!cboTechnician.RowSource & strTechnician & ";"
technicianRS.MoveNext
Loop
End If
If Not phoneRS.EOF Then
'order changed TAH 10/11/01
phoneRS.MoveFirst
phoneRS.MoveLast
cboTechnician = phoneRS!Technician
DateofCall = phoneRS!DateofCall
MemoField = phoneRS!MemoField
chkPrintThis.Value = phoneRS!PrintThis
Call RefreshRecSelectors(phoneRS, txtnum, btnplus, btnminus)
Else
btnphonesave.Enabled = False
btnphonedelete.Enabled = False
cboTechnician.Enabled = False
DateofCall.Enabled = False
MemoField.Enabled = False
btnphonenew.SetFocus
btnminus.Enabled = False
btnplus.Enabled = False
txtnum.Enabled = False
End If
lblphonetitle.Caption = "Phone Log Information"
btnphonesave.Enabled = False
technicianRS.Close
Exit Sub
fail:
MsgBox Err.Description, vbExclamation
Exit Sub

End Sub

The boolean variable for the password is a bit strange. It is just to give a warm fuzzy feeling to certain management types and just keeps controls greyed out and background colors as light grey. Actual security is handled by permissions Terry (cyberbiker)

 

[LittleSmudge]

Is there any chance that a user has added a single or double quote text character into any of the text fields that you are picking up ?


I've seen that "too few parameters, One expected" error generated because of this before.



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[cyberbiker]

Thanks. I just found the problem and it is even dumber than that. Seems that I have a "helper" who has (now had) permissions and "fixed" my table by deleteing an "unused field". Terry (cyberbiker)

 

[JeremyNYC]

Ouch. =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[cyberbiker]

Yes, I now have some advice could get me a star if it were not anatomically unfeasable. Terry (cyberbiker)