Code doesn't work: db.OpenRecordset

Schlingel

Registered User.
Local time
Today, 06:20
Joined
Oct 26, 2006
Messages
32
Hi, Could u please help me and tell me why the following code doesn't work:

Private Sub bflEntschuldigung_Click()
On Error GoTo Err_bflEntschuldigung_Click
Dim i As Integer
Set db = CurrentDb
'Set rst = db.OpenRecordset("Entschuldigung")
Set rst = Me.Recordset

'rst.MoveLast
'rst!Datum = Forms!Entschuldigung!Date
For i = 1 To 8
rst.Open "Entschuldigung"
rst!Datum = Forms!Entschuldigung!Datum
rst!Stunde = i
rst!Tag = Format(rst!Datum, "dddd")
rst!Modul = "Entschuldigt"
rst![Inhalt, Komentar] = "Entschuldigt"
rst!KW = Format(rst!Datum, "WW")
rst.Update
Next

Exit_bflEntschuldigung_Click:
Exit Sub

Err_bflEntschuldigung_Click:
MsgBox Err.Description
Resume Exit_bflEntschuldigung_Click

End Sub

Thanks in advance
 
I have a Table "Entschuldigung" (Datum, Tag, Stunde, Modul, Inhalt, TN, KW).
Every day consists of 8 Records of Stunde. It looks like it:
Datum Day Stunde Modul Inhalt TN KW
24.10.2006 Tuesday 1 Economics MWF ... 42
24.10.2006 Tuesday ... .... ... ... ...
24.10.2006 Tuesday 8 Literature etc Mark Twain ... 42
Now i created a form. The user has to input a date in the text field and the code must add 8 Records to the table (best if between the dates, if u know what i mean) for the chosen day on ONE CLICK. Modul, Inhalt will be constants and TN must be a function, which shows which date is friday on the week of the chosen day and KW is a number of the Week
Again 1000 thanks in advance
 
try this

Private Sub bflEntschuldigung_Click()
On Error GoTo Err_bflEntschuldigung_Click
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Entschuldigung") - this is the correct usage, but it is referring to your table called "entschuldigung", not a form)
'Set rst = Me.Recordset

'these not needed but you had commented them out
'rst.MoveLast
'rst!Datum = Forms!Entschuldigung!Date

For i = 1 To 8
rst.addnew - adds a new record anyway - you don't need to be at the end

rst!Datum = Forms!Entschuldigung!Datum
rst!Stunde = i

rst!Modul = ??? don't know where you get this from - this is your subject
rst!Inhalt = ??? don't know where you get this from - not sure what it is
'rst![Inhalt, Komentar] = "Entschuldigt" - dont understand this - you have to refer to the rst fields one at a time.
'rst!komentar is ok if you have a field called komentar

rst!Tag = Format(rst!Datum, "dddd")
rst!TN = your function to return the friday
rst!KW = Format(rst!Datum, "WW")

'in fact although its not a problem, but rst!tn, rst!kw, rst!tag are all superfluous as you can calculate these rom the date whenever you need them.

rst.Update
Next

Hope this helps
 
May i ask u another question?
Is there any possibility to check whether the records with this date are already in the table?
As always, thanks in advance
 
try

dim items as long
items = dcount("*", "tablename", "[datum] = #" & thedate & "#")
if items>0 then
deal with it-----
end if

you have to wrap the date in # characters. you might get incorrect results because of US/European date formats so try format(thedate,"long date") if youget wrong results.
 
Hi again,
Sorry that i ask u so stupid questions.
I really appreciate ur advice. So i get the code as following:

*******************************
Private Sub bflEntschuldigung_Click()
On Error GoTo Err_bflEntschuldigung_Click
Dim i As Integer
Dim Frage
Dim items As Long
Set db = CurrentDb
Set rst = db.OpenRecordset("Entschuldigung")
rst!Datum = Forms!Entschuldigung!Datum
items = DCount("*", "Entschuldigung", "[datum] = Format(rst!Datum, Short Date)")
If items > 0 Then
MsgBox "There is a record with the same date in the table!"
Else:
For i = 1 To 8
rst.AddNew
rst!Datum = Forms!Entschuldigung!Datum
rst!Stunde = i
rst!Modul = "Entschuldigt"
rst![Inhalt, Kommentar] = "Entschuldigt"
rst!Tag = Format(rst!Datum, "dddd")
rst!KW = Format(rst!Datum, "WW")
rst.Update
Next
Frage = MsgBox("Sie haben erfolgreich Entschuldigung für " & rst!Datum & " geschrieben")
End If

Exit_bflEntschuldigung_Click:
Exit Sub

Err_bflEntschuldigung_Click:
MsgBox Err.Description
Resume Exit_bflEntschuldigung_Click

End Sub
*************************************

I get the error message "Update or CancelUpdate without AddNew or Edit"
What can i do now?
Thanks in advance
 
items = DCount("*", "Entschuldigung", "[datum] = Format(rst!Datum, Short Date)")

s/b

items = DCount("*", "Entschuldigung", "[datum] = #" & Format(rst!Datum, Short Date) & "#")

you have to wrap the date to produce a valid string - this line is causing a spurious error trap i think, because its not correct.
 
