Query By Form

[c29537]

First time asking a question on Tek-Tips......

Access 2003

Bound Form to table

combobox on bound form with other table as source

on the combobox change event, i need to run an update query so that the pk of the table bound to the combobox updates a field in the table that is bound to the form.

UPDATE MainData INNER JOIN [Processors-Closers-Post-Closers] ON MainData.Processor = [Processors-Closers-Post-Closers].Processor SET MainData.[Processor Log On ID] = [Processors-Closers-Post-Closers]![Log On ID]
WHERE (((MainData.Case)=[forms]![MainData]![CaseID]));


Keeps askimg me to "Enter Parameter Value" any thoughts or ideas?

 

[dhookom]

Welcome to Tek-Tips.

Is your SQL a query or built in VBA? Is CaseID text or numeric? Are you being prompted with a reference to a particular name?

Can you share some code for the Change Event? I think I would use the After Update event since the Change event occurs with every keystroke.

Duane
Hook'D on Access
MS Access MVP

 

[c29537]

The SQL is a query and in the combo box change event I am running a macro that runs the query.

CaseID is a text data type.

In the macro I:

SaveRecord
Set Warnings = No

Open Query = SQL above

Run Command = Refresh

 

[c29537]

When I change the combo box entry, the "Enter Parameter Value" message box is displayed with [forms]![MainData]![CaseID] displayed.

 

[dhookom]

What happens if you set the query parameters like:
[forms]![MainData]![CaseID] Text

I typically don't work with macros and would use something like this in the after update of the combo box:

Dim strSQL as String
strSQL = "UPDATE MainData INNER JOIN [Processors-Closers-Post-Closers] ON " & _
    "MainData.Processor = [Processors-Closers-Post-Closers].Processor " & _
    "SET MainData.[Processor Log On ID] = [Processors-Closers-Post-Closers]![Log On ID] " & _
    "WHERE MainData.Case=""" & Me.[CaseID] & """"
Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[c29537]

I tried the "[forms]![MainData]![CaseID] Text" and got an invalid syntax error.


Tried the vba code above in the after update event of the combo box and when I change the combo box value I get:

The expression After Update you entered as the event property setting produced the following error: Object or class does not support the set of events.


BTW..thanks for looking at this.....

 

[dhookom]

What was the SQL of the query after you added the parameter?

Duane
Hook'D on Access
MS Access MVP

 

[c29537]

Sorry, I tried it again and got no syntax errors however the query still does not reconized CaseID.


PARAMETERS forms!MainData!CaseID Text ( 255 );
UPDATE MainData INNER JOIN [Processors-Closers-Post-Closers] ON MainData.Processor=[Processors-Closers-Post-Closers].Processor SET MainData.[Processor Log On ID] = [Processors-Closers-Post-Closers]![Log On ID]
WHERE (((MainData.Case)=forms!MainData!CaseID));

 

[dhookom]

What do you get if you open the debug window (press Ctrl+G) and type

?forms!MainData!CaseID

Did you enter the code I suggested into the VBA window or just the event property? It should have been into the code window.

Duane
Hook'D on Access
MS Access MVP

 

[c29537]

Private Sub Processor_AfterUpdate()

Dim strSQL As String
strSQL = "UPDATE MainData INNER JOIN [Processors-Closers-Post-Closers] ON " & _
"MainData.Processor = [Processors-Closers-Post-Closers].Processor " & _
"SET MainData.[Processor Log On ID] = [Processors-Closers-Post-Closers]![Log On ID] " & _
"WHERE MainData.Case=""" & Me.[CaseID] & """"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


Processor is the combo box.


When I make a change in the combo box I get:
"The expression After Update you entered as the event property setting produced the following error: Object or class does not support the set of events."


When I type "?forms!MainData!CaseID" in the immediate window and press enter I get:
"Run-time error '459':
"Object or class does not support the set level of events

 

[dhookom]

The issue seems to be with the reference to the control on your form. I would confirm all names.

Duane
Hook'D on Access
MS Access MVP

 

[c29537]

I fixed it by opening a new project and importing all objects into my new project. Found that no vba code would run from any type of event. It must have some type of corruption.