 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / October 2007Find button to locate record in form and display that record
Hi, I have a unbound text field. I want to add a find button that when the user clicks, take the value typed in the unbound text field and searches all my forms. When it locates this unique ID in one of the forms, it opens up the form and displays that specific record. The unbound text field is called searchID The button is called clickFind Any help would be great! > Hi, > [quoted text clipped - 7 lines] > > Any help would be great! I would suggest an alternative approach as remembering an ID value is not what people do best. People remember words, i.e. Names, Company Names, etc. We know we wish to find records for Ford Motor Co. but who can remember that their ID number is 83162. Add an unbound combo box to the Form Header. If you use the Combo Box Wizard for this, select the 3rd option on the first set of questions, something like "Find the record ... etc." Continue on, including the RecordID field as well as the associated text name field. Hide the ID field. When done, start entering the word value. The combo will search ahead as you type and display all those values. When you find the correct one, select it. That record will be displayed on the form.  SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail > > Hi, > [quoted text clipped - 26 lines] > Please respond only to this newsgroup. > I do not reply to personal e-mail Hi, Perhaps I didn't do a good job explaining... I have a form (call this Aform) which lists results from a query; but I also want to add a find option to the form so that the user can just enter in the unique ID ( which is display on the result form). When the user clicks find, it searches 6 other different types of forms (more detail information on these forms). If it finds the ID, it opens the new form (Bform) and displays that record on Bform. I tried DoCmd.OpenForm but I couldn't get it to work. Thanks > > Hi, > [quoted text clipped - 25 lines] > Please respond only to this newsgroup. > I do not reply to personal e-mail This is a very interesting approach and I would like to be able to utilize it. I have added the unbound Combo box to the header as suggested. The combo box populates but nothing shows up in the form. Any further thoughts? >>> Hi, >> [quoted text clipped - 30 lines] > suggested. The combo box populates but nothing shows up in the form. > Any further thoughts? I need some help from you with this. What is the actual Rowsource of the Combo box? (Copy and Paste it) What is the Combo box Bound Column number? What is the Combo Box Column Count? What is the Combo Box Column Width property? What is the Combo Box Control source? It should be blank. What is the code behind the Combo Box's AfterUpdate event? (Copy and Paste it). SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail > >>> Hi, > [quoted text clipped - 46 lines] > > - Show quotedtext- Thanks for response. Following are answers in same order. 1) SELECT qryMSDSdata.ProdName FROM qryMSDSdata; 2) 1 3) 1 4) 3.4063 5) BLANK 6) Private Sub Combo77_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ProdName] = '" & Me![Combo77] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub *** snipped *** >>> This is a very interesting approach and I would like to be able to >>> utilize it. I have added the unbound Combo box to the header as [quoted text clipped - 32 lines] > If Not rs.EOF Then Me.Bookmark = rs.Bookmark > End Sub You are generating just one column in the rowsource; it's showing 1 column and bound to that column. I'll assume that the column width is in inches 3.0463". The AfterUpdate code looks good. This control should not be bound to any field in the table. All of the above looks OK. When you select one of the rows in the combo box, that record should be displayed on the form. 1) Are you absolutely sure that the value in the row selected actually exists in the form's recordsource. 2) Are you sure that the datatype of the bound column in the Combo Box is the same as the Table field's datatype (your code shows it's text). In other words, while the combo may show "Coffee Pot" the [ProdName] field in the table is really a Number datatype and is storing, for example an 8 (which corresponds to "Coffee Pot"). This can occur if your table is using that ill begotten LookUp field, where it stores one value but displays another. Make sure of the table field's actual datatype. If in fact it is a number, than change the AfterUpdate event to: rs.FindFirst "[ProdName] = " & Me![Combo77] SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail > *** snipped *** > >>> This is a very interesting approach and I would like to be able to [quoted text clipped - 64 lines] > > - Show quotedtext- While awaiting your reply I created a new temporary form in order to try to correct this The following are the answers to your original six questions as relating to this new form. 1) SELECT tblMSDSdata.RecordNum, tblMSDSdata.ProdName FROM tblMSDSdata; 2) 1 3) 2 4) 0";3.5417" 5)Blank 6) Private Sub Combo4_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo4], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Now in this case the process works up to a point. I originally had the data for the combo box come directly from the table but because we are using "FINDFIRST" and was getting something like the following. Lets say my input to the combo is "adhesive" adhesive wool soap nails adhesive Thought I'd try a query sorted by ProdName but still get the same thing I think because it is referencing the [RecordNum] rather than [ProdName]. > > *** snipped *** > > >>> This is a very interesting approach and I would like to be able to [quoted text clipped - 99 lines] > > - Show quotedtext- Success! I changed the RowSource to this "SELECT tblMSDSdata.RecordNum, tblMSDSdata.ProdName FROM qryMSDSdataIndex; And it now works just fine. Ignore that last nonsense. Thanks for asking those questions that pointed me in the right direction of thinking. Best regards > > > *** snipped *** > > > >>> This is a very interesting approach and I would like to be able to [quoted text clipped - 109 lines] > > - Show quotedtext- Tomorrow is another day, already. I have come across a strange anomaly this morning having had another look at it. The last post functions as expected for every letter of the alphabet except the letter "aA". When entering "aA" as the first letter into Combo4 the curser immediately jumps to the end of the field and adds any further input to the end.????? Andy other start letter operates properly. Any thoughts on that one? Don't fully understand you, and haven't read the whole thread, but Aa is the same as, or replacement for, Å in the nordic languages. Access therefore thinks it's dealing with that letter Pieter >> > > *** snipped *** >> > > >>> This is a very interesting approach and I would like to be able [quoted text clipped - 124 lines] > > Andy other start letter operates properly. Any thoughts on that one? On Oct 17, 9:52 am, "Pieter Wijnen" <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.ple...@online.replace.with.norway> wrote: > Don't fully understand you, and haven't read the whole thread, but Aa is the > same as, or replacement for, ? in the nordic languages. [quoted text clipped - 132 lines] > > - Show quotedtext- Sorry to mislead you. That was intended to depict both upper and lower case. |
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.