 |
 | 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
In the db I built I have a form that searches for a value in a specific column in the db. I have used the expression builder to link the button to the field in the db. The problem is that if the value is not found in the table it goes to a new record. I need an error message to come up but I do not know where the code would be since the procedure was built in expression builder and not a module or form code in vb. Any help will be greatly appreciated. Cindy, You wrote... >I have used the expression builder to link the button to the field in the >db. It would have been helpful if you had included that code so we could see what you're trying to do. I'm guessing that you're asking the user for a value for a specific field, then finding the record that has that value. Try doing a Dlookup on that value, and if the Dlookup returns Null, then post a message box, and don't do the Find. hth Al Camp > In the db I built I have a form that searches for a value in a specific > column in the db. I have used the expression builder to link the button [quoted text clipped - 7 lines] > > Any help will be greatly appreciated. I enter a value in the search text box on the form and click on the button which is linked to this code - [Tbl_CCD]![ORB_ID]=[Forms]![frmSearch]![Search] - If the value is in the table a form will come up with the record information if the value is not in the form a blank form comes up for a new record to be entered. I just want an error message if the value is not in the table. I have 3 different search options in the search form so I do not want new records entered that may already be in the table. > Cindy, > You wrote... [quoted text clipped - 24 lines] > > > > Any help will be greatly appreciated. Cindy, OK... that just what I thought... > I'm guessing that you're asking the user for a value for a specific field, > then finding the record that has that value. Did you try my suggestion? > Try doing a Dlookup on that value, and if the Dlookup returns Null, then > post a message box, and don't do the Find. You'll need to do that for each of your 3 individual search fields. hth Al Camp >I enter a value in the search text box on the form and click on the button > which is linked to this code - [quoted text clipped - 41 lines] >> > >> > Any help will be greatly appreciated. I tried the DLookup set in the ControlSource for the text box used to enter the value to search for. When I tried to test this I could not enter the value in the text box. Have I done something wrong. Is the value entered another way? > Cindy, > OK... that just what I thought... [quoted text clipped - 55 lines] > >> > > >> > Any help will be greatly appreciated. Cindy, Let's just work with one search value, and apply the same solution process to all three... *I'll use example names, you use your own.* Here's the steps you'll need to take... 1. Enter a CustomerID in your search textbox called FindCustomerID. 2. Click your FindCustomerID button. 3. Determine if such a CustomerID exists 4. If it does, run code to find it. 5. If NOT, alert the user and quit the sub. Leave your text box the way it was originally, so you can enter a value to search for. You said you had a button that you click when you want to try to find that value. Using the OnClick event of that button... Note: Watch out for Email text on this note that "wraps" to another line... Private Sub cmdFindCustomerID_Click() If IsNull(DLookup("[CustomerID]","tblCustomers","[CustomerID] = " & [FindCustomerID]) Then MsgBox "No such Customer ID", vbOKOnly, "CustomerID not found" Exit Sub Else 'Put your original Finding code here End If End Sub Use the same process for the other two Find fields. If you still have problems, please tell me exactly what process you used, and any code involved. hth Al Camp >I tried the DLookup set in the ControlSource for the text box used to enter > the value to search for. When I tried to test this I could not enter the > value in the text box. Have I done something wrong. Is the value entered > another way? Thanks for all you help. I got the DLookup part working but where you said to put my code for the search I had built that in expression builder. Here is what I have so far: 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 [Tbl_CCA]![AGY_ID] = [Forms]![frmSearch]![Search] End If End Sub The line of code after Else is what I took from the macro expression field the error says it cannot find that field. Is there specific VB code for the search instead of the way I did it? Thanks >-----Original Message----- >Cindy, [quoted text clipped - 40 lines] > >. 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 > Thanks for all you help. I got the DLookup part working > but where you said to put my code for the search I had [quoted text clipped - 79 lines] >> >>. 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 > 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] = [quoted text clipped - 110 lines] > >> > >>. |
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.