Change rowsource on cbo

[Tvanduzee]

Hello

Ok, here is the structure of my form/subform:
Combo box that displays work order numbers from my tbl_WO. Populates the rest of the main form and the details associated with it on the sub-form. -- Works as it should.

Because I do not want orphan data, and because I do have fields that are required, I cannot generate new WorkOrders in the Work Orders Table.
What I have done is this:

I created a form where the user can generate new Work Orders and print them off (for the guys in the shop). When they are generated, they are generated into a "tempWo" table. - So far no problem.

Those work orders are then printed off and the shop guys can fill them out.
When the office gets the work orders back from the shop, they enter the data.

The work orders form has a couple different things it does. They can view the WOs one by one or they can search to find a work order. Again, so far no problem. The problem is that when the user goes to enter a new Work Order, I need to change the source to display only the Work Orders from the tempWO table. This way, they cannot select a work order that does not exist yet. I have programmed a button on my form to add a new record. I now need to put some vba in the OnClick event for the button, to change the row source of the combo box, and have the value selected become the Work Order value in the WorkOrders table (as well as all other data inputted). I then need to remove the value from the TempWOs table once the record is committed, but I can figure that out...
Here is the code I have tried, but does not work the way I need it to. I can't figure out how to get the value into the Work Orders Combo box.

Private Sub Command71_Click()
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.Combo63.RowSourceType = "Table/Query"
Me.Combo63.RowSource = "TempWO"
End Sub

Any suggestions?

Thank You
Terry

[June7]

You have to requery the combobox after changing the RowSource:
Me.Combo63.Requery

Removing record from TempWO table would be DELETE sql action. Assuming WO is text:
CurrentDb.Execute "DELETE FROM tablename WHERE WO='" & Me.Combo63 & "'"

Why would a WO be 'orphan'? Personally, I would find a different method than temp table for input. I use 'temp' tables only for extreme situations where they facilitate data manipulation for output, in which case I would purge all records when the output process is completed.