There must be a better way

[Guest]

I have a Table to store Stock Items and this consists of Make - Model -
Serial Number - description amongst others. In order that I dont have the
same Make - Model information repeated throughout the Form, I Have seperate
Tables for the Make and Model and these consist of Make Table = MakeID and
Make; Model Table = ModelID and Model etc, etc. I then store 1 in the Stock
Table, Make Field to represent Make1. 2 in the Model Table, Model Field to
represent Model2 etc.

The problem is that when I want to look at the information in the Stock
Table, I have to go though a very convolouted process to get the Make Item
Number (Say 10) and look up that number in the Make Table so that I can
return and print "Make10" (rather than just the number 10) in the screen or
printout. I know that I can pick up a different column if I were using a
Combo Box but I want to use a Text Box and I can not pick up different
columns in a Text Box. There must be a better way, can anyone help>

Thanks RayC

 

[ruralguy via AccessMonster.com]

Hi Ray,
Simply include the Make and Model tables in a query of the Stock table and
the data you need will seem like it is in the same table, just a different
field.

 

[Guest]

Tthanks for this. I think I know how to do this from within a Combo Box, but
not from within a Text Box. Can you point me in the right direction please?
RayC

 

[ruralguy via AccessMonster.com]

Just add the tables to the query to which the form is bound.

Tthanks for this. I think I know how to do this from within a Combo Box, but
not from within a Text Box. Can you point me in the right direction please?
RayC

Hi Ray,
Simply include the Make and Model tables in a query of the Stock table and

[quoted text clipped - 18 lines]

 

[Guest]

Hi, Sorry bto be thick but I have not done this before. My form is bound to a
Table, do I interperet what you are saying to be that I should not have my
Form bound to a Table but to a Query. In order to do this, I will need to
open a Query, name it, do something to put the "Stock" Table, The "Make"
Table, The "Model" Table etc in it and then bind my Form to whatever I have
named the Query?
As I said, sorry to be so thick.
Thanks RayC

Just add the tables to the query to which the form is bound.

Tthanks for this. I think I know how to do this from within a Combo Box, but
not from within a Text Box. Can you point me in the right direction please?
RayC

Hi Ray,
Simply include the Make and Model tables in a query of the Stock table and

[quoted text clipped - 18 lines]

Thanks RayC

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Hi Ray,
You explained it exactly right. You are about to explore the magic and power
of queries.

Hi, Sorry bto be thick but I have not done this before. My form is bound to a
Table, do I interperet what you are saying to be that I should not have my
Form bound to a Table but to a Query. In order to do this, I will need to
open a Query, name it, do something to put the "Stock" Table, The "Make"
Table, The "Model" Table etc in it and then bind my Form to whatever I have
named the Query?
As I said, sorry to be so thick.
Thanks RayC

Just add the tables to the query to which the form is bound.

[quoted text clipped - 7 lines]

 

[Guest]

