Combo pulls up subform record names...

[Guest]

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hi, John. First of all, the only thing you need to store is the foreign key
field that identifies the specific brand name. Your tables will be something
like:

Generics
=========================
GenericID AutoNumber (Primary Key)
Description Text

Drugs
=========================
DrugID AutoNumber (Primary Key)
Description Text
GenericID Number (Foreign Key to Generics)

TheTableYourFormIsBasedOn
=========================
ID AutoNumber (Primary Key)
DrugID Number
....the rest of your fields...

On the form, you need an Unbound combo box that pulls its rows from
Generics. Based on the above definition, the Row Source would be:

SELECT Generics.GenericID, Generics.Description FROM Generics ORDER BY
Generics.Description;

The Column Widths property would be 0";x", where x is the width in inches
wide enough to display the widest column. A 0" setting means the column is
not displayed at all in the drop-down. The BoundColumn is 1 (you're storing
the non-visible GenericID in the control, even though you're displaying the
first non-zero column--the description).

The easiest way to add a combo box is with the wizard. Enable the wizard
with View, Toolbox, <toggle on the button with the wand and stars>. Then
just place your combo box, and it will prompt you for the table and the
fields. Choose Hide Key Field (recommended), and tell the wizard to remember
the value for later.

Even though you will be setting the RowSource of the 2nd combo box manually,
use the wizard again so that it will adjust the columns for you and set the
ControlSource. This one gets its rows from Drugs. Select the key and the
description, Hide Key Field, but this time tell it to save the value to field
in the form's underlying table. Access will set the RowSource to:

SELECT Drugs.ID, Drugs.Description FROM Drugs ORDER BY Drugs.Description;

To filter the box by the selection in the first, you will change its
RowSource property in the AfterUpdate event procedure of the first:

Me!MySecondComboBox.RowSource = "SELECT Drugs.ID, Drugs.Description FROM
Drugs WHERE [GenericID] = " & Me!MyFirstComboBox & " ORDER BY
Drugs.Description"

Welcome to the bigs, Rookie.

Hope that helps.
Sprinks

 

[Guest]

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

 

[Guest]

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.

 

[Douglas J. Steele]

It's actually Acetylsalicylic acid

Check http://www.nlm.nih.gov/medlineplus/druginfo/medmaster/a682878.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I
couldn't
even find acetylsalicitic acid.

Thanks again,

John.

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is
a
very different chemical--acetylsalicitic acid.

Sprinks

 

[Guest]

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

 

[Guest]

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hi, Rook.

In the AfterUpdate event, the right hand side of the assignment statement
must evaluate to a valid string expression, so all literals (and only
literals) must be enclosed in quotes.

To improve readability, most programmers make separate substrings out of
each clause and place them on their own line, concatenating them with the &
operator, and unifying the string into a single statement with the
continuation sequence at the end of each line other than the last—a space
followed by an underscore. Although often not necessary, it’s good practice
to delimit controls by brackets.

Try the following:

Me![TradeDrug].RowSource = _
"SELECT tbldrugs.DrugID, tbldrugs.DrugName " & _
"FROM tbldrugs " & _
"WHERE [GenericDrugID] = " & Me![GenericDrugID] & _
"ORDER BY tbldrugs.DrugName"

You can cut and paste this posting, but for future applications, remember to
include a space at the end of each substring except the last as shown.

Best regards.
Sprinks

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.


:

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hey Sprinks,
I can successfully change the generic name for my Generic name
combo;however, when I go to my trade name combo, I get the error:
Syntax error: missing operator in query expression '[GenericDrugID] =2ORDER
BY tbldrugs.DrugName.' When I choose the first generic drug, I get
'[GenericDrugID] =1ORDER BY tbldrugs.DrugName.'

Before I continue, I noticed something. My control source for my Generic
name combo (first) is GenericDrugID. My control source for my second was
pointing to last (a field that contains last name). I changed it to an
unbound combo (blanked out the control source) I did that but then came out
with a blank on the second combo. Could this be the problem? I'm not really
sure what the control source for the second combo should be, as the first
combo tells it where it should come from. (I think)

