primary key in 1 table is negative in another

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry
 
You can do this in the SQL statement. But note that, once done, you will not
be able to view the query in the design view without "losing" the setup.

What you do is to create the query in design view, including making the join
between Table 1 and Table 3 on the appropriate fields. Then, change to SQL
view, where you'll see the actual SQL statement. In that statement, you'll
see a "text string" similar to this:

ON [Table 1].ID = [Table 3].ID

Simply insert a hyphen (negative sign) after the = sign:

ON [Table 1].ID = -[Table 3].ID

Now the join will work as you desire. Be sure to save the query while in the
SQL view.

An alternative approach would be to create a query that is based on Table 3
(include all fields from Table 3), and add a calculated field to that query:
JoinFieldID: -[ID]

Then, instead of using Table 3 in your original query design, use this new
query, and then do the join to the created query on the calculated field
instead of the ID field.
 
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
.... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP
 
Steve Schapel said:
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP


I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry
Click to expand...
Click to expand...

I fooled around and came up with the following SQL query that seems to
work:
Any comments appreciated.

SELECT [Clients].[ID], [Clients].[Cltsort], [Clients].[Cltname],
[Clients].[Cinvname], [Clients].[CInvAddr1], [Clients].[CInvAddr2],
[Clients].[CInvAddr3], [Clients].[Cinvcity], [Clients].[Cinvst],
[Clients].[Cinvzip], [ClientContact].[ClcFname],
[ClientContact].[ClcContact], [ClientContact].[ClcCltID],
[ClientContact].[ClcLname], [ClientContact].[clcInvAddr1],
[ClientContact].[clcInvAddr2], [ClientContact].[clcInvAddr3],
[ClientContact].[clcInvcity], [ClientContact].[clcInvst],
[ClientContact].[clcInvZip], [MailersSelected].[MailSelID],
[MailersSelected].[MailSelNumber]
FROM Clients RIGHT JOIN (ClientContact RIGHT JOIN MailersSelected ON
[ClientContact].[ClcContact]=[MailersSelected].[MailSelID]) ON
[Clients].[ID]=-[MailersSelected].[MailSelID];

Now I want to go 1 step further. There is a Table called Mailers. It
has MailerNumbers and MailersDescriptions. What I want to now do is
have a user input a particular MailerNumber based on showing him the
MailerNumbers and MailerDescriptions in the Mailers Table. Then use
the user's input of MailerNumber to add sort of a criteria to the
above SQL of "Where MailerNumber = user input".

Thanks.

Barry
 
Easiest way to let user select an item and use it in the query is to create
a form and put a combo box and command button on the form.

Use a query as the Row Source for the combo box, and have that query show
all the values from which the user can select. Then run code/macro on the
form that opens the query. Use this criterion expression to read the value
of the combo box in the query:

WHERE Mailers.MailerNumber = [Forms]![FormName]![ComboboxName]


Note that you'll need to add the Mailers table into the query as well.


--
Ken Snell
<MS ACCESS MVP>

Barry said:
Steve Schapel <[email protected]> wrote in message
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP


I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry
Click to expand...
Click to expand...

I fooled around and came up with the following SQL query that seems to
work:
Any comments appreciated.

SELECT [Clients].[ID], [Clients].[Cltsort], [Clients].[Cltname],
[Clients].[Cinvname], [Clients].[CInvAddr1], [Clients].[CInvAddr2],
[Clients].[CInvAddr3], [Clients].[Cinvcity], [Clients].[Cinvst],
[Clients].[Cinvzip], [ClientContact].[ClcFname],
[ClientContact].[ClcContact], [ClientContact].[ClcCltID],
[ClientContact].[ClcLname], [ClientContact].[clcInvAddr1],
[ClientContact].[clcInvAddr2], [ClientContact].[clcInvAddr3],
[ClientContact].[clcInvcity], [ClientContact].[clcInvst],
[ClientContact].[clcInvZip], [MailersSelected].[MailSelID],
[MailersSelected].[MailSelNumber]
FROM Clients RIGHT JOIN (ClientContact RIGHT JOIN MailersSelected ON
[ClientContact].[ClcContact]=[MailersSelected].[MailSelID]) ON
[Clients].[ID]=-[MailersSelected].[MailSelID];

