Daily change label

[Striker]

I have a database that has a main form. ON the main form is a label at the
top. I have some people who want to cahnge the label randomly each time the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.

 

[fredg]

I have a database that has a main form. ON the main form is a label at the
top. I have some people who want to cahnge the label randomly each time the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.

Simplest way, for me....
Have a table with, for example, 31 messages.
ID Message
1 "First Message"
2 "Second Message"
etc.

Have an unbound control on your form.
Set it's Control Source to:

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()))

You can adapt the above to more than 31 messages with some fancier
coding.

Have the table with 366 ,messages/

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()) *
Month(Date()))

Good for 366 messages, 1 for each day and month of the year.

 

[KARL DEWEY]

Try this method --
Message table -
Autonumber - random
Message - text
Used - Yes/No
LastCol - integer

Autoexec macro -
Condition - check if message table has a record not marked as used
No - run Update1 query
Yes - run Message query
Yes - run Update2 query

MessageQuery -
SELECT TOP 1 [Message].[Message]
FROM Message
WHERE Used = False
ORDER BY Mid([Autonumber], 2);

Update1 query -
UPDATE Message SET Message.Used= False, Message.LastCol=Message.LastCol+1;

Update2 query -
UPDATE MessageQuery INNER JOIN Message ON MessageQuery.Autonumber =
Message.Autonumber SET Message.Used= True;

 

[Striker]

just produces an error.

Have created a table - two fields Message and ID. ID numbers 1-31. Message
text field 255 length.
Added a textbox to foem and set the control source to
=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()))

When I view the form, thext text box just says #error

I have a database that has a main form. ON the main form is a label at
the
top. I have some people who want to cahnge the label randomly each time
the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure
out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.

Simplest way, for me....
Have a table with, for example, 31 messages.
ID Message
1 "First Message"
2 "Second Message"
etc.

Have an unbound control on your form.
Set it's Control Source to:

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()))

You can adapt the above to more than 31 messages with some fancier
coding.

Have the table with 366 ,messages/

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()) *
Month(Date()))

Good for 366 messages, 1 for each day and month of the year.

 

[fredg]

just produces an error.

Have created a table - two fields Message and ID. ID numbers 1-31. Message
text field 255 length.
Added a textbox to foem and set the control source to
=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()))

When I view the form, thext text box just says #error

I have a database that has a main form. ON the main form is a label at
the
top. I have some people who want to cahnge the label randomly each time
the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure
out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.

Simplest way, for me....
Have a table with, for example, 31 messages.
ID Message
1 "First Message"
2 "Second Message"
etc.

Have an unbound control on your form.
Set it's Control Source to:

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()))

You can adapt the above to more than 31 messages with some fancier
coding.

Have the table with 366 ,messages/

=DLookUp("[Message]","tblMessages","[ID] = " & Day(Date()) *
Month(Date()))

Good for 366 messages, 1 for each day and month of the year.

1) Make sure the name of this control is not "Message" or "ID".
2) In the table, did you set the [ID] datatype to Number, Integer
Field Size?

 

[Striker]

can this be done in a module on form load maybe?

Try this method --
Message table -
Autonumber - random
Message - text
Used - Yes/No
LastCol - integer

Autoexec macro -
Condition - check if message table has a record not marked as used
No - run Update1 query
Yes - run Message query
Yes - run Update2 query

MessageQuery -
SELECT TOP 1 [Message].[Message]
FROM Message
WHERE Used = False
ORDER BY Mid([Autonumber], 2);

Update1 query -
UPDATE Message SET Message.Used= False, Message.LastCol=Message.LastCol+1;

Update2 query -
UPDATE MessageQuery INNER JOIN Message ON MessageQuery.Autonumber =
Message.Autonumber SET Message.Used= True;


--
KARL DEWEY
Build a little - Test a little

I have a database that has a main form. ON the main form is a label at
the
top. I have some people who want to cahnge the label randomly each time
the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure
out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.

 

[KARL DEWEY]

I am not experinced with modules. I would use a macro named Autoexec that
runs on opening the databse unless you hold down the shift key.
--
KARL DEWEY
Build a little - Test a little

can this be done in a module on form load maybe?

Try this method --
Message table -
Autonumber - random
Message - text
Used - Yes/No
LastCol - integer

Autoexec macro -
Condition - check if message table has a record not marked as used
No - run Update1 query
Yes - run Message query
Yes - run Update2 query

MessageQuery -
SELECT TOP 1 [Message].[Message]
FROM Message
WHERE Used = False
ORDER BY Mid([Autonumber], 2);

Update1 query -
UPDATE Message SET Message.Used= False, Message.LastCol=Message.LastCol+1;

Update2 query -
UPDATE MessageQuery INNER JOIN Message ON MessageQuery.Autonumber =
Message.Autonumber SET Message.Used= True;


--
KARL DEWEY
Build a little - Test a little

I have a database that has a main form. ON the main form is a label at
the
top. I have some people who want to cahnge the label randomly each time
the
DB is opened. Seems a little silly to me but they want a motivational
message to change daily.

So how to do this is my question. I'm pretty new to access 2007. I was
thinking about a table what all the messages in it. Just need to figure
out
some code to change the label on form load.

If anyone has any better ides how to accomplish I'm interested.