By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Auto Values for Text Boxes

JamesDC
P: 66
Hey all,

I'm working with Access 2002. I'm working on an inherited access file and I'm trying to figure out how the text boxes work in order to try and fix a bug which occurs.

The situation is as follows:
Users have access to a Form which allows them to submit records. The records are created from the information put into the text boxes on this form when a Save button is clicked. When the New Record button is clicked the records are set to blank.

Currently, when one clicks on a text box it comes up with the data from the last entry. So if John put into the Name Text Box his name, when the next entry is created, click on the Name Text Box will bring up John.

The text box has nothing under it's default value, so I don't even know why this is happeneing.

Occasionally while working with the program users will have the text boxes cease to automatically call up the last entry, causing an increase in manual work. (It is the function of the program that someone with the same Name make several entries before someone new takes over and thus a new Name must be entered).

Does anyone know why this is occuring? Or why the text boxes are bringing up the last entries data?

Thanks in advance,
James

I found this code in the "On Click" command:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Employee_Name_Click()
  3. SendKeys "+^'", True
  4. End Sub
  5.  
Mar 14 '07 #1
Share this Question
Share on Google+
33 Replies


Rabbit
Expert Mod 10K+
P: 12,364
There's most likely VBA code. Check the event properties of the text boxes and the visual basic editor for code.
Mar 14 '07 #2

JamesDC
P: 66
See edit at end of last post. I don't know what the code means.
Mar 14 '07 #3

Rabbit
Expert Mod 10K+
P: 12,364
That sends Control+Shift+' when they click the textbox.

I was testing it and it seems to insert the value of the record before it.
Mar 14 '07 #4

JamesDC
P: 66
That sends Control+Shift+' when they click the textbox.

I was testing it and it seems to insert the value of the record before it.
So any idea on why it would stop working randomly?

I'm looking for other methods to complete this task. Is there a way to use the Defualt Value property to show the last entries data when a new Form is loaded?
Mar 14 '07 #5

Rabbit
Expert Mod 10K+
P: 12,364
It only triggers if they click on the field, not if they tab into it or use some other method to get into the field. And obviously it won't work if you're on the first record or there are no records in there.
Mar 14 '07 #6

Rabbit
Expert Mod 10K+
P: 12,364
So any idea on why it would stop working randomly?

I'm looking for other methods to complete this task. Is there a way to use the Defualt Value property to show the last entries data when a new Form is loaded?
What you want to do can be done in several ways, it depends on what you mean last entries data and what you mean by new Form is loaded.
Mar 14 '07 #7

JamesDC
P: 66
On the form one can clikc forward and back buttons to view the entries. Eahc entry is given an AutoNumber. When the New Entry button is clicked it clears all the Text Boxes and starts a new record with a new AutoNumber. I would want for the Name Text Box for it to automatically bring up the Name in the Name Text Box of entry n-1 when the New Entry button is clicked and a new record is created.
Mar 14 '07 #8

Rabbit
Expert Mod 10K+
P: 12,364
This should be happening as long as the user clicks into the field. If you want it to happen whenever they enter the field, try putting the code in the On Focus event.
Mar 14 '07 #9

JamesDC
P: 66
This should be happening as long as the user clicks into the field. If you want it to happen whenever they enter the field, try putting the code in the On Focus event.
Alright, I'm going to try putting the code under On Got Focus to see if that stops the problems.

That bit of code was under On Click and On Enter, and it still worked if someone tabbed accross. Just sometimes when clicking it would stop working...
Mar 15 '07 #10

