Table looks like:<br>PONumber Vendor<br>PO1022 MUW-001<br>PO9303 KEK-203<br><br>This code is attached to a ponum combo box, what I need to happen is if the ponum exists in the table, then fill the vend combo box on the form with the vendor number, otherwise, if the ponumber doesn't exist, leave the vend field blank so I can type a vendor.<br><br>Private Sub ponum_AfterUpdate()<br> Dim db As Database<br> Dim rs As Recordset<br> Dim strSeek As String<br><br> Set db = CurrentDb<br> Set rs = db.OpenRecordset("SR-SYMIXPO", dbOpenTable)<br><br> With rs<br> .Index = "PONumber"<br><br> .MoveLast<br> .MoveFirst<br> <br> strSeek = Me!ponum<br> <br> .Seek "=", Val(strSeek)<br> If .NoMatch Then<br> End Sub<br> End If<br> Me!vend = ![Vendor]<br> .close<br> End With<br> db.close<br>End Sub<br><br>thanks in advance!!<br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HELP! Can't get VB code to work! 1
- Thread starter Rubius
- Start date
-
1
- #2
personally, i use this code to populate my table. put it in the After Update property of the combo box:<br><b><br>Me.RecordsetClone.FindFirst "[PONumber] = '" & Me![PONumberComboBoxName] & "'"<br>Me.Bookmark = Me.RecordsetClone.Bookmark<br>Me.Refresh<br></b><br><br>and put this in the NotInList property:<br><b><br>Dim iAnswer As Integer<br>iAnswer = MsgBox("Vendor is not currently in list. Please Add", _vbOKCancel + vbQuestion)<br>If iAnswer = vbOK Then<br>Me.PONumberComboBoxName.Value = Null<br>DoCmd.GoToRecord acActiveDataObject, , acNewRec<br></b><br><br>try this, but change "PONumberComboBoxName" to whatever the actually name is in both sets of code.<br>this requires the PONumber field to be on the form, but it can be hidden - i would put the combo box in the form header, and hide the header after update, and then have the actual PONumber field in the detail section of the form.<br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
- Thread starter
- #3
ok try to follow me here.<br>if I type in a valid ponum then vend2 will fill and it should use the Else and then set vendname to itself.<br>if it is an unvalid ponum, then vend2 will not fill and it will use whatever I type in vend.<br>right now what is happening is I enter a po, press enter, vendname goes to #Name? and about .25 seconds later, vend2 will show the vendor. If I type a unvalid po, then vendname goes to #Name? and vend2 goes blank. Do you think I need some sort of delay before the if statements? <br><br>Private Sub ponum_AfterUpdate()<br> If Me!vend2 = Null Or Me!vend2 = "" Then<br> Me!vendname.ControlSource = Me!vend<br> Else<br> Me!vendname.ControlSource = Me!vend2<br> End If<br> Me.Refresh<br>End Sub<br><br>famousb, thanks for you help, but all I need to do is get these forms to display the correct data because I am adding all the records to a completly different table..I already have the code I need for that task and it works fine..<br>thanks again.<br>
ok, i'm following a bit more now.<br>but firstly<br>nothing can = Null, that's an impossibility. it is null, it doesn't equal null, there you're if should look like:<br><b><br>Private Sub ponum_AfterUpdate()<br> If IsNull(Me!vend2) Then<br> Me!vendname.ControlSource = Me!vend<br> Else<br> Me!vendname.ControlSource = Me!vend2<br> End If<br> Me.Refresh<br>End Sub<br></b><br><br>try that instead. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
- Thread starter
- #5
No it's doing the exact same thing. How about this...this is what I need it to do:<br>if a valid po number is entered, vendname is to use the data in vend2.<br>if a unvalid po is entered, then it needs to use the data from vend, which I need to enter after I type in the PO.<br>It's like it needs two seperate control sources, with ifs controlling which one to pick.<br><br>fields are:<br>ponum (combo box 2 rows - POnumber and nameofvendor)<br>vend2 (combo box controlsource=[ponum].[Column](1))<br>vend (combo box - unbound)<br>vendname (text box - either vend2 or vend depending)<br><br>I beginning to think this can't be done!<br>thanks..
ok, i'll try this again.<br><b><br>Private Sub ponum_AfterUpdate()<br>If IsNull(Me.vend2) Then<br>Me.vendname.value = Me.vend.value<br>Else<br>Me.vendname.value = Me.vend2.value<br>End If<br>Me.Refresh<br>End Sub<br></b><br><br>ok, i should have looked at it closer earlier. haste makes waste.<br>if this doesn't work i'll guarantee a solution next try.<br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>
- Thread starter
- #7
THANK YOU SO MUCH FOR ALL YOUR HELP! After I typed the last message, I thought to myself, why in gods name do I even HAVE vendname! Deleted it and made the code look like this:<br><br>Private Sub ponum_AfterUpdate()<br><br> Me!vend = Me![vend2].Column(0)<br><br>End Sub<br><br>Don't even need the .Column(0) part..:\ ahh well!<br>Now even if vend2 is null it will fill vend with a blank that I can type anything in and if not it will fill it with the name of the vendor!!<br>Don't think you code is going to waste though, because my next assignment is going to have 2 different fields that one will control off of..so it will be perfect! Thanks again man!!!
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question