next record please

J

Jean-Paul

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
 
M

mscertified

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

-Dorian
 
J

Jean-Paul

When I enter "de" I get at least 15 so.....
That isn't the problem
Maybe you only retrieved one record in your recordset so there is no next
record?

-Dorian

Jean-Paul said:
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
Click to expand...
Click to expand...
 
N

n00b

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 said:
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
Click to expand...
 
J

Jean-Paul

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
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 said:
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
Click to expand...
Click to expand...
 
N

n00b

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.



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


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.
Click to expand...
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.  
Click to expand...
The RecordSource of the leerkrachten Form should be set like this:
Click to expand...
Private Sub Form_Open(Cancel As Integer)
Click to expand...
Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
leerkrachten.NAAM= '" & Replace(Forms!Selectie!Naam_lk, "'", "''") & "' ;"
Click to expand...
If Me.RecordsetClone.RecordCount = 0 Then
Click to expand...
Me.RecordSource = "SELECT leerkrachten.* FROM leerkrachten WHERE
 leerkrachten.Naam Like '" & "*" & Replace(Forms!Selectie!Naam_lk, "'",
 "''") & "*" & "';"
Click to expand...
End If
Click to expand...
End Sub
Click to expand...
(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.)
Click to expand...
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.
Click to expand...
Now when you open the leerkrachten form from the Selectie form, you willbe
able to navigate with the button you created without error.
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
J

Jean-Paul

Seems to be OK now....
Thanks
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.



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


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.
:
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 -
Click to expand...
- Show quoted text -
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

recordset "over" 2 forms 3
entryfield linked to selectionlist 1
Editing record 2

Top