Now I want to go 1 step further. There is a Table called Mailers. It
has MailerNumbers and MailersDescriptions. What I want to now do is
have a user input a particular MailerNumber based on showing him the
MailerNumbers and MailerDescriptions in the Mailers Table. Then use
the user's input of MailerNumber to add sort of a criteria to the
above SQL of "Where MailerNumber = user input".

Thanks.

Barry
Click to expand...
 
I've created a form with the 2 fields, with only the mailerdescription
field visible, and a command button to run the query. On the bottom
of the form (I did not put it there) is a box for record # and arrows.
When I select, let's say Mailerdescription#7, in the combo box the
bottom record number remains at record 1 and the form updates the
Mailers table by writing to record 1 mailerdescription#7. I don't
want any changing of any records on this form, how can I change this
behavior? Also, shouldn't the record # on bottom change when I select
different maildescriptions?

I noticed that when I run the query without using the form first, I
get prompted for a Mailernumber. I'm curious if there is any way to
do what the form does totaly in the SQL of the query? That is, read
the values of the 2 fields in the Mailers table and offer them to the
user for selection.

Thanks for your help.

Barry



Ken Snell said:
Easiest way to let user select an item and use it in the query is to create
a form and put a combo box and command button on the form.

Use a query as the Row Source for the combo box, and have that query show
all the values from which the user can select. Then run code/macro on the
form that opens the query. Use this criterion expression to read the value
of the combo box in the query:

WHERE Mailers.MailerNumber = [Forms]![FormName]![ComboboxName]


Note that you'll need to add the Mailers table into the query as well.


--
Ken Snell
<MS ACCESS MVP>

Barry said:
Steve Schapel <[email protected]> wrote in message
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP


On 1 Jan 2004 09:59:56 -0800, (e-mail address removed) (Barry) wrote:

I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry
Click to expand...

I fooled around and came up with the following SQL query that seems to
work:
Any comments appreciated.

SELECT [Clients].[ID], [Clients].[Cltsort], [Clients].[Cltname],
[Clients].[Cinvname], [Clients].[CInvAddr1], [Clients].[CInvAddr2],
[Clients].[CInvAddr3], [Clients].[Cinvcity], [Clients].[Cinvst],
[Clients].[Cinvzip], [ClientContact].[ClcFname],
[ClientContact].[ClcContact], [ClientContact].[ClcCltID],
[ClientContact].[ClcLname], [ClientContact].[clcInvAddr1],
[ClientContact].[clcInvAddr2], [ClientContact].[clcInvAddr3],
[ClientContact].[clcInvcity], [ClientContact].[clcInvst],
[ClientContact].[clcInvZip], [MailersSelected].[MailSelID],
[MailersSelected].[MailSelNumber]
FROM Clients RIGHT JOIN (ClientContact RIGHT JOIN MailersSelected ON
[ClientContact].[ClcContact]=[MailersSelected].[MailSelID]) ON
[Clients].[ID]=-[MailersSelected].[MailSelID];

Now I want to go 1 step further. There is a Table called Mailers. It
has MailerNumbers and MailersDescriptions. What I want to now do is
have a user input a particular MailerNumber based on showing him the
MailerNumbers and MailerDescriptions in the Mailers Table. Then use
the user's input of MailerNumber to add sort of a criteria to the
above SQL of "Where MailerNumber = user input".

Thanks.

Barry
Click to expand...
Click to expand...
 
Comments inline...
--
Ken Snell
<MS ACCESS MVP>

Barry said:
I've created a form with the 2 fields, with only the mailerdescription
field visible, and a command button to run the query.
Click to expand...

OK. But this isn't what you want to do. Instead, create the form and don't
base it on a table at all. Just create the form and, in design view, put two
combo boxes on the form. For one combo box, set its Row Source to be a query
that returns all the values that you want to use as selection options. Do
the same for the other combo box. These combo boxes are "unbound" and won't
change any values in your tables; they'll just let you select from the
values that are "appropriate".

On the bottom of the form (I did not put it there) is a box for record #
Click to expand...
and arrows.

This is normal for the form. These are called navigation buttons.
When I select, let's say Mailerdescription#7, in the combo box the
bottom record number remains at record 1 and the form updates the
Mailers table by writing to record 1 mailerdescription#7. I don't
want any changing of any records on this form, how can I change this
behavior?
Click to expand...

This behavior is because of how you created the form. See my comment above
for how to do this differently.
Also, shouldn't the record # on bottom change when I select
different maildescriptions?
Click to expand...

