Limiting values 3

[nayfeh]

I don't think this is too tough. I have a linked ODBC table. There are 3 fields: CustomerNumber, OrderDate, and DeliveryDate.

How can I limit the values entered into OrderDate and DeliveryDate to weekdays only. They are both text fields. So, I'd like the user to be able to enter ONLY:
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

I have a form setup so that this isn't directly entered into the table but through that form. Should I do it through the form or the table?

This is to avoid spelling errors by the user.

Thanks,
TN

 

[scriverb]

Well I am a little confused. You said you only wanted weekdays only but you listed all seven days of the week including Sunday and Saturday. Please clarify this for us. You have named this fields OrderDate and DeliveryDate but you don't want a date in them but rather a literal text field string called Monday, etc? I am going to assume that that is correct and that you only want Monday - Friday to be a possibility.

The easiest way is to create a table with two fields in it called DayOfWeek and SortField. Call the table tblDaysOfWeek. Now enter five records in the table each having a sort value of 1-5 to keep them in the right order. (Monday, 1; Tuesday, 2;, Wednesday, 3; Thursday, 4, Friday, 5)

Now create a query with the following SQL code and name it qryDaysOfWeek:

SELECT tblDaysOfWeek.DayOfWeek
FROM tblDaysOfWeek
ORDER BY tblDaysOfWeek.SortField;

Now on your form which is bound to your table we are going to create two combobox objects OrderDateCombo and DeliveryDateCombo which will each be bound to their corresponding fields OrderDate and DeliverDate in the table. This is done by setting the ControlSource property of the ComboBox to the field names. In addition the properties of the ComboBox should be set in this manner:

RowSource qryDaysOfWeek
ColumnCount 1
ColumnWidths 1"
BoundColumn 1
ListRows 5
LimitToList Yes

Put the following code in the OnGotFocus Event Procedure:

Me![OrderDateCombo].dropdown

or

me![DeliveryDateCombo].dropdown

The above setup will only allow for the days Monday - Friday as text entries without any typing by the end user and ensure the integrity of the data for field.

Here is another way to do this. We could have simply put the days ("Monday" Or "Tuesday" Or "Wednesday" Or "Thursday" Or "Friday&quot in the Validation Rule Property of a text box. Then put "Only Weekdays may be entered in this field." in the Validation Text Property to achieve the same results but the combo box doesn't require any typing a works really well.

Let me know which one your chose to use and if you were successful.

Bob Scriver

 

[LittleSmudge]

You seem to have made that hard work Bob .

Here's a simpler alternative

On the form create a combo box with the following parameters
Name = cboWeekDay
ControlSource = DayField ' Where DayField is the name of the field in the table that you want to store this data in
RowSourceType = ValueList
RowSource = "Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"
ColunmCount = 1
BoundColumn = 1
LimitToList = Yes


And there you have it.
It will list in the order that you type in the text in RowSource. Put each row item in it's own double quotes and delimit with semicolons.
It being bound to the field in the table - it will display the field's data once set and will update the field whenever you change the combo box setting.


'ope-that-'elps.

G LS

 

[Quehay]

Hey you're both right! In this case GLS has a more efficient approach, since weekdays can safely be assumed to be a stable domain. Thus there's no need to worry about updating values based on changes in the lookup table, and the storage issue is outweighed by the cost of the join.

But, for anything that isn't so stable Bob's advice is very sound. Or if you needed international references, the integer stored would make for a very clean Switch function translation.

 

[nayfeh]

Thanks so much guys. I think I left some info out when I was explaining my predicament (it was late)

The 3 fields: CustomerID, OrderDate, DeliveryDate linked to an ODBC table. The date fields can be any days of the week (not weekdays). Our work week is SUNDAY through SATURDAY.

My input form for the user has unbound text fields for Customer # and Starting Weekday. The subform retrives the corresponding records. So, for example....user enters customer # 1 with Start Del. Day: Sunday. This retrieve any deliveries from Sunday to Saturday.

Now, within the subform, I have appropriate code that allows the user to append to the table, but how can

With Me
.CustomerID = Forms![frmSchedule].txtCustomerID
.OrderDate = .txtOrderDate
.DeliveryDate = .txtDelivery


Now, there are no limitations are to what the user can enter for OrderDate or DeliveryDate. I'd like to have a validation within this subform that checks if the values are either Sunday through Saturday.

Thanks for the help guys.

TN

 

[LittleSmudge]

Okay TN, so have two combo boxes, similar to my original post and have them Unbound instead
Call them cboOrderDate and cboDelivery

then change your code to

With Me
.CustomerID = Forms![frmSchedule].txtCustomerID
.OrderDate = cboOrderDate
.DeliveryDate = cboDelivery



QED?

G LS