Thanks, this sounds interesting.
Because I have my "entry_Make" Table containing "MakeID" - "Make" and the
"Model" Table containing "modelID - "Model" and my "Stock Table" containing
"Make", "Model" (where the fields "Make & Model contain the "MakeID" (number)
and the "ModelID", Will I need to rename the Fields in the Query? Otherwise,
I will have confusion if I am wanting to display the "Model" as it is held in
the "Stock" Table (this is a number that equates to the "MakeID" (as held in
the "entry_Make" Table) or "Model" as it is held in the "entry_Model" Table,
if you understand what I am trying to say.
Thanks RayC

Hi Ray,
You explained it exactly right. You are about to explore the magic and power
of queries.

Hi, Sorry bto be thick but I have not done this before. My form is bound to a
Table, do I interperet what you are saying to be that I should not have my
Form bound to a Table but to a Query. In order to do this, I will need to
open a Query, name it, do something to put the "Stock" Table, The "Make"
Table, The "Model" Table etc in it and then bind my Form to whatever I have
named the Query?
As I said, sorry to be so thick.
Thanks RayC

Just add the tables to the query to which the form is bound.

[quoted text clipped - 7 lines]

Thanks RayC

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Normally a ForeignKey field is named the same name as the PrimaryKey field it
contains. It helps eliminate confusion. Access has no difficulty with
fields named the same in different tables.

Thanks, this sounds interesting.
Because I have my "entry_Make" Table containing "MakeID" - "Make" and the
"Model" Table containing "modelID - "Model" and my "Stock Table" containing
"Make", "Model" (where the fields "Make & Model contain the "MakeID" (number)
and the "ModelID", Will I need to rename the Fields in the Query? Otherwise,
I will have confusion if I am wanting to display the "Model" as it is held in
the "Stock" Table (this is a number that equates to the "MakeID" (as held in
the "entry_Make" Table) or "Model" as it is held in the "entry_Model" Table,
if you understand what I am trying to say.
Thanks RayC

Hi Ray,
You explained it exactly right. You are about to explore the magic and power

[quoted text clipped - 14 lines]

 

[Guest]

Thanks, I will give it a go. I appreciate your help and support.
RayC

Normally a ForeignKey field is named the same name as the PrimaryKey field it
contains. It helps eliminate confusion. Access has no difficulty with
fields named the same in different tables.

Thanks, this sounds interesting.
Because I have my "entry_Make" Table containing "MakeID" - "Make" and the
"Model" Table containing "modelID - "Model" and my "Stock Table" containing
"Make", "Model" (where the fields "Make & Model contain the "MakeID" (number)
and the "ModelID", Will I need to rename the Fields in the Query? Otherwise,
I will have confusion if I am wanting to display the "Model" as it is held in
the "Stock" Table (this is a number that equates to the "MakeID" (as held in
the "entry_Make" Table) or "Model" as it is held in the "entry_Model" Table,
if you understand what I am trying to say.
Thanks RayC

Hi Ray,
You explained it exactly right. You are about to explore the magic and power

[quoted text clipped - 14 lines]

Thanks RayC

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

By all means post back if you need additional assistance.

Thanks, I will give it a go. I appreciate your help and support.
RayC

Normally a ForeignKey field is named the same name as the PrimaryKey field it
contains. It helps eliminate confusion. Access has no difficulty with

[quoted text clipped - 16 lines]

 

[Guest]

OK, I opened the Query Wizard and put the "EntryMake", EntryModel" and
"Stock" Tables in there. They are all connected in the right way and I have
put the fields in the columns below. (i.e. "MakeID" & "Make" from the
"EntryMake" Table, "modelID" & "Model" from the Model Table and "Make" &
"Model" from the "Stock Table. I must be doing somethinmg wrong because when
I switch the Form from being bound to the "Stock" Table to being bound to the
Query I just generated, the whole form displays as Blank. I loose all of the
Text boxes that are in there. When I switch the Form back to being bound to
the Stock Table, everything re-appears.
Any thoughts?
RayC

By all means post back if you need additional assistance.

Thanks, I will give it a go. I appreciate your help and support.
RayC

Normally a ForeignKey field is named the same name as the PrimaryKey field it
contains. It helps eliminate confusion. Access has no difficulty with

[quoted text clipped - 16 lines]

Thanks RayC

 

[ruralguy via AccessMonster.com]

In the query builder if you switch to DataSheet view you will see what
records are pulled with the query. Use left joins rather than inner joins and
you will return some records. The form issue you describe is caused by no
records in the recordset. Keep playing in the query builder until you get
the results you want. Right clicking on the line joining the tables will let
you examine the type of join.

OK, I opened the Query Wizard and put the "EntryMake", EntryModel" and
"Stock" Tables in there. They are all connected in the right way and I have
put the fields in the columns below. (i.e. "MakeID" & "Make" from the
"EntryMake" Table, "modelID" & "Model" from the Model Table and "Make" &
"Model" from the "Stock Table. I must be doing somethinmg wrong because when
I switch the Form from being bound to the "Stock" Table to being bound to the
Query I just generated, the whole form displays as Blank. I loose all of the
Text boxes that are in there. When I switch the Form back to being bound to
the Stock Table, everything re-appears.
Any thoughts?
RayC

By all means post back if you need additional assistance.

[quoted text clipped - 6 lines]

 

[Guest]

I am getting there in that I am now able to see a list of all my "Stock"
Table produced by my query and I will need to tie the results to a specific
record in a table (don't know how to do that at the moment but suspect that I
will need to put someting in the criterion section of my Query) however, I
must resolve a much larger problem first.
Keeping in mind that the Form was originaly based on the "Stock" Table and
worked (although it returned the numeric information stored in the "Stock"
Table), when I now change the form to be based on the Query I just built I
get an Error notice, "The Microsoft Jet Database does not recognise
'SerialNo' as a valid field name or expression". This comes from my search of
the "Stock" Table using the expression
With frm.recordsetclone
.findfirst strCriteria (this is th line that produced the error)

Where strCriterion = "SerialNo ='12ab3456'" and frm = the form that works
when it is bound to the "Stock" Table but gives the Error when bound to the
query.

The Form is bound to the Query and the query has the three other tables
included in it ("enterMake" Tablke, "EnterModel" Table and "Stock" Table).
All these three tables are linked together by the appropriat Joins and the
Query lists the serial numbers when it is viewed. Where am I going wrong?
Ray C

In the query builder if you switch to DataSheet view you will see what
records are pulled with the query. Use left joins rather than inner joins and
you will return some records. The form issue you describe is caused by no
records in the recordset. Keep playing in the query builder until you get
the results you want. Right clicking on the line joining the tables will let
you examine the type of join.

OK, I opened the Query Wizard and put the "EntryMake", EntryModel" and
"Stock" Tables in there. They are all connected in the right way and I have
put the fields in the columns below. (i.e. "MakeID" & "Make" from the
"EntryMake" Table, "modelID" & "Model" from the Model Table and "Make" &
"Model" from the "Stock Table. I must be doing somethinmg wrong because when
I switch the Form from being bound to the "Stock" Table to being bound to the
Query I just generated, the whole form displays as Blank. I loose all of the
Text boxes that are in there. When I switch the Form back to being bound to
the Stock Table, everything re-appears.
Any thoughts?
RayC

By all means post back if you need additional assistance.

[quoted text clipped - 6 lines]

Thanks RayC

 

[ruralguy via AccessMonster.com]

Try: strCriterion = "[Stock]![SerialNo] ='12ab3456'"
There is probably more than one [SerialNo] field in the recordset now and you
need to specify which one to use.

I am getting there in that I am now able to see a list of all my "Stock"
Table produced by my query and I will need to tie the results to a specific
record in a table (don't know how to do that at the moment but suspect that I
will need to put someting in the criterion section of my Query) however, I
must resolve a much larger problem first.
Keeping in mind that the Form was originaly based on the "Stock" Table and
worked (although it returned the numeric information stored in the "Stock"
Table), when I now change the form to be based on the Query I just built I
get an Error notice, "The Microsoft Jet Database does not recognise
'SerialNo' as a valid field name or expression". This comes from my search of
the "Stock" Table using the expression
With frm.recordsetclone
.findfirst strCriteria (this is th line that produced the error)

Where strCriterion = "SerialNo ='12ab3456'" and frm = the form that works
when it is bound to the "Stock" Table but gives the Error when bound to the
query.

The Form is bound to the Query and the query has the three other tables
included in it ("enterMake" Tablke, "EnterModel" Table and "Stock" Table).
All these three tables are linked together by the appropriat Joins and the
Query lists the serial numbers when it is viewed. Where am I going wrong?
Ray C

In the query builder if you switch to DataSheet view you will see what
records are pulled with the query. Use left joins rather than inner joins and

[quoted text clipped - 20 lines]