Hi,
Tomorrow i will try it. Again, thanks, gemma-the-husky, u helped me very very much.
 
Hi again!
Unfortunately, the idea with items=... was wrong. Access gives me the same Error (Update or CancelUpdate without AddNew or Edit)
Any other ideas?
Thanks in advance
 
an error is right at the top after
Set rst = db.OpenRecordset("Entschuldigung")

you cannot use rst without finding a record first, but you don't need to. All you want to do is count how many records are currently in the TABLE for the given date

so you don't need this line, WHICH IS PRODUCING THE ERROR I THINK
'rst!Datum = Forms!Entschuldigung!Datum

'then you don't need to refer to the rst in the count expression. You want the date from your form. You only need the rst later, to insert new records.

items = DCount("*", "Entschuldigung", "[datum] = #" & Format(Forms!Entschuldigung!Datum, "Long Date") & "#)"



As you are using the form "Entschuldigung" then you can use the field directly.

items = DCount("*", "Entschuldigung", "[datum] = #" & Format(Datum, "Long Date") & "#)"

the right side dhould end up looking like
[datum] = #31 May 2006#

the hashes are necessary so that access knows its a date.

use long date to format the date, not shortdate. Shortdate formats the date like 11/1/06. The query will take this as a US date I think, 11th Jan, instead of 1st November. Long Date avoids any confusion.
 
Last edited:
Thanks again
I will try but i can't use Long Date, because i have to consolidate 2 tables after all and in the first table the dates and the days are NOT in one Field (it's because of the mistake i did at the beginning of designing of that database). Now i have to pay for this. That's why i try to use Short Date...
 
I have tried it right now and there's no Update Error any more! Thanks
But it doesn't really matter if i use Long or Short Date, because access returns me an error "Syntaxis error in date in '[datum]=#Here stays the date in format i give, but it doesn't matter whether Short or Long or dd/mm/yyyy#'. Can it depend on the fact, that Datum in the tale not a text but a date variable ist?
Thanks in advance
 
Last edited:
Hi again,
it was completely my error, the DCount must look like this:
items = DCount("*", "Entschuldigung", "Datum")
Now it was perfect, thanks
 
Hi again, i have tried it once more and found out that i was too soon so glad.
items = DLookup("[Datum]", "Entschuldigung", "[Datum]")
returns me the day that is in the table in Datum
items = DCount("[Datum]", "Entschuldigung", "[Datum]")
returns me e.g. 8, doesn't matter what i input in the text box in form.
When i try to use:
items = DLookup("[Datum]", "Entschuldigung", "[Datum]= 10.10.2006")
i get an error "type mismatch".
When i try:
items = DLookup("[Datum]", "Entschuldigung", "[Datum]= " & Forms!Entschuldigung!Date)
i get a syntax error.
items = DLookup("[Datum]", "Entschuldigung", "[Datum]= #" & Forms!Entschuldigung!Date & "#")
returns a syntax error too
Please, help me... Why am i so stupid???
Thanks in advance
 
It must look like this:
*******************************************************

Private Sub bflEntschuldigung_Click()
On Error GoTo Err_bflEntschuldigung_Click
Dim i As Integer
Dim x As Integer
Dim Frage
Dim items As Long
Dim items2 As Long
Dim thedate As String
Dim thedate1 As String
Dim thedate2 As String
Dim thedate3 As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Entschuldigung")


thedate1 = Format(Forms!Entschuldigung!Datum, "mm")
thedate2 = Format(Forms!Entschuldigung!Datum, "dd")
thedate3 = Format(Forms!Entschuldigung!Datum, "yyyy")
thedate = thedate1 & "/" & thedate2 & "/" & thedate3
'MsgBox thedate
items = DCount("[Datum]", "Entschuldigung", "[Datum]= " & "#" & thedate & "#" & "")
items2 = DCount("[Datum]", "Nachweis", "[Datum]= " & "#" & thedate & "#" & "")
'MsgBox items
'MsgBox items2
If items = 0 Then
If items2 = 0 Then
For i = 1 To 8
rst.AddNew
rst!Datum = Forms!Entschuldigung!Datum
rst!Stunde = i
rst!Modul = "Entschuldigt"
rst![Inhalt, Kommentar] = "Entschuldigt"
rst!Tag = Format(rst!Datum, "dddd")
rst!KW = Format(rst!Datum, "WW")
rst.Update
Next
Frage = MsgBox("Sie haben erfolgreich Entschuldigung für " & Datum & " geschrieben")
Else
MsgBox "Die Eingaben zu diesem Tag existieren schon in der Haupttabelle"
GoTo Exit_bflEntschuldigung_Click
End If
Else
MsgBox "Die Eingaben zu diesem Tag existieren schon"
GoTo Exit_bflEntschuldigung_Click
End If

Exit_bflEntschuldigung_Click:

Exit Sub

Err_bflEntschuldigung_Click:
MsgBox Err.Description
Resume Exit_bflEntschuldigung_Click

End Sub
*********************************************************
Thank you, gemma-the-husky for your help.
You helped me a lot.
Thanks
 

Users who are viewing this thread

Back
Top Bottom