No, not the way you designed the form. The form that you've designed/created
would be used to make edits/changes to the values that are stored in the
table's fields. Changing the values won't move you to a different record --
at least not with this setup.
I noticed that when I run the query without using the form first, I
get prompted for a Mailernumber. I'm curious if there is any way to
do what the form does totaly in the SQL of the query? That is, read
the values of the 2 fields in the Mailers table and offer them to the
user for selection.
Click to expand...

Unless the form is open when you run the query, it will ask you for the
parameters (the [Forms]![FormName]![ComboboxName] value). There is no way to
avoid that with this setup. But based on what you've posted before, why
would you run the query without the form being open?

But, if you want to "read" values from a table as a criterion in a query,
you can use the DLookup function to find a value in a table, based on a
criterion. DLookup is discussed in the Help files. It would be used this
way:

WHERE Mailers.MailerNumber = DLookup("FieldName", "TableName",
"[PrimaryKeyName]= 'A Value'")

Thanks for your help.

Barry



"Ken Snell" <[email protected]> wrote in message
Easiest way to let user select an item and use it in the query is to create
a form and put a combo box and command button on the form.

Use a query as the Row Source for the combo box, and have that query show
all the values from which the user can select. Then run code/macro on the
form that opens the query. Use this criterion expression to read the value
of the combo box in the query:

WHERE Mailers.MailerNumber = [Forms]![FormName]![ComboboxName]


Note that you'll need to add the Mailers table into the query as well.


--
Ken Snell
<MS ACCESS MVP>

Barry said:
Steve Schapel <[email protected]> wrote in message
Click to expand...
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP


On 1 Jan 2004 09:59:56 -0800, (e-mail address removed) (Barry) wrote:

I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry

I fooled around and came up with the following SQL query that seems to
work:
Any comments appreciated.

SELECT [Clients].[ID], [Clients].[Cltsort], [Clients].[Cltname],
[Clients].[Cinvname], [Clients].[CInvAddr1], [Clients].[CInvAddr2],
[Clients].[CInvAddr3], [Clients].[Cinvcity], [Clients].[Cinvst],
[Clients].[Cinvzip], [ClientContact].[ClcFname],
[ClientContact].[ClcContact], [ClientContact].[ClcCltID],
[ClientContact].[ClcLname], [ClientContact].[clcInvAddr1],
[ClientContact].[clcInvAddr2], [ClientContact].[clcInvAddr3],
[ClientContact].[clcInvcity], [ClientContact].[clcInvst],
[ClientContact].[clcInvZip], [MailersSelected].[MailSelID],
[MailersSelected].[MailSelNumber]
FROM Clients RIGHT JOIN (ClientContact RIGHT JOIN MailersSelected ON
[ClientContact].[ClcContact]=[MailersSelected].[MailSelID]) ON
[Clients].[ID]=-[MailersSelected].[MailSelID];

Now I want to go 1 step further. There is a Table called Mailers. It
has MailerNumbers and MailersDescriptions. What I want to now do is
have a user input a particular MailerNumber based on showing him the
MailerNumbers and MailerDescriptions in the Mailers Table. Then use
the user's input of MailerNumber to add sort of a criteria to the
above SQL of "Where MailerNumber = user input".

Thanks.

Barry
Click to expand...
Click to expand...
Click to expand...
 
From what I've read and learned is that there is no way that a query's
SQL commands can open a form, please confirm I am correct in this?

The reason I'm trying to open a form in the query is that some of the
query results are going back to a word mail merge document. Word mail
merge documents can open a query but cannot open an Access form.

Thank you so much for your help. I think I've got everything working
as well as possible.

Barry


Ken Snell said:
Comments inline...
--
Ken Snell
<MS ACCESS MVP>

Barry said:
I've created a form with the 2 fields, with only the mailerdescription
field visible, and a command button to run the query.
Click to expand...

OK. But this isn't what you want to do. Instead, create the form and don't
base it on a table at all. Just create the form and, in design view, put two
combo boxes on the form. For one combo box, set its Row Source to be a query
that returns all the values that you want to use as selection options. Do
the same for the other combo box. These combo boxes are "unbound" and won't
change any values in your tables; they'll just let you select from the
values that are "appropriate".

On the bottom of the form (I did not put it there) is a box for record #
Click to expand...
and arrows.

This is normal for the form. These are called navigation buttons.
When I select, let's say Mailerdescription#7, in the combo box the
bottom record number remains at record 1 and the form updates the
Mailers table by writing to record 1 mailerdescription#7. I don't
want any changing of any records on this form, how can I change this
behavior?
Click to expand...

