Desperate for help

[Vella]

Duane has been helping me quite a bit, however, I need to get this script
(his script) to work on my form...ASAP. Even I can't be on the computer 24/7

Can anyone tell me why this is not working? (aside from stupidity on my part
....this has been established)

Private Function 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

I am calling this function with the Got Focus for ATime field
UpdateRowSourceProp().

What I need to establish is that if a time is entered for a date that
already has that time then the list should only be populated by those times
not selected on that day. At this point, i get a blank list. However, when i
look at the properties for ATime, the rowsource is filled with the SQL
statement. VERY ODD.

Any ideas?

 

[J. Goddard]

Hi Vella -

(Sorry - I had meant to get back to you and didn't)

... However, when i look at the properties for ATime, the rowsource

is filled with the SQL statement. VERY ODD.

That's what the rowsource property should have. You will often see the
last SQL that was used in a list box in that property.

Can you bear any more questions?

What is the data type and size of the field ApptTime in the table
AppointmentTimes? Can you give some example of what the data looks
like? How many records are in that table?

Is the list box Column Count set to 1 , and is the bound column set to 1?

Does table1 contain blank records for appointments at all possible times
on any given date, even times for appointments that have not been filled
yet? In other words, if there are NO appointments scheduled for
tomorrow, then there should be NO records in table1 with tomorrow's date.

"(aside from stupidity on my part ...this has been established)"
Don't confuse lack of knowledge with stupidity - not the same at all
(though too many people have a great deal of difficulty with that concept)

Let's hope the answers to these get us pointed in the right direction.

John

 

[Duane Hookom]

Can you tell us anything about the properties of the list box such as the
Column Widths, Bound Column, Number of columns?
What happens if you remove the "Private " since clearly you want to expose
this function to event properties?
Did you include the "=" in the property?

Can you create a command button and set the On Click event to:
=UpdateRowSourceProp()

Do you get a pop-up if you use:
Public Function 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
MsgBox "Your Row Source is: " & Me.ATime.RowSource
End Function

 

[Vella]

John~
“What is the data type and size of the field ApptTime in the table
AppointmentTimes? Can you give some example of what the data looks
like? How many records are in that table?â€

ApptTime in AppointmentTimes table is a Date/Time field. Should this be
something else?

“Is the list box Column Count set to 1 , and is the bound column set to 1?â€

Yes and Yes.

“Does table1 contain blank records for appointments at all possible times
on any given date, even times for appointments that have not been filled
yet? In other words, if there are NO appointments scheduled for
tomorrow, then there should be NO records in table1 with tomorrow's date.â€

Yes, there are NO records in Table1 without all fields filled. No empty
records.

Duane~
“Can you tell us anything about the properties of the list box such as the
Column Widths, Bound Column, Number of columns?
What happens if you remove the "Private " since clearly you want to expose
this function to event properties?
Did you include the "=" in the property?â€

The Number of Columns =1 and the Bound Column =1. The Column width is blank.
I have tried both private and public with the function and get just about
the same results. (no populated drop down list.)
Yes I did include the ‘=’ in the event property.

“Can you create a command button and set the On Click event to:
=UpdateRowSourceProp()â€

Ok, bear with me on this one? Shouldn’t the click on the drop list box work
just the same? I really have to use the list box...do you think the list box
is the problem?


“Do you get a pop-up if you use:
Public Function 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
MsgBox "Your Row Source is: " & Me.ATime.RowSource
End Functionâ€

This is what is returned:
Your RowSource is:
SELECT ApptTime FROM AppointmentTimes WHERE ApptTime not in (SELECT
Appointment_Time FROM Table1 WHERE Appt_Date = #8/1/2006#);

Still the list box isn’t populated.

 

[Vella]

So, I'm trying to play with a ton of different options since I don't really
know what the heck I'm doing.
I just put in the following:
on the form design properties for the ATime list box, I changed everything to:
RowSource Type: Table/Query
RowSource: Tracker

the code in the Tracker query is:

SELECT AppointmentTimes.ApptTime
FROM AppointmentTimes
WHERE (((AppointmentTimes.ApptTime) Not In (Select Table1.Appointment_Time
from Table1 where Table1.Appt_Date = me.Appt_Date AND TABLE1.WORKER =
ME!WORKER)));

The problem now, is that when the list box is selected, one box pops up,
asking for dates and one box pops up asking for names. Once these are
entered, the correct data populates the list box but I really want to get rid
of the pop ups.
Any ideas?

 

[Duane Hookom]

If you would like, you can email me off line to get instructions on how to
send me a copy of your file.
Use duaneAThookomDOTnet.

 

[John Vinson]

The problem now, is that when the list box is selected, one box pops up,
asking for dates and one box pops up asking for names. Once these are
entered, the correct data populates the list box but I really want to get rid
of the pop ups.

Me. is useful in VBA code - but not in queries (it will generate a
prompt as you see). Change this to

SELECT AppointmentTimes.ApptTime
FROM AppointmentTimes
WHERE (((AppointmentTimes.ApptTime) Not In (Select
Table1.Appointment_Time
from Table1
where Table1.Appt_Date = [Forms]![yourformname]![Appt_Date]
AND TABLE1.WORKER = [Forms]![yourformname]![WORKER])));

using the actual name of your form in place of yourformname.

John W. Vinson[MVP]

 

[Vella]

Duane,
I sent you an email from work but I wonder if you got it?
I changed AT and Dot to the symbols...
I'd love to have some input on this monster I've created.

 

[Vella]

John,
Thank you so much. This helped me get the thing...kinda sorta where it
should be. I really think I need to script this in VB so that it will requery
without having to F9 or save before going to the next entry. I am working
with people who are VERY computer illiterate and I have to make it as simple
for them as possible.
YOU ROCK.
v~

The problem now, is that when the list box is selected, one box pops up,
asking for dates and one box pops up asking for names. Once these are
entered, the correct data populates the list box but I really want to get rid
of the pop ups.

Me. is useful in VBA code - but not in queries (it will generate a
prompt as you see). Change this to

SELECT AppointmentTimes.ApptTime
FROM AppointmentTimes
WHERE (((AppointmentTimes.ApptTime) Not In (Select
Table1.Appointment_Time
from Table1
where Table1.Appt_Date = [Forms]![yourformname]![Appt_Date]
AND TABLE1.WORKER = [Forms]![yourformname]![WORKER])));

using the actual name of your form in place of yourformname.

John W. Vinson[MVP]

 

[Duane Hookom]

I don't recall getting it unless you were the person who without asking sent
me an email with an attached Excel file.

 

[Vella]

Nope that wasn't me. So, I wonder what I did wrong with your address?
To be honest, I've never seen that before.
duaneAThookomDOTnet

just change out the at and the dot for their respective symbols?

 

[Duane Hookom]

Your de-mudging should work.

 

[Duane Hookom]

Reviewed the field and found the function was not being called from any
event on the form.