Error 3061 - produced by 2 out of 3 same reports

[Wullie]

I have 3 forms in a database which allow users to edit information contained
in different tables - 1 form for each table.

The following code works perfectly well, displays the current list, moves
the selection from the list to the edit box and updates the table with the
new value

---------------------

Dim nameToUpdate As Integer
Dim SQL As String

nameToUpdate = Me.Edit_Current_List.Value

SQL = "UPDATE Staff SET StaffName = '" & Replace(Me.NewItem, "'", "''") & "'
WHERE ID = " & nameToUpdate

CurrentDb.Execute SQL, dbFailOnError

Me.NewItem = ""
Me.Refresh

--------------------

The other 2 have exactly the same code (except different table names/field
names) but I get an error stating too few parameters. When it breaks and I
hover over the SQL statement (or pull up an immediate window), I get the SQL
statement I would expect.

I have searched and I would hazard a guess that the correct way to do it
would be through setting parameters, but I didn't fully understand the
parameters (I tried it, but it skipped the loop, which gives the impression
that maybe my QueryDef is wrong - before any points it out, I know the below
is commented)

---------------------------

'Dim db As DAO.Database
'Dim qdf As DAO.QueryDef
'Dim prm As DAO.Parameter
'Dim rst1 As Recordset
'Dim nameToUpdate As Integer

'Set db = CurrentDb
'Set qdf = db.QueryDefs("AreaQuery")

'nameToUpdate = Me.Edit_Current_List.Value

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

'Set rst1 = qdf.OpenRecordset
'With rst1
'[AreaName] = nameToUpdate
'End With
'rst1.Close
'Set rst1 = Nothing

'Set qdf = Nothing
'Set db = Nothing

 

[Dirk Goldgar]

I have 3 forms in a database which allow users to edit information
contained
in different tables - 1 form for each table.

The following code works perfectly well, displays the current list, moves
the selection from the list to the edit box and updates the table with the
new value

---------------------

Dim nameToUpdate As Integer
Dim SQL As String

nameToUpdate = Me.Edit_Current_List.Value

SQL = "UPDATE Staff SET StaffName = '" & Replace(Me.NewItem, "'", "''") &
"'
WHERE ID = " & nameToUpdate

CurrentDb.Execute SQL, dbFailOnError

Me.NewItem = ""
Me.Refresh

--------------------

The other 2 have exactly the same code (except different table names/field
names) but I get an error stating too few parameters. When it breaks and I
hover over the SQL statement (or pull up an immediate window), I get the
SQL
statement I would expect.

In the cases where it breaks, what is the SQL statement you get?

 

[Wullie]

I was testing it with the current item test, which gets pulled into the edit
box, and changing this to testing.

The SQL comes up as

UPDATE Category SET CategoryName = 'Testing' WHERE ID = 9

where ID the ID of the selected value in the listbox on the form, which is
what I would expect to come up, but it appears to not be reading in the
parameter.

 

[Dirk Goldgar]

I was testing it with the current item test, which gets pulled into the
edit
box, and changing this to testing.

The SQL comes up as

UPDATE Category SET CategoryName = 'Testing' WHERE ID = 9

where ID the ID of the selected value in the listbox on the form, which is
what I would expect to come up, but it appears to not be reading in the
parameter.

What parameter? I'm a bit confused. Is there a field named "ID" in the
table named "Category"? Is that field a numeric type?

 

[Wullie]

The Category table contains 2 fields

ID - AutoNumber
CategoryName - Text (50)

The other 2 tables, for the other 2 forms, Area and Staff are the same
settings except CategoryName is AreaName/StaffName.

The listbox in the form has 2 columns, the ID and the CategoryName (although
only the CategoryName is displayed). Once selected and moved to the Edit
Current List text box, the CategoryName is displayed. After clicking the
Update button, this code kicks in to update the text value based on the ID
selected in the listbox.

Hope that makes more sense.

 

[Dirk Goldgar]

The Category table contains 2 fields

ID - AutoNumber
CategoryName - Text (50)

The other 2 tables, for the other 2 forms, Area and Staff are the same
settings except CategoryName is AreaName/StaffName.

So you're saying that, with that table definition, this SQL:

UPDATE Category SET CategoryName = 'Testing' WHERE ID = 9

give you error 3061 when you execute it via CurrentDb.Execute?

I cannot imagine why that would be. Have you tried just executing a line
like this in the Immediate Window?

CurrentDb.Execute "UPDATE Category SET CategoryName = 'Testing' WHERE ID
= 9"

(That should all be on one line.) If that gives an error, then your table
structure or field names can't be what you think they are. If it doesn't
give an error, then I think the SQL statement you constructed in code can't
be what you think it is, by the time it gets executed.