FJ,
A form has a Record Source property, which determines which data the
from relates to. This is normally either a table or a query. The
"trusty" from wizard assigns this property to the form in the process of
setting it up. But you can just as easily do it yourself, by going into
the Properties of the form, and putting the appropriate entry in the
Record Source property.
If I understand you correctly, you want fields from the Credit Limit
Check - Debtors 4 query to be included in the Record Source of the form,
instead of the way the form was first set up. So you will need to
create another query, which gives you the data you need on your form.
This will presumably mean a query that includes the original query, plus
the Credit Limit Check - Debtors 4 query, suitably joined.
I didn't study your queries in depth, but I did notice an oddity... the
first SQL string you mentioned includes Assignors.Advance_Percent but
the Assignors table does not appear in the FROM clause. Strange?
When you start basing forms on complex queries, you must also consider
whether you want the form to be editable. Queries are updateable or
not, depending on a number of factors which I won't go into here. But
if a form is based on a non-updateable query, you won't be able to
add/edit records there. Credit Limit Check - Debtors 4 I am pretty
certain will not be updateable, and thus any query that includes the
Credit Limit Check - Debtors 4 query will not be updateable.
One possible way around this is to use an unbound textbox on the form,
with its Control Source property set to a Domain function, such as...
=DLookup("[Credit Available]","Credit Limit Check - Debtors
4","[AssignorID]=" & [AssignorID])
If you have a lot of data, this method will result in the form being
slow to load.
By the way, as an aside, it is not a good idea to use a - as part of the
name of a query (or field, or control, or any other database object for
that matter).
--
Steve Schapel, Microsoft Access MVP
Steve,
As noted earlier the query you recommended worked fine. However now I have
the problem of slipping it into the proper form. Currently the data in the
form is as listed below. All I need to do is add one field called [Credit
Available] located within the Query called [Credit Limit Check - Debtors 4].
The query is linked to the form with the field [DEBTORID]:
SELECT Invoices.INVOICEID, Invoices.ASSIGNMENTREQUESTID, Invoices.STATUSID,
Invoices.EntryDate, Invoices.DEBTORID, Invoices.STOREID,
Invoices.AssignorInvoiceNumber, Invoices.InvoiceDate,
Invoices.InvoiceDueDate, Invoices.InvoiceAmount,
Invoices.AllowableDiscountPct, Invoices.AdvanceRate AS Invoices_AdvanceRate,
Invoices.AdvanceAmount, Invoices.Closed, Invoices.ClosedHow,
Invoices.DateClosed, Invoices.Rebated, Invoices.REBATEID, Invoices.DiscFee,
Invoices.DateUsedforDiscFee, Invoices.ReceivedAmount, Invoices.NetRebate,
Invoices.Verify, Invoices.Verified, Invoices.VerificationDate,
Invoices.VerificationNotes, Invoices.CollectionNotes, Invoices.Memo,
Assignors.Advance_Percent, Debtors.AdvanceRate AS Debtors_AdvanceRate,
Debtors.RecourseDays, Debtors.VerificationThreshold FROM Debtors INNER JOIN
Invoices ON Debtors.DEBTORID=Invoices.DEBTORID;
Similarly I have another form called [Assignment Requests]. I want to add
the field called [Credit Available] from the query called [Credit Limit
Cheque - Assignors 4] into the form. It will be linked using the field
[ASSIGNORID]. Currently the form's data is as follows:
SELECT [Assignment Requests].ASSIGNMENTREQUESTID, [Assignment
Requests].ASSIGNORID, [Assignment Requests].AdvanceDate, [Assignment
Requests].CURRENCYID, [Assignment Requests].ExchangeRate, [Assignment
Requests].ChequeNumber, [Assignment Requests].AdvanceNotes, [Assignment
Requests].Posted, [Assignment Requests].QBExported, [Assignment
Requests].QBExportDate, [Assignment Requests].BatchID, [Assignment
Requests].StatusID, [Assignment Requests].AdvanceReconciled, [Assignment
Requests].BankStatementDate, Assignors.CompanyName,
Assignors.Advance_Percent, Currencies.Currency FROM Currencies INNER JOIN
(Assignors INNER JOIN [Assignment Requests] ON
Assignors.ASSIGNORID=[Assignment Requests].ASSIGNORID) ON
Currencies.CURRENCYID=[Assignment Requests].CURRENCYID;
Could you let me know how to insert the two fields from the two queries into
the two forms. Otherwise I'll have to start the forms fropm scratch using
the trusty (but clumsy) wizard!
Thanks again for the bail out!