Access list box

[Guest]

Based on a scheduling database
I would like a list box to be populated based on times a certain worker DOES
NOT have an appointment.

ie: Dave has appointments at 7:15 & 8:00 on the 8th of october. If the
secretary goes in to add an appointment, those times should NOT show in the
drop down list.

I'm pretty new at all of this so indepth answers would be most
appreciated...aka write dummy proof answers please.

Thanks so much,
Vella

 

[Duane Hookom]

Are we supposed to know how/where you are storing existing appointments?
I think we need some information about:
-Tables and Fields
-Time intervals
-Sample records

 

[Guest]

Duane,
the form Schedule has three fields I need to compare. Appointment_Date,
Worker, Appointment_time.
Appt_Time is the table where all times for Appointment_time list box are
stored.
What I need to do is to see...
if a worker wants to make an appointment on a given day and already has an
appointment, that time should not populate the list box.

This is the Query I am running to check it. However, a date box pops up
every time and I also have to find a way to make the form refresh instead of
pushing the f9 key after each record entry.

SELECT ApptTime from AppointmentTimes
WHERE ApptTime not in (Select
Table1.Appointment_Time from Table1 where
Table1.Date = me!Date);

 

[Duane Hookom]

Can we assume the sql you provided is the Row Source property of the combo
box? If so, try use code to change the row source to:

strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Table1.Appointment_Time from Table1 where " & _
"Table1.Date = #" & Me!Date & "#);"

 

[Guest]

Duane,
Yes, I am using a sql query as the row source. I understand that the code
you wrote is VB but I am unsure of how to place it in order for it to be used
as a row source. Would that be an Onclick event...? totally lost.

Newbies can be such a trial! I appologize.

 

[Duane Hookom]

I would write a function in the form's module
Public Function UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"[Date] = #" & Me!Date & "#);"
Me.lboSelectTime.RowSource = strSQL
End Function

You can place this function in the Form On Current and Me.Date After Update
event properties.


--
Duane Hookom
MS Access MVP

Duane,
Yes, I am using a sql query as the row source. I understand that the code
you wrote is VB but I am unsure of how to place it in order for it to be
used
as a row source. Would that be an Onclick event...? totally lost.

Newbies can be such a trial! I appologize.

 

[Guest]

Duane,
I must be very simple. I thought I had put everything in the appropriate
place(s).
Yet, I get an error code of : can not find object and the lboSelectTime is
highlighted. Or when I make adjustments, it just doesn't work.
I know this is entirely due to the fact that I am not putting things where
they belong.
I'm sure this is frustrating to you but if you could see your way clear to
walking me through this process, I would appreciate that very much.

I would write a function in the form's module
Public Function UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"[Date] = #" & Me!Date & "#);"
Me.lboSelectTime.RowSource = strSQL
End Function

You can place this function in the Form On Current and Me.Date After Update
event properties.

 

[Duane Hookom]

Since I was not sure what your list box of times was named, I provided one
for you of "lboSelectTime". Substitute your actual list box name or change
your name to match the code.

--
Duane Hookom
MS Access MVP

Duane,
I must be very simple. I thought I had put everything in the appropriate
place(s).
Yet, I get an error code of : can not find object and the lboSelectTime is
highlighted. Or when I make adjustments, it just doesn't work.
I know this is entirely due to the fact that I am not putting things where
they belong.
I'm sure this is frustrating to you but if you could see your way clear to
walking me through this process, I would appreciate that very much.

I would write a function in the form's module
Public Function UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"[Date] = #" & Me!Date & "#);"
Me.lboSelectTime.RowSource = strSQL
End Function

You can place this function in the Form On Current and Me.Date After
Update
event properties.


--
Duane Hookom
MS Access MVP

Duane,
Yes, I am using a sql query as the row source. I understand that the
code
you wrote is VB but I am unsure of how to place it in order for it to
be
used
as a row source. Would that be an Onclick event...? totally lost.

Newbies can be such a trial! I appologize.

:

Can we assume the sql you provided is the Row Source property of the
combo
box? If so, try use code to change the row source to:

strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Table1.Appointment_Time from Table1 where " & _
"Table1.Date = #" & Me!Date & "#);"

 

[Guest]

