Navigation Buttons Cause Error on Autofill Form

[Wayne]

I have a form linked to a master contracts table. I have setup the
form so that when the Contract_Number is selected from a combobox the
other contract fields autofill using DLookUp in the Control Source for
each textbox. Here’s an example:

=DLookUp("[Client_Name]","tblContracts","[ContractID] = " &
[Contract_Number])

This works great. The problem is when I use the Navigation button to
step to the next record I get the following message:

“The value you entered isn’t valid for this field”

I believe the error is due to the Contract_Number combobox since I am
using a query in the Row Source as follows:

SELECT tblContracts.ContractID, tblContracts.Contract_Number
FROM tblContracts
ORDER BY tblContracts.Contract_Number;

In the Contract_Number textbox I have set the following:
Column Count = 2
Column Width = 0”;1”
Bound Column = 1

I think when I use the Navigation buttons it trys to insert the
ContractID into the Contract_Number field which is a Text field. Not
sure how to fix this.

Is there a way to autofill plus be able to use the navigation buttons
to step through records?

 

[BruceM]

An easier way to display the value would be to have the combo box Row Source
query include the ClientName:

SELECT ContractID, Contract_Number, Client_Name
FROM tblContracts
ORDER BY Contract_Number;

Change the combo box Column Count to 3. In the text box Control Source:
=cboContractNum.Column(2)

Column numbering is 0-based, so Column(2) is the third column.

Another option may be to include the ClientName in the form's Record Source
query, depending on how your database is set up.

One advantage of this is that the query is more efficient than DMax, which
can become rather slow in some circumstances.

When you say "step to the next record" do you mean "go to a new record", or
do you mean when you navigate to any record? I can understand the error at
a new record, but unless I have missed something I don't see why you would
get the error in an existing record.

I have a form linked to a master contracts table. I have setup the
form so that when the Contract_Number is selected from a combobox the
other contract fields autofill using DLookUp in the Control Source for
each textbox. Here’s an example:

=DLookUp("[Client_Name]","tblContracts","[ContractID] = " &
[Contract_Number])

This works great. The problem is when I use the Navigation button to
step to the next record I get the following message:

“The value you entered isn’t valid for this field”

I believe the error is due to the Contract_Number combobox since I am
using a query in the Row Source as follows:

SELECT tblContracts.ContractID, tblContracts.Contract_Number
FROM tblContracts
ORDER BY tblContracts.Contract_Number;

In the Contract_Number textbox I have set the following:
Column Count = 2
Column Width = 0”;1”
Bound Column = 1

I think when I use the Navigation buttons it trys to insert the
ContractID into the Contract_Number field which is a Text field. Not
sure how to fix this.

Is there a way to autofill plus be able to use the navigation buttons
to step through records?

 

[Wayne]

An easier way to display the value would be to have the combo box Row Source
query include the ClientName:

SELECT ContractID, Contract_Number, Client_Name
FROM tblContracts
ORDER BY Contract_Number;

Change the combo box Column Count to 3.  In the text box Control Source:
=cboContractNum.Column(2)

Column numbering is 0-based, so Column(2) is the third column.

Another option may be to include the ClientName in the form's Record Source
query, depending on how your database is set up.

One advantage of this is that the query is more efficient than DMax, which
can become rather slow in some circumstances.

When you say "step to the next record" do you mean "go to a new record", or
do you mean when you navigate to any record?  I can understand the error at
a new record, but unless I have missed something I don't see why you would
get the error in an existing record.


I have a form linked to a master contracts table.  I have setup the
form so that when the Contract_Number is selected from a combobox the
other contract fields autofill using DLookUp in the Control Source for
each textbox.  Here’s an example:

=DLookUp("[Client_Name]","tblContracts","[ContractID] = " &
[Contract_Number])

This works great.  The problem is when I use the Navigation button to
step to the next record I get the following message:

“The value you entered isn’t valid for this field”

I believe the error is due to the Contract_Number combobox since I am
using a query in the Row Source as follows:

SELECT tblContracts.ContractID, tblContracts.Contract_Number
FROM tblContracts
ORDER BY tblContracts.Contract_Number;

In the Contract_Number textbox I have set the following:
Column Count = 2
Column Width = 0”;1”
Bound Column = 1

I think when I use the Navigation buttons it trys to insert the
ContractID into the Contract_Number field which is a Text field.  Not
sure how to fix this.

Is there a way to autofill plus be able to use the navigation buttons
to step through records?

Thanks Bruce. Originially I setup the autofill as you suggested
using:
"=cboContractNum.Column(X) " for all the columns I needed filled.
This autofill part worked OK but would OVERWRITE the existing record
in my Contracts table when I used the Navigation buttons to go to
another record. When I say navigate I mean, navigate to any record.
Ideally what I want is to autofill the form as if I had typed in the
Record Number which would be based on the Contract_Number. So I need
to get rid of the error above and also NEVER overwrite the Contracts
table data from this form. Possible?