VBA, Form displays recordset depending of a value

Form displays recordset depending of a value

Author	Message
Johan #1 / 4	Form displays recordset depending of a value As a student i'm designing a database of recipes Having design a form which displays the list of recipes, an input box offers the possibility of accessing a recipe by entering the recip's ID number then clicking a command button. Not familiar with VB , i'm lost. If anyone as any suggestions they will be welcome, here is how i started. ThanksPrivate Sub Display_Click() Dim db As database Dim rs As Recordset Dim number As Integer Set db = CurrentDb("db1") Set rs = db.Openrecord("Recipe by ID number", dbOpenTable) Set number = Forms![Recipe List Form]![IDtextbox.value] If Forms![Recipe by ID number]![RecipeID.value] = number Then DoCmd.OpenForm Forms![Recipe by ID number] Else: number = "" DoCmd.OpenForm = Forms![No Value frm] End If End Sub
Fri, 17 Oct 2003 07:23:26 GMT

Tony Oakle #2 / 4	Form displays recordset depending of a value Quote: > As a student i'm designing a database of recipes > Having design a form which displays the list of recipes, an input box offers the > possibility of accessing a recipe by entering the recip's ID number then clicking > a command button. > Not familiar with VB , i'm lost. > If anyone as any suggestions they will be welcome, here is how i started. > ThanksPrivate Sub Display_Click() > Dim db As database > Dim rs As Recordset > Dim number As Integer > Set db = CurrentDb("db1") > Set rs = db.Openrecord("Recipe by ID number", dbOpenTable) > Set number = Forms![Recipe List Form]![IDtextbox.value] > If Forms![Recipe by ID number]![RecipeID.value] = number Then > DoCmd.OpenForm Forms![Recipe by ID number] > Else: number = "" > DoCmd.OpenForm = Forms![No Value frm] > End If > End Sub There are a lot of syntactical errors in your code, if I understand the logic, you want to check that a record with the required recipeID exists, if it does, open up a form with the selected record, otherwise, open up a different form, if so try the following, otherwise let me know. Private Sub Display_Click() Dim intNum As Integer intNum = Me!IDtextbox.value If IsNull(DLookup("RecipeID", "Recipe by ID Number", "RecipeID = " & intNum)) Then DoCmd.OpenForm "No Value frm" Else DoCmd.OpenForm "Recipe by ID number", , , "RecipeID = " & intNum End If End Sub Let me know if this works. Hope this helps Tony Oakley
Fri, 17 Oct 2003 15:49:00 GMT

johan #3 / 4	Form displays recordset depending of a value Private Sub Display_Click() Dim intNum As Integer intNum = Me!IDtxtbox.Value If IsNull(DLookup("RecipeID", "IDnumber criteria query", "RecipeID = " & intNum)) Then DoCmd.OpenForm "No Value frm" Else DoCmd.OpenForm "Recipe by ID number", , , "RecipeID = " & intNum End If End Sub Great, i tried it and didn't have to change much to it, as you can see. Sadly Error 94 comes in if the user click the command button and no value is entered in the input box. I thought of using an On error GoTo, but wherever it is written in the sub, it comes in at the same time as the "Recipe by ID number" Form. Although i appreciated your considerable help, i must admit i'm stranger to Me! and other syntaxes. Do you know by any chance where to find an easy to read documentation on the subject. Thank you,
Fri, 17 Oct 2003 18:49:49 GMT

Tony Oakle #4 / 4	Form displays recordset depending of a value Quote: > Private Sub Display_Click() > Dim intNum As Integer > intNum = Me!IDtxtbox.Value > If IsNull(DLookup("RecipeID", "IDnumber criteria query", "RecipeID = " & > intNum)) Then > DoCmd.OpenForm "No Value frm" > Else > DoCmd.OpenForm "Recipe by ID number", , , "RecipeID = " & intNum > End If > End Sub > Great, i tried it and didn't have to change much to it, as you can see. > Sadly Error 94 comes in if the user click the command button and no value is > entered in the input box. > I thought of using an On error GoTo, but wherever it is written in the sub, it > comes in at the same time as the "Recipe by ID number" Form. > Although i appreciated your considerable help, i must admit i'm stranger to Me! > and other syntaxes. Do you know by any chance where to find an easy to read > documentation on the subject. > Thank you, Sorry! Should have thought about that replace the first line with: intNum = Nz(Me!IDtxtbox.Value, 0) This will replace any null (nothing) value with a zero. The error was caused because an integer data type can't handle a null value. 'Me' refers to the form (or report) that your module/code is attached to. When writing error handles use the following format: Sub mySub() On Error Goto myErrorLabel 'code goes here Exit Sub 'important, stops code running on into error handler myErrorLabel: 'error handling code End Sub As far as books are concerned I can't help you there I'm afraid. It may pay you to do an internet search for an on-line tutorial though, there's a few around. Hope this helps Tony Oakley
Fri, 17 Oct 2003 19:54:57 GMT

Page 1 of 1

[ 4 post ]