Re: Scripting conditional list box


Hiyas John,
I did everything you said, right down to matching fields meticulously. For
some reason, the drop down box(ATime) won't populate. I wonder if the
Appt_Date field needs to be forced into Table1 before this will work? (or
does this happen dynamically?)

I'm a bit weary of adding any more script but I will have to add worker ID
to this as well. *sigh* I wish I knew where I was going wrong.

"J. Goddard" wrote:

Hi Vella -

You said this:
> I realized this had to be a VB code so I put this in under form
properties
> (code builder) as an On Focus for the list box ATime. ...

I'm not sure what you mean by "Form Properties ... as On Focus for the
list Box", but I think you just did not word it right. Anyway, try this:

In the properties for the List Box, put this code in the "on Got Focus"
event. The code SQL = .... has to be all on one line - your browser is
probably wrapping it so it looks like it is on several lines:


'========
Dim SQL as string

SQL = "SELECT ApptTime FROM AppointmentTimes WHERE ApptTime not in
Select Table1.Appointment_Time from Table1 where Table1.Date = #" &
me!Date & "#)";

me!atime.rowsource = sql
'========

Replace AppointmentTime with the name of the table containing the list
of times
Replace ApptTime with the name of the time field in that table (DON'T
call it "Time")
Replace Table1 with the name of your table of Appointments
Replace Date in Table1.date with the dame of the Appointment Date field
(Dont call it "Date")
Replace Date in me!date with the name of the form control containing the
requested date (Don't call it "date")

Before closing the code window, from the Menu, click Debug - Compile.
Any errors?

John

P.S.
> ...I had set up a query called Time that executed when the list
> box was clicked on... this didn't really work as it popped up a box
asking for the date before the results populated the list box.

This might have been working, except that you called the query "Time".
Try renaming the query, because "Time" is an MS Access reserved word.

J





Vella wrote:
hiyas John,
ok. at first I had set up a query called Time that executed when the list
box was clicked on... this didn't really work as it popped up a box asking
for the date before the results populated the list box.
Then you gave me this code:

SQL = "SELECT ApptTime
FROM AppointmentTimes
WHERE ApptTime not in (Select
Table1.Appointment_Time from Table1 where
Table1.Date = #" & me!Date & "#)";

I realized this had to be a VB code so I put this in under form properties
(code builder) as an On Focus for the list box ATime. Still, it wouldn't
populate the list box. I have to wonder if I didn't use the wrong syntax?
This VB coding is a bit harsh for a newbie.
So, I think I might need a bit more explicit (read:) dummy proof instruction?
"J. Goddard" wrote:


Hi -

It sounds like you may be using the SQL statement in a db.execute or
docmd.runsql or something like that. Can you post your code to show how
you are using it, please?

Thanks

John


Vella wrote:


Thanks John,
ok...
I tried the statement that you provided and got an error "Invalid SQL
statement: expected 'DELETE', 'INSERT'...ECT."
So, I'm thinking I needed to deliniate something in there? Feeling rather
silly.

As far as employee, you are right, employee number would be best to negate
redundancies. So, If I check for the employee name and time, then this should
be a perfect statement for my needs. Only, Im feeling slightly lost now.





.

Relevant Pages

  • Re: Query will not populate with data
    ... I then created a query that combined the two mentioned above. ... query does not populate properly. ... 5 are access tables and one is a SQL table. ... I need to connect to the Client table via the NFS_Data table and the FS_Data ...
    (microsoft.public.access.queries)
  • Re: Populate table with results from search
    ... search button which opens frmSearch. ... You can populate record on many ways, maybe with SQL command as best ... Create query which do that and simply call query, ...
    (microsoft.public.access.forms)
  • Re: Populate table with results from search
    ... search button which opens frmSearch. ... You can populate record on many ways, maybe with SQL command as best ... Create query which do that and simply call query, ...
    (microsoft.public.access.forms)
  • Sql query from Access returns no records, but SQL Query Analyser d
    ... I want to populate a temporary table '#tablename' with the results of ... serveral queries and then pass all the records back to a recordset for ... the coding is in Access 2003 database linked to the SQL. ... If I run it from SQL Query ...
    (microsoft.public.access.queries)
  • Re: Re: Inserting large amounts of data
    ... > I'm using DTS to populate a staging table with raw streams of data (a ... I thought about imbedding the SQL ... Doing the substringing in T-SQL is not the ...
    (comp.databases.ms-sqlserver)
Loading