This behavior is because of how you created the form. See my comment above
for how to do this differently.
Also, shouldn't the record # on bottom change when I select
different maildescriptions?
Click to expand...

No, not the way you designed the form. The form that you've designed/created
would be used to make edits/changes to the values that are stored in the
table's fields. Changing the values won't move you to a different record --
at least not with this setup.
I noticed that when I run the query without using the form first, I
get prompted for a Mailernumber. I'm curious if there is any way to
do what the form does totaly in the SQL of the query? That is, read
the values of the 2 fields in the Mailers table and offer them to the
user for selection.
Click to expand...

Unless the form is open when you run the query, it will ask you for the
parameters (the [Forms]![FormName]![ComboboxName] value). There is no way to
avoid that with this setup. But based on what you've posted before, why
would you run the query without the form being open?

But, if you want to "read" values from a table as a criterion in a query,
you can use the DLookup function to find a value in a table, based on a
criterion. DLookup is discussed in the Help files. It would be used this
way:

WHERE Mailers.MailerNumber = DLookup("FieldName", "TableName",
"[PrimaryKeyName]= 'A Value'")

Thanks for your help.

Barry



"Ken Snell" <[email protected]> wrote in message
Easiest way to let user select an item and use it in the query is to create
a form and put a combo box and command button on the form.

Use a query as the Row Source for the combo box, and have that query show
all the values from which the user can select. Then run code/macro on the
form that opens the query. Use this criterion expression to read the value
of the combo box in the query:

WHERE Mailers.MailerNumber = [Forms]![FormName]![ComboboxName]


Note that you'll need to add the Mailers table into the query as well.


--
Ken Snell
<MS ACCESS MVP>

Steve Schapel <[email protected]> wrote in message
Click to expand...
Barry,

You could first of all make a query based on Table 1 but with a
calculated field int he query, something like:
AdjustedID: -[ID]
... and then use this query within your other query in the place of
Table 1, with the join on the AdjustedID field.

- Steve Schapel, Microsoft Access MVP


On 1 Jan 2004 09:59:56 -0800, (e-mail address removed) (Barry) wrote:

I have 3 tables selected in a Query. Table 1 is clients, table 2 is
contacts, table 3 is reports. I want to retrieve names and addresses
from Table 1 and Table 2 based on data in Table 3. Table 2 and Table
3 each have a key field called ID and they are equal to one another.
Table's 3 key field of ID is a negative of Table 1's ID field (for
example, table 1 ID field=2000, Table 3 ID field=-2000), which is what
is giving me a problem. I can query the data by joining Tables 2 and
3 (since the ID's equal one another) but don't know how to do the same
thing with Table 1 and Table 3 (where the ID's are the negative of one
another). The access database comes from a program so it cannot be
changed. I'm assuming the reason for the negative ID's is that there
can be an ID from Table 1 that is the same in Table 2 even though they
are not related (Table 1 is a client, Table 2 is a contact, there is
another field in Table 2 that relates back to Table 1). Basically, if
the ID in Table 3 is negative, the names/addresses come from Table 1,
if the ID in Table 3 is positive, the names/addresses come from Table
2. Can anyone lead me in the right direction?

Barry

I fooled around and came up with the following SQL query that seems to
work:
Any comments appreciated.

SELECT [Clients].[ID], [Clients].[Cltsort], [Clients].[Cltname],
[Clients].[Cinvname], [Clients].[CInvAddr1], [Clients].[CInvAddr2],
[Clients].[CInvAddr3], [Clients].[Cinvcity], [Clients].[Cinvst],
[Clients].[Cinvzip], [ClientContact].[ClcFname],
[ClientContact].[ClcContact], [ClientContact].[ClcCltID],
[ClientContact].[ClcLname], [ClientContact].[clcInvAddr1],
[ClientContact].[clcInvAddr2], [ClientContact].[clcInvAddr3],
[ClientContact].[clcInvcity], [ClientContact].[clcInvst],
[ClientContact].[clcInvZip], [MailersSelected].[MailSelID],
[MailersSelected].[MailSelNumber]
FROM Clients RIGHT JOIN (ClientContact RIGHT JOIN MailersSelected ON
[ClientContact].[ClcContact]=[MailersSelected].[MailSelID]) ON
[Clients].[ID]=-[MailersSelected].[MailSelID];

Now I want to go 1 step further. There is a Table called Mailers. It
has MailerNumbers and MailersDescriptions. What I want to now do is
have a user input a particular MailerNumber based on showing him the
MailerNumbers and MailerDescriptions in the Mailers Table. Then use
the user's input of MailerNumber to add sort of a criteria to the
above SQL of "Where MailerNumber = user input".

Thanks.

Barry
Click to expand...
Click to expand...
Click to expand...
 
From what I've read and learned is that there is no way that a query's
SQL commands can open a form, please confirm I am correct in this?
Click to expand...

You are correct.
The reason I'm trying to open a form in the query is that some of the
query results are going back to a word mail merge document. Word mail
merge documents can open a query but cannot open an Access form.
Click to expand...

Turn your logic around - open the form FIRST, and launch the mail
merge from a command button on the form. The mail merge will use a
Query which references the (unbound) form.
 
John Vinson said:
You are correct.


Turn your logic around - open the form FIRST, and launch the mail
merge from a command button on the form. The mail merge will use a
Query which references the (unbound) form.
Click to expand...

Oh gosh, ask and learn. What would I need to do on the form to launch
the Word mail merge document.

Barry
 
I followed John's advice and created an unbound form with 2 combo
boxes on it, one combox box for MailerNumber, one for
MailerDescription. What's happening now is that when I select a
MailerDescription, which is the only thing I want the users to be able
to select (I will eventually hide the MailerNumber combo box), the
Mailernumber of that MailerDescription is not being passed to Word.
What am I missing?

Barry
 
I followed John's advice and created an unbound form with 2 combo
boxes on it, one combox box for MailerNumber, one for
MailerDescription. What's happening now is that when I select a
MailerDescription, which is the only thing I want the users to be able
to select (I will eventually hide the MailerNumber combo box), the
Mailernumber of that MailerDescription is not being passed to Word.
What am I missing?
Click to expand...

Well, that's *not* what I was suggesting... and I really don't
understand what you're doing. Why two combo boxes, for (what seems to
be) two fields in the same table? Of what use is a hidden combo box!?

