Type Mismatch Error Locks Database

[Les Coover]

I am using the following code to insert First Name in an adjacent field
when a Last Name is selected from a Combo Box drop down list on
a form.

Everything works all right EXCEPT if the First Name field is sorted
(ascending) a "Type Mismatch" error is displayed, the DB locks up
and will not permit re-entry into the form. Any suggestions?

There is a good example of this on Northwind, when the Product ID field is
selected, the associated Unit Price is auto inserted. But why do I get
an error (and lockout) when the Last Name field is sorted. Perhaps I should
disable
the sort function for this field??? If so could someone explain how to do
that, or maybe you have better suggestions.

Thanks

Les

**********************************************************

Private Sub LName_AfterUpdate()
On Error GoTo Err_LName_AfterUpdate


Dim strVar As Variant

' Evaluate filter before it's passed to DLookup function.
strVar = "[ID01] = " & Me![LName]

' Look up student's first name and assign it to FName control.
Me!FName = DLookup("FName", "tblArchiveAdmissions2004", strVar)
Exit_LName_AfterUpdate:
Exit Sub

Err_LName_AfterUpdate:
MsgBox Err.Description
Resume Exit_LName_AfterUpdate


End Sub

**********************************************************

Here is what some of the property box for LName looks like

Name = LName
Control Source = LName
Row Source Type = Table/Query
Row Source:
SELECT [tbleArchiveAdmissions2004].[ID01],[tblArchiveAdmissions2004].[LName]
FROM tblArchiveAdmissions2004 ORDER BY [LName];

Column Count = 2
Column Width = 0";1"

***********************************************************

 

[Guest]

Check you bound column or referrence the column required, cboName.Column(Z) first column is 0 second column is 1 so if you want the second Z = 1 unless you have changed the Option Base.

ciao
KM

----- Les Coover wrote: -----

I am using the following code to insert First Name in an adjacent field
when a Last Name is selected from a Combo Box drop down list on
a form.

Everything works all right EXCEPT if the First Name field is sorted
(ascending) a "Type Mismatch" error is displayed, the DB locks up
and will not permit re-entry into the form. Any suggestions?

There is a good example of this on Northwind, when the Product ID field is
selected, the associated Unit Price is auto inserted. But why do I get
an error (and lockout) when the Last Name field is sorted. Perhaps I should
disable
the sort function for this field??? If so could someone explain how to do
that, or maybe you have better suggestions.

Thanks

Les

**********************************************************

Private Sub LName_AfterUpdate()
On Error GoTo Err_LName_AfterUpdate


Dim strVar As Variant

' Evaluate filter before it's passed to DLookup function.
strVar = "[ID01] = " & Me![LName]

' Look up student's first name and assign it to FName control.
Me!FName = DLookup("FName", "tblArchiveAdmissions2004", strVar)
Exit_LName_AfterUpdate:
Exit Sub

Err_LName_AfterUpdate:
MsgBox Err.Description
Resume Exit_LName_AfterUpdate


End Sub

**********************************************************

Here is what some of the property box for LName looks like

Name = LName
Control Source = LName
Row Source Type = Table/Query
Row Source:
SELECT [tbleArchiveAdmissions2004].[ID01],[tblArchiveAdmissions2004].[LName]
FROM tblArchiveAdmissions2004 ORDER BY [LName];

Column Count = 2
Column Width = 0";1"

***********************************************************

 

[Les Coover]

Kevin, thank you for your response.

I think I resolved the problem. Instead of using

Me!FName=Dlookup("[FName]","tblArchiveAdmissions2004","[ID01]= "&Me!LName)

I used

Me.FName=[Forms]![frmPostFacultyEval]![LName].Column(1)

I noticed that Bound Column = 1 in form properties refers to the first field
in a Query,
whereas in Visual Basic Column(0) refers to the first field in the same
Query.
Provided Option Base is in default mode.

Les

Check you bound column or referrence the column required,

cboName.Column(Z) first column is 0 second column is 1 so if you want the
second Z = 1 unless you have changed the Option Base.

ciao
KM

----- Les Coover wrote: -----

I am using the following code to insert First Name in an adjacent field
when a Last Name is selected from a Combo Box drop down list on
a form.

Everything works all right EXCEPT if the First Name field is sorted
(ascending) a "Type Mismatch" error is displayed, the DB locks up
and will not permit re-entry into the form. Any suggestions?

There is a good example of this on Northwind, when the Product ID field is
selected, the associated Unit Price is auto inserted. But why do I get
an error (and lockout) when the Last Name field is sorted. Perhaps I should
disable
the sort function for this field??? If so could someone explain how to do
that, or maybe you have better suggestions.

Thanks

Les

**********************************************************

Private Sub LName_AfterUpdate()
On Error GoTo Err_LName_AfterUpdate


Dim strVar As Variant

' Evaluate filter before it's passed to DLookup function.
strVar = "[ID01] = " & Me![LName]

' Look up student's first name and assign it to FName control.
Me!FName = DLookup("FName", "tblArchiveAdmissions2004", strVar)
Exit_LName_AfterUpdate:
Exit Sub

Err_LName_AfterUpdate:
MsgBox Err.Description
Resume Exit_LName_AfterUpdate


End Sub

**********************************************************

Here is what some of the property box for LName looks like

Name = LName
Control Source = LName
Row Source Type = Table/Query
Row Source:
SELECT [tbleArchiveAdmissions2004].[ID01],[tblArchiveAdmissions2004].[LName]
FROM tblArchiveAdmissions2004 ORDER BY [LName];

Column Count = 2
Column Width = 0";1"

***********************************************************