Getting a date field to change a combo box

[Heather99]

I am trying to get a date to change the value in a combo box, and am having some trouble. Essentially, if the date entered in the field is equal to the current date, I want the combo box to display 'Available'. I've tried a bunch of different ways, and can't get it to work everytime. Any suggestions?

 

[Topher]

hmmm what event are you using to check when the date changes??

i believe you want to put your code in the AfterUpdate event of the txtbox where you enter in the date. the code will only run, once the cursor moves out of the txtbox.

how do you have your information set up in the combo box?? is it based on a table or did you fill it yourself?

if its based on a table you could use code such as

cboAvailable.Value = #

where # would be a row in the table the combobox is linked to

# Description
1 Available
2 Not Available

what is the code u are using to compare the dates?

hope some of this helps
- Topher

 

[Heather99]

Thanks for your help! It sort of worked...but not completely. I put this code in the AfterUpdate of the date field and OnActivate for the form:
If dteAvailable <= Date Then
ctrStaffStatus.Value = "Available"

Essentially, when the form opens, I want it to check the date in date field (which could be in the future), and if that day is today, change the combo box to "Available."

The above code worked, but only on the first record. It didn't affect any of the others.

 

[Topher]

when u say it didnt work on the other records is it b/c you are cycling thru them??

hmmm if you are - try putting it on the On Change event...

actually you should be able to put it on the On Current event. that event is supposed to control the current record - so when you change from one rec to another it'll run your check.

- Topher

 

[Heather99]

Yes that worked! Thanks so much Topher!

 

[Topher]

Welcome =D

you dont know anything about changing the properties on 40 forms at once do you??

 

[Heather99]

Sorry, I have no idea...I'd put that out as a new post!

I actually just realized that this still won't totally work...the only way to run the check is if I flip through every record. I need it to run the check on all records automatically, pretty much when the database opens. I think I need some sort of End of Recordset loop, but I'm not sure how to make it happen. Think I should post in the VBA/modules forum?

 

[Topher]

hmmm i might be able to help ya.

ok if you want it to check the dates at the startup then the dates that are entered are saved in your db right??

if so then you would have to create a recordset via a query - that checks the dates in the table to the current date. and also open a recordset of the table(s) where the date and availability fields are. then in code, you can change the availability field in the table by comparing say ID #'s with the query and table. follow me?

i did something simliar - see if the code below helps

set rst1 = db.openrecordset ("qryDates")
set rst2 = db.openrecordset ("tblDates",dbopendynaset)

dim strID as string
dim strCriteria as string

Do
With rst1
strID = !ID 'this gets the ID# for the 1st recordset
criteria = "[ID] = " & strID ' this will create the string variable needed to search the 2nd recordset for the ID#

With rst2
'find the first matching ID#'s
.FindFirst criteria

If Not .NoMatch Then 'meaning if there is a match
.Edit
![availability] = Yes or No - whatever you want
.Update
Else
MsgBox "No Records Match", vbOKOnly
End If
End With
End With
Until rst1.EOF

theres code in there to gaurd against some errors as well - but that should work or something like it.

- Topher