Auto Values for Text Boxes

[JamesDC]

Hey,

Thanks for trying to help by the way,

I used the code with the error check and there are no debugging issues. When I click on that text box, however, an error comes up as follows:

17 Type mismatch

Also what is the H in:
strNme = rs!H

Changing this to Employee_Name still causes the same error to come up.

EDIT: I just noticed that the variables are set as Strings, the Employee name field is set to Text and all entries are numeric. I don't think this would affect the String, but I'm just stating it just in case.

[JamesDC]

Hey,
17 Type mismatch

Sorry it's actually:
13 Type mismatch

[Denburt]

rs!H
oops sorry that should read:

rs!Employee_Nam e

Also you said text box but if it is a drop down box or list box they could be using number (you stated they were) so you would simply change the data type from strings to a numeric type such as a long, this will resolve the type mismatch error.

Dim LngValue as long
strNme = rs!Employee_Nam e
Me!Employee_Nam e = LngValue

[JamesDC]

The text box for Employee Name is a regular text box. Other areas are dropdown lists but I haven't tried this code there yet. Here is what I have under Employee Name On Enter Event:

Private Sub Employee_Name_Enter()

On Error GoTo Err_Employee_Name_Enter
Dim rs As Recordset
Dim strCriteria As String
Dim strNme As String
Dim LngValue As Long

If Me.NewRecord Then
    Set rs = Me.RecordsetClone

    If Not rs.EOF Then
        rs.MoveLast
        strNme = rs!Employee_Name
        Me!Employee_Name = LngValue
    End If

    rs.Close
    Set rs = Nothing
End If

Exit_Employee_Name_Enter:
Exit Sub

Err_Employee_Name_Enter:
'Change the message box to something usefull to the user when you are done testing
MsgBox Err.Number & " " & Err.Description
Resume Exit_Employee_Name_Enter

End Sub

I'm still getting the type mismatch error.

In the MainDB, the Employee Name field has Text as it's datatype. I think this would save the numerical values (ie. Employee numbers) put in for Employee Name as Strings...

[Denburt]

Still waking up haven't finished my first cup of mud yet but working on it.

My code in the previos post should have read:

Dim LngValue as long
LngValue = rs!Employee_Nam e
Me!Employee_Nam e = LngValue



Complete code:

Private Sub Employee_Name_E nter()
On Error GoTo Err_Employee_Na me_Enter
Dim rs As Recordset
Dim LngValue As Long

If Me.NewRecord Then
Set rs = Me.RecordsetClo ne

If Not rs.EOF Then
rs.MoveLast
LngValue = rs!Employee_Nam e
Me!Employee_Nam e = LngValue
End If

rs.Close
Set rs = Nothing
End If

Exit_Employee_N ame_Enter:
Exit Sub

Err_Employee_Na me_Enter:
'Change the message box to something usefull to the user when you are done testing
MsgBox Err.Number & " " & Err.Description
Resume Exit_Employee_N ame_Enter

End Sub



If this still causes an issue.... let me know.

[JamesDC]

Still getting the type mismatch error :(

[Denburt]

I can only say that you need to know what type the source is so you can resolve this final issue. Looking at the properties of the control in question you can see the controlsource that name will appear in the forms RecordSource the RecordSource might be a table or query if a query open it then look to find the table that is associated with that controlsource name and view the table properties for that controlsource to see what type you are dealing with.

the only thing you need to change in the code I provided is the datatype

Dim LngValue as long

or

Dim LngValue as string

That really should do it if you need help finding the datatype i'll be here.

[JamesDC]

On the Employee Name text box on the form, under control source is Employee Name.

Employee Name is a Field heading in the table MainDB.

In MainDB the Field Employee Name is Data Type Text with a Display Control of Text Box.

I'm not really sure where RecordSource comes into play. Is this the information you're looking for?

[Denburt]

So did you try the

Dim LngValue as string

If you still get the Type mismatch error then comment out this line:
(Simply add a hyphen ' )

'On Error GoTo Err_Employee_Na me_Enter

Then when the error appears press the control key and hold it then hit the break key and paste in the line that is highlighted.
It will probably be the following line:

strNme = rs!Employee_Nam e

That is telling us we dont have the correct variable type.

Not the best solution but try this:

Me("Employee_Na me") = rs!Employee_Nam e

[JamesDC]

I tried the code with String and got the same error.

tracing the error ended with this line highlighted:
Set rs = Me.RecordsetClo ne

[Denburt]

OK in the VBA window Click tools, references look at the top three boxes checked off and they should be

1. Visual Basic for Applications
2. Microsoft Access 11.0 Object Library
3. DAO 3.6 (or another version) Object Library

If not then you need to correct this if this is correct then I would have to say that the DAO reference type has an issue such as a corrupted file or something. let me know.

[JamesDC]

I have the following boxes checked:

1. Visual Basic for Applications
2. Microsoft Access 10.0 Object Library
3. OLE Automation
4. Microsoft Active X Data Objects 2.1 Library
5. Microsoft Calender Control 8.0

I cannot find any DAO references. Also it appears that you are using a newer verion of the Access object library, does 11.0 introduce the item in the code causing the error?

[Denburt]

Yep, check the list it's in there once you click the check box on it move it up till it won't go no more (usually sits in third place).

You will be looking for:

Microsoft DAO

This will resolve your problem.

[JamesDC]

Found the DAO 3.6 Library and added it and moved it up,

Now I get this error:
3265: Item Not Found In This Collection

With this highlighted when error is traced:
LngValue = rs!Employee_Nam e

Happens when LngValue is set as String or Long.

I tried to use
Me("Employee_Na me") = rs!Employee_Nam e
instead but it still comes up with an error.

[Denburt]

Yeah well I was trying to guess the field name.

rs!Employee_Nam e

is the culprit What is the name of the field in the underlying table or query would it be "Employee Name" perhaps no underscore have a look your almost there.

rs![Employee Name]