Run cmd action on Form Submit

[glenmac]

I have a form with cmd button on it that inserts value in a table. Is there a way to have the value in the same record the form creates when submitted. As it stands now 2 records are created one from the form and one from the cmd button. I need to input the values in the same record.
here's the code from the cmd button.

Private Sub Command36_Click()

On Error GoTo Err_Command36_Click

For Each num In List37.ItemsSelected
var = var & List37.ItemData(num) & "_"
Next
If Len(var) > 0 Then
DoCmd.RunSQL "insert into Orders (Custom_Ingedients) values ('" & Left(var, Len(var) - 1) & "')"
'MsgBox var
End If



Exit_Command36_Click:
    Exit Sub

Err_Command36_Click:
    If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Then
      Resume Next
    End If
    MsgBox Err.Description
   Resume Exit_Command36_Click
    
End Sub

All help will be greatly appreciated !!

 

[psemianonymous]

You haven't provided exactly what I need to give you an exact answer. Here's a general one:


You can instead of using an INSERT statement, use an UPDATE statement: UPDATE Custom_Ingredients SET fldname = " & var & " WHERE [an appropriate where statement to get the current record, usually the best way is to find the ID]



--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[glenmac]

Thanks for the input beeing a rrokies with acces I'm having trouble getting the syrax right can you help? This is what I have.

DoCmd.RunSQL "Update Orders  set  (Custom_Ingedients)=('" & Left(var, Len(var) - 1) & "')where SELECT Max([orders].[Order_ID]-1);"

 

[psemianonymous]

I think you're looking for:

DoCmd.RunSQL "Update Orders  set  (Custom_Ingedients)=('" & Left(var, Len(var) - 1) & "') where Order_ID IN (SELECT Max([orders].[Order_ID]));"

There was a better way to do this, but I lost the link: it involves something like "@@Order_ID".


Another way to do it is first save the record, then use the control bound to the Order_ID field to retrieve the actual value of the ID and use that instead of a complicated query.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[glenmac]

I copied a pasted your code but get error "syntax error in update statement" Thanks for your help. (sorry about the previous typing errors)

Private Sub Command11_Click()

On Error GoTo Err_Command11_Click

For Each num In List3.ItemsSelected
var = var & List3.ItemData(num) & "---"
Next
If Len(var) > 0 Then
DoCmd.RunSQL "Update Orders  set  (Custom_Ingedients)=('" & Left(var, Len(var) - 1) & "') where Order_ID IN (SELECT Max([orders].[Order_ID]));"
'MsgBox var
End If



Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Then
      Resume Next
    End If
    MsgBox Err.Description
   Resume Exit_Command11_Click
    
End Sub

 

[psemianonymous]

Blah, I messed up.

DoCmd.RunSQL "Update Orders  set  (Custom_Ingedients)=('" & Left(var, Len(var) - 1) & "') where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);"

Note the extra bit at the end.

 

[glenmac]

Same error

 

[beetee]

howdy glenmac:

I don't want to hijack this thread, but I would like to point out something (which applies to others as well) (it's kind of meta-advice):

You can get results a little faster if you provide more useful information. In this case, what would really help foolio12 out is if you were to post the ACTUAL Sql that is generated.

Do this by adding a statement like:

debug.print "Update Orders set (Custom_Ingedients)=('" & Left(var, Len(var) - 1) & "') where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);"

Just before the RunSQL statement.

Press ^G to open the debug window, and copy the actual statement to a message.

HTH

 

[glenmac]

This is what I get after running the debug statement. Sorry for my ignorance and thank you for the tip.

Update Orders set (Custom_Ingedients)=('test---Salami--') where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);

 

[glenmac]

I can't see what's wrong with the update statement. If I pull it apart it works fine???

 

[psemianonymous]

DoCmd.RunSQL "Update Orders  set   Custom_Ingedients = '" & Left(var, Len(var) - 1) & "' where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);"

That's all I have. I took out the extraneous parentheses. Beyond that, I don't know what's going wrong.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[glenmac]

Thanks for your attempt. It is appreciated!!! Here is the last error I get when I run your suggestions:

[TCX][MYODBC]You have an error in yourSQL syntax. Check the manual that corresponds to your MYsQL server version for the right syntax to use near '(SELECT MAX(MS2.Order_ID) FROM orders MS2))'at the line 1 (#1064)

Any help would be appreciated..

 

[beetee]

http://www.mysql.com/doc/en/

 

[glenmac]

Thanks beetee. I'm assuming as I'm using an MySQL backend with linked tables in an Access frontend I'm getting the MS2 in the error statement. Is that correct?

 

[beetee]

That's what I'm guessing, based on the error message you got:

[TCX][MYODBC]You have an error in yourSQL syntax. Check the manual that corresponds to your MYsQL server version for the right syntax to use near '(SELECT MAX(MS2.Order_ID) FROM orders MS2))'at the line 1 (#1064)

I thought the yourSQL part was kinda funny. It's probably some minor glitch in the subquery grammar that was used. I don't use MySql at all (in fact, my SQL knowledge in general is pretty weak [especially using subqueries]), so I hesitate to offer any advice other than the location of the manual.

Good luck!

 

[psemianonymous]

SELECT MAX(MS2.Order_ID) FROM orders MS2


I'd say that's where the problem is, though like beetee I have no way of knowing for sure. I know that the FROM clause should only have only "orders" in it. Maybe MS2 is the database name inside MySQL? Good luck.

Tek-Tips MySQL forum: forum436

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[glenmac]

Thank you guys very much for your time. I was trying to get away from Access limitations on DB size but I guess it aint possible or at least not for me, With my limited knowledge.

 

[beetee]

I think using MySql is a great idea. Anything to get away from the Jet Engine. This link may give you more insight:

You might want to take a moment and look over the help.

Your query looks like this:

Update Orders set (Custom_Ingedients)=('test---Salami--') where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);

After reading the manual, I learned:
1) I'm not sure that you can use '[' and ']', you may want to try to use the query without those charcters.

2) MySql has limited ability to deal with sub-selects (the SELECT Max portion):

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

3) in any case, the sub-select seems to be missing a 'group by' clause, e.g.
SELECT Max(orders.Order_ID) from Orders group by CustomerID
although since I don't have a copy of MySql I can't be sure this will help

One final thought: if you can get to the MySql server, perhaps you can experiment creating the query using their command line interface.

 

[glenmac]

Thaks Bee Tee. I'll keep digging there must be a way. If I get there I'll post the code.

 

[glenmac]

OK got some info Apparently Mysql doesn't support sub queries (yet). So I thought I 'd separate the queries into 2 simpler ones and set a variable for the max Order_ID. So I tried this query.
DoCmd.RunSQL "SELECT Max([Orders].[Order_ID]) AS ID FROM Orders;"
But I get an error "RunSQL command requires an argument consisting of a SQL Statement" When I run this statement as a query it runs fine. When I run debug it gives me
SELECT Max([Orders].[Order_ID]) AS ID FROM Orders; For the life of me I can't see where the error's coming from! Can anyone see where I'm going wrong?