Fire query when making on last action in form

M

Marinus

I'm learning... :)
Local time
Today, 18:08
Joined
Jun 16, 2010
Messages
140
Hi Guys, the last action on my entry form is to select a Sales Rep from a listbox. Is there a way to fire query, or mayby quicker to run the SQL statement.

Code for the listbox =

Code: 
[COLOR="Red"]Private Sub List203_Click()
DoCmd.Save
DoCmd.Requery
End Sub[/COLOR]

The Save and Requery are done to update the form with the latest changes.

The Query/SQL I like to run before the save =

Code: 
[COLOR="red"]UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice]
WHERE (((Docket.ID)=[Forms]![Touchscreen]![List210]) AND ((Materials.ID)=[Forms]![Touchscreen]![List125]));[/COLOR]

As usual any help appreciated..
 
1. DoCmd.Save doesn't have to do with records. If you want to save the record it would be DoCmd.RunCommand acCmdSaveRecord

2. Use the AFTER UPDATE event, not the click event, of the list box.

3. Then you can use:

Code: 
[COLOR=black]Private Sub List203_AfterUpdate()
Dim strSQL As String[/COLOR]
 
[COLOR=black]DoCmd.[/COLOR][URL="http://www.access-programmers.co.uk/forums/showthread.php?t=202694#"][COLOR=black]RunCommand[/COLOR][/URL] acCmdSaveRecord
 
strS[COLOR=black]QL = "UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials " & _[/COLOR]
[COLOR=black]"SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice] " & _
"WHERE (((Docket.ID)=" & Forms!Touchscreen.List210 & ") AND ((Materials.ID)= " & Forms!Touchscreen.List125 & "));"[/COLOR]
 
CurrentDb.Execute strSQL, dbFailOnError

[COLOR=black]Me.Requery
[/COLOR]
[COLOR=black]End Sub[/COLOR]
[COLOR=black]
[/COLOR]
 
boblarson said:
1. DoCmd.Save doesn't have to do with records. If you want to save the record it would be DoCmd.RunCommand acCmdSaveRecord

2. Use the AFTER UPDATE event, not the click event, of the list box.

3. Then you can use:

Code: 
[COLOR=black]Private Sub List203_AfterUpdate()
Dim strSQL As String[/COLOR]
 
[COLOR=black]DoCmd.[/COLOR][URL="http://www.access-programmers.co.uk/forums/showthread.php?t=202694#"][COLOR=black]RunCommand[/COLOR][/URL] acCmdSaveRecord
 
strS[COLOR=black]QL = "UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials " & _[/COLOR]
[COLOR=black]"SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice] " & _
"WHERE (((Docket.ID)=" & Forms!Touchscreen.List210 & ") AND ((Materials.ID)= " & Forms!Touchscreen.List125 & "));"[/COLOR]
 
CurrentDb.Execute strSQL, dbFailOnError

[COLOR=black]Me.Requery
[/COLOR]
[COLOR=black]End Sub[/COLOR]
[COLOR=black]
[/COLOR]
Click to expand...


Oops.. Run-time error 3075
Extra ) in query expression '(Docket.ID)=161 AND ((Materials.ID)=7)'.
 
Just pull out all of the parens.

"WHERE Docket.ID = " & Forms!Touchscreen.List210 & " AND Materials.ID = " & Forms!Touchscreen.List125 & ";"
 
boblarson said:
Just pull out all of the parens.

"WHERE Docket.ID = " & Forms!Touchscreen.List210 & " AND Materials.ID = " & Forms!Touchscreen.List125 & ";"
Click to expand...

Double Oops.. Syntax error (missing operator) in Query expression 'Docket.ID= and Material.ID=1'.
 
Your error message is showing you the key. There is no value for

Forms!Touchscreen.List210