Duane,
This just goes to prove that time should be taken to concentrate on what
you're doing...specially when you're multi-tasking. Will change things around
at work today.
Thanks so very much.

 

[Guest]

Duane,
I did as you said but I think I may have done it incorrectly. I created a
module for the form. I placed your code in it with my data fields.
Then, I placed the code in a private sub form for the got focus event on
Atime. This didn't work so I thought I needed to call the module function.
However, I must be doing that incorrectly as well. How does one call a
function in VB for the private sub?
Would it be:
UpdateRowSourceProp
or is it much more complicated than this?
I really do appreciate the time you've spent walking me through this process.

Since I was not sure what your list box of times was named, I provided one
for you of "lboSelectTime". Substitute your actual list box name or change
your name to match the code.

--
Duane Hookom
MS Access MVP

Duane,
I must be very simple. I thought I had put everything in the appropriate
place(s).
Yet, I get an error code of : can not find object and the lboSelectTime is
highlighted. Or when I make adjustments, it just doesn't work.
I know this is entirely due to the fact that I am not putting things where
they belong.
I'm sure this is frustrating to you but if you could see your way clear to
walking me through this process, I would appreciate that very much.

I would write a function in the form's module
Public Function UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"[Date] = #" & Me!Date & "#);"
Me.lboSelectTime.RowSource = strSQL
End Function

You can place this function in the Form On Current and Me.Date After
Update
event properties.


--
Duane Hookom
MS Access MVP


Duane,
Yes, I am using a sql query as the row source. I understand that the
code
you wrote is VB but I am unsure of how to place it in order for it to
be
used
as a row source. Would that be an Onclick event...? totally lost.

Newbies can be such a trial! I appologize.

:

Can we assume the sql you provided is the Row Source property of the
combo
box? If so, try use code to change the row source to:

strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Table1.Appointment_Time from Table1 where " & _
"Table1.Date = #" & Me!Date & "#);"

 

[Duane Hookom]

You can try:
Form: On Current: =UpdateRowSourceProp()
After Update: =UpdateRowSourceProp()

--
Duane Hookom
MS Access MVP

Duane,
I did as you said but I think I may have done it incorrectly. I created a
module for the form. I placed your code in it with my data fields.
Then, I placed the code in a private sub form for the got focus event on
Atime. This didn't work so I thought I needed to call the module function.
However, I must be doing that incorrectly as well. How does one call a
function in VB for the private sub?
Would it be:
UpdateRowSourceProp
or is it much more complicated than this?
I really do appreciate the time you've spent walking me through this
process.

Since I was not sure what your list box of times was named, I provided
one
for you of "lboSelectTime". Substitute your actual list box name or
change
your name to match the code.

--
Duane Hookom
MS Access MVP

Duane,
I must be very simple. I thought I had put everything in the
appropriate
place(s).
Yet, I get an error code of : can not find object and the lboSelectTime
is
highlighted. Or when I make adjustments, it just doesn't work.
I know this is entirely due to the fact that I am not putting things
where
they belong.
I'm sure this is frustrating to you but if you could see your way clear
to
walking me through this process, I would appreciate that very much.

:

I would write a function in the form's module
Public Function UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"[Date] = #" & Me!Date & "#);"
Me.lboSelectTime.RowSource = strSQL
End Function

You can place this function in the Form On Current and Me.Date After
Update
event properties.


--
Duane Hookom
MS Access MVP


Duane,
Yes, I am using a sql query as the row source. I understand that
the
code
you wrote is VB but I am unsure of how to place it in order for it
to
be
used
as a row source. Would that be an Onclick event...? totally lost.

Newbies can be such a trial! I appologize.

:

Can we assume the sql you provided is the Row Source property of
the
combo
box? If so, try use code to change the row source to:

strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Table1.Appointment_Time from Table1 where " & _
"Table1.Date = #" & Me!Date & "#);"

 

[Guest]

Ok. I tried this and when I compile, I get the msg:
Invalid use of the Me keyword.

 

[Duane Hookom]

"Me." should work if your code is in the module of the form containing the
controls.

 

[Guest]

