Linking to update fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

buzz - 07 Jun 2005 20:07 GMT

I've got two fields on a form and I'm trying to input data from one field to
change the output on another field. Field one that I'm inputting data into
is a combo box listing values (from a table) that I can select from. Field
two is a list box to show the output of the below statement (could be
multiple rows):

SELECT DocFamily.Tranlogs, DocFamily.Interleave, DocFamily.[Media Size] FROM
DocFamily WHERE DocFamily.[family name]=[Combo20];

The main issue I am experiencing is that the data in the list box for field
two does not change. If I enter into the form displaying something in
Combo20 (which is field one), then the list box (field two) displays what I
want it to but if I change the data in Combo20, I want the data to also
change simultaneously on the list box (field two) which is not occurring.
What am I missing??

Reply to this Message

Albert D.Kallal - 07 Jun 2005 20:30 GMT

> SELECT DocFamily.Tranlogs, DocFamily.Interleave, DocFamily.[Media Size]
> FROM
> DocFamily WHERE DocFamily.[family name]=[Combo20];

if the listbox data source is the above sql, then you MUST FULLY qualify the
reference to combo20. (you might have 5 forms open...which value does combo
20 refer to ?

So, in the query builder, you can use:

> SELECT DocFamily.Tranlogs, DocFamily.Interleave, DocFamily.[Media Size]
> FROM
> DocFamily WHERE DocFamily.[family name]=[forms]![MyFormName]![Combo20];

In the comb box after update event, you probably do have to tell the listbox
that the combo box has changed. So, you would put in the comb box after
update.

me.MylistBox.Requery

I never did like sql with direct references to forms, as they are hard to
read, messy, and force the sql to ONLY be used when the particular form is
opened (and, thus as a result the query can only be used for one thing).

You could use the after update event of the comb box, and stuff the sql
directly into the listbox as follows:

dim strSql as string

strSql = "SELECT Tranlogs, Interleave, [Media Size] FROM DocFamily " & _
" WHERE [family name] = '" me.Combo20 & "'"

me.ListBox.RowSource = strSql

There is many other approaches, and the best one is gong to be based on your
particular situation, and how the controls will be used.

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

Reply to this Message