So, is that a multiselect listbox? (because a multiselect listbox cannot be referred to like this. It has no value - it is null. You would need to build a string in code iterating through the items selected collection and set that to the values to limit by.
 
boblarson said:
Your error message is showing you the key. There is no value for

Forms!Touchscreen.List210

So, is that a multiselect listbox? (because a multiselect listbox cannot be referred to like this. It has no value - it is null. You would need to build a string in code iterating through the items selected collection and set that to the values to limit by.
Click to expand...

It wasn't a multi select listbox, however I think this is not the error, I put the code in the After Update event of txtDocket_Number so there would be no listerrors. Still no joy and now it doesn't even give a error.

The field buyprice and sales only update when running query manually, any idea where I go wrong??
 

Attachments

The After Update of txtDocket_Number isn't firing. How does the value for this text box get there?
 
boblarson said:
The After Update of txtDocket_Number isn't firing. How does the value for this text box get there?
Click to expand...

First the number is created by an On Click event, then I would like to fire the query in the After Update, of course there is a very big chance of a beginner mistake here.

The code;

Code: 
[COLOR="Green"]Private Sub txtDocket_Number_Click()
Me.Docket_Number = Nz(DMax("Val([Docket_Number])", "Docket"), 0) + 1
 
DoCmd.RunCommand acCmdSaveRecord




End Sub[/COLOR]

[COLOR="Red"]Private Sub txtDocket_Number_AfterUpdate()
Dim strSQL As String

strSQL = "UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials " & _
"SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice] " & _
"WHERE Docket.ID=" & Forms!Touchscreen.txtDocket_Number & " AND Materials.ID= " & Forms!Touchscreen.List125 & ";"
 
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery

End Sub[[/COLOR]/CODE]

Sorry if I think to simple..
 
The after update event doesn't fire if the changes are done by code. So you would need to call it explicitly in the click event. Like this:

Code: 
[COLOR=green]Private Sub txtDocket_Number_Click()[/COLOR]
[COLOR=green]  Me.Docket_Number = Nz(DMax("Val([Docket_Number])", "Docket"), 0) + 1[/COLOR]
 
[COLOR=green]  DoCmd.RunCommand acCmdSaveRecord[/COLOR]
 
[COLOR=green][COLOR=#ff0000]  [/COLOR][COLOR=blue][B]txtDocket_Number_AfterUpdate[/B][/COLOR]
 
End Sub[/COLOR]
 
[COLOR=red]Private Sub txtDocket_Number_AfterUpdate()[/COLOR]
[COLOR=red]Dim strSQL As String[/COLOR]
 
[COLOR=red]  strSQL = "UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials " & _[/COLOR]
[COLOR=red]"SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice] " & _[/COLOR]
[COLOR=red]"WHERE Docket.ID=" & Forms!Touchscreen.txtDocket_Number & " AND Materials.ID= " & Forms!Touchscreen.List125 & ";"[/COLOR]
 
[COLOR=red]  CurrentDb.Execute strSQL, dbFailOnError[/COLOR]
 
[COLOR=red]  Me.Requery[/COLOR]
 
[COLOR=red]End Sub [/COLOR]
 
boblarson said:
The after update event doesn't fire if the changes are done by code. So you would need to call it explicitly in the click event. Like this:

Code: 
[COLOR=green]Private Sub txtDocket_Number_Click()[/COLOR]
[COLOR=green]  Me.Docket_Number = Nz(DMax("Val([Docket_Number])", "Docket"), 0) + 1[/COLOR]
 
[COLOR=green]  DoCmd.RunCommand acCmdSaveRecord[/COLOR]
 
[COLOR=green][COLOR=#ff0000]  [/COLOR][COLOR=blue][B]txtDocket_Number_AfterUpdate[/B][/COLOR]
 
End Sub[/COLOR]
 
[COLOR=red]Private Sub txtDocket_Number_AfterUpdate()[/COLOR]
[COLOR=red]Dim strSQL As String[/COLOR]
 
[COLOR=red]  strSQL = "UPDATE Materials INNER JOIN Docket ON Materials.ID = Docket.Materials " & _[/COLOR]
[COLOR=red]"SET Docket.BuyPrice = [Materials].[BuyPrice], Docket.SalePrice = [Materials].[SalePrice] " & _[/COLOR]
[COLOR=red]"WHERE Docket.ID=" & Forms!Touchscreen.txtDocket_Number & " AND Materials.ID= " & Forms!Touchscreen.List125 & ";"[/COLOR]
 
[COLOR=red]  CurrentDb.Execute strSQL, dbFailOnError[/COLOR]
 
[COLOR=red]  Me.Requery[/COLOR]
 
[COLOR=red]End Sub [/COLOR]
Click to expand...

Still no luck, when I do that I am thrown to the first record, and no update..:confused:
 
Last edited:
You must log in or register to reply here.

Similar threads

B
Problem with bound field refresh on a form
Replies
1
Views
1,151
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom