Share one Qry for 2 Forms for two way data entry not all fileds

  • Thread starter Thread starter Kelly
  • Start date Start date
K

Kelly

I have two main forms I am trying to get operational. The first form contains
fields from one table (designorders). The second form contains some of the
same fileds from (designorders) but also fields from two additional linked
tables (account) and (sublist). The goal is to populate first table for
information then extract that data to second form and be able to data entry
in either form with a reqnum as the common field. I will use the same subform
on both and just hide the columns in the second form that doesn't need to be
viewed. Reqnum is my child/master link for the main and subform also. For
some reason the linked tables want to be populated in the first form even
though I am not showing those fields in the form. It will not allow me to go
to the subform because it is asking for the fields from the (account) or
(sublist). Is there a way around this? Or a better way to accomplish this?

Kelly
 
If I got this straight you will have Reqnum available for both queries as it
comes from the same table. Now for the other fields from [account] and
[sublist] just add calculated fields like this --
MyMissingField: Null
This way the subform will see the field it is looking for.
 
Kelly said:
I have two main forms I am trying to get operational. The first form
contains
fields from one table (designorders). The second form contains some of the
same fileds from (designorders) but also fields from two additional linked
tables (account) and (sublist). The goal is to populate first table for
information then extract that data to second form and be able to data
entry
in either form with a reqnum as the common field. I will use the same
subform
on both and just hide the columns in the second form that doesn't need to
be
viewed. Reqnum is my child/master link for the main and subform also. For
some reason the linked tables want to be populated in the first form even
though I am not showing those fields in the form. It will not allow me to
go
to the subform because it is asking for the fields from the (account) or
(sublist). Is there a way around this? Or a better way to accomplish this?

Kelly
Click to expand...
 
Thanks for your reply. I'm new to Access and have been muddleing through
trying to make sense of some of the rules. The reqnum is populated but what
is giving the error is the linked tables. It is asking for the fields in the
(sublist) and (account) tables. I have them linked with one-many
relationships and I wonder if the query is forcing me to have these
populated. None of the other fields give an error. It's hard to describe what
I am trying but maybe this will help. Table One has fields A,B,C,D,E,F, table
Two has A,B,C,E,G,H,I,J,K,L,M,N,O. A,B,C common in both forms. A=reqnum
field. C=employeeID field in DesignOrders with a look up coming from an
Employee table. J=AccountID field in DesignOrders with a look up coming from
Account table. L=SubTableID field in DesignOrders with a look up coming from
the Sublist table. M & N are fileds in the Account table being populated with
a look up at J. O is a field in the Sublist table being populated with a look
up at L. These fields A-L are in a table called DesignOrders. So the
mainquery for both forms has fields A-O which means it has linked tables of
Account, Sublist and Employee to DesignOrders. If I data entry into form Two
I can populate the fields no problem. If I data entry into form One it gives
me the error it can't find AccountID or SubTableID. I thought if I used the
reqnum (primary key for DesignOrders table) as a common filed to move
betweeen the forms it would pick up from one to the other. Do you have to
show the linked fields from Account and Sublist tables in form One eventhough
I don't want them? Or is there a way to pull up what you data entry in form
Two into form One with out a query?

Kelly
 
Since I am new to Access I'm not sure where I put this. Do I use this on the
criteria in the query under an additional AccountID and SubTableID?
Kelly


KARL DEWEY said:
If I got this straight you will have Reqnum available for both queries as it
comes from the same table. Now for the other fields from [account] and
[sublist] just add calculated fields like this --
MyMissingField: Null
This way the subform will see the field it is looking for.

Kelly said:
I have two main forms I am trying to get operational. The first form contains
fields from one table (designorders). The second form contains some of the
same fileds from (designorders) but also fields from two additional linked
tables (account) and (sublist). The goal is to populate first table for
information then extract that data to second form and be able to data entry
in either form with a reqnum as the common field. I will use the same subform
on both and just hide the columns in the second form that doesn't need to be
viewed. Reqnum is my child/master link for the main and subform also. For
some reason the linked tables want to be populated in the first form even
though I am not showing those fields in the form. It will not allow me to go
to the subform because it is asking for the fields from the (account) or
(sublist). Is there a way around this? Or a better way to accomplish this?