JamesDC
P: 66
In an attempt to solve the problem I changed the location of the code to the On Got Focus event property. After having the program used for a few hours the same problem would show up again. Here is the complete code for the data entry form:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Area_GotFocus()
  4. SendKeys "+^'", True
  5. End Sub
  6.  
  7. Private Sub Date_GotFocus()
  8.     If IsNull(Me!Date) Then Me!Date = DateValue(DateAdd("h", -7, Now()))
  9. End Sub
  10.  
  11. Private Sub Employee_Name_GotFocus()
  12. SendKeys "+^'", True
  13. End Sub
  14.  
  15. Private Sub GoBackMainMenu_Click()
  16. On Error GoTo Err_GoBackMainMenu_Click
  17.  
  18.     DoCmd.Close
  19.  
  20.     Dim stDocName As String
  21.     Dim stLinkCriteria As String
  22.  
  23.     stDocName = "Main Menu"
  24.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  25.  
  26. Exit_GoBackMainMenu_Click:
  27.     Exit Sub
  28.  
  29. Err_GoBackMainMenu_Click:
  30.     MsgBox Err.Description
  31.     Resume Exit_GoBackMainMenu_Click
  32.  
  33. End Sub
  34. Private Sub GoToFirst_Click()
  35. On Error GoTo Err_GoToFirst_Click
  36.  
  37.  
  38.     DoCmd.GoToRecord , , acFirst
  39.  
  40. Exit_GoToFirst_Click:
  41.     Exit Sub
  42.  
  43. Err_GoToFirst_Click:
  44.     MsgBox Err.Description
  45.     Resume Exit_GoToFirst_Click
  46.  
  47. End Sub
  48. Private Sub GoToPrev_Click()
  49. On Error GoTo Err_GoToPrev_Click
  50.  
  51.  
  52.     DoCmd.GoToRecord , , acPrevious
  53.  
  54. Exit_GoToPrev_Click:
  55.     Exit Sub
  56.  
  57. Err_GoToPrev_Click:
  58.  
  59.     Resume Exit_GoToPrev_Click
  60.  
  61. End Sub
  62. Private Sub GoToNext_Click()
  63. On Error GoTo Err_GoToNext_Click
  64.  
  65.  
  66.     DoCmd.GoToRecord , , acNext
  67.  
  68. Exit_GoToNext_Click:
  69.     Exit Sub
  70.  
  71. Err_GoToNext_Click:
  72.  
  73.     Resume Exit_GoToNext_Click
  74.  
  75. End Sub
  76. Private Sub GoToLast_Click()
  77. On Error GoTo Err_GoToLast_Click
  78.  
  79.  
  80.     DoCmd.GoToRecord , , acLast
  81.  
  82. Exit_GoToLast_Click:
  83.     Exit Sub
  84.  
  85. Err_GoToLast_Click:
  86.     MsgBox Err.Description
  87.     Resume Exit_GoToLast_Click
  88.  
  89. End Sub
  90. Private Sub NewRecord_Click()
  91. On Error GoTo Err_NewRecord_Click
  92.  
  93.  
  94.     DoCmd.GoToRecord , , acNewRec
  95.  
  96.  
  97. Exit_NewRecord_Click:
  98.     Exit Sub
  99.  
  100. Err_NewRecord_Click:
  101.     MsgBox Err.Description
  102.     Resume Exit_NewRecord_Click
  103.  
  104. End Sub
  105. Private Sub FindRecord_Click()
  106. On Error GoTo Err_FindRecord_Click
  107.  
  108.  
  109.     Screen.PreviousControl.SetFocus
  110.     DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  111.  
  112. Exit_FindRecord_Click:
  113.     Exit Sub
  114.  
  115. Err_FindRecord_Click:
  116.     MsgBox Err.Description
  117.     Resume Exit_FindRecord_Click
  118.  
  119. End Sub
  120.  
  121.  
  122.  
  123.  
  124.  
  125. Private Sub Product_GotFocus()
  126. SendKeys "+^'", True
  127. End Sub
  128.  
  129. Private Sub Save_Click()
  130. On Error GoTo Err_Save_Click
  131.  
  132.  
  133.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  134.  
  135. Exit_Save_Click:
  136.     Exit Sub
  137.  
  138. Err_Save_Click:
  139.     MsgBox "Record Already Saved!"
  140.     Resume Exit_Save_Click
  141.  
  142. End Sub
  143. Private Sub Command35_Click()
  144. On Error GoTo Err_Command35_Click
  145.  
  146.  
  147.     Screen.PreviousControl.SetFocus
  148.     DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
  149.  
  150. Exit_Command35_Click:
  151.     Exit Sub
  152.  
  153. Err_Command35_Click:
  154.     MsgBox Err.Description
  155.     Resume Exit_Command35_Click
  156.  
  157. End Sub
  158.  
  159. Private Sub GoToShift_Click()
  160. On Error GoTo Err_GoToShift_Click
  161.  
  162.     DoCmd.Close
  163.  
  164.     Dim stDocName As String
  165.     Dim stLinkCriteria As String
  166.  
  167.     stDocName = "ShiftSelect"
  168.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  169.  
  170. Exit_GoToShift_Click:
  171.     Exit Sub
  172.  
  173. Err_GoToShift_Click:
  174.     MsgBox Err.Description
  175.     Resume Exit_GoToShift_Click
  176.  
  177. End Sub
  178.  
  179. Private Sub Shift_GotFocus()
  180. SendKeys "+^'", True
  181. End Sub
  182.  
