Update Subform Table data using entry on main form

[Cheryl R]

I have a list of tools to send to outside vendors. There are four possible outside vendors.
I have created a form with a combo box with the vendor names and the subform lists the tools to go to that specific vendor (this comes from a query).

What I need now is to be able to enter a PO number on the main form and for that to update the Outside PO# and today's date for the items in the subform.
Currently, the linked master/child fields are the outside vendor.

My question is, do I need to create an update query to make this happen or is there an easier way I am missing?

And if I have to use an update query, how do I use the selection in the drop-down of the form and not have to enter it when the query runs (as it is happening currently)?

Thank you in advance for your help.

CR

[Dal Jeanis]

Suggestion -
0) As always, make a backup of your database, make a "play" copy, and verify the code on the play copy before putting it into the real database...
　
1) Put a command button on the form

2) Have the Click event of the command button check to make sure the dropdown contains a valid PO number

3) load the value from the cmboPO into a Access 2010 temp variable
TempVars.Add "tempPO", cmboPO.Column(0)

4) use the temp variable in your stored update query
"WHERE tblYourTable.YourPO = [TempVars]![tempPO];"
article on temp variables
http://office.microsoft.com/en-us/access-help/settempvar-macro-action-HA010120216.aspx

NOTE 1 - Steps 3 and 4 can be combined and you can skip the temp variable if you are building the SQL in VBA in your click event.

Dim ldbs As Database
Dim strSQL As String
Set ldbs = OpenDatabase(CurrentProject.FullName)
strSQL = "UPDATE tblWhatever SET Blahblah = Blahblah WHERE tblYourTable.YourPO = " & cmboPO.Column(0) & ";"
ldbs.Execute (strSQL)

NOTE 2 - Use the appropriate property value from your combo box, for instance if there are multiple columns on the dropdown
　
　
　
　

[Cheryl R]

Thanks for the advice to always play in a backup copy... my 'play' copy has crashed several times already and won't reopen. Could that be an update query I am running on form load?

So, I have the form with the button but not quite clear what you mean when you say Have the Click event of the command button check to make sure the dropdown contains a valid PO number. I have tried entering this several ways but just can't make it work...

When I bind SQL to the VBA event, not sure how to do this. I copied your code and entered my data and the SQL statement behind my update query but got a "bad syntax error".

There is only one column on my combo box, so not sure what the problem is now.

[Dal Jeanis]

Here's a slightly different coding convention, with the whole code from behind one of my command buttons.

To add this kind of code behind a button,
1) look at the form in design view,
2) right click the command button,
3) open the properties pane,
4) switch to the events tab,
5) click in the box for the CLICK event,
6) click the three dots to the right of the box,
7) and select code builder.

The VB editor will start you with a stub of code.
8) Copy this code in its place.

Private Sub cmdAddAll_Click()
On Error GoTo Err_cmdAddAll_Click
Dim strSQL As String
Dim intItem As Integer
Dim listChanged As Boolean
' turn off warnings for updates
 DoCmd.SetWarnings False

       ' build sql
       strSQL = "INSERT INTO tblYourTable ( [FieldName1], [FieldName2] ) " _
         & "VALUES ( Value1, Value2 ); "
         
         
       ' for testing, show SQL
       ' MsgBox strSQL
                
       ' run sql
       DoCmd.RunSQL strSQL

' turn on warnings for updates
DoCmd.SetWarnings True
' redisplay
Me.Requery

Exit_cmdAddAll_Click:
    Exit Sub
Err_cmdAddAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddAll_Click
    
End Sub

9) Change the name of the command button in the above code to match yours (use global replace). In this example, the button name was cmdAddAll.

10) Replace the SQL with your own.

11) You might want to uncomment the Msgbox statement to see what youre SQL looks like before it executes.

12) switch the form back to form view and click the button.

If this doesn't work, please post your code here and describe exactly what happened when you clicked the button. If it does work, please mark the thread solved.

[Dal Jeanis]

OOOPS -

10B) Comment out the line that says DoCmd.SetWarnings False (or you won't see any warning messages...)

[Cheryl R]

This works perfectly!
My error was that my code was updating the query rather than the tables in the background. What a HUGE difference that makes! (you can't do that, by the way!)

Thank you thank you for your help!

[RuralGuy]

@Dal: I assume you know that Currentdb.Execute strSQL, dbFailOnError will not show any of the warnings and so NO "DoCmd.SetWarnings False" is ever required. Just an FYI post. Good job.

[Dal Jeanis]

@RuralGuy - I could fudge and say "I was vaguely aware of that", but instead I'll just say it was a cut and paste job from a working form that did almost exactly what she needed.

Just as well, because I probably wouldn't have noticed the dbFailonError and then Cheryl wouldn't have been able to debug the SQL she built!

@Cheryl - glad to help.

[RuralGuy]

@Dal - I think you are doing a bang up job of helping people and we all can learn a little something new once in a while. Again, great job!