Me.Requery and Me.Recordsetclone don't work in Module

[insideout786]

I have a welcome form which captures the date and cleaners form the
text box.
On enter it checks to see if the record exit in the table.
if it does it needs to open the form "CPA" to that record
else it need to first add the record to the table than open the form
"CPA" to that record.

I tried using the me.recordsetclone after opening and requerying the
form "CPA" in a module but it fails to compile when it encounters the
me.requery or me.recordsetclone.

Function setFormonOpen2()
' this query to see if the record exits in the table and returns the x
as 1 or 0
Call searchQuery
If x = 0 Then
Call AddRecordOnOpen
Me.Requery
DoCmd.OpenForm "cpa"
Call setFormOnOpen1
Else
DoCmd.OpenForm "CPA"
Call setFormOnOpen1
End If
End Function


Function AddRecordOnOpen()

Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)

rs.AddNew
rs!Date = gbldate
rs!Day = Format(gbldate, "dddd")
rs!Cleaner = gblcleaner
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Function setFormOnOpen1()
Me.Requery
Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)
Set rs = Me.RecordsetClone
With Me.RecordsetClone
rs.FindFirst "[date]=#" & gbldate & "# and [cleaner] = '"
& gblcleaner & "'"

If .NoMatch Then
MsgBox "No more records for this location."
Else
Me.Bookmark = .Bookmark
End If
End With

rs.Close
Set rs = Nothing
Set db = Nothing


End Function


Any Idea why???

 

[Dirk Goldgar]

I have a welcome form which captures the date and cleaners form the
text box.
On enter it checks to see if the record exit in the table.
if it does it needs to open the form "CPA" to that record
else it need to first add the record to the table than open the form
"CPA" to that record.

I tried using the me.recordsetclone after opening and requerying the
form "CPA" in a module but it fails to compile when it encounters the
me.requery or me.recordsetclone.

Function setFormonOpen2()
' this query to see if the record exits in the table and returns the x
as 1 or 0
Call searchQuery
If x = 0 Then
Call AddRecordOnOpen
Me.Requery
DoCmd.OpenForm "cpa"
Call setFormOnOpen1
Else
DoCmd.OpenForm "CPA"
Call setFormOnOpen1
End If
End Function


Function AddRecordOnOpen()

Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)

rs.AddNew
rs!Date = gbldate
rs!Day = Format(gbldate, "dddd")
rs!Cleaner = gblcleaner
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Function setFormOnOpen1()
Me.Requery
Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)
Set rs = Me.RecordsetClone
With Me.RecordsetClone
rs.FindFirst "[date]=#" & gbldate & "# and [cleaner] = '"
& gblcleaner & "'"

If .NoMatch Then
MsgBox "No more records for this location."
Else
Me.Bookmark = .Bookmark
End If
End With

rs.Close
Set rs = Nothing
Set db = Nothing


End Function


Any Idea why???

When you say "in a module", do you mean in a standard module, as opposed
to the form's class module? The keyword "Me" refers to the class module
containing the running code, so it would not work in a standard module.
If your code is in a standard module, you need to either pass a
reference to the form in question, or get one from the database's Forms
collection, or maybe use Screen.ActiveForm to get a reference to the
active form. Then your Requery and RecordsetClone methods would be
qualified by that reference, rather than by "Me".

 

[insideout786]

Just want to makesure, It is an standalone module so I should replace
the me.recordsetclone and me.requery to
screen.activeform.recordsetclone and screen.activeform.requery? right?

I have a welcome form which captures the date and cleaners form the
text box.
On enter it checks to see if the record exit in the table.
if it does it needs to open the form "CPA" to that record
else it need to first add the record to the table than open the form
"CPA" to that record.

I tried using the me.recordsetclone after opening and requerying the
form "CPA" in a module but it fails to compile when it encounters the
me.requery or me.recordsetclone.

Function setFormonOpen2()
' this query to see if the record exits in the table and returns the x
as 1 or 0
Call searchQuery
If x = 0 Then
Call AddRecordOnOpen
Me.Requery
DoCmd.OpenForm "cpa"
Call setFormOnOpen1
Else
DoCmd.OpenForm "CPA"
Call setFormOnOpen1
End If
End Function

Function AddRecordOnOpen()

Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)

rs.AddNew
rs!Date = gbldate
rs!Day = Format(gbldate, "dddd")
rs!Cleaner = gblcleaner
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Function setFormOnOpen1()
Me.Requery
Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)
Set rs = Me.RecordsetClone
With Me.RecordsetClone
rs.FindFirst "[date]=#" & gbldate & "# and [cleaner] = '"
& gblcleaner & "'"

If .NoMatch Then
MsgBox "No more records for this location."
Else
Me.Bookmark = .Bookmark
End If
End With

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Any Idea why???When you say "in a module", do you mean in a standard module, as opposed

to the form's class module? The keyword "Me" refers to the class module
containing the running code, so it would not work in a standard module.
If your code is in a standard module, you need to either pass a
reference to the form in question, or get one from the database's Forms
collection, or maybe use Screen.ActiveForm to get a reference to the
active form. Then your Requery and RecordsetClone methods would be
qualified by that reference, rather than by "Me".

 

[Dirk Goldgar]

Just want to makesure, It is an standalone module so I should replace
the me.recordsetclone and me.requery to
screen.activeform.recordsetclone and screen.activeform.requery? right?

That may work, but with your code, I'm not sure that the form you want
to process will have become active yet, as far as Access is concerned.
You can try that and see; just add a line at the start of one of those
functions:

MsgBox Screen.ActiveForm.Name

and then run the process normally, to check.

If you find that the form you intend is indeed the active form, then all
is well. If it isn't, if the previous form is still the active form,
then you probably need to change those functions so that you pass either
the name of the form or a direct reference to the form object, and then
modify the code in the functions appropriately.

 

[David W. Fenton]

(e-mail address removed) wrote in

Function setFormOnOpen1()
Me.Requery
Set db = CurrentDb
Set rs = db.OpenRecordset("cpa", dbOpenTable)
Set rs = Me.RecordsetClone

Um, which recordset do you want to use? Right here you've opened one
recordset, then immediately destroyed it by assigning the
recordsetclone to the same veriable.

In any event, I never use a recordset variable for using the
recordsetclone -- instead I use a WITH statement, just as you
actually do in your code. So, I'm pretty confused by what you're
trying to accomplish.