Open form with specific record, but other records accessible
in [Forms]
|
Prev: How do I save data on a form to my database?
Next: Missing reference when updating from Access 2003 to Access 2007
From: Cpthooker on 16 Mar 2010 09:15 I have been pulling my hair out trying to figure out how to pull this off, what I have is a list of the records in the database and a way to open the detailed form showing more data. But when I used the wizard to make the command button it prevents me from changing the record in the detailed form as it is locked onto the choosen record which is no good as I want the user to be able to pick another record. What is the best way to pull this off?
From: Jack Leach dymondjack at hot mail dot on 16 Mar 2010 10:24 Open the form without supplying a where clause, and pass the ID of the record you want as the OpenArg... in the Open event of the detail form, use the recordset bookmark to make the said ID current. For this example we'll pretend you have a numeric ID: Private Sub Form_Open(Cancel As Integer) Dim lID As Long lID = Clng(Me.OpenArgs) With Me.RecordsetClone .FindFirst "[IDField] = " & lID If .NoMatch Then MsgBox "Record Not Found!" Else Me.Bookmark = .Bookmark End If End With End Sub hth -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t work." -Thomas Edison (1847-1931) "Cpthooker" wrote: > I have been pulling my hair out trying to figure out how to pull this > off, what I have is a list of the records in the database and a way to > open the detailed form showing more data. But when I used the wizard > to make the command button it prevents me from changing the record in > the detailed form as it is locked onto the choosen record which is no > good as I want the user to be able to pick another record. What is the > best way to pull this off? > . >
From: Cpthooker on 16 Mar 2010 11:17 On Mar 16, 2:24 pm, Jack Leach <dymondjack at hot mail dot com> wrote: > Open the form without supplying a where clause, and pass the ID of the record > you want as the OpenArg... in the Open event of the detail form, use the > recordset bookmark to make the said ID current. For this example we'll > pretend you have a numeric ID: > > Private Sub Form_Open(Cancel As Integer) > Dim lID As Long > lID = Clng(Me.OpenArgs) > > With Me.RecordsetClone > .FindFirst "[IDField] = " & lID > If .NoMatch Then > MsgBox "Record Not Found!" > Else > Me.Bookmark = .Bookmark > End If > End With > > End Sub > > hth > -- > Jack Leachwww.tristatemachine.com > > "I haven''t failed, I''ve found ten thousand ways that don''t work." > -Thomas Edison (1847-1931) > > > > "Cpthooker" wrote: > > I have been pulling my hair out trying to figure out how to pull this > > off, what I have is a list of the records in the database and a way to > > open the detailed form showing more data. But when I used the wizard > > to make the command button it prevents me from changing the record in > > the detailed form as it is locked onto the choosen record which is no > > good as I want the user to be able to pick another record. What is the > > best way to pull this off? > > .- Hide quoted text - > > - Show quoted text - Sorry but its not working I have changed what I thought needed to be changed to match my db, but when I click on the command button it brings up an error saying 'Invalid use of null' This is what I am using for the command button to get onto the form Private Sub Command129_Click() On Error GoTo Err_Command129_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "LeafletDetail" stLinkCriteria = "[LeafletID]=" & Me![LeafletID] DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Command129_Click: Exit Sub Err_Command129_Click: MsgBox Err.Description Resume Exit_Command129_Click End Sub and this is the code on the opening form Private Sub Form_Open(Cancel As Integer) Dim lID As Long LeafletID = CLng(Me.LeafletID) With Me.RecordsetClone .FindFirst "[LeafletID] = " & LeafletID If .NoMatch Then MsgBox "Record Not Found!" Else Me.Bookmark = .Bookmark End If End With End Sub Maybe you can see something I missed
From: Jack Leach dymondjack at hot mail dot on 16 Mar 2010 12:37 Let's take a closer look at the line you're using to open the form... DoCmd.OpenForm stDocName, , , stLinkCriteria if you type DoCmd.OpenForm into the VBA editor, followed by a space, you will see a list of arguments brought up by intellisense... these are various options, or parameters (or however you want to think of them, but they are actually called arguments), that you can pass to dictate some base properties of the form that is being opened... DoCmd.OpenForm FormName, View, Filter, Where, Datamode, Windowmode, OpenArgs FormName: self explanitory (should be!) View: see dropdown list of options Filter: Filters the form records to whatever criteria you pass Where: Restrics records by whatever criteria you pass Datamode: see dropdown, probably nothing to worry about for now Windowmode: see Datamode description OpenArgs: any misc values you want to pass to the form Filter vs Where Clause: Say you're opening a form with 10k records... a filter will load all 10k records even if the filter will only show 3 of them. A Where clause, on the other hand, will only load the 3 records rather than all 10k. Hence, using a Where clause is much more efficient, and filter's should only be used at a specific need... The boilerplate cmd button wizard enters the Form Name into the variable stDocName, and a where clause into a variable stLinkCriteria. So with your where clause set to open the form with only a single record, that of which has the same ID as what you pass in your criteria. Therefore, you are noticing that none of the other records are accessible. You could, if you wanted, move this stLinkCriteria to the Filter argument, and when you want to show records in the popup form, use the following line to turn the filter off: Me.FilterOn = False However, this requires a bit more code than necessary for your purposes. To do what you want, the general method is to pass the ID as an OpenArg (misc data for the form), and read the OpenArg when the form opens, and navigate as required. The reason you are getting the invalid use of null error when opening the form is because no information is being passed as an OpenArg (and therefore Me.OpenArgs in the form is Null). Basically, you want to leave the Where argument blank (so the records aren't restricted), and move that ID to the OpenArgs argument. Also, you can get rid of those two variables that are needlessly supplied by the wizard... the finished product will look like this: Private Sub Command129_Click() On Error GoTo Err_Command129_Click DoCmd.OpenForm "LeafletDetail", , , , , CStr(Me![LeafletID]) Exit_Command129_Click: Exit Sub Err_Command129_Click: etc etc End Sub Note that OpenArgs takes a String (text) data, rather than a number. We use CStr() to convert the number to a string, and on the other side, will use CLng() to convert that string back to a number. next... > > Private Sub Form_Open(Cancel As Integer) > Dim lID As Long > LeafletID = CLng(Me.LeafletID) > > > With Me.RecordsetClone > .FindFirst "[LeafletID] = " & LeafletID > If .NoMatch Then > MsgBox "Record Not Found!" > Else > Me.Bookmark = .Bookmark > End If > End With > > End Sub > Dim lID As Long > LeafletID = CLng(Me.LeafletID) instead of using LeafletID = CLng(Me.LeafletID), you want: lID = CLng(Me.OpenArgs) You are not yet working with the LeafletID of this form (Me.LeafletID), and using a variable name that is the same as a field or control name is never a good idea. > .FindFirst "[LeafletID] = " & LeafletID again, LeafletID is not a good variable name, IMO. Change this line to: ..FindFirst "LeafletID = " & lID (side note: square brackets around field/table/form names are only required if you use spaces in your names, which is supposed to be avoided) Those few minor corrections should work... hopefully the drawn out explanation will give you WHY it works. One more additional note... Checking for Nulls in OpenArgs (or anything else): If Len(Nz(Me.OpenArgs, "")) = 0 Then Cancel = True 'close form if no openargs supplied End If Check the VBA help on Len() and Nz() functions and you should be able to figure out what's going on there. The above example will prevent the error from raising and subsequently close the form if no OpenArg is supplied. hth -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t work." -Thomas Edison (1847-1931) "Cpthooker" wrote: > On Mar 16, 2:24 pm, Jack Leach <dymondjack at hot mail dot com> wrote: > > Open the form without supplying a where clause, and pass the ID of the record > > you want as the OpenArg... in the Open event of the detail form, use the > > recordset bookmark to make the said ID current. For this example we'll > > pretend you have a numeric ID: > > > > Private Sub Form_Open(Cancel As Integer) > > Dim lID As Long > > lID = Clng(Me.OpenArgs) > > > > With Me.RecordsetClone > > .FindFirst "[IDField] = " & lID > > If .NoMatch Then > > MsgBox "Record Not Found!" > > Else > > Me.Bookmark = .Bookmark > > End If > > End With > > > > End Sub > > > > hth > > -- > > Jack Leachwww.tristatemachine.com > > > > "I haven''t failed, I''ve found ten thousand ways that don''t work." > > -Thomas Edison (1847-1931) > > > > > > > > "Cpthooker" wrote: > > > I have been pulling my hair out trying to figure out how to pull this > > > off, what I have is a list of the records in the database and a way to > > > open the detailed form showing more data. But when I used the wizard > > > to make the command button it prevents me from changing the record in > > > the detailed form as it is locked onto the choosen record which is no > > > good as I want the user to be able to pick another record. What is the > > > best way to pull this off? > > > .- Hide quoted text - > > > > - Show quoted text - > > Sorry but its not working I have changed what I thought needed to be > changed to match my db, but when I click on the command button it > brings up an error saying 'Invalid use of null' > > This is what I am using for the command button to get onto the form > > Private Sub Command129_Click() > On Error GoTo Err_Command129_Click > > Dim stDocName As String > Dim stLinkCriteria As String > > stDocName = "LeafletDetail" > > stLinkCriteria = "[LeafletID]=" & Me![LeafletID] > DoCmd.OpenForm stDocName, , , stLinkCriteria > > Exit_Command129_Click: > Exit Sub > > Err_Command129_Click: > MsgBox Err.Description > Resume Exit_Command129_Click > > End Sub > > > and this is the code on the opening form > > Private Sub Form_Open(Cancel As Integer) > Dim lID As Long > LeafletID = CLng(Me.LeafletID) > > > With Me.RecordsetClone > .FindFirst "[LeafletID] = " & LeafletID > If .NoMatch Then > MsgBox "Record Not Found!" > Else > Me.Bookmark = .Bookmark > End If > End With > > End Sub > > Maybe you can see something I missed > . >
From: Cpthooker on 17 Mar 2010 05:29
On Mar 16, 4:37 pm, Jack Leach <dymondjack at hot mail dot com> wrote: > Let's take a closer look at the line you're using to open the form... > > DoCmd.OpenForm stDocName, , , stLinkCriteria > > if you type DoCmd.OpenForm into the VBA editor, followed by a space, you > will see a list of arguments brought up by intellisense... these are various > options, or parameters (or however you want to think of them, but they are > actually called arguments), that you can pass to dictate some base properties > of the form that is being opened... > > DoCmd.OpenForm FormName, View, Filter, Where, Datamode, Windowmode, OpenArgs > > FormName: self explanitory (should be!) > View: see dropdown list of options > Filter: Filters the form records to whatever criteria you pass > Where: Restrics records by whatever criteria you pass > Datamode: see dropdown, probably nothing to worry about for now > Windowmode: see Datamode description > OpenArgs: any misc values you want to pass to the form > > Filter vs Where Clause: Say you're opening a form with 10k records... a > filter will load all 10k records even if the filter will only show 3 of them. > A Where clause, on the other hand, will only load the 3 records rather than > all 10k. Hence, using a Where clause is much more efficient, and filter's > should only be used at a specific need... > > The boilerplate cmd button wizard enters the Form Name into the variable > stDocName, and a where clause into a variable stLinkCriteria. So with your > where clause set to open the form with only a single record, that of which > has the same ID as what you pass in your criteria. Therefore, you are > noticing that none of the other records are accessible. > > You could, if you wanted, move this stLinkCriteria to the Filter argument, > and when you want to show records in the popup form, use the following line > to turn the filter off: > > Me.FilterOn = False > > However, this requires a bit more code than necessary for your purposes. To > do what you want, the general method is to pass the ID as an OpenArg (misc > data for the form), and read the OpenArg when the form opens, and navigate as > required. > > The reason you are getting the invalid use of null error when opening the > form is because no information is being passed as an OpenArg (and therefore > Me.OpenArgs in the form is Null). > > Basically, you want to leave the Where argument blank (so the records aren't > restricted), and move that ID to the OpenArgs argument. Also, you can get > rid of those two variables that are needlessly supplied by the wizard... the > finished product will look like this: > > Private Sub Command129_Click() > On Error GoTo Err_Command129_Click > > DoCmd.OpenForm "LeafletDetail", , , , , CStr(Me![LeafletID]) > > Exit_Command129_Click: > Exit Sub > Err_Command129_Click: > etc etc > End Sub > > Note that OpenArgs takes a String (text) data, rather than a number. We use > CStr() to convert the number to a string, and on the other side, will use > CLng() to convert that string back to a number. > > next... > > > > > > > > > Private Sub Form_Open(Cancel As Integer) > > Dim lID As Long > > LeafletID = CLng(Me.LeafletID) > > > With Me.RecordsetClone > > .FindFirst "[LeafletID] = " & LeafletID > > If .NoMatch Then > > MsgBox "Record Not Found!" > > Else > > Me.Bookmark = .Bookmark > > End If > > End With > > > End Sub > > Dim lID As Long > > LeafletID = CLng(Me.LeafletID) > > instead of using LeafletID = CLng(Me.LeafletID), you want: > > lID = CLng(Me.OpenArgs) > > You are not yet working with the LeafletID of this form (Me.LeafletID), and > using a variable name that is the same as a field or control name is never a > good idea. > > > .FindFirst "[LeafletID] = " & LeafletID > > again, LeafletID is not a good variable name, IMO. Change this line to: > > .FindFirst "LeafletID = " & lID > > (side note: square brackets around field/table/form names are only required > if you use spaces in your names, which is supposed to be avoided) > > Those few minor corrections should work... hopefully the drawn out > explanation will give you WHY it works. > > One more additional note... > > Checking for Nulls in OpenArgs (or anything else): > > If Len(Nz(Me.OpenArgs, "")) = 0 Then > Cancel = True 'close form if no openargs supplied > End If > > Check the VBA help on Len() and Nz() functions and you should be able to > figure out what's going on there. The above example will prevent the error > from raising and subsequently close the form if no OpenArg is supplied. > > hth > > -- > Jack Leachwww.tristatemachine.com > > "I haven''t failed, I''ve found ten thousand ways that don''t work." > -Thomas Edison (1847-1931) > > > > "Cpthooker" wrote: > > On Mar 16, 2:24 pm, Jack Leach <dymondjack at hot mail dot com> wrote: > > > Open the form without supplying a where clause, and pass the ID of the record > > > you want as the OpenArg... in the Open event of the detail form, use the > > > recordset bookmark to make the said ID current. For this example we'll > > > pretend you have a numeric ID: > > > > Private Sub Form_Open(Cancel As Integer) > > > Dim lID As Long > > > lID = Clng(Me.OpenArgs) > > > > With Me.RecordsetClone > > > .FindFirst "[IDField] = " & lID > > > If .NoMatch Then > > > MsgBox "Record Not Found!" > > > Else > > > Me.Bookmark = .Bookmark > > > End If > > > End With > > > > End Sub > > > > hth > > > -- > > > Jack Leachwww.tristatemachine.com > > > > "I haven''t failed, I''ve found ten thousand ways that don''t work." > > > -Thomas Edison (1847-1931) > > > > "Cpthooker" wrote: > > > > I have been pulling my hair out trying to figure out how to pull this > > > > off, what I have is a list of the records in the database and a way to > > > > open the detailed form showing more data. But when I used the wizard > > > > to make the command button it prevents me from changing the record in > > > > the detailed form as it is locked onto the choosen record which is no > > > > good as I want the user to be able to pick another record. What is the > > > > best way to pull this off? > > > > .- Hide quoted text - > > > > - Show quoted text - > > > Sorry but its not working I have changed what I thought needed to be > > changed to match my db, but when I click on the command button it > > brings up an error saying 'Invalid use of null' > > > This is what I am using for the command button to get onto the form > > > Private Sub Command129_Click() > > On Error GoTo Err_Command129_Click > > > Dim stDocName As String > > Dim stLinkCriteria As String > > > stDocName = "LeafletDetail" > > > stLinkCriteria = "[LeafletID]=" & Me![LeafletID] > > DoCmd.OpenForm stDocName, , , stLinkCriteria > > > Exit_Command129_Click: > > Exit Sub > > > Err_Command129_Click: > > MsgBox Err.Description > > Resume Exit_Command129_Click > > > End Sub > > > and this is the code on the opening form > > > Private Sub Form_Open(Cancel As Integer) > > Dim lID As Long > > LeafletID = CLng(Me.LeafletID) > > > With Me.RecordsetClone > > .FindFirst "[LeafletID] = " & LeafletID > > If .NoMatch Then > > MsgBox "Record Not Found!" > > Else > > Me.Bookmark = .Bookmark > > End If > > End With > > > End Sub > > > Maybe you can see something I missed > > .- Hide quoted text - > > - Show quoted text -- Hide quoted text - > > - Show quoted text - Its still saying there is a null value, would you be able to look at the db as I have looked to see what is wrong but cannot see it, I am useless on VBA |