This contains the code for the navigation buttons on the page as well as the code for the text boxes. It seems that the Employee Name text box continues to work but the Shift Text Box and Product text boxes stop working with respect to calling up the last entry. These Text Boxes are bound lists, could that be a cause of problems?

Also the code for Command35 and FindRecord are the same... Command35 is used by the search button where FindRecord is not used at all, I suspect it's just forgotten code by the author.

Thanks for any help in advance
Mar 15 '07 #11

Rabbit
Expert Mod 10K+
P: 12,364
I don't see anything glaringly wrong with the code. As fas as I can tell it should work. Let's see if someone else will be able to answer it.
Mar 15 '07 #12

Denburt
Expert 100+
P: 1,356
Sendkeys are sketchy to say the least. First make sure you make a backup copy of your database then try using the following code in the on enter event it should work.

Dim rs As dao.Recordset
Dim strCriteria As String
Dim strNme As String
Set rs = Me.RecordsetClone
If Not rs.EOF Then
rs.MoveLast
rs.Bookmark = Me.Bookmark
rs.MovePrevious
strNme = rs!myName

Me!myName = strNme

End If
rs.Close
Set rs = Nothing


The following line "Me!myName" in the preceeding code should be changed to you field name that you want the persons name entered into. Such as
Me!YOURFIELDNAME also change rs!myName to the field name in the table (controlsource of the text box).
Mar 15 '07 #13

JamesDC
P: 66
Hey Denburt,

I tried your code but I got this error:

Compile Error: Use-defined type not defined

It then grey highlights:
rs As dao.Recordset

And yellow highlights:
Private Sub Employee_Name_Enter()

Both my field name and text box are called "Employee Name", so for the !myName's I replaced them both with ![Employee Name]

Know why this is happening?
Mar 16 '07 #14

Denburt
Expert 100+
P: 1,356
O.K. I didnt provide error trapping wich should be done but the compile error sounds like a reference is missing or corrupted also I did use code off the cuff.
I just created a new db, new table, form and text boxs.
The following is tried and tested let me know if you have any issues, we may need to look at some other things in your db though.

Private Sub Employee_Name_Enter()
On Error GoTo Err_Employee_Name_Enter
Dim rs As Recordset
Dim strCriteria As String
Dim strNme As String
If Me.NewRecord Then
Set rs = Me.RecordsetClone
If Not rs.EOF Then
rs.MoveLast

strNme = rs!H
Me!Employee_Name = strNme
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
Mar 16 '07 #15

JamesDC
P: 66
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.
Mar 16 '07 #16

JamesDC
P: 66
Hey,
17 Type mismatch
Sorry it's actually:
13 Type mismatch
Mar 16 '07 #17

