Problem with form being automatically populated

[DoctorJDM]

Hi

This should be trivial but is causing a problem.

Have an order form fed by a query based on two tables. The Customer field in one table has a lookup to a Customers table, with the Customer field as key.

This allows the form to pull in the Customer's address when any Customer is selected.

If a new order is aborted before the Customer is selected there's an error saying Access can't find a record in tblCustomers corresponding to the selected Customer. This is a nuisance because it then insists on abandoning the new record and closing the form.

The error also appears in aborting the query, ie without opening the form.

 

[TheAceMan1]

How are ya DoctorJDM . . .

[blue] . . . If a new order is aborted before the Customer is selected there's an error saying [purple]Access can't find a record in tblCustomers[/purple] . . .[/blue]

When you abort, your code apparently uses [blue]Customer[/blue] wether a selection was made or not and your searching for a [blue]null[/blue] or [blue]empty string![/blue]

In your code you need to bypass customer if none selected:

[blue]   If Trim(Me![[purple][b]CustomerFieldName][/b][/purple]) & "" <> "" Then
      [green]'Process Code[/green]
   End If[/blue]

If your still having problems, post the abort code! . . .

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[DoctorJDM]

Hiya AceMan - and thanks for responding, as ever. Feel in safe hands now.

There isn't any error code, just this
The Microsoft Jet engine cannot find a record in the table 'tblCustomers' with matching field(s) 'Customer'

It originates in the underlying query if starting a new entry and then aborting it, and I can't catch it in the form. As I said, the query has one table containing a Customer field with look-up to a second table tblCustomers containing Customer as primary key. In the query the join properties are the top option, ie normal join.

 

[TheAceMan1]

DoctorJDM . . .

Sorry to get back so late. Post the SQL of the query! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[DoctorJDM]

AceMan

Here goes

SELECT tblJobs.Status, tblJobs.QuoteRef, tblJobs.Customer, tblJobs.CustomerContact, tblJobs.EnquiryReceivedDate, tblJobs.EnquiryReceivedBy, tblJobs.Description, tblJobs.SiteVisitDate, tblJobs.EstCompletionWeeks, tblJobs.MaterialsFactor, tblJobs.SubconFactor, tblJobs.OverallFactor, tblJobs.QuoteSentDate, tblJobs.DepositReceivedDate, tblJobs.HoursFactor, tblJobs.MaterialsCostsSubtotal, tblJobs.HoursCostsSubtotal, tblJobs.SubconCostsSubtotal, tblJobs.TotalValue, tblJobs.JobRef, tblJobs.QuoteHourRate, tblJobs.QuoteTotalMaterialCosts, tblJobs.QuoteTotalHoursCosts, tblJobs.QuoteTotalSubconCosts, tblJobs.QuoteTimberCosts, tblJobs.QuotePlywoodCosts, tblJobs.QuoteComponentCosts, tblJobs.QuoteOtherItemCosts, tblJobs.QuoteSubconCosts, tblJobs.QuoteJobHours, tblJobs.QuoteTravelHours, tblJobs.QuoteOtherHours, Address([Address1],[Address2],[Town],[County],[Postcode]) AS Address, tblJobs.QuoteTotalValue, tblCustomers.Address1, tblCustomers.Address2, tblCustomers.Town, tblCustomers.County, tblCustomers.Postcode, tblCustomers.Tel, tblCustomers.Fax, tblCustomers.Email
FROM tblCustomers INNER JOIN tblJobs ON tblCustomers.Customer = tblJobs.Customer;

 

[DoctorJDM]

Seem to have to change to LEFT JOIN to cure this. Are there likely to be any consequences?