By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Creating new labels/bottons using code...

P: 59
I know I already asked this question before, but I didn't get an answer..

I'll try to explain what I'm trying to do:

I have a table with pepole with birthdates, and I want to present all the pepole with a bitrthday on the current month... and to add a botton for each persone to send him email of happy bday.

I can't tell wather it will be 1 persone of 100 or 300... thats why I must create a labels/botton for each persone using the SELECT loop..
I'm doing to loop on form load

hope someone could help me out, or advice me a better idea...

thanks in advance,
Idan
Jan 11 '08 #1
Share this Question
Share on Google+
11 Replies


Minion
Expert 100+
P: 108
I know I already asked this question before, but I didn't get an answer..

I'll try to explain what I'm trying to do:

I have a table with pepole with birthdates, and I want to present all the pepole with a bitrthday on the current month... and to add a botton for each persone to send him email of happy bday.

I can't tell wather it will be 1 persone of 100 or 300... thats why I must create a labels/botton for each persone using the SELECT loop..
I'm doing to loop on form load

hope someone could help me out, or advice me a better idea...

thanks in advance,
Idan

Right off I wonder why you want do this dynamically. There is a rare occasion that you will want to produce a form on the fly. But there are a couple good methods to complete what you are asking. I'll try and give you two ideas.

First:

If you are going to retreive a list of everyone with birthdays in a month and each are going to get an email message why not simplly use a loop to send them all at once. It would be far better than haveing to click on 300+ boxes on the form. This could be accomplished by retreiving the said recordset and looping through each record adding the information into an email. For more information on this part I would check out Emailing From Access as it is a good basic answer to this problem.

Second:

If you really need to a button for each it would probably be best to create a continuous form with the button attached. From here it would show all the records in what ever format you desired and you could have the requesit email button on the form.

Hope this helps.

- Minion -
Jan 11 '08 #2

P: 59
thanks Minion..

I know how to do the firts way you suggested, but I would like to choose which person to send, and the most importent when.. I want only to display the pepole.

I didn't really understood your second suggestion.. will it display only the pepole which have bday on the current month ? or will it show all the pepole from the table ?
maybe it will help if I could fillter between specific dates.. if someone know how ?

though I prefer creating new labels to display the pepole if its possible...


Right off I wonder why you want do this dynamically. There is a rare occasion that you will want to produce a form on the fly. But there are a couple good methods to complete what you are asking. I'll try and give you two ideas.

First:

If you are going to retreive a list of everyone with birthdays in a month and each are going to get an email message why not simplly use a loop to send them all at once. It would be far better than haveing to click on 300+ boxes on the form. This could be accomplished by retreiving the said recordset and looping through each record adding the information into an email. For more information on this part I would check out Emailing From Access as it is a good basic answer to this problem.

Second:

If you really need to a button for each it would probably be best to create a continuous form with the button attached. From here it would show all the records in what ever format you desired and you could have the requesit email button on the form.

Hope this helps.

- Minion -
Jan 11 '08 #3

P: 59
using

Dim Label3 As New Label

can't work ?
Jan 11 '08 #4

Minion
Expert 100+
P: 108
Zivon it sounds like you're right on track and understand my second suggestion more than you might think.

What you'll need to do is create a form based on the employee table. Right off this will display all employees, but that's ok because as you said we're going to add a filter to the form. Now that you have the form you will want to make it look like what you want and have only the info you want with a button to send the email. For best results try and make it about one line long (remember this will be repeated when we're done).

Now you'll notice this only displays on employee at a time and you have to use the navigation buttons to move between records. This is where the continuous form actually comes into play. Switch to design mode and select the form element (little box in upper left corner of grid will select it). Now goto the format tab in the properties window (brought up with Alt+Tab) and change default view to Continuous Forms. Now the form should repeat itself one on top of the other in Form View with each line having the employee's info.