Denburt
Expert 100+
P: 1,356
rs!H
oops sorry that should read:

rs!Employee_Name

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_Name
Me!Employee_Name = LngValue
Mar 16 '07 #18

JamesDC
P: 66
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Employee_Name_Enter()
  2.  
  3. On Error GoTo Err_Employee_Name_Enter
  4. Dim rs As Recordset
  5. Dim strCriteria As String
  6. Dim strNme As String
  7. Dim LngValue As Long
  8.  
  9. If Me.NewRecord Then
  10.     Set rs = Me.RecordsetClone
  11.  
  12.     If Not rs.EOF Then
  13.         rs.MoveLast
  14.         strNme = rs!Employee_Name
  15.         Me!Employee_Name = LngValue
  16.     End If
  17.  
  18.     rs.Close
  19.     Set rs = Nothing
  20. End If
  21.  
  22. Exit_Employee_Name_Enter:
  23. Exit Sub
  24.  
  25. Err_Employee_Name_Enter:
  26. 'Change the message box to something usefull to the user when you are done testing
  27. MsgBox Err.Number & " " & Err.Description
  28. Resume Exit_Employee_Name_Enter
  29.  
  30. End Sub
  31.  
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...
Mar 16 '07 #19

Denburt
Expert 100+
P: 1,356
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_Name
Me!Employee_Name = LngValue



Complete code:

Private Sub Employee_Name_Enter()
On Error GoTo Err_Employee_Name_Enter
Dim rs As Recordset
Dim LngValue As Long

If Me.NewRecord Then
Set rs = Me.RecordsetClone

If Not rs.EOF Then
rs.MoveLast
LngValue = 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



If this still causes an issue.... let me know.
Mar 16 '07 #20

JamesDC
P: 66
Still getting the type mismatch error :(
Mar 16 '07 #21

Denburt
Expert 100+
P: 1,356
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.
Mar 16 '07 #22

JamesDC
P: 66
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?
Mar 16 '07 #23

Denburt
Expert 100+
P: 1,356
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_Name_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_Name

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

Not the best solution but try this:

Me("Employee_Name") = rs!Employee_Name
Mar 16 '07 #24

JamesDC
P: 66
I tried the code with String and got the same error.

tracing the error ended with this line highlighted:
Set rs = Me.RecordsetClone
Mar 16 '07 #25

Denburt
Expert 100+
P: 1,356
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.
Mar 16 '07 #26

JamesDC
P: 66
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?
Mar 16 '07 #27

Denburt
Expert 100+
P: 1,356
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.
Mar 16 '07 #28

JamesDC
P: 66
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_Name

Happens when LngValue is set as String or Long.

I tried to use
Me("Employee_Name") = rs!Employee_Name
instead but it still comes up with an error.
Mar 16 '07 #29

Denburt
Expert 100+
P: 1,356
Yeah well I was trying to guess the field name.

rs!Employee_Name

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]
Mar 16 '07 #30

JamesDC
P: 66
Aha! I tried the same thing with the suqare brackets and it worked! It called up the last entry.

I'm going to copy this code for the rest of my fields, hopefully I can figure those parts out now. Then I'll put it on as the program used and see if the not bringing up last data bug occurs again.

Thank you so much for your help :)

I'll let you know how it turns out
Mar 16 '07 #31

Denburt
Expert 100+
P: 1,356
Great I will be anxiously waiting glad to have helped.
Mar 16 '07 #32

JamesDC
P: 66
Hey Denburt,

The program was used over the weekend and all of this morning so far without the issue arising. It looks like that little problem has been solved.

I've replaced the sendkeys code for all the other areas with the same code and it works like a charm :)

Thank you so much for your help,

James
Mar 19 '07 #33

Denburt
Expert 100+
P: 1,356
Very nice to know glad I was able to be of assistance. :)
Mar 19 '07 #34

Post your reply

Sign in to post your reply or Sign up for a free account.