Duane,
When I run the form and click on the ATime field, I get the following error:
"The expression On Got Focus you entered as the event property setting
produced the following error:
The expression you entered has a function name that Microsoft Access can't
find."
I have put the code you wrote in form module with the same name I called
using the expression builder (to avert spelling errors).
The module is called UpDateRowSourceProp(). It is a public function for this
worksheet. (I never could find how to make a private module for the form only)
When I compile, I get the error:
Invalid use of the Me keyword.

So, do you think that maybe I put everything in the wrong place?

 

[Duane Hookom]

"is a public function for this worksheet"?

Duane,
When I run the form and click on the ATime field, I get the following
error:
"The expression On Got Focus you entered as the event property setting
produced the following error:
The expression you entered has a function name that Microsoft Access can't
find."
I have put the code you wrote in form module with the same name I called
using the expression builder (to avert spelling errors).
The module is called UpDateRowSourceProp(). It is a public function for
this
worksheet. (I never could find how to make a private module for the form
only)
When I compile, I get the error:
Invalid use of the Me keyword.

So, do you think that maybe I put everything in the wrong place?

 

[Guest]

sorry, my semantics are off. I meant, Public Function for this Form...
I did go back and try to make that a Private Function for the form with
little to no difference.

 

[Duane Hookom]

Confirm for me:
Your function is in the form's module. Your use of "The module is called
UpDateRowSourceProp()" suggests you had the opportunity to name a module
which you can't if the function is in a form's module. When viewing the
code, the title bar of the window should state something like:
Microsoft Visual basic - [mdb name] -[Form-....(Code)]

Also, reply back with your exact function.

 

[Guest]

the form as seen:

timesheets - form_scheduler (Code)

General UpdateRowSourceProp

Option Compare Database

PrivateFunction UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"Appt_Date = #" & Me!Appt_Date & "#);"
Me!ATime.RowSource = strSQL
End Function

The problem I am having is that the Atime combolist is not being populated.

Confirm for me:
Your function is in the form's module. Your use of "The module is called
UpDateRowSourceProp()" suggests you had the opportunity to name a module
which you can't if the function is in a form's module. When viewing the
code, the title bar of the window should state something like:
Microsoft Visual basic - [mdb name] -[Form-....(Code)]

Also, reply back with your exact function.


--
Duane Hookom
MS Access MVP

sorry, my semantics are off. I meant, Public Function for this Form...
I did go back and try to make that a Private Function for the form with
little to no difference.

 

[Duane Hookom]

What are you doing to trigger the function?

--
Duane Hookom
MS Access MVP

the form as seen:

timesheets - form_scheduler (Code)

General UpdateRowSourceProp

Option Compare Database

PrivateFunction UpdateRowSourceProp()
Dim strSQL as String
strSQL = "SELECT ApptTime from AppointmentTimes " & _
"WHERE ApptTime not in (Select " & _
"Appointment_Time from Table1 where " & _
"Appt_Date = #" & Me!Appt_Date & "#);"
Me!ATime.RowSource = strSQL
End Function

The problem I am having is that the Atime combolist is not being
populated.

Confirm for me:
Your function is in the form's module. Your use of "The module is called
UpDateRowSourceProp()" suggests you had the opportunity to name a module
which you can't if the function is in a form's module. When viewing the
code, the title bar of the window should state something like:
Microsoft Visual basic - [mdb name] -[Form-....(Code)]

Also, reply back with your exact function.


--
Duane Hookom
MS Access MVP

sorry, my semantics are off. I meant, Public Function for this Form...
I did go back and try to make that a Private Function for the form with
little to no difference.

:

"is a public function for this worksheet"?


Duane,
When I run the form and click on the ATime field, I get the
following
error:
"The expression On Got Focus you entered as the event property
setting
produced the following error:
The expression you entered has a function name that Microsoft Access
can't
find."
I have put the code you wrote in form module with the same name I
called
using the expression builder (to avert spelling errors).
The module is called UpDateRowSourceProp(). It is a public function
for
this
worksheet. (I never could find how to make a private module for the
form
only)
When I compile, I get the error:
Invalid use of the Me keyword.

So, do you think that maybe I put everything in the wrong place?

 

[Guest]

in form design, I have chosen the On Got Focus from the Event menu I put:
= UpdateRowSourceProp()