Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query By Form

Status
Not open for further replies.

c29537

Programmer
Mar 12, 2013
10
US
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?
 
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
 
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
 
When I change the combo box entry, the "Enter Parameter Value" message box is displayed with [forms]![MainData]![CaseID] displayed.
 
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:

Code:
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
 
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.....
 
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));
 
What do you get if you open the debug window (press Ctrl+G) and type

Code:
?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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top