next record please

Discussion in 'Microsoft Access Form Coding' started by Jean-Paul, Jan 17, 2008.

  1. Jean-Paul

    Jean-Paul Guest

    I created a form with 1 entryfield and a pushbutton
    I enter a full name or part of a name in this entryfield
    When I click the pushbutton a form is opened and following code runs

    Private Sub Form_Load()
    Dim f As Form
    Dim db As Database
    Set db = CurrentDb()
    Dim tb As Recordset
    Dim sql As String
    Set f = Forms![leerkrachten]
    sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    Set tb = db.OpenRecordset(sql)
    If tb.RecordCount = 0 Then
    sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    "''") & "*" & "';"
    Set tb = db.OpenRecordset(sql)
    End If
    f!nm_lk = tb!naam
    f!str_lk = tb!straat
    f!gem_lk = tb!plaats
    f!Geb_lk = tb!geboortedatum
    f!PN_lk = tb!postnummer
    f!GSM_lk = tb!GSM
    f!mail_lk = tb!Email
    f!Synd_lk = tb!gesyndiceerd
    f!Nu_lk = tb![Nu nog actief]
    End Sub

    The correct record is shown.... so far no problem.
    On this newly opened record, I created a pushbutton "Next"
    Clicking on this button should show me the next record in the recordset
    Alas, I get a message saying the program can not go to the desired record.
    The code is:

    Private Sub Knop28_Click()
    DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    End Sub

    How to solve this problem?
    Thank you so much
     
    Jean-Paul, Jan 17, 2008
    #1
    1. Advertisements

  2. Jean-Paul

    mscertified Guest

    Maybe you only retrieved one record in your recordset so there is no next
    record?

    -Dorian

    "Jean-Paul" wrote:

    > I created a form with 1 entryfield and a pushbutton
    > I enter a full name or part of a name in this entryfield
    > When I click the pushbutton a form is opened and following code runs
    >
    > Private Sub Form_Load()
    > Dim f As Form
    > Dim db As Database
    > Set db = CurrentDb()
    > Dim tb As Recordset
    > Dim sql As String
    > Set f = Forms![leerkrachten]
    > sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    > Set tb = db.OpenRecordset(sql)
    > If tb.RecordCount = 0 Then
    > sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    > "''") & "*" & "';"
    > Set tb = db.OpenRecordset(sql)
    > End If
    > f!nm_lk = tb!naam
    > f!str_lk = tb!straat
    > f!gem_lk = tb!plaats
    > f!Geb_lk = tb!geboortedatum
    > f!PN_lk = tb!postnummer
    > f!GSM_lk = tb!GSM
    > f!mail_lk = tb!Email
    > f!Synd_lk = tb!gesyndiceerd
    > f!Nu_lk = tb![Nu nog actief]
    > End Sub
    >
    > The correct record is shown.... so far no problem.
    > On this newly opened record, I created a pushbutton "Next"
    > Clicking on this button should show me the next record in the recordset
    > Alas, I get a message saying the program can not go to the desired record.
    > The code is:
    >
    > Private Sub Knop28_Click()
    > DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    > End Sub
    >
    > How to solve this problem?
    > Thank you so much
    >
     
    mscertified, Jan 17, 2008
    #2
    1. Advertisements

  3. Jean-Paul

    Jean-Paul Guest

    When I enter "de" I get at least 15 so.....
    That isn't the problem

    mscertified wrote:
    > Maybe you only retrieved one record in your recordset so there is no next
    > record?
    >
    > -Dorian
    >
    > "Jean-Paul" wrote:
    >
    >> I created a form with 1 entryfield and a pushbutton
    >> I enter a full name or part of a name in this entryfield
    >> When I click the pushbutton a form is opened and following code runs
    >>
    >> Private Sub Form_Load()
    >> Dim f As Form
    >> Dim db As Database
    >> Set db = CurrentDb()
    >> Dim tb As Recordset
    >> Dim sql As String
    >> Set f = Forms![leerkrachten]
    >> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >> leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    >> Set tb = db.OpenRecordset(sql)
    >> If tb.RecordCount = 0 Then
    >> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >> leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    >> "''") & "*" & "';"
    >> Set tb = db.OpenRecordset(sql)
    >> End If
    >> f!nm_lk = tb!naam
    >> f!str_lk = tb!straat
    >> f!gem_lk = tb!plaats
    >> f!Geb_lk = tb!geboortedatum
    >> f!PN_lk = tb!postnummer
    >> f!GSM_lk = tb!GSM
    >> f!mail_lk = tb!Email
    >> f!Synd_lk = tb!gesyndiceerd
    >> f!Nu_lk = tb![Nu nog actief]
    >> End Sub
    >>
    >> The correct record is shown.... so far no problem.
    >> On this newly opened record, I created a pushbutton "Next"
    >> Clicking on this button should show me the next record in the recordset
    >> Alas, I get a message saying the program can not go to the desired record.
    >> The code is:
    >>
    >> Private Sub Knop28_Click()
    >> DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    >> End Sub
    >>
    >> How to solve this problem?
    >> Thank you so much
    >>
     
    Jean-Paul, Jan 17, 2008
    #3
  4. Jean-Paul

    n00b Guest

    It appears to me that your leerkrachten Form is unbound. At least, your post
    doesn't really specify wheter or not it is. I don't understand why you would
    fill in the form fields in code if it was bound, that seems a little
    senseless. If you are really trying to navigate an unbound form, you will
    definitely get the error you mention.

    Instead of trying to "fix" your original coding, let me suggest another
    approach. Bind your leerkrachten Form to a select query of the leerkrachten
    table.

    The RecordSource of the leerkrachten Form should be set like this:

    Private Sub Form_Open(Cancel As Integer)

    Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"

    If Me.RecordsetClone.RecordCount = 0 Then

    Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    "''") & "*" & "';"

    End If

    End Sub

    (Use the Form_Open event instead to insure the code always gets run not just
    when the form is loaded into memory for the first time.)

    To setup the initial bindings of the controls, just go into the leerkrachten
    Form properties and set the RecordSource to the leerkrachten table. Clear
    the RecordSource property of the form when you have the controls bound to
    fields in the leerkrachten table.

    Now when you open the leerkrachten form from the Selectie form, you will be
    able to navigate with the button you created without error.


    "Jean-Paul" wrote:

    > I created a form with 1 entryfield and a pushbutton
    > I enter a full name or part of a name in this entryfield
    > When I click the pushbutton a form is opened and following code runs
    >
    > Private Sub Form_Load()
    > Dim f As Form
    > Dim db As Database
    > Set db = CurrentDb()
    > Dim tb As Recordset
    > Dim sql As String
    > Set f = Forms![leerkrachten]
    > sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    > Set tb = db.OpenRecordset(sql)
    > If tb.RecordCount = 0 Then
    > sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    > "''") & "*" & "';"
    > Set tb = db.OpenRecordset(sql)
    > End If
    > f!nm_lk = tb!naam
    > f!str_lk = tb!straat
    > f!gem_lk = tb!plaats
    > f!Geb_lk = tb!geboortedatum
    > f!PN_lk = tb!postnummer
    > f!GSM_lk = tb!GSM
    > f!mail_lk = tb!Email
    > f!Synd_lk = tb!gesyndiceerd
    > f!Nu_lk = tb![Nu nog actief]
    > End Sub
    >
    > The correct record is shown.... so far no problem.
    > On this newly opened record, I created a pushbutton "Next"
    > Clicking on this button should show me the next record in the recordset
    > Alas, I get a message saying the program can not go to the desired record.
    > The code is:
    >
    > Private Sub Knop28_Click()
    > DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    > End Sub
    >
    > How to solve this problem?
    > Thank you so much
    >
     
    n00b, Jan 17, 2008
    #4
  5. Jean-Paul

    Jean-Paul Guest

    Hey,
    Thanks for your help...
    The reason why it isn't bound is because I need to autofill and
    calculate some fields. So I finally will add a kind of "save" button to
    correctly edit, fill and calculate fields.
    Will this change anything to your aproach?

    JP

    n00b wrote:
    > It appears to me that your leerkrachten Form is unbound. At least, your post
    > doesn't really specify wheter or not it is. I don't understand why you would
    > fill in the form fields in code if it was bound, that seems a little
    > senseless. If you are really trying to navigate an unbound form, you will
    > definitely get the error you mention.
    >
    > Instead of trying to "fix" your original coding, let me suggest another
    > approach. Bind your leerkrachten Form to a select query of the leerkrachten
    > table.
    >
    > The RecordSource of the leerkrachten Form should be set like this:
    >
    > Private Sub Form_Open(Cancel As Integer)
    >
    > Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    >
    > If Me.RecordsetClone.RecordCount = 0 Then
    >
    > Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    > "''") & "*" & "';"
    >
    > End If
    >
    > End Sub
    >
    > (Use the Form_Open event instead to insure the code always gets run not just
    > when the form is loaded into memory for the first time.)
    >
    > To setup the initial bindings of the controls, just go into the leerkrachten
    > Form properties and set the RecordSource to the leerkrachten table. Clear
    > the RecordSource property of the form when you have the controls bound to
    > fields in the leerkrachten table.
    >
    > Now when you open the leerkrachten form from the Selectie form, you will be
    > able to navigate with the button you created without error.
    >
    >
    > "Jean-Paul" wrote:
    >
    >> I created a form with 1 entryfield and a pushbutton
    >> I enter a full name or part of a name in this entryfield
    >> When I click the pushbutton a form is opened and following code runs
    >>
    >> Private Sub Form_Load()
    >> Dim f As Form
    >> Dim db As Database
    >> Set db = CurrentDb()
    >> Dim tb As Recordset
    >> Dim sql As String
    >> Set f = Forms![leerkrachten]
    >> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >> leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    >> Set tb = db.OpenRecordset(sql)
    >> If tb.RecordCount = 0 Then
    >> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >> leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    >> "''") & "*" & "';"
    >> Set tb = db.OpenRecordset(sql)
    >> End If
    >> f!nm_lk = tb!naam
    >> f!str_lk = tb!straat
    >> f!gem_lk = tb!plaats
    >> f!Geb_lk = tb!geboortedatum
    >> f!PN_lk = tb!postnummer
    >> f!GSM_lk = tb!GSM
    >> f!mail_lk = tb!Email
    >> f!Synd_lk = tb!gesyndiceerd
    >> f!Nu_lk = tb![Nu nog actief]
    >> End Sub
    >>
    >> The correct record is shown.... so far no problem.
    >> On this newly opened record, I created a pushbutton "Next"
    >> Clicking on this button should show me the next record in the recordset
    >> Alas, I get a message saying the program can not go to the desired record.
    >> The code is:
    >>
    >> Private Sub Knop28_Click()
    >> DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    >> End Sub
    >>
    >> How to solve this problem?
    >> Thank you so much
    >>
     
    Jean-Paul, Jan 17, 2008
    #5
  6. Jean-Paul

    n00b Guest

    You could maybe add fields in the leerkrachten table that can hold the
    calculated values so you can use binding and retain the easy
    navigation. Depending on when the user should see the calculated
    values will determine where you have to put the calculations. If they
    can be calculated when the user saves, use the before update event.
    If they should be calculated after another field is updated, use the
    after update events in either the controls or the Form. If the user
    needs to see the calculations right when they open the form, put the
    calcs in the form open event.

    I am assuming you can add fields to the leerkrachten table and write
    data to it.



    On Jan 17, 5:24 pm, Jean-Paul <> wrote:
    > Hey,
    > Thanks for your help...
    > The reason why it isn't bound is because I need to autofill and
    > calculate some fields. So I finally will add a kind of "save" button to
    > correctly edit, fill and calculate fields.
    > Will this change anything to your aproach?
    >
    > JP
    >
    >
    >
    > n00b wrote:
    > > It appears to me that your leerkrachten Form is unbound.  At least, your post
    > > doesn't really specify wheter or not it is.  I don't understand why you would
    > > fill in the form fields in code if it was bound, that seems a little
    > > senseless.  If you are really trying to navigate an unbound form, you will
    > > definitely get the error you mention.

    >
    > > Instead of trying to "fix" your original coding, let me suggest another
    > > approach.  Bind your leerkrachten Form to a select query of the leerkrachten
    > > table.  

    >
    > > The RecordSource of the leerkrachten Form should be set like this:

    >
    > > Private Sub Form_Open(Cancel As Integer)

    >
    > > Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > > leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"

    >
    > > If Me.RecordsetClone.RecordCount = 0 Then

    >
    > > Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > >  leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    > >  "''") & "*" & "';"

    >
    > > End If

    >
    > > End Sub

    >
    > > (Use the Form_Open event instead to insure the code always gets run not just
    > > when the form is loaded into memory for the first time.)

    >
    > > To setup the initial bindings of the controls, just go into the leerkrachten
    > > Form properties and set the RecordSource to the leerkrachten table.  Clear
    > > the RecordSource property of the form when you have the controls bound to
    > > fields in the leerkrachten table.

    >
    > > Now when you open the leerkrachten form from the Selectie form, you willbe
    > > able to navigate with the button you created without error.

    >
    > > "Jean-Paul" wrote:

    >
    > >> I created a form with 1 entryfield and a pushbutton
    > >> I enter a full name or part of a name in this entryfield
    > >> When I click the pushbutton a form is opened and following code runs

    >
    > >> Private Sub Form_Load()
    > >>      Dim f As Form
    > >>      Dim db As Database
    > >>      Set db = CurrentDb()
    > >>      Dim tb As Recordset
    > >>      Dim sql As String
    > >>          Set f = Forms![leerkrachten]
    > >>          sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > >> leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    > >>          Set tb = db.OpenRecordset(sql)
    > >>          If tb.RecordCount = 0 Then
    > >>              sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    > >> leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    > >> "''") & "*" & "';"
    > >>              Set tb = db.OpenRecordset(sql)
    > >>          End If
    > >>          f!nm_lk = tb!naam
    > >>          f!str_lk = tb!straat
    > >>          f!gem_lk = tb!plaats
    > >>          f!Geb_lk = tb!geboortedatum
    > >>          f!PN_lk = tb!postnummer
    > >>          f!GSM_lk = tb!GSM
    > >>          f!mail_lk = tb!Email
    > >>          f!Synd_lk = tb!gesyndiceerd
    > >>          f!Nu_lk = tb![Nu nog actief]
    > >> End Sub

    >
    > >> The correct record is shown.... so far no problem.
    > >> On this newly opened record, I created a pushbutton "Next"
    > >> Clicking on this button should show me the next record in the recordset
    > >> Alas, I get a message saying the program can not go to the desired record.
    > >> The code is:

    >
    > >> Private Sub Knop28_Click()
    > >>      DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    > >> End Sub

    >
    > >> How to solve this problem?
    > >> Thank you so much- Hide quoted text -

    >
    > - Show quoted text -
     
    n00b, Jan 18, 2008
    #6
  7. Jean-Paul

    Jean-Paul Guest

    Seems to be OK now....
    Thanks

    n00b wrote:
    > You could maybe add fields in the leerkrachten table that can hold the
    > calculated values so you can use binding and retain the easy
    > navigation. Depending on when the user should see the calculated
    > values will determine where you have to put the calculations. If they
    > can be calculated when the user saves, use the before update event.
    > If they should be calculated after another field is updated, use the
    > after update events in either the controls or the Form. If the user
    > needs to see the calculations right when they open the form, put the
    > calcs in the form open event.
    >
    > I am assuming you can add fields to the leerkrachten table and write
    > data to it.
    >
    >
    >
    > On Jan 17, 5:24 pm, Jean-Paul <> wrote:
    >> Hey,
    >> Thanks for your help...
    >> The reason why it isn't bound is because I need to autofill and
    >> calculate some fields. So I finally will add a kind of "save" button to
    >> correctly edit, fill and calculate fields.
    >> Will this change anything to your aproach?
    >>
    >> JP
    >>
    >>
    >>
    >> n00b wrote:
    >>> It appears to me that your leerkrachten Form is unbound. At least, your post
    >>> doesn't really specify wheter or not it is. I don't understand why you would
    >>> fill in the form fields in code if it was bound, that seems a little
    >>> senseless. If you are really trying to navigate an unbound form, you will
    >>> definitely get the error you mention.
    >>> Instead of trying to "fix" your original coding, let me suggest another
    >>> approach. Bind your leerkrachten Form to a select query of the leerkrachten
    >>> table.
    >>> The RecordSource of the leerkrachten Form should be set like this:
    >>> Private Sub Form_Open(Cancel As Integer)
    >>> Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >>> leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    >>> If Me.RecordsetClone.RecordCount = 0 Then
    >>> Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >>> leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    >>> "''") & "*" & "';"
    >>> End If
    >>> End Sub
    >>> (Use the Form_Open event instead to insure the code always gets run not just
    >>> when the form is loaded into memory for the first time.)
    >>> To setup the initial bindings of the controls, just go into the leerkrachten
    >>> Form properties and set the RecordSource to the leerkrachten table. Clear
    >>> the RecordSource property of the form when you have the controls bound to
    >>> fields in the leerkrachten table.
    >>> Now when you open the leerkrachten form from the Selectie form, you will be
    >>> able to navigate with the button you created without error.
    >>> "Jean-Paul" wrote:
    >>>> I created a form with 1 entryfield and a pushbutton
    >>>> I enter a full name or part of a name in this entryfield
    >>>> When I click the pushbutton a form is opened and following code runs
    >>>> Private Sub Form_Load()
    >>>> Dim f As Form
    >>>> Dim db As Database
    >>>> Set db = CurrentDb()
    >>>> Dim tb As Recordset
    >>>> Dim sql As String
    >>>> Set f = Forms![leerkrachten]
    >>>> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >>>> leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
    >>>> Set tb = db.OpenRecordset(sql)
    >>>> If tb.RecordCount = 0 Then
    >>>> sql = "SELECT leerkrachten.* FROM leerkrachten WHERE
    >>>> leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
    >>>> "''") & "*" & "';"
    >>>> Set tb = db.OpenRecordset(sql)
    >>>> End If
    >>>> f!nm_lk = tb!naam
    >>>> f!str_lk = tb!straat
    >>>> f!gem_lk = tb!plaats
    >>>> f!Geb_lk = tb!geboortedatum
    >>>> f!PN_lk = tb!postnummer
    >>>> f!GSM_lk = tb!GSM
    >>>> f!mail_lk = tb!Email
    >>>> f!Synd_lk = tb!gesyndiceerd
    >>>> f!Nu_lk = tb![Nu nog actief]
    >>>> End Sub
    >>>> The correct record is shown.... so far no problem.
    >>>> On this newly opened record, I created a pushbutton "Next"
    >>>> Clicking on this button should show me the next record in the recordset
    >>>> Alas, I get a message saying the program can not go to the desired record.
    >>>> The code is:
    >>>> Private Sub Knop28_Click()
    >>>> DoCmd.GoToRecord acDataForm, "leerkrachten", acNext
    >>>> End Sub
    >>>> How to solve this problem?
    >>>> Thank you so much- Hide quoted text -

    >> - Show quoted text -

    >
     
    Jean-Paul, Jan 19, 2008
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Thomas Simsion

    Next record update from current record

    Thomas Simsion, Nov 10, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    167
    Thomas Simsion
    Nov 11, 2003
  2. Thomas Simsion
    Replies:
    3
    Views:
    201
    Kelvin
    Nov 17, 2003
  3. Alp Bekisoglu

    Prevent "next record" button opening a new record

    Alp Bekisoglu, Mar 19, 2004, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    422
    Alp Bekisoglu
    Mar 19, 2004
  4. Guest
    Replies:
    1
    Views:
    175
    Guest
    Nov 5, 2004
  5. Orv

    Display The Next Record Number on 'New Record'?

    Orv, Nov 25, 2004, in forum: Microsoft Access Form Coding
    Replies:
    6
    Views:
    261
    Guest
    Dec 2, 2004
Loading...

Share This Page