 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / January 2005Event procedure
Al thanks for all your help. New issue. The search is not working. Originally I had the button tied to a macro that would open the form that contained the record information. When I enter the Id and click the button it says there is no field name in the current record. How do I direct the button to the table and the associated form. Thanks, Cindy "AlCamp" wrote: > OK, we're good so far... now we just need to do a FindRecord if the ID > exists. Your cut & paste ([Tbl_CCA]![AGY_ID] = > [Forms]![frmSearch]![Search]) from the "macro builder" won't work. Putting > a bit of sweat equity into learning to use event procedures and code is well > worth the effort! > > I take it your text control with the entered search value is called > [Search], and [Search] is on the same form, and the field you're searching > on is [AGY_ID]. > > Try this code... (again... use your own names) > > Private Sub cmdSearchAGYID_Click() > If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " & [Search])) Then > MsgBox "No such AGYID", vbOKOnly, "AGYID not found" > Exit Sub > Else > DoCmd.GoToControl "AGY_ID" > DoCmd.FindRecord [Search] > End If > End Sub > > If AGY_ID is unique, this method will find it, if there are multiple records > this method will return the First record that meets the criteria. > > So... just create a button for each of the other 2 fields you want to search > on, and just tweak the same basic code to work accordingly, and that should > do it. > hth > Al Camp Cindy, If your old macro did the Find correctly, then just convert that sequence of macro instructions into VB code behind your FindID button. You don't tell me what those macro functions are, so I'll just have to guess... IF you have a seprate "dialog form" (form Search)** where you are entering your search criteria then you'll have to use a FIND sequence like this... DoCmd.OpenForm "YourIDForm" DoCmd.GoToControl "AGY_ID" DoCmd.FindRecord Forms!YourDialogFormName!Search This is just a "typical" sequence... there's no way that I can know exactly how you have everything set up. Use it as a guide, and use Help to understand the Functions, their associated syntax, and then customize this my process for your particular needs. **Right now you I think you have a form named Search, and a field on that form called Search... (duplicates)... that's not good. Your dialog form should be named frmSearchDialog for clarity, and your field on the form for the AGY_ID value should be renamed to FindAGY_ID. (and change the code above accordingly) hth Al Camp > Al thanks for all your help. New issue. The search is not working. > Originally I had the button tied to a macro that would open the form that [quoted text clipped - 46 lines] >> hth >> Al Camp Al, thank you so much for the time you are takeing to helping me. Below is the code I currently have: Private Sub cmdSearchRPPSID_Click() If IsNull(DLookup("[RPPSID]", "Tbl_CCA", "[RPPSID] = " & [Search])) Then MsgBox "This RPPSID does not exist in the Table", vbOKOnly, "RPPSID Not Found" Exit Sub Else DoCmd.OpenForm "frmCCA" DoCmd.GoToControl "RPPSID" DoCmd.FindRecord Forms!frmSearchDialog!Search End If End Sub The error message box works. I get this error on the DoCmd.GoToControl "RPPSID" line: Error 2109 - There is no field named "RPPSID" in the current record This is the field in my table that I am searching for the record. I was reading about control sources yesterday, but was not successful in getting this line to work. The Macro I originally had tied to the button was OpenForm, the name of the form is frmCCA, View Form, Where Condition - [Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search] This found the correct record. If the RPPSID was not in the table the form would open to a new blank record, I did not want that. I wanted to put another button on the search form for creating a new record that would bring up an empty for for data entry. Again, thank you, Cindy > Cindy, > If your old macro did the Find correctly, then just convert that [quoted text clipped - 70 lines] > >> hth > >> Al Camp Cindy, I would much rather have stuck with searching for AGY_ID... now, we're doing RPPSID?? Please... let's try to stick to one problem at a time. frmCCA should have text control on it named RPPSID. The ControlSource for that is field RPPSID from the tbl_CCA. Do you have that? Al Camp > Al, thank you so much for the time you are takeing to helping me. Below > is [quoted text clipped - 121 lines] >> >> hth >> >> Al Camp Regarding my previous response... try this instead. It should work whether you actually have [AGY_ID] on the form or not You wrote... > The Macro I originally had tied to the button was OpenForm, the name of > the > form is frmCCA, View Form, Where Condition - > [Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search] OK... this is good info.... this is what you had in the old macro. Now we just have to translate it to VB. I'll use the old AGY_ID code... Private Sub cmdSearchAGYID_Click() If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " & [Search])) Then MsgBox "No such AGYID", vbOKOnly, "AGYID not found" Exit Sub Else '------------- try this DoCmd.OpenForm "frmACC", , , "[AGY_ID] = Forms!frmSearch!Search" End If End Sub Al Camp That worked, thank you. I had a question about another db I am working on. Can I use that same type of code to search for multiple records with the same id. maybe a search by id and a date range that will bring up a list of all occurences that meet that criteria then select the correct record to go to. > Regarding my previous response... try this instead. It should work whether > you actually have [AGY_ID] on the form or not [quoted text clipped - 19 lines] > > Al Camp If you're searching a field with that contains only "unique" values... (probably indexed with no dupes) this code will find the exact record. If you're searching a field that does not have "unique" values (contains duplicate values) this code will find the First instance of that value. A method I use for finding multiple answers to a recordset criteria is to "filter" the dataset for that value. ONLY those records that meet the criteria are returned, and they are contiguous, allowing for easy browsing from one to another. Check out Filter and Filteron functions in Help. Al Camp > That worked, thank you. I had a question about another db I am working > on. [quoted text clipped - 31 lines] >> >> Al Camp |
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.