Possible VBA issue with cbo / break and Medium Time? (1 Viewer)

[theoret]

Possible VBA issue with cbo and Medium Time?

Hello everyone,

New to the forums. I'm hoping someone would be able to spot why I'm unable to get this combo box to show as Medium Time. I've changed everything I've thought would control it in table properties. By belief is that it has something to do with the VBA code that was written. I have been able to change all others TIME to Medium Time. My apologizes if this is not VBA related and in the wrong section.

I'm slowly learning and this was written following an online tutorial. I have been trying to make modifications and would like the cbo box to read;

2:00 PM rather than 2:00:00 PM.

----
I have attached the database those those that may be able to take a quick look.

mainform
*cboTime


(I also haven't been able to figure out how to remove the Break from the tblSchedule without breaking everything. I only require a schedule between 2 - 4 PM and we do not have a break window during that time.)

Any assistance is appreciated.

Theo.

 

[Cronk]

The row source of your combo is being set in the code in the line

cboTime.AddItem i

If you were to change that to

cboTime.AddItem Format(i, "Medium time")

it should give you what you want.

But as it seems you only want 15 minute intervals between 2pm and 4pm then replace the whole procedure with

sub cboTime_enter()
   dim dteTime As date
   
   me.cboTime.rowwource = ""
   dtetime = #2:00:00 PM#
   do while dtetime <= #4:00:00 PM#
      if dtetime >= time() Then
         me.cboTime.AddItem Format(dtetime, "Medium time")
      end if
      dtetime = dateadd("n", 15, dtetime)
   loop
end sub

 

[theoret]

Re: Possible VBA issue with cbo and Medium Time?

The row source of your combo is being set in the code in the line

cboTime.AddItem i

If you were to change that to

cboTime.AddItem Format(i, "Medium time")

This is much better, thank you. Any idea why it shows as 02:00 PM rather than 2:00 PM as the other Medium Times are?

But as it seems you only want 15 minute intervals between 2pm and 4pm then replace the whole procedure with

sub cboTime_enter()
   dim dteTime As date
   
   me.cboTime.row[COLOR="Red"]s[/COLOR]ource = ""
   dtetime = #2:00:00 PM#
   do while dtetime <= #4:00:00 PM#
      if dtetime >= time() Then
         me.cboTime.AddItem Format(dtetime, "Medium time")
      end if
      dtetime = dateadd("n", 15, dtetime)
   loop
end sub

The combo box is coming up empty when I clear it and put this on enter.
I've also created a new cboTime to double check there weren't any lingering field entries. If this works as I read it, this would be a more appreciated version. Would this also remove the already selected appointment times from the dropdown?

You are correct. The appointment window is between 2 - 4 pm. With 15 min appoinments. Only 1 appointment per 15 mins.

Thank you for your feedback Cronk.

 

[Cronk]

If you want to suppress the leading zero in the time, then use

format(dtetime, "h:nn AMPM")

There's nothing wrong with the code I supplied. If you click on the combo after 4 pm it will always be blank.

Does the code run for you? Have you tried setting a break point and stepping through? I noticed you do not enforce variable declaration which is good practice. Add the line

Option explicit

at the top of the code module.

The system will automatically generate the line on creating a new module if you go Tools | Options and check the Declare Variables box.

 

[theoret]

I had suspected that was the case based on the code but vba is still a work in progress with me.

Your supplied code works. Though when I change the date to tomorrow for example, the appointment times are still hidden. In this case they should be visible to allow selection for anyone wishing to book a day out.

Thanks again Cronk. You've been spot on and it's well appreciated.

 

[Cronk]

About times for tomorrow, the algorithm in your code only shows times if the system time on the PC is less than 4 pm.

Run the code in the morning, and the times will be populated for a date not only tomorrow but any date in the future or the past.

I'd suggest you develop your logic on a bit of paper then code it, rather than the other way around.