Changing a bound control to unbound.

[Rubius]

I have a box that is bound to column 1 of a combo box called &quot;PONumber&quot;, it opens the name of the vendor and automatically fills it in when a PO number is selected. Now some PO's are not in the system and need to be given a vendor name when a referencing PONumber doesn't exist. What I have right now is something like <br>=IIf(IsNull([ponum].[Column](1)),&quot;&quot;,[ponum].[Column](1))<br>so if a ponumber exists it'll use column one and if not, nothing will happen. How can I change that &quot;&quot; to something that will allow me to enter a vendor in the field. So I need a command that changes it to a Unbound control.<br><br>thanks in advance.

 

[elizabeth]

I <i>think</i> what you want to do instead is have a combo that allows you to select the proper PO number, with the combo's LimitToList property set to No, and some code in the combo's NotInList event to pop up a form to enter the new PO. Something like this:<br><br>Private Sub Building_NotInList(NewData As String, Response As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim intAnswer As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;intAnswer = MsgBox(&quot;Building not in table. Edit tblBuilding?&quot;, vbYesNo, vbQuestion)<br>&nbsp;&nbsp;&nbsp;&nbsp;If intAnswer = vbYes Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenForm &quot;frmBuilding&quot;, acNormal, , , acFormEdit, acDialog ' opens form allows add/edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Response = acDataErrAdded<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Response = acDataErrContinue<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>End Sub<br><br>This allows you to add the new PO to your database, and then use the combobox as usual.

 

[Rubius]

Ok, that wasn't really what I was looking for, I will be more specific, sorry.<br>Ok, lets say I have 2 combo boxes one called PONumber and the other called Supplier.<br>PONumber is Unbound, so anything can be typed here, real PO or not, it doesn't matter. There is a table with a bunch of PO's and each one has a supplier beside it.<br>Table looks like:<br>PO&nbsp;&nbsp;&nbsp;Supplier<br>29&nbsp;&nbsp;&nbsp;TekTips<br>40&nbsp;&nbsp;&nbsp;elizabeth<br>etc...<br>The purpose of my form is to create a totally new table with all the received info on it. (So the new table has info such as PO, Invoice #, Date, Supplier, etc) I know how to do this, isn't a problem. What the problem is, is that we have probably 300 suppliers all with different codes and such. What I want to be able to do is the following:<br>User types PONumber<br>If it exists then fill supplier field with the supplier.<br>If it does not exist, allow user to type in a supplier.<br><br>My problem is that, I can't edit the field because it is bound, I guess what I'm asking is how can I Unbind the field when the PONumber doesn't exist?<br><br>any more questions let me know!<br>thanks! <br>Justin

 

[JimConrad]

You may be confusing the term BOUND.<br><br>A bound field is one that refers to a column in a database table.&nbsp;&nbsp;An unbound field is one in which the data will not come from your database and Access will not use to store data into your table.<br><br>If you are trying to update the VENDOR column in your database then you MUST use a bound control (or use an unbound control and then do the database update yourself using some VBA code, but it makes more sense to let Access handle this automatically).<br><br>The fact that the control you are using is a combobox may be causing your problem.&nbsp;&nbsp;If the VENDOR combobox property &quot;Limit To List&quot; is set to true, then you are stuck with the values in the combo.&nbsp;&nbsp;Change this to &quot;false&quot;.&nbsp;&nbsp;Then you can enter whatever you like into the VENDOR field.<br> <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[Rubius]

hmmmm, ok, ok....well the limit to list is set to &quot;no&quot;, but because I have that line in the controlsource I cannot edit it. But updating it through VB code makes a lot more sense. I guess this is what the code I need would be..I don't know the code though but in theory it would have to be:<br><br>on afterupdate_ponumber<br>&nbsp;&nbsp;&nbsp;&nbsp;dim i as string<br>&nbsp;&nbsp;&nbsp;&nbsp;i=ponumber field<br>&nbsp;&nbsp;&nbsp;&nbsp;if exist ponumber in potable<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;then supplierfield = potable.supplier<br>&nbsp;&nbsp;&nbsp;&nbsp;end if<br>end sub<br><br>Could someone give me the actual code for this?? I don't have very much experence working with recordsets.<br><br>thanks again!<br>Justin