Error message on adding data to query via linked cbo

[T Payne]

I have restricted values in one combo box to categories listed in another,
as per:

http://office.microsoft.com/en-us/assistance/HA011730581033.aspx

I want to be able to add new data to both boxes, as per:

http://www.mvps.org/access/forms/frm0015.htm

This NotInList event works fine for the "Categories" box. But consistently
returns an error message when new data is added to the restricted
("Products") box. The error is:

Run-time error '3061':

Too few parameters. Expected 1.

The line of code where the debugger stops is:

Set rs = db.OpenRecordset("qryProductsList", dbOpenDynaset)

The same event associated with the Categories combo box (with reference to
"qryCategoriesList" instead of "qryProductsList") works fine.

The queries and tables underlying them are all fine.

Any thoughts pn why this might be happening? Thanks in advance.

 

[Guest]

Here is an example of opening a recordset in VBA code for a parameter query
that gets it’s parameters from an open form, ie.
=[Forms]![FormName]![ControlName]

I believe you will need to do something similar. Perhaps you can use a saved
parameter query as the rowsource for the second combo box. The parameter
query picks up it's values by using a statement such as the above, where
ControlName is the name of the first combo box.


Function RunParamQuery()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("ParameterQueryName")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)

With rs
Do Until (.BOF Or .EOF) = True
':
' Your code goes here.
':
Loop
End with

ExitProc:
‘ Cleanup
On Error Resume Next
qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in RunParamQuery Function..."
Resume ExitProc
Resume
End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________