Ccreate Macro with MsgBox. Pls. help!

[Oamya Kim]

Hi,

I've created a form consists of data fileds: licenceNo, NoOfValidDays
and now wanted to create a macro with MsgBox which can auto - check if the
field of "NoOfValidDays" <= 45 days, then a message box would appear.

Msg Box should contains the information such as "the licene no.
###[licenceNo] will be expired in next -- days [NoOfValidDays]

This message box is for auto - reminder purposes.
I tried to create macro with MsgBox action, and in the condition equivalent
of:

[Forms]![FormName]![NoOfValidDays]<45
when run this macro, it shows "Type Mismatch" ???

Any suggestions would be greately appreciated!
Thanks a lot,
Kim

 

[Ken Snell \(MVP\)]

Is NoOfValidDays the name of a field or the name of a control? If it's a
field, then your condition should be this:

[Forms]![FormName].Recordset.Fields("NoOfValidDays").Value<45


What data type is the NoOfValidDays -- numeric? text?

 

[Oamya Kim]

Hi Ken,
Thanks for ur reply!

NoOfValidDays is a filed name that derived from ExpiredDate filed & currDate
filed in the Form by expression: =DateDiff("d",[CurrDate],[ExpiredDate]),
data type is numeric

Pls. be patient and instruct me more details how to do it, i am not a
programmer. Please!

My Form Name is CheckExpiredDate
Do I need to replace the 'Recordset.Fields' by something? bcos I put the
condition as following:
[Forms]![CheckExpiredDate].Recordset.Fields("NoOfValidDays").Value<45

but it doesnt' work. error msg shows: "Access failed to evaluate the
expression bcos "Recordset" was referenced in the expression..."

Btw, Can I just add this expression:
=DateDiff("d",[CurrDate],[ExpiredDate]) into table instead of a form? If
can use to check the condition in table, so that I can omit a unnecessary
form in my database.

Thanks for ur help.
Kim

Is NoOfValidDays the name of a field or the name of a control? If it's a
field, then your condition should be this:

[Forms]![FormName].Recordset.Fields("NoOfValidDays").Value<45


What data type is the NoOfValidDays -- numeric? text?
--

Ken Snell
<MS ACCESS MVP>

Hi,

I've created a form consists of data fileds: licenceNo, NoOfValidDays
and now wanted to create a macro with MsgBox which can auto - check if the
field of "NoOfValidDays" <= 45 days, then a message box would appear.

Msg Box should contains the information such as "the licene no.
###[licenceNo] will be expired in next -- days [NoOfValidDays]

This message box is for auto - reminder purposes.
I tried to create macro with MsgBox action, and in the condition
equivalent
of:

[Forms]![FormName]![NoOfValidDays]<45
when run this macro, it shows "Type Mismatch" ???

Any suggestions would be greately appreciated!
Thanks a lot,
Kim

 

[Ken Snell \(MVP\)]

Hi Ken,
Thanks for ur reply!

NoOfValidDays is a filed name that derived from ExpiredDate filed &
currDate
filed in the Form by expression: =DateDiff("d",[CurrDate],[ExpiredDate]),
data type is numeric

Is this expression in the query? or in the textbox on the form? Assuming
that it's in the textbox, then the condition would be this:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45

However, you say that this is returning a data type mismatch error?

