 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / August 2006Combo Box Integrity
I have set up a form to search for a record in a combo box under field Request ID. When the field is unbound i can carry out the search and find the record but when i scroll through the records this Request ID remains even as I am scrolling through. When the requestID is bound, it produces an error message about duplicating the data! Any help would be much appreciated Matt Matt, An unbound control is simply a container that displays data. It has the same value for all records, or rather, it has a single value for the form, and *displays* this value on all records. When a control is Bound, Access attempts to save data entered into it to the field to which it is Bound. Since, in your case, you'd entered a value that already existed, which presumably is the primary key of your table, Access is displaying a warning message. Since the combo box is just meant as a lookup value and has no meaning once you've reached the desired record, set it to Null or a blank string either at the end of your code or in the On Current event, which triggers when you arrive at a new record: Me![YourComboBox] = "" Hope that helps. Sprinks > I have set up a form to search for a record in a combo box under field > Request ID. When the field is unbound i can carry out the search and find the [quoted text clipped - 3 lines] > Any help would be much appreciated > Matt Sprinks, As there is no option to tab On Current within properties of my combo box should it be stored within Afer Update? Or should i be donig this within the form properties? My VBA currently reads as: Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Request ID] = '" & Me![Combo24] = "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub What alterations do i need to make? Many Thanks, Matt > Matt, > [quoted text clipped - 24 lines] > > Any help would be much appreciated > > Matt Matt, On Current is a Form event that is triggered when you move to another record. But you don't need to use it--simply modify your AfterUpdate event procedure: Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone ' Use the current value of the combo box to find the 1st matching record rs.FindFirst "[Request ID] = '" & Me![Combo24] If Not rs.EOF Then Me.Bookmark = rs.Bookmark ' Reset the combo box to a blank string Me![Combo24] = "" End Sub Also, if you're planning to get into Access VBA programming, the first smart thing to do is to name your form controls meaningfully, preferably per established naming conventions which can be found in any good Access reference. Bound controls are normally named by the field to which they are Bound prefaced by a three-character prefix that identifies the control type, e.g., txtLName cboCustomerID chkActive or, in your case, cboRequestIDLookup Following these conventions make your programming life *much* easier. Should you choose to rename this particular control, unfortunately, Access does not automatically rename your event procedure. To rectify, create a new AfterUpdate event procedure for the renamed control, cut and paste the body of the old procedure into the new one, and delete the old. Hope that helps. Sprinks > Sprinks, > [quoted text clipped - 45 lines] > > > Any help would be much appreciated > > > Matt Sprinks, Right ok thankyou. Maybe i wont go into the VBA route as i am puzzled about it all, was mainly showing it to you as you might understand where I was going wrong From your procedure i get an error message with run time error 3077 about a syntax error within the following expression: rs.FindFirst "[Request ID] = '" & Me![Combo24] That seems to be the only problem I believe and i am just not going to bother going into it anymore after this Many Thanks Matt > Matt, > [quoted text clipped - 90 lines] > > > > Any help would be much appreciated > > > > Matt Matt, My apologies. I'd thought you'd added code to the end of your Rs.Findfirst line. Return it to what it was, adding my new line at the end: Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Request ID] = '" & Me![Combo24] = "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me![Combo24] = "" End Sub Hope that helps. Sprinks > Sprinks, > [quoted text clipped - 107 lines] > > > > > Any help would be much appreciated > > > > > Matt Sprinks, Ok still no luck, but am determined not to give it up as a bad job The error message now is: Run time Error 3020, Update or cancelupdate without addnew or edit. It has highlighted the bottom line i.e. just Me![Combo24] = "" out of the whole thing as below Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Request ID] = '" & Me![Combo24] = "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me![Combo24] = "" End Sub Cheers, Matt > Matt, > [quoted text clipped - 128 lines] > > > > > > Any help would be much appreciated > > > > > > Matt Matt, I still screwed you up; my apologies. First, be sure your lookup combo box is Unbound, that is, its ControlSource property is blank. The syntax for the rs.Findfirst line depends on whether the *value* of the combo is text or numeric. The *value* of the row selected is the value in the Bound column. Depending on how the ColumnWidth property is set, what is *displayed* might be different from the value. Furthermore, a RequestID field, although normally numeric, might be set as a text field to accomodate leading zeros or alphabetic suffixes. Let's assume your field is a text field. Try cutting and pasting the following: Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone ' This line contantenates the literal string [RequestID] = ' with the value of the combo box, placing single quotes ' around the combo box value. This is the way to handle ' string values rs.FindFirst "[Request ID] = '" & Me![Combo24] & "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark ' Set the combo box to a blank string Me![Combo24] = "" End Sub If this doesn't work, the value must be a number. Cut and paste: Private Sub Combo24_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone ' Single quotes around the combo box value are not needed ' for a numeric value rs.FindFirst "[Request ID] = " & Me![Combo24] If Not rs.EOF Then Me.Bookmark = rs.Bookmark ' Set the combo box to a blank string Me![Combo24] = "" End Sub Hope that helps. Sprinks > Sprinks, > [quoted text clipped - 151 lines] > > > > > > > Any help would be much appreciated > > > > > > > Matt |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.