What are the Row Sources of these two combo boxes? How are you
"passing" them to Word?
 
Sorry, I should have said I thought I followed Ken's advice. Anyway,
the row source for the combo box is a query of the fields MailerNumber
and MailerDescription from the Mailers table. The second combo box I
now deleted from the form. After the user selects the appropriate
MailerDescription, I want the respective MailerNumber to be passed to
a query. I believe I do now have it working. My last question
(hopefully), how do I close the form automatically upon clicking the
form's command button?

Barry
 
My last question
(hopefully), how do I close the form automatically upon clicking the
form's command button?
Click to expand...

Just include a line in the button's Click event:

DoCmd.Close acForm, Me.Name
 
Private Sub cmdButtonName_Click()
DoCmd.Close acForm, Me.Name
End Sub


--
Ken Snell
<MS ACCESS MVP>

Barry said:
Sorry, I should have said I thought I followed Ken's advice. Anyway,
the row source for the combo box is a query of the fields MailerNumber
and MailerDescription from the Mailers table. The second combo box I
now deleted from the form. After the user selects the appropriate
MailerDescription, I want the respective MailerNumber to be passed to
a query. I believe I do now have it working. My last question
(hopefully), how do I close the form automatically upon clicking the
form's command button?

Barry




Well, that's *not* what I was suggesting... and I really don't
understand what you're doing. Why two combo boxes, for (what seems to
be) two fields in the same table? Of what use is a hidden combo box!?

What are the Row Sources of these two combo boxes? How are you
"passing" them to Word?
Click to expand...
[/QUOTE]
 
The Command button I have on the form is a button to start Word when
it is clicked. It has the following code:

Private Sub Click_to_Run_Word_Click()
On Error GoTo Err_Click_to_Run_Word_Click

Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

Exit_Click_to_Run_Word_Click:
Exit Sub


Err_Click_to_Run_Word_Click:
MsgBox Err.Description
Resume Exit_Click_to_Run_Word_Click

End Sub

When I tried to put the "DoCmd.Close acForm, Me.Name" in the above
sub, the form did close but the MailerNumber parameter did not flow to
Word and the query. With the above code (without the DoCmd.Close
line) the MailerNumber parameter did flow to Word and the query. Does
the form have to be open for the parameter to flow? Where exactly in
the above code should I put the "DoCmd.Close" line?

Barry
 
Back
Top