If it's an expression in the query, then the condition would be this (I had
typo in my first post):
[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45

Pls. be patient and instruct me more details how to do it, i am not a
programmer. Please!

My Form Name is CheckExpiredDate
Do I need to replace the 'Recordset.Fields' by something? bcos I put the
condition as following:
[Forms]![CheckExpiredDate].Recordset.Fields("NoOfValidDays").Value<45

but it doesnt' work. error msg shows: "Access failed to evaluate the
expression bcos "Recordset" was referenced in the expression..."

Btw, Can I just add this expression:
=DateDiff("d",[CurrDate],[ExpiredDate]) into table instead of a form? If
can use to check the condition in table, so that I can omit a unnecessary
form in my database.

You cannot use an expression in a table, but you can use it in a calculated
field in a query, and then use that query as the RecordSource for a form.

 

[Oamya Kim]

Hi Ken,
1. If I use this condition:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45
Error Msg: "


2.If I use 2nd condition:
[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45

Hi Ken,
Thanks for ur reply!

NoOfValidDays is a filed name that derived from ExpiredDate filed &
currDate
filed in the Form by expression: =DateDiff("d",[CurrDate],[ExpiredDate]),
data type is numeric

Is this expression in the query? or in the textbox on the form? Assuming
that it's in the textbox, then the condition would be this:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45

However, you say that this is returning a data type mismatch error?

If it's an expression in the query, then the condition would be this (I had
typo in my first post):
[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45

Pls. be patient and instruct me more details how to do it, i am not a
programmer. Please!

My Form Name is CheckExpiredDate
Do I need to replace the 'Recordset.Fields' by something? bcos I put the
condition as following:
[Forms]![CheckExpiredDate].Recordset.Fields("NoOfValidDays").Value<45

but it doesnt' work. error msg shows: "Access failed to evaluate the
expression bcos "Recordset" was referenced in the expression..."

Btw, Can I just add this expression:
=DateDiff("d",[CurrDate],[ExpiredDate]) into table instead of a form? If
can use to check the condition in table, so that I can omit a unnecessary
form in my database.

You cannot use an expression in a table, but you can use it in a calculated
field in a query, and then use that query as the RecordSource for a form.

Thanks for ur help.
Kim

 

[Oamya Kim]

Hi Ken,
1. If I use this condition:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45
Error Msg: "The object u referenced in the VB procedure as an OLE
object isn't an OLE object"

2.If I use 2nd condition:

[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45
Error Msg: "Access failed to evaluate the expression bcos "Recordset"
was referenced in the expression. Only functions considers to be safe are
allowed when Access runs in sandbox mode"
I dont know why :-(

FYI, NoOfValidDays field is an textbox on the form

Thanks,
Kim

Hi Ken,
Thanks for ur reply!

NoOfValidDays is a filed name that derived from ExpiredDate filed &
currDate
filed in the Form by expression: =DateDiff("d",[CurrDate],[ExpiredDate]),
data type is numeric

Is this expression in the query? or in the textbox on the form? Assuming
that it's in the textbox, then the condition would be this:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45

However, you say that this is returning a data type mismatch error?

If it's an expression in the query, then the condition would be this (I had
typo in my first post):
[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45

Pls. be patient and instruct me more details how to do it, i am not a
programmer. Please!

My Form Name is CheckExpiredDate
Do I need to replace the 'Recordset.Fields' by something? bcos I put the
condition as following:
[Forms]![CheckExpiredDate].Recordset.Fields("NoOfValidDays").Value<45

but it doesnt' work. error msg shows: "Access failed to evaluate the
expression bcos "Recordset" was referenced in the expression..."

Btw, Can I just add this expression:
=DateDiff("d",[CurrDate],[ExpiredDate]) into table instead of a form? If
can use to check the condition in table, so that I can omit a unnecessary
form in my database.

You cannot use an expression in a table, but you can use it in a calculated
field in a query, and then use that query as the RecordSource for a form.

Thanks for ur help.
Kim

 

[Ken Snell \(MVP\)]

OK, NoOfValidDays is the name of a textbox on the form. Therefore, use this
expression in the macro's Condition column:

[Forms]![CheckExpiredDate]![NoOfValidDays].Value<45

or

[Forms]![CheckExpiredDate]![NoOfValidDays]<45


And, check the Format property for that textbox. It should be empty or
should show General Number.

 

[Oamya Kim]

Hi Ken,

Thanks a million!
It works now. Thanks for ur grate help.

Best rdgs,
Kim

OK, NoOfValidDays is the name of a textbox on the form. Therefore, use this
expression in the macro's Condition column:

[Forms]![CheckExpiredDate]![NoOfValidDays].Value<45

or

[Forms]![CheckExpiredDate]![NoOfValidDays]<45


And, check the Format property for that textbox. It should be empty or
should show General Number.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
1. If I use this condition:
[Forms]![CheckExpiredDate]!("NoOfValidDays").Value<45
Error Msg: "The object u referenced in the VB procedure as an OLE
object isn't an OLE object"

2.If I use 2nd condition:

[Forms]![CheckExpiredDate].Form.Recordset.Fields("NoOfValidDays").Value<45
Error Msg: "Access failed to evaluate the expression bcos "Recordset"
was referenced in the expression. Only functions considers to be safe are
allowed when Access runs in sandbox mode"
I dont know why :-(

FYI, NoOfValidDays field is an textbox on the form

Thanks,
Kim