Thanks for putting up with my ignorance,
John.

Hi, Rook.

In the AfterUpdate event, the right hand side of the assignment statement
must evaluate to a valid string expression, so all literals (and only
literals) must be enclosed in quotes.

To improve readability, most programmers make separate substrings out of
each clause and place them on their own line, concatenating them with the &
operator, and unifying the string into a single statement with the
continuation sequence at the end of each line other than the last—a space
followed by an underscore. Although often not necessary, it’s good practice
to delimit controls by brackets.

Try the following:

Me![TradeDrug].RowSource = _
"SELECT tbldrugs.DrugID, tbldrugs.DrugName " & _
"FROM tbldrugs " & _
"WHERE [GenericDrugID] = " & Me![GenericDrugID] & _
"ORDER BY tbldrugs.DrugName"

You can cut and paste this posting, but for future applications, remember to
include a space at the end of each substring except the last as shown.

Best regards.
Sprinks

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

:

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.


:

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Many apologies, Rookie. I omitted the space that's required after inserting
the value of your first combo box. Change the WHERE clause line to:

"WHERE [GenericDrugID] = " & Me![GenericDrugID] & " " & _

A ControlSource is the field of the underlying table or query as specified
in the form's RecordSource property where you wish to store the data entered
into the control. Since the combo box is set up to display drug names, you
should be storing the DrugID in the underlying table. The properties of the
2nd combo box should be:

Bound Column 1
ColumnWidths 0";x", where x is any decimal number
ControlSource The *numeric* field in the form's underlying table where you
will store the DrugID

Hope that resolves it.
Sprinks

Hey Sprinks,
I can successfully change the generic name for my Generic name
combo;however, when I go to my trade name combo, I get the error:
Syntax error: missing operator in query expression '[GenericDrugID] =2ORDER
BY tbldrugs.DrugName.' When I choose the first generic drug, I get
'[GenericDrugID] =1ORDER BY tbldrugs.DrugName.'

Before I continue, I noticed something. My control source for my Generic
name combo (first) is GenericDrugID. My control source for my second was
pointing to last (a field that contains last name). I changed it to an
unbound combo (blanked out the control source) I did that but then came out
with a blank on the second combo. Could this be the problem? I'm not really
sure what the control source for the second combo should be, as the first
combo tells it where it should come from. (I think)

Thanks for putting up with my ignorance,
John.

Hi, Rook.

In the AfterUpdate event, the right hand side of the assignment statement
must evaluate to a valid string expression, so all literals (and only
literals) must be enclosed in quotes.

To improve readability, most programmers make separate substrings out of
each clause and place them on their own line, concatenating them with the &
operator, and unifying the string into a single statement with the
continuation sequence at the end of each line other than the last—a space
followed by an underscore. Although often not necessary, it’s good practice
to delimit controls by brackets.

Try the following:

Me![TradeDrug].RowSource = _
"SELECT tbldrugs.DrugID, tbldrugs.DrugName " & _
"FROM tbldrugs " & _
"WHERE [GenericDrugID] = " & Me![GenericDrugID] & _
"ORDER BY tbldrugs.DrugName"

You can cut and paste this posting, but for future applications, remember to
include a space at the end of each substring except the last as shown.

Best regards.
Sprinks

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.



:

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

:

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.


:

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Hey Sprinks,

YOU ROCK!!!!!!!!!!

It works...thank you so much!

John.

Ps - I looked up Acetylsalicylic acid...you were right!

Many apologies, Rookie. I omitted the space that's required after inserting
the value of your first combo box. Change the WHERE clause line to:

"WHERE [GenericDrugID] = " & Me![GenericDrugID] & " " & _

A ControlSource is the field of the underlying table or query as specified
in the form's RecordSource property where you wish to store the data entered
into the control. Since the combo box is set up to display drug names, you
should be storing the DrugID in the underlying table. The properties of the
2nd combo box should be:

Bound Column 1
ColumnWidths 0";x", where x is any decimal number
ControlSource The *numeric* field in the form's underlying table where you
will store the DrugID

Hope that resolves it.
Sprinks

Hey Sprinks,
I can successfully change the generic name for my Generic name
combo;however, when I go to my trade name combo, I get the error:
Syntax error: missing operator in query expression '[GenericDrugID] =2ORDER
BY tbldrugs.DrugName.' When I choose the first generic drug, I get
'[GenericDrugID] =1ORDER BY tbldrugs.DrugName.'

Before I continue, I noticed something. My control source for my Generic
name combo (first) is GenericDrugID. My control source for my second was
pointing to last (a field that contains last name). I changed it to an
unbound combo (blanked out the control source) I did that but then came out
with a blank on the second combo. Could this be the problem? I'm not really
sure what the control source for the second combo should be, as the first
combo tells it where it should come from. (I think)

Thanks for putting up with my ignorance,
John.

Hi, Rook.

In the AfterUpdate event, the right hand side of the assignment statement
must evaluate to a valid string expression, so all literals (and only
literals) must be enclosed in quotes.

To improve readability, most programmers make separate substrings out of
each clause and place them on their own line, concatenating them with the &
operator, and unifying the string into a single statement with the
continuation sequence at the end of each line other than the last—a space
followed by an underscore. Although often not necessary, it’s good practice
to delimit controls by brackets.

Try the following:

Me![TradeDrug].RowSource = _
"SELECT tbldrugs.DrugID, tbldrugs.DrugName " & _
"FROM tbldrugs " & _
"WHERE [GenericDrugID] = " & Me![GenericDrugID] & _
"ORDER BY tbldrugs.DrugName"

You can cut and paste this posting, but for future applications, remember to
include a space at the end of each substring except the last as shown.

Best regards.
Sprinks


:

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.



:

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

:

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.


:

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.

 

[Guest]

Rookie,

My pleasure.

If you're planning to continue in Access programming, the Access Developer's
Handbook by Getz, Litwin, et al is the best I've ever found. It along with
VBA Help, and this newsgroup is all I've ever needed. In case you've never
accessed it, VBA Help is available from the module window menu. You can also
highlight a keyword such as a method and press F1 to invoke context-sensitive
help.

Good luck.
Sprinks

Hey Sprinks,

YOU ROCK!!!!!!!!!!

It works...thank you so much!

John.

Ps - I looked up Acetylsalicylic acid...you were right!

Many apologies, Rookie. I omitted the space that's required after inserting
the value of your first combo box. Change the WHERE clause line to:

"WHERE [GenericDrugID] = " & Me![GenericDrugID] & " " & _

A ControlSource is the field of the underlying table or query as specified
in the form's RecordSource property where you wish to store the data entered
into the control. Since the combo box is set up to display drug names, you
should be storing the DrugID in the underlying table. The properties of the
2nd combo box should be:

Bound Column 1
ColumnWidths 0";x", where x is any decimal number
ControlSource The *numeric* field in the form's underlying table where you
will store the DrugID

Hope that resolves it.
Sprinks

Hey Sprinks,
I can successfully change the generic name for my Generic name
combo;however, when I go to my trade name combo, I get the error:
Syntax error: missing operator in query expression '[GenericDrugID] =2ORDER
BY tbldrugs.DrugName.' When I choose the first generic drug, I get
'[GenericDrugID] =1ORDER BY tbldrugs.DrugName.'

Before I continue, I noticed something. My control source for my Generic
name combo (first) is GenericDrugID. My control source for my second was
pointing to last (a field that contains last name). I changed it to an
unbound combo (blanked out the control source) I did that but then came out
with a blank on the second combo. Could this be the problem? I'm not really
sure what the control source for the second combo should be, as the first
combo tells it where it should come from. (I think)

Thanks for putting up with my ignorance,
John.

:

Hi, Rook.

In the AfterUpdate event, the right hand side of the assignment statement
must evaluate to a valid string expression, so all literals (and only
literals) must be enclosed in quotes.

To improve readability, most programmers make separate substrings out of
each clause and place them on their own line, concatenating them with the &
operator, and unifying the string into a single statement with the
continuation sequence at the end of each line other than the last—a space
followed by an underscore. Although often not necessary, it’s good practice
to delimit controls by brackets.

Try the following:

Me![TradeDrug].RowSource = _
"SELECT tbldrugs.DrugID, tbldrugs.DrugName " & _
"FROM tbldrugs " & _
"WHERE [GenericDrugID] = " & Me![GenericDrugID] & _
"ORDER BY tbldrugs.DrugName"

You can cut and paste this posting, but for future applications, remember to
include a space at the end of each substring except the last as shown.

Best regards.
Sprinks


:

Hey Sprinks,
For my generic combo, my row source:

SELECT tblgenericdrug.GenericDrugID, tblgenericdrug.GenericName
FROM tblgenericdrug
ORDER BY tblgenericdrug.GenericName, tblgenericdrug.GenericDrugID;

The After update for the generic combo:

Private Sub GenericDrug_AfterUpdate()
Me!TradeDrug.RowSource = "SELECT tbldrugs.DrugID, tbldrugs.DrugName "
FROM tbldrugs
WHERE [GenericDrugID] = "&Me!GenericDrug&"
ORDER BY tbldrugs.DrugName
End Sub

(Order by tbldrugs.DrugName was in red when I copied this)

For my second combo (drug name) rowsource:
SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
ORDER BY tbldrugs.DrugName, tbldrugs.DrugID;

My tblgenericdrug and tbldrugs are related by genericdrugID; the
genericdrugID is the PK in my tblgenericdrug and the FK in my tbldrugs. It's
one to many.

Thanks for all your help on this.

John.



:

Hi, Rook.

Please post the RowSources of both combo boxes and the entire AfterUpdate
procedure, and we'll wrap this up.

Best regards.
Sprinks

:

Hey Sprinks,

Sorry for the delay; I was off work.
Actually, the expresssion builder automatically added the = at the beginning
of Me!
I tried it in the code window; after changing the value, it brings up an
error:
Compile Error - sub or function not defined.
This was in the after update ppty of the combo.
It highlighted the FROM and then when I clicked OK, it highlighted the title
of the sub: Private Sub GenericDrug_AfterUpdate() in yellow with a yellow
arrow next to it.
Also, my ORDER BY Drug.Name was in red.

Slightly confused...

John.


:

AR,

Remove the initial equals sign. Me!TradeDrug.RowSource is what you're
assigning.

Sprinks

:

Hey Sprinks,
Thank you so much for your detailed help! I did run into some problems.
I got the error: :The expression AfterUpdate produced the following error:
The object doesn't contain the automation object 'Me'
Here's what my SQL looks like for my first combo box:

=Me!TradeDrug.RowSource="SELECT tbldrugs.DrugID, tbldrugs.DrugName
FROM tbldrugs
WHERE [GenericDrugID] = " & Me!GenericDrug & "
ORDER BY Drug.Name"

As for the Aspirin Name, I'll have to double check my pill book: I couldn't
even find acetylsalicitic acid.

Thanks again,

John.




:

Hi, Access rookie.

I forgot one thing. Aspirin is not a tradename for Acetaminophen, it is a
very different chemical--acetylsalicitic acid.

Sprinks

:

Hello,
I have a table that lists generic drug names. Each generic drug has many
trade names, eg. Generic Name: ACETAMINOPHEN, trade names: Tylenol, Aspirin,
etc.
I am planning to use a one to many relationship for this.
My goal is for the data-entry person to select the generic name in the first
combo, and then have the second combo list only the tradenames for that
generic name - kind of like the choice in the first box determines which
records show up in the second.
So if they choose ACETAMINOPHEN in the first, only Tylenol and Aspirin etc.
show up, but if they choose SILDENAFIL CITRATE, only Viagra comes up in the
second.
They will be linked by GenericDrugID.

Hopful,

John.