where is the data coming from?

  • Thread starter Thread starter kwgwynn
  • Start date Start date
K

kwgwynn

I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
 
First, are you sure the drop-down lists have a row source that is a query or
table?
Open the form in design view and click on the drop-down list and get its
properties. If the row source type is Value List then the list is typed in
the row source property. (generally a bad design feature - especially if
things can change.

If you mean you cannot Access the design view of forms, etc. Are you sure
that this is not an .mde or .ade database? If the database has been made
into an mde or ade you cannot change the forms or reports or vba code. You
need the original mdb or adb to make changes.

Data in Tables can be changed.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.
 
Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

Steve Schapel said:
Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
Click to expand...
Click to expand...
 
Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

Steve Schapel said:
Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
Click to expand...
Click to expand...
Click to expand...

What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
 
Look in the Row Source and see what table/query.
--
KARL DEWEY
Build a little - Test a little


kwgwynn said:
Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

Steve Schapel said:
Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
Click to expand...
Click to expand...
Click to expand...
 
In the Rowsource you'll see something like

SELECT Employee.Name FROM Employee;

where Employee will be the name of the table or query that the combobox is
based on.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
 
Thanks for everyone's help thus far. Here's where I stand.

I've checked the properties on the combobox.

The Row/Source type is Table/Query and the Row Source is something called
SELECT DISTINCTRC.

I assume that is either a table or a query. I do not see any options for
finding either tables or queries when I'm looking at all the menu options.
Best I can find are the tables that are displayed when I look at the
Relationships report, and there isn't anything by this name there. So where
do I go from here?

fredg said:
Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

Steve Schapel said:
Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP

kwgwynn wrote:
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
Click to expand...
Click to expand...

What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
Click to expand...
 
Thanks for everyone's help thus far. Here's where I stand.

I've checked the properties on the combobox.

The Row/Source type is Table/Query and the Row Source is something called
SELECT DISTINCTRC.

I assume that is either a table or a query. I do not see any options for
finding either tables or queries when I'm looking at all the menu options.
Best I can find are the tables that are displayed when I look at the
Relationships report, and there isn't anything by this name there. So where
do I go from here?

fredg said:
Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

:

Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP

kwgwynn wrote:
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.
Click to expand...

What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
Click to expand...
Click to expand...

Well, there must be more to the Rowsource than you have written above
(SELECT DISTINCTRC).
That is the rowsource, but you have not posted the complete statement.
Click on that rowsource property line. A little button with 3 dots
will appear. Click on it. The Query by Example grid will open. Click
on View + SQL View.
That is where your combo box is getting it's data from. If you change
something here and exit the grid, you will see the changes in the
rowsource.
 
this is what I found for the query
SELECT DISTINCTROW PreferredPositionQuery.PreferredPositionID,
PreferredPositionQuery.PreferredPosition
FROM PreferredPositionQuery;

Once again, any help for this newbie is really appreciated!!! (happy new year)

fredg said:
Thanks for everyone's help thus far. Here's where I stand.

I've checked the properties on the combobox.

The Row/Source type is Table/Query and the Row Source is something called
SELECT DISTINCTRC.

I assume that is either a table or a query. I do not see any options for
finding either tables or queries when I'm looking at all the menu options.
Best I can find are the tables that are displayed when I look at the
Relationships report, and there isn't anything by this name there. So where
do I go from here?

fredg said:
On Thu, 20 Dec 2007 10:55:02 -0800, kwgwynn wrote:

Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

:

Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP

kwgwynn wrote:
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.


What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
Click to expand...
Click to expand...

Well, there must be more to the Rowsource than you have written above
(SELECT DISTINCTRC).
That is the rowsource, but you have not posted the complete statement.
Click on that rowsource property line. A little button with 3 dots
will appear. Click on it. The Query by Example grid will open. Click
on View + SQL View.
That is where your combo box is getting it's data from. If you change
something here and exit the grid, you will see the changes in the
rowsource.
Click to expand...
 
this is what I found for the query
SELECT DISTINCTROW PreferredPositionQuery.PreferredPositionID,
PreferredPositionQuery.PreferredPosition
FROM PreferredPositionQuery;

Once again, any help for this newbie is really appreciated!!! (happy new year)

fredg said:
Thanks for everyone's help thus far. Here's where I stand.

I've checked the properties on the combobox.

The Row/Source type is Table/Query and the Row Source is something called
SELECT DISTINCTRC.

I assume that is either a table or a query. I do not see any options for
finding either tables or queries when I'm looking at all the menu options.
Best I can find are the tables that are displayed when I look at the
Relationships report, and there isn't anything by this name there. So where
do I go from here?

:

On Thu, 20 Dec 2007 10:55:02 -0800, kwgwynn wrote:

Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

:

Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP

kwgwynn wrote:
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.


What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
Click to expand...

Well, there must be more to the Rowsource than you have written above
(SELECT DISTINCTRC).
That is the rowsource, but you have not posted the complete statement.
Click on that rowsource property line. A little button with 3 dots
will appear. Click on it. The Query by Example grid will open. Click
on View + SQL View.
That is where your combo box is getting it's data from. If you change
something here and exit the grid, you will see the changes in the
rowsource.
Click to expand...
Click to expand...

So you have found your answer.
The combo box gets it's data from the PreferredPositionQuery.
Click on that little button with the 3 dots that appears on that line.
The Query window will appear. Click on the Bang tool button (!). The
query will run and display the appropriate records.

Look up

Select + ALL, DISTINCT, DISTINCTROW, TOP Predicates

in SQL help to see how Select Distinctrow differs from Select and
Select Distinct.
 
okay, but what I still don't understand is where the query gets all its data!
Those are the records I want to change! The results of that query. WHERE DOES
THAT DATA COME FROM and how can I change it?????? (cause its wrong)

fredg said:
this is what I found for the query
SELECT DISTINCTROW PreferredPositionQuery.PreferredPositionID,
PreferredPositionQuery.PreferredPosition
FROM PreferredPositionQuery;

Once again, any help for this newbie is really appreciated!!! (happy new year)

fredg said:
On Mon, 24 Dec 2007 07:42:01 -0800, kwgwynn wrote:

Thanks for everyone's help thus far. Here's where I stand.

I've checked the properties on the combobox.

The Row/Source type is Table/Query and the Row Source is something called
SELECT DISTINCTRC.

I assume that is either a table or a query. I do not see any options for
finding either tables or queries when I'm looking at all the menu options.
Best I can find are the tables that are displayed when I look at the
Relationships report, and there isn't anything by this name there. So where
do I go from here?

:

On Thu, 20 Dec 2007 10:55:02 -0800, kwgwynn wrote:

Okay, good news. I can access the design view (its an .mdb file) and the
properties show the data is coming from a Table/Query.

Now...?

:

Kwgwynn,

If you go to the design view of the form, select the combobox, and look
at the Properties, you will seea property called Row Source. This
defines where the combobox's drop-down list of items comes from. You
will see it is either the name of a table or query in your database, or
else it is a list of items typed directly into the property box. So
that will tell you where you need to go in order to edit the list.

A possible complicating factor could be that there is a macro or VBA
procedure running on the form or elsewhere in the application that
assigns the RowSource of the combobox at runtime... but let's tackle
that op[tion later if it seems that it might apply.

--
Steve Schapel, Microsoft Access MVP

kwgwynn wrote:
I have some Access experience -- creating some simple databases -- and now I
am at a new job where there is an Access database with no "access" to the
author. The database uses forms for data entry (something I've never
programed; don't know if that's an issue here). This is a fairly simple
membership database.

What I'm trying to do is this: there are multiple drop-down lists that
provide choices to idenify a member's interests. However, the choices are out
of date. I want to go in and update these lists. But I can't find them. Where
are these lists? Best I've found are the table descriptions by view the
relationships, and that isn't at all helpful.

Is this going to be a permission thing? Any assistance is greatly appreciate.


What is the actual RowSource property?
If it is a table or query name, then that is where the combo gets it's
data.
If it is a "Select ...etc." then that is where the combo gets it's
data.

If it is blank, then you will need to see the form's code sheet and
search through all the other combo box's AfterUpdate events to see
which one is setting the Rowsource for this combo box. Something like:

Private Sub ComboName_AfterUpdate()
OtherComboName.Rowsource = "Select .......etc."
End Sub
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Well, there must be more to the Rowsource than you have written above
(SELECT DISTINCTRC).
That is the rowsource, but you have not posted the complete statement.
Click on that rowsource property line. A little button with 3 dots
will appear. Click on it. The Query by Example grid will open. Click
on View + SQL View.
That is where your combo box is getting it's data from. If you change
something here and exit the grid, you will see the changes in the
rowsource.
Click to expand...
Click to expand...

So you have found your answer.
The combo box gets it's data from the PreferredPositionQuery.
Click on that little button with the 3 dots that appears on that line.
The Query window will appear. Click on the Bang tool button (!). The
query will run and display the appropriate records.

Look up

Select + ALL, DISTINCT, DISTINCTROW, TOP Predicates

in SQL help to see how Select Distinctrow differs from Select and
Select Distinct.
Click to expand...
 
kwgwynn said:
okay, but what I still don't understand is where the query gets all
its data! Those are the records I want to change! The results of that
query. WHERE DOES THAT DATA COME FROM and how can I change it??????
(cause its wrong)
Click to expand...

Look at the FROM clause...

FROM PreferredPositionQuery;

That tells you that the data is coming from another query named
"PreferredPositionQuery". You need to look at the structure of that query to
examine its FROM clause. Eventually there will be a FROM clause that names one
or more tables and THAT is where the data comes from.
 
this is what I found for the query
SELECT DISTINCTROW PreferredPositionQuery.PreferredPositionID,
PreferredPositionQuery.PreferredPosition
FROM PreferredPositionQuery;
Click to expand...

You should then have a Query in Access' Queries window named
PreferredPositionQuery. Try opening this Query in design view; that should
show you the tables and fields containing the data that the combo displays.

Why the original developer did this "double layer" - putting a SQL statement
which just recapitulates the data in PreferredPositionQuery into the combo -
is anyone's guess.

John W. Vinson [MVP]
 
well, I don't really understand what is going on here (and I agree with
John's statement: why the original developer did what he did is beyond me),
but I think I can do the changes I need to. When I run the query the result
is a data table. I edited then saved the table and the changes are available
on the form. So I guess that's what is needed.

When I look at the query itself, the FROM statement keeps pointing to
another query statement. There seems to be no end to the querying! I am
queried out and hope that updating these tables is the right thing to do.

Thanks again to everyone who chimed in with a suggestion.

Karen
 
Back
Top