Update query using form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a database that takes a standard product bill of materials
and creates a base quote. The sales person can then modify the BOM to suit
the quote.
I am using an insert query to copy a particular model component records into
a quote components table and attach a quote number. My problem is getting
the quote number into the quote components record. There is a quote table
which has the quote number, and a quote components table where the model
components are copied with the quote number attached.

The SQL is
INSERT INTO tblQuoteComponents ( ComponentlNo, ComponentlDescription,
MaterialNo, ItemsQty, UnitsPerItem, SubAssemblyNo, QuoteNo )
SELECT tblComponents.ComponentlNo, tblComponents.ComponentlDescription,
tblComponents.MaterialNo, tblComponents.ItemsQty, tblComponents.UnitsPerItem,
tblComponents.SubAssemblyNo, [Forms]![frmQuoteHeader]![txtQuoteNo]
FROM tblComponents
WHERE (((tblComponents.ModelNo)=[Forms]![frmQuoteHeader]![cmbBaseModelNo]))
WITH OWNERACCESS OPTION;

The problem is that the quote components quote number is not having the
[Forms]![frmQuoteHeader]![txtQuoteNo] field inserted which causes an error.
Assistance would be appreciated.
 
Neville

Just a wild shot in the dark -- is the form that is being referenced
actually open when the SQL statement runs?
 
Yes it is open. I think that mixing fields from the tblComponents table with
a field from a form is causing the issue but cannot see a way around it.

Jeff Boyce said:
Neville

Just a wild shot in the dark -- is the form that is being referenced
actually open when the SQL statement runs?

--
Regards

Jeff Boyce
<Office/Access MVP>

NevilleT said:
I am developing a database that takes a standard product bill of materials
and creates a base quote. The sales person can then modify the BOM to suit
the quote.
I am using an insert query to copy a particular model component records into
a quote components table and attach a quote number. My problem is getting
the quote number into the quote components record. There is a quote table
which has the quote number, and a quote components table where the model
components are copied with the quote number attached.

The SQL is
INSERT INTO tblQuoteComponents ( ComponentlNo, ComponentlDescription,
MaterialNo, ItemsQty, UnitsPerItem, SubAssemblyNo, QuoteNo )
SELECT tblComponents.ComponentlNo, tblComponents.ComponentlDescription,
tblComponents.MaterialNo, tblComponents.ItemsQty, tblComponents.UnitsPerItem,
tblComponents.SubAssemblyNo, [Forms]![frmQuoteHeader]![txtQuoteNo]
FROM tblComponents
WHERE (((tblComponents.ModelNo)=[Forms]![frmQuoteHeader]![cmbBaseModelNo]))
WITH OWNERACCESS OPTION;

The problem is that the quote components quote number is not having the
[Forms]![frmQuoteHeader]![txtQuoteNo] field inserted which causes an error.
Assistance would be appreciated.
Click to expand...
Click to expand...
 
Neville

What happens if you create a (test) query that only selects the (SELECT)
portion of your append query? Does it return the rows you expect?

--
Regards

Jeff Boyce
<Office/Access MVP>

NevilleT said:
Yes it is open. I think that mixing fields from the tblComponents table with
a field from a form is causing the issue but cannot see a way around it.

Jeff Boyce said:
Neville

Just a wild shot in the dark -- is the form that is being referenced
actually open when the SQL statement runs?

--
Regards

Jeff Boyce
<Office/Access MVP>

NevilleT said:
I am developing a database that takes a standard product bill of materials
and creates a base quote. The sales person can then modify the BOM to suit
the quote.
I am using an insert query to copy a particular model component
Click to expand...
records
into
a quote components table and attach a quote number. My problem is getting
the quote number into the quote components record. There is a quote table
which has the quote number, and a quote components table where the model
components are copied with the quote number attached.

The SQL is
INSERT INTO tblQuoteComponents ( ComponentlNo, ComponentlDescription,
MaterialNo, ItemsQty, UnitsPerItem, SubAssemblyNo, QuoteNo )
SELECT tblComponents.ComponentlNo, tblComponents.ComponentlDescription,
tblComponents.MaterialNo, tblComponents.ItemsQty, tblComponents.UnitsPerItem,
tblComponents.SubAssemblyNo, [Forms]![frmQuoteHeader]![txtQuoteNo]
FROM tblComponents
WHERE (((tblComponents.ModelNo)=[Forms]![frmQuoteHeader]![cmbBaseModelNo]))
WITH OWNERACCESS OPTION;

The problem is that the quote components quote number is not having the
[Forms]![frmQuoteHeader]![txtQuoteNo] field inserted which causes an error.
Assistance would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
In the end I wrote a took a different approach. I wrote a select query then
looped through the database putting required fields into variable and the
form field into a variable. I than ran an update query to put the variables
into the quote file. With a bit of fiddling it works. It is a lot longer
but does the trick. Thanks for your help Jeff.
Jeff Boyce said:
Neville

What happens if you create a (test) query that only selects the (SELECT)
portion of your append query? Does it return the rows you expect?

--
Regards

Jeff Boyce
<Office/Access MVP>

NevilleT said:
Yes it is open. I think that mixing fields from the tblComponents table with
a field from a form is causing the issue but cannot see a way around it.

Jeff Boyce said:
Neville

Just a wild shot in the dark -- is the form that is being referenced
actually open when the SQL statement runs?

--
Regards

Jeff Boyce
<Office/Access MVP>

I am developing a database that takes a standard product bill of materials
and creates a base quote. The sales person can then modify the BOM to
suit
the quote.
I am using an insert query to copy a particular model component records
into
a quote components table and attach a quote number. My problem is getting
the quote number into the quote components record. There is a quote table
which has the quote number, and a quote components table where the model
components are copied with the quote number attached.

The SQL is
INSERT INTO tblQuoteComponents ( ComponentlNo, ComponentlDescription,
MaterialNo, ItemsQty, UnitsPerItem, SubAssemblyNo, QuoteNo )
SELECT tblComponents.ComponentlNo, tblComponents.ComponentlDescription,
tblComponents.MaterialNo, tblComponents.ItemsQty,
tblComponents.UnitsPerItem,
tblComponents.SubAssemblyNo, [Forms]![frmQuoteHeader]![txtQuoteNo]
FROM tblComponents
WHERE
(((tblComponents.ModelNo)=[Forms]![frmQuoteHeader]![cmbBaseModelNo]))
WITH OWNERACCESS OPTION;

The problem is that the quote components quote number is not having the
[Forms]![frmQuoteHeader]![txtQuoteNo] field inserted which causes an
error.
Assistance would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top