| Author |
Topic |
|
surya
Starting Member
7 Posts |
 Posted - 2003-08-04 : 22:36:21
|
| Hello Forum,I have developed forms in MS Access. The database too is MS Access. I am trying to validate the date entered in the text box. The validation is for accepting only the dates entered being "Monday, "Wednesday", and "Friday". I am unable to arrive at the solution for this. Any help would be appreciated.Also, can we attach a calendar to more than one text field so that the user can enter the date by clicking on the calendar.Expect an early reply.Thank you |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-05 : 00:02:00
|
1. Are you saying that they can only enter mondays, wednesdays and fridays? Or only the words "Monday", "Wednesday" and "Friday"?If you want to restrict to mondays, wednesdays and fridays then how about....'check for mondays, wednesdays and fridays onlyIf IsDate(Me.txtDate) Then Me.txtDate = Format(Me.txtDate, "dd-mmm-yyyy") dayofweek = Weekday(Me.txtDate) If dayofweek = vbMonday Or dayofweek = vbWednesday Or dayofweek = vbFriday Then MsgBox "Date entered is OK" End IfEnd If The code in red is a suggestion only - I always "reflect" back the date entered in a standard format, so that the user knows that I have understood what they entered. This is more important if you're not living in the US....but I believe it should be done anyway.For the calendar, plonk down the Microsoft Calendar control (You'll probably want to do it on a separate form so it "pops up"). This gives you the functionality you need...then make a browse button for each date entry. The code of the browse button should then call a function which1. opens the calendar form2. waits until the form is closed with DoEvents3. returns the value of the date selected.HTH--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-05 : 21:49:10
|
| Hello RRB,Thank you for your immediate reply. I guess your date code has helped me solve the immediate problem of validation. I tested the code and it works. But the problem is that after setting focus back to the text field to re-enter a valid date and when the user presses the tab key the column value in the table is getting update with null. I put the column as required value and even that does not seem to solve the problem. I want the focus to remain on the same field until the user enters a valid date that is a wednesday or monday or friday. I do not want Access to automatically update the value and allow the user to press the save button to save the value.Hope I made my problem clear to you.Thank you in advance. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-05 : 22:07:15
|
| hi suryaa few points:firstly - I'd validate the date as part of the code of the save key - or as a function called by the save key - that way there is no chance of invalid dates trying to be "saved"secondly - playing with the focus in Access can be a bit of a pain, but in general I think the right aproach is to do the validation in the "before update" event of the field. (Someone could correct me here if I'm wrong...). No harm in validating twice (when the key is updated and at the save key) - make your code a function and call it in both places...Basically though you're going to continue having problems if your fields are directly connected to the columns in the table. You really should take a "transactional" approach to designing your app - ie, decoupling the data and the form - the data in the form should be a "copy" of the data in the table, and when all entries are verified and the save button clicked, then the data in the table should be updated. In my experience, trying to control focus when you're form is directly bound to the data is problematic.Let me know if I can help further...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-05 : 22:13:03
|
| Hello RRB,I am impressed with your commitment to users problems. I will try out what you advised and let you know the result.Please keep up the good work.Thank you |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-05 : 22:16:07
|
All in return for the excellent help I get at this site!Cheers --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-05 : 22:21:36
|
He's just gonna use your experiences as the basis for another limerick.  |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-05 : 22:32:47
|
Now robvolk, what makes you say that?I'm on a diet, I think rhymes make me fat.I generally withhold'less someone posts something boldOr stupid, at least, that's my caveat... --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-07 : 21:34:15
|
| Hello RRB,Thank you for coming to my help. I am trying to use transactions to update, insert, and delete data using Access forms. I am trying to carry data from one form to another form and then inserting the values by clicking the save button on the third and final form. But I am facing errors in executing them as part of a transaction. Can you help me with this. Also, can I disable to Auto save option in Access so that I can add a save record button in a form for saving the record.Looking forward for your reply.Thank you |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-11 : 20:37:13
|
quote:
Originally posted by surya
Hello RRB,Thank you for coming to my help. I am trying to use transactions to update, insert, and delete data using Access forms. I am trying to carry data from one form to another form and then inserting the values by clicking the save button on the third and final form. But I am facing errors in executing them as part of a transaction. Can you help me with this. Also, can I disable to Auto save option in Access so that I can add a save record button in a form for saving the record.Looking forward for your reply.Thank you
Hi suryaYour forms should be unbound - and so should the controls on the form - all of them (ie control source should be blank).It sounds like you're creating some kind of wizard - form1, form2 , form3 and then save - is that right? You'll have all kinds of problems doing this if any of the forms are bound. It also sounds like you're starting the transaction with form1, but not committing (or rolling back) until form3 - is that correct?You need to decouple your code from the forms. Store all the data entered in form1 and form2 in an array, an object, or whatever. build all your SQL in the last form, start the transaction, run the SQL and then commit or rollback - all on the save button.You can post me your access mdb if you like - Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-23 : 14:40:19
|
| Hello RRB,I apologize for the delay in responding to your msg. I have done the validation of date using the calendar.I owe you for your ideas. It was your ideas that got me going and made my work possible. I had to do lot of things in a short span of time. I have to now set up security for the mdb file. I have to restrict users based on the objects. I am doing some reading on this topic and hope to understand it well. If you can help me again with your excellent ideas pls do not hold them back.Thank you once again.Have a wonderful time.Regards |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-24 : 19:05:21
|
| Hi surya, glad to hear it worked out ok.Trying to secure an mdb is problematic, and I've never found a way which I couldn't also bypass. It depends how seriously you want to "secure" the data - ie - if it's just to stop people meddling, or if it's something like credit-card information or health info, but I suggest you start a new thread - because security is an area I don't know too much about...Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-26 : 23:07:23
|
| Hello RRB,Thank you for continuing to extend your help.Basically I am trying to restrict the users from accessing the mdb. I have to set up the security so that users access only the objects that they are supposed to. The users who have to add data can only add data and users who can view and edit data can only view and edit data.I have provided different forms for all of these things and now I have to figure out how to set up restrictions to the forms, queries and reports that I have created and I am creating.I will try to create a separate thread. Any input from you is invaluable. Have a wonderful time.Best Wishes |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-26 : 23:20:35
|
| When you do, post a link to it here, so I follow. I'll be interested to hear what others recommend.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
surya
Starting Member
7 Posts |
Posted - 2003-08-28 : 07:56:13
|
| Hello RRB,I will make sure that I post the link in this thread too.Have a nice time.Best Wishes |
|
|
|
|
Date Validation in Form