PC Review


Reply
Thread Tools Rate Thread

next record please

 
 
Jean-Paul
Guest
Posts: n/a
 
      17th Jan 2008
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
 
Reply With Quote
 
 
 
 
mscertified
Guest
Posts: n/a
 
      17th Jan 2008
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
>

 
Reply With Quote
 
 
 
 
Jean-Paul
Guest
Posts: n/a
 
      17th Jan 2008
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
>>

 
Reply With Quote
 
n00b
Guest
Posts: n/a
 
      17th Jan 2008
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
>

 
Reply With Quote
 
Jean-Paul
Guest
Posts: n/a
 
      18th Jan 2008
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
>>

 
Reply With Quote
 
n00b
Guest
Posts: n/a
 
      18th Jan 2008
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 <(E-Mail Removed)> 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 -


 
Reply With Quote
 
Jean-Paul
Guest
Posts: n/a
 
      19th Jan 2008
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 <(E-Mail Removed)> 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 -

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nvidia's Ageia Purchase - Why Ageia matters - Next Generation SonyPlayStation GPU - Next Generation PC GPUs - Next Generation Console GPU -Nvidia Inside - Nvidia Everywhere sprite scaler AMD 64 Bit 0 8th Feb 2008 02:43 AM
Nvidia's Ageia Purchase - Why Ageia matters - Next Generation SonyPlayStation GPU - Next Generation PC GPUs - Next Generation Console GPU -Nvidia Inside, Nvidia Everywhere sprite scaler Video Cards 0 8th Feb 2008 02:41 AM
Next Record : Want it to stop going through next blank record reco =?Utf-8?B?Z29waQ==?= Microsoft Access 2 4th May 2006 03:41 PM
Want to copy a cell down until it comes to the next nonblank cell, and then take this next nonblank cell until it comes to the next nonblank cell, and so on... GretOgrady Microsoft Excel Discussion 2 5th Jan 2005 09:29 PM
Help! Anyone! Please Please Please Please Please Please =?Utf-8?B?UmFuZHk=?= Microsoft Access Macros 2 17th May 2004 07:16 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:03 AM.