Type Mismatch Error Locks Database

  • Thread starter Thread starter Les Coover
  • Start date Start date
L

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"

***********************************************************
 
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"

***********************************************************
 
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


Kevin McCartney said:
Check you bound column or referrence the column required,
Click to expand...
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"

***********************************************************
Click to expand...
 
Back
Top