Kelly
Click to expand...
Click to expand...
 
Post the SQL of your queries. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

Kelly said:
Since I am new to Access I'm not sure where I put this. Do I use this on the
criteria in the query under an additional AccountID and SubTableID?
Kelly


KARL DEWEY said:
If I got this straight you will have Reqnum available for both queries as it
comes from the same table. Now for the other fields from [account] and
[sublist] just add calculated fields like this --
MyMissingField: Null
This way the subform will see the field it is looking for.

Kelly said:
I have two main forms I am trying to get operational. The first form contains
fields from one table (designorders). The second form contains some of the
same fileds from (designorders) but also fields from two additional linked
tables (account) and (sublist). The goal is to populate first table for
information then extract that data to second form and be able to data entry
in either form with a reqnum as the common field. I will use the same subform
on both and just hide the columns in the second form that doesn't need to be
viewed. Reqnum is my child/master link for the main and subform also. For
some reason the linked tables want to be populated in the first form even
though I am not showing those fields in the form. It will not allow me to go
to the subform because it is asking for the fields from the (account) or
(sublist). Is there a way around this? Or a better way to accomplish this?

Kelly
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your reply. Also look at the reply I sent bhicks11 describing what
I am trying to accomplish. This is the latest test I have ran. I'm using this
query for both forms.

SELECT DesignerOrders.RequisitionNumber, DesignerOrders.JobName,
DesignerOrders.OutdoorDesignerName, DesignerOrders.CivilDesignerName,
DesignerOrders.DateOrdered, DesignerOrders.ConstructionStartDate,
DesignerOrders.EWO, DesignerOrders.PENumber AS DesignerOrders_PENumber,
DesignerOrders.PEItem, DesignerOrders.CommentsStructure,
DesignerOrders.CommentsFoundation, DesignerOrders.SubmitToCivilDate,
DesignerOrders.SubTableID, DesignerOrders.AccountID, Account.FERC,
Account.SUB, SubList.Division, OutdoorEmployee.Designer,
CivilEmployee.CivilDesigner
FROM SubList INNER JOIN (PEList INNER JOIN (Account INNER JOIN
(OutdoorEmployee INNER JOIN (CivilEmployee INNER JOIN DesignerOrders ON
CivilEmployee.CivilEmployeeID = DesignerOrders.CivilDesignerName) ON
OutdoorEmployee.EmployeeID = DesignerOrders.OutdoorDesignerName) ON
Account.AccountnumID = DesignerOrders.AccountID) ON PEList.PENumber =
DesignerOrders.PENumber) ON SubList.SubstationID = DesignerOrders.SubTableID;

Kelly


KARL DEWEY said:
Post the SQL of your queries. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

Kelly said:
Since I am new to Access I'm not sure where I put this. Do I use this on the
criteria in the query under an additional AccountID and SubTableID?
Kelly


KARL DEWEY said:
If I got this straight you will have Reqnum available for both queries as it
comes from the same table. Now for the other fields from [account] and
[sublist] just add calculated fields like this --
MyMissingField: Null
This way the subform will see the field it is looking for.

:

I have two main forms I am trying to get operational. The first form contains
fields from one table (designorders). The second form contains some of the
same fileds from (designorders) but also fields from two additional linked
tables (account) and (sublist). The goal is to populate first table for
information then extract that data to second form and be able to data entry
in either form with a reqnum as the common field. I will use the same subform
on both and just hide the columns in the second form that doesn't need to be
viewed. Reqnum is my child/master link for the main and subform also. For
some reason the linked tables want to be populated in the first form even
though I am not showing those fields in the form. It will not allow me to go
to the subform because it is asking for the fields from the (account) or
(sublist). Is there a way around this? Or a better way to accomplish this?

Kelly
Click to expand...
Click to expand...
Click to expand...
 
sorry for the delay. I did rething what you suggestted and decided to not
share the same queries beacuse of the additional control. It was too
complicated for me at that point. Now that I have been learning bit by bit it
was the best decission to separate the queries. I'm learning so bare with me.
 
Back
Top