With this done we need to add a filter to the overall form that will only show those employees with the birthdate falling in the current month. (If you want a more complex filtering system that can be arrange, but we'll start simple) I'm taking that in your form view you made a text box linked to the employee's birthdate and I'll say it's called txtBDay and field in the table is called DOB. Time to set the filter.

Go back to the properties for the form and select the data tab. The second option down should read "Filter" in that box type the following.
Expand|Select|Wrap|Line Numbers
  1. Month(DOB)=Month(Now)
  2.  
If everything is done right this should make a continuous form that only shows the employees who's DOB is in the same month as today's date.

Hope this helps.

- Minion -
Jan 11 '08 #5

P: 59
: \
that doesn't seem to work... I tried even more simple filter and it doesn't work:
FirstName = "Idan"
the allow filters = yes

when I open the form with a botton from another form I mannage to filter by code.. but I can't write there Month(Birthdate)
I dont know why...

and about the more complex, I will need a more complex filter. ie not on the same month, between one week before today and one week after today.
thats exactly what I want, I gave the example of on the same month just to simplify it..

hope you could help me out..
idan


Zivon it sounds like you're right on track and understand my second suggestion more than you might think.

What you'll need to do is create a form based on the employee table. Right off this will display all employees, but that's ok because as you said we're going to add a filter to the form. Now that you have the form you will want to make it look like what you want and have only the info you want with a button to send the email. For best results try and make it about one line long (remember this will be repeated when we're done).

Now you'll notice this only displays on employee at a time and you have to use the navigation buttons to move between records. This is where the continuous form actually comes into play. Switch to design mode and select the form element (little box in upper left corner of grid will select it). Now goto the format tab in the properties window (brought up with Alt+Tab) and change default view to Continuous Forms. Now the form should repeat itself one on top of the other in Form View with each line having the employee's info.

With this done we need to add a filter to the overall form that will only show those employees with the birthdate falling in the current month. (If you want a more complex filtering system that can be arrange, but we'll start simple) I'm taking that in your form view you made a text box linked to the employee's birthdate and I'll say it's called txtBDay and field in the table is called DOB. Time to set the filter.

Go back to the properties for the form and select the data tab. The second option down should read "Filter" in that box type the following.
Expand|Select|Wrap|Line Numbers
  1. Month(DOB)=Month(Now)
  2.  
If everything is done right this should make a continuous form that only shows the employees who's DOB is in the same month as today's date.

Hope this helps.

- Minion -
Jan 12 '08 #6

Minion
Expert 100+
P: 108
My apologies Zivon that the last piece I gave you did not work. It would seem that in my attempts to help simplify matters I only failed to make an accurate snippet for you.

If I understand what you want as a final result I have put together the code for a command button to add to the form. When clicked this will filter the records showing all records falling 7 days before today up to 7 days after today. I have tried this code on one of my own projects so I know it will work. Again the field name I'm guessing is Birthdate.

Place this in the Click event of the command button:
Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.  
  3.     strFilter = "Birthdate >= #" & Now - 7 & "# and Birthdate <= #" & Now + 7 & "#"
  4.     Me.Filter = strFilter
  5.     Me.FilterOn = True
  6.  
Hope this works for you. Keep me posted.

- Minion -

BTW... As I was typing this I thought you might want the button to act as a toggle button that would turn the filtering on and off in case you wanted to view more records. So below is the code to do that, just use it instead of the above code.
Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.  
  3.     If Me.Filter = "" Then
  4.         strFilter = "Birthdate >= #" & Now - 7 & "# and Birthdate <= #" & Now + 7 & "#"
  5.         Me.Filter = strFilter
  6.         Me.FilterOn = True
  7.     Else
  8.         Me.Filter = ""
  9.         Me.FilterOn = False
  10.     End If
  11.  

: \
that doesn't seem to work... I tried even more simple filter and it doesn't work:
FirstName = "Idan"
the allow filters = yes

when I open the form with a botton from another form I mannage to filter by code.. but I can't write there Month(Birthdate)
I dont know why...

and about the more complex, I will need a more complex filter. ie not on the same month, between one week before today and one week after today.
thats exactly what I want, I gave the example of on the same month just to simplify it..

hope you could help me out..
idan
Jan 14 '08 #7

P: 59
First of all, you have no reason to apologies, since you've done nothing wrong, the other way around, you are only helping ! its even better, because I also learn from the mistakes.

Sorry I didn't reply before, I was away... any way, I tried the code.. and it didn't work.

first, I relaized it will only work for the same year* which is a big problam... and it pretty depressing you're doing everything for me, and I can't figure out how to do this stuff : <

second..
I entered a birthdate 22/1/2008

and tried manualy:
strFilter = "Birthdate < #26/01/2008# and Birthdate > #13/01/2008#"

it worked.

but when I tried:

strFilter = "Birthdate < #26/01/2008# and Birthdate > #12/01/2008#"

it didn't worked : (

I dont know why, but compring dates isn't that faithful in vb...

I have an idea, but I dont know how to implement it... : \

DatePart("m",Birthdate) = DatePart("m", Date) and DatePart("d",Birthdate) < DatePart("d",dateadd("d", 7, date)) and DatePart("d",Birthdate) > DatePart("d",dateadd("d", -7, date))


My apologies Zivon that the last piece I gave you did not work. It would seem that in my attempts to help simplify matters I only failed to make an accurate snippet for you.

If I understand what you want as a final result I have put together the code for a command button to add to the form. When clicked this will filter the records showing all records falling 7 days before today up to 7 days after today. I have tried this code on one of my own projects so I know it will work. Again the field name I'm guessing is Birthdate.

Place this in the Click event of the command button:
Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.  
  3.     strFilter = "Birthdate >= #" & Now - 7 & "# and Birthdate <= #" & Now + 7 & "#"
  4.     Me.Filter = strFilter
  5.     Me.FilterOn = True
  6.  
Hope this works for you. Keep me posted.

- Minion -

BTW... As I was typing this I thought you might want the button to act as a toggle button that would turn the filtering on and off in case you wanted to view more records. So below is the code to do that, just use it instead of the above code.
Expand|Select|Wrap|Line Numbers
  1.     Dim strFilter As String
  2.  
  3.     If Me.Filter = "" Then
  4.         strFilter = "Birthdate >= #" & Now - 7 & "# and Birthdate <= #" & Now + 7 & "#"
  5.         Me.Filter = strFilter
  6.         Me.FilterOn = True
  7.     Else
  8.         Me.Filter = ""
  9.         Me.FilterOn = False
  10.     End If
  11.  
Jan 19 '08 #8

ADezii
Expert 5K+
P: 8,623
I know I already asked this question before, but I didn't get an answer..

I'll try to explain what I'm trying to do:

I have a table with pepole with birthdates, and I want to present all the pepole with a bitrthday on the current month... and to add a botton for each persone to send him email of happy bday.

I can't tell wather it will be 1 persone of 100 or 300... thats why I must create a labels/botton for each persone using the SELECT loop..
I'm doing to loop on form load

hope someone could help me out, or advice me a better idea...

thanks in advance,
Idan
Unless you are very proficient in writing VBA code, your dynamic Label and Button creation approach will never work. In my opinion, the best approach is to populate a MultiSelect Listbox of all people whose birthdays occur during the Current Month. This way you can control to Whom, how Many, and When the E-mails are sent. You could also provide a built-in mechanism for not displaying names who already received an E-Mail.
Jan 20 '08 #9

P: 59
ADezii, thanks for the reply.

No, I'm not proficient in VBA coding, maybe a newbie is the right word to describe me in VBA :)

anyway, I already dropped the idea of creating new labels (though I still wish to know how to do so...)

Minion suggestion will do for what I'm trying to achieve, if it will work like I want it to...

filter a form with this somehow:

DatePart("m",Birthdate) = DatePart("m", Date) and DatePart("d",Birthdate) < DatePart("d",dateadd("d", 7, date)) and DatePart("d",Birthdate) > DatePart("d",dateadd("d", -7, date))



Unless you are very proficient in writing VBA code, your dynamic Label and Button creation approach will never work. In my opinion, the best approach is to populate a MultiSelect Listbox of all people whose birthdays occur during the Current Month. This way you can control to Whom, how Many, and When the E-mails are sent. You could also provide a built-in mechanism for not displaying names who already received an E-Mail.
Jan 21 '08 #10

P: 59
thanks for you both, I manage to solve it out.

I used:

Expand|Select|Wrap|Line Numbers
  1.  strFilter = "Month([Birthdate]) = " & Month(Date) & " and Day([Birthdate]) < " & Day(DateAdd("d", 7, Date)) & " and Day([Birthdate]) > " & Day(DateAdd("d", -7, Date)) 
Jan 21 '08 #11

P: 59
doh... actually that doesn't solve the problam at all.

when I use this it doesn't work for the end+beging of a month (between the dates 24th-7th and also depend on the month lenth...)
the problam is due to the +-7

I manage to think of a better filter, but still aint perfect..

using the "day of year"

Expand|Select|Wrap|Line Numbers
  1.  DatePart('y',Birthdate) < " & DatePart("y", DateAdd("d", 7, Date)) & " and DatePart('y',Birthdate) > " & DatePart("y", DateAdd("d", -7, Date)) 
now the problam only occur in the beging+end of the year... but it still occur : (

I can't think of a better way... I hope one of you could..

thanks in advance,
Idan
Jan 26 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.