On Close

[Guest]

I have the following code attached to the on close command of a form but I
get a error saying the "sub or function not defined" One step forward three
steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub

 

[stefan hoffmann]

hi,

I have the following code attached to the on close command of a form but I
get a error saying the "sub or function not defined" One step forward three
steps back!!!

Add appropriate line breaks:

Private Sub Form_Close()

CurrentDb.Execute "

Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No] "

End Sub

mfG
--> stefan <--

 

[fredg]

On Sat, 18 Feb 2006 21:51:27 -0800,

I have the following code attached to the on close command of a form but I
get a error saying the "sub or function not defined" One step forward three
steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub

What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Crystal]

You will need to put this on the form UNLOAD event if you
want it to look at the form you are closing for values... if
you wait until the CLOSE event, the form will already have
been unloaded...

Building on to what Fred gave you, I have use line
continuation sysmbols _ (just in case the lines break) and
assigned your SQL to a string so that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not
filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug
window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar,
first icon)
3. cut the SQL statement from the debug window (select it
and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where
the problem is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

On Sat, 18 Feb 2006 21:51:27 -0800,

I have the following code attached to the on close command of a form but I
get a error saying the "sub or function not defined" One step forward three
steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub

What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Crystal]

oops!

change

CurrentDb.Execute , dbFailOnError

to

CurrentDb.Execute strSQL, dbFailOnError

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You will need to put this on the form UNLOAD event if you want it to
look at the form you are closing for values... if you wait until the
CLOSE event, the form will already have been unloaded...

Building on to what Fred gave you, I have use line continuation sysmbols
_ (just in case the lines break) and assigned your SQL to a string so
that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window (select it and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where the problem
is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

On Sat, 18 Feb 2006 21:51:27 -0800,

I have the following code attached to the on close command of a form
but I get a error saying the "sub or function not defined" One step
forward three steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub

What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Guest]

Thank You very much Fred and Crystal, that works. Crystal can I hire you to
teach me Access VBA???

Thanks Again!!!!

oops!

change

CurrentDb.Execute , dbFailOnError

to

CurrentDb.Execute strSQL, dbFailOnError

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You will need to put this on the form UNLOAD event if you want it to
look at the form you are closing for values... if you wait until the
CLOSE event, the form will already have been unloaded...

Building on to what Fred gave you, I have use line continuation sysmbols
_ (just in case the lines break) and assigned your SQL to a string so
that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window (select it and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where the problem
is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

I have the following code attached to the on close command of a form
but I get a error saying the "sub or function not defined" One step
forward three steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub



What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Guest]

One last question on this subject. Along with that update I want to put the
default value of the storage location. I added this code:
and [storagelocation] = [tbl-offsites]![storagelocation.defaultvalue] where
[vault No]..... but it to has problems. It that not the same format??

oops!

change

CurrentDb.Execute , dbFailOnError

to

CurrentDb.Execute strSQL, dbFailOnError

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You will need to put this on the form UNLOAD event if you want it to
look at the form you are closing for values... if you wait until the
CLOSE event, the form will already have been unloaded...

Building on to what Fred gave you, I have use line continuation sysmbols
_ (just in case the lines break) and assigned your SQL to a string so
that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window (select it and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where the problem
is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

I have the following code attached to the on close command of a form
but I get a error saying the "sub or function not defined" One step
forward three steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub



What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Crystal]

Sure! I do remote training -- just send me an email. I am
writing a book on VBA -- have 3 chapters done that I send
that out free for self-study to all who ask.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Thank You very much Fred and Crystal, that works. Crystal can I hire you to
teach me Access VBA???

Thanks Again!!!!

:

oops!

change

CurrentDb.Execute , dbFailOnError

to

CurrentDb.Execute strSQL, dbFailOnError

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You will need to put this on the form UNLOAD event if you want it to
look at the form you are closing for values... if you wait until the
CLOSE event, the form will already have been unloaded...

Building on to what Fred gave you, I have use line continuation sysmbols
_ (just in case the lines break) and assigned your SQL to a string so
that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window (select it and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where the problem
is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



fredg wrote:


I have the following code attached to the on close command of a form
but I get a error saying the "sub or function not defined" One step
forward three steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub



What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError

 

[Crystal]

you can reference a default value of a field like this:

currentdb.TableDefs("tbl-offsites").Fields("storagelocation").DefaultValue

as a shortcut, you can use this:

currentdb("tbl-offsites")("storagelocation").DefaultValue

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

One last question on this subject. Along with that update I want to put the
default value of the storage location. I added this code:
and [storagelocation] = [tbl-offsites]![storagelocation.defaultvalue] where
[vault No]..... but it to has problems. It that not the same format??

:

oops!

change

CurrentDb.Execute , dbFailOnError

to

CurrentDb.Execute strSQL, dbFailOnError

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

You will need to put this on the form UNLOAD event if you want it to
look at the form you are closing for values... if you wait until the
CLOSE event, the form will already have been unloaded...

Building on to what Fred gave you, I have use line continuation sysmbols
_ (just in case the lines break) and assigned your SQL to a string so
that you can debug it

dim strSQL as string
strSQL = "Update [tbl-offsites] " _
& " SET ReleasedDate = #" & Date() & "#" _
& " WHERE [Vault No] = " & nz(Me![Vault No]) & ";"
debug.print strSQL
CurrentDb.Execute , dbFailOnError

and, then, if [Vault No] is text, you will need to delimit it

& " WHERE [Vault No] = '" & nz(Me![Vault No]) & "';"

I have also used NZ (null to zero) in case the field is not filled out

~~~~~
debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

If you have problems after you run it, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)
2. choose View, SQL from the menu (or SQL from the toolbar, first icon)
3. cut the SQL statement from the debug window (select it and then CTRL-X)
4. paste into the SQL window of the Query (CTRL-V)
5. run ! from the SQL window -- Access will tell you where the problem
is in the SQL

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



fredg wrote:


I have the following code attached to the on close command of a form
but I get a error saying the "sub or function not defined" One step
forward three steps back!!!

Private Sub Form_Close()
Update [tbl-offsites]
Set ReleasedDate = Date
where [forms!frmrelease]![Vault No] = [Vault No]

End Sub



What is the datatype of [Vault No]?
Number datatype?
What is the name of the form on which this code is placed?
"frmRelease"? You can substitute the Me keyword for forms!frmRelease.

Private Sub Form_Close()
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl_offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= " & Me![Vault No] & ";", dbFailOnError
End Sub

Text datatype?
CurrentDb.Execute "Update [tbl-offsites] Set
[tbl-offsites].ReleasedDate = Date() where [tbl-offsites].[Vault No]
= '" & Me![Vault No] & "';", dbFailOnError