Thank you very much for your reply...
Explaining what my problem is and even mentioning the errormessage can
be troublesome since I have to translate it all from Dutch
Here I go.
The main idea is:
I have this form based upon a querry I added in the recordsource property.
So far... no problem
I created 2 pushbuttons on this form to navigate to the next or the
previuos record.
The idea is:
When the program sees that 2 fields are empty, it has to fill it before
gonig to the next or previous record.
"datum1" is such a field
Go to the next record, datum 1 of the current one is empty,
automatically enter date in the empty datum1 field.
To do so I wrote following code.... (I'm sure there must be a far more
elegant way)
Private Sub Knop154_Click()
Dim db As DAO.Database
Dim TB As Recordset
Dim sql As String, llnaam As String
Set db = CurrentDb()
sql = "SELECT Patienten.* FROM Patienten WHEre Patienten.ID= " &
[Forms]![leerlingenfiche]![id] & "; "
Set TB = db.OpenRecordset(sql)
If IsNull(TB!Datum1) = False Then
TB.Edit
TB!Datum1 = Date
TB.Update
End If
If IsNull(TB!jaar) = True Then
TB.Edit
TB!foto = "C:\DBI\Foto\" + (TB!Klas) + (TB!Afdeling) + "\" +
(TB!Naam) + ".bmp"
TB.Update
Else
TB.Edit
TB!foto = "C:\DBI\Foto\" + (TB!Klas) + (TB!jaar) +
(TB!Afdeling) + "\" + (TB!Naam) + ".bmp"
TB.Update
End If
sql = "SELECT Patienten.Naam, Patienten.* FROM Patienten ORDER BY
Patienten.Naam;"
Set TB = db.OpenRecordset(sql)
TB.MoveNext
'DoCmd.GoToRecord , , acNext
End Sub
What I do is... create a recordset based upon "ID", change the data, en
go back to the recordsource throughout a new SQL statement
When I run this code, of course I stick with the first record every time
I click the "next" button since a new recordset is opened again and again.
I think there must be a way to add data to the current record without
having to create the recordset.
With the "DoCmd.GoToRecord , , acNext" I got a messagebox saying
something about copying data to a clipboard, and then something like:
Can not go to the given record.
Hope I explained it better now...
Thanks again
JP
You don't tell us what your error is, and that's a big no-no in a user forum.
It's anyone's guess as to what the problem is.
Having said that, I have a few questions for you, so you can clarify
everything at once.
First of all, is the ID field numeric or string? Second, how many records
usually have the same ID number in the Patienten table? Third, I'm not sure,
but I think that before you can .edit a record, you have to access that
record. Where's the TB.MoveFirst statement? Fourth, what is the form's
RecordSource? The DoCmd.GoToRecord , , acNext statement can only address the
form's recordsource, not a recordset you have open with SQL. For that, you
need to use TB.MoveNext, and you'll want to use it in a loop to make sure you
access all the records. If the SQL statement is the RecordSource, you need to
initialize the recordset in the form's Open event and set the form's
RecordSource property to the resulting recordset.
If you resolve these issues, I think you're question will go away.
HTH
Jean-Paul De Winter said:
Hi,
I have this form, based upon a querry.
I created 2 pushbuttons to navigate to the next or the previous record.
Now, when I click the next button I want to check if a field is empty.
If it's empty I want to add dome date.
The code I wrote isn't the correct one:
Private Sub Knop154_Click()
Dim db As DAO.Database
Dim TB As Recordset
Dim sql As String, llnaam As String
Set db = CurrentDb()
sql = "SELECT Patienten.* FROM Patienten WHEre Patienten.ID= " &
[Forms]![leerlingenfiche]![id] & "; "
Set TB = db.OpenRecordset(sql)
If IsNull(TB!Datum1) = False Then
TB.Edit
TB!Datum1 = Date
TB.Update
End If
DoCmd.GoToRecord , , acNext
End Sub
Sinde I change the recordset, I get an error in the "acNext" line.
Anybody to help me solve my (and I think it's a basic) problem?
Thanks
JP
