Auto Values for Text Boxes

[JamesDC]

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:

 
Private Sub Employee_Name_Click()
SendKeys "+^'", True
End Sub

[Rabbit]

There's most likely VBA code. Check the event properties of the text boxes and the visual basic editor for code.

[JamesDC]

See edit at end of last post. I don't know what the code means.

[Rabbit]

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.

[JamesDC]

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?

[Rabbit]

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.

[Rabbit]

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.

[JamesDC]

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.

[Rabbit]

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.

[JamesDC]

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

[JamesDC]

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:

Option Compare Database

Private Sub Area_GotFocus()
SendKeys "+^'", True
End Sub

Private Sub Date_GotFocus()
    If IsNull(Me!Date) Then Me!Date = DateValue(DateAdd("h", -7, Now()))
End Sub

Private Sub Employee_Name_GotFocus()
SendKeys "+^'", True
End Sub

Private Sub GoBackMainMenu_Click()
On Error GoTo Err_GoBackMainMenu_Click

    DoCmd.Close

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Main Menu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_GoBackMainMenu_Click:
    Exit Sub

Err_GoBackMainMenu_Click:
    MsgBox Err.Description
    Resume Exit_GoBackMainMenu_Click
    
End Sub
Private Sub GoToFirst_Click()
On Error GoTo Err_GoToFirst_Click


    DoCmd.GoToRecord , , acFirst

Exit_GoToFirst_Click:
    Exit Sub

Err_GoToFirst_Click:
    MsgBox Err.Description
    Resume Exit_GoToFirst_Click
    
End Sub
Private Sub GoToPrev_Click()
On Error GoTo Err_GoToPrev_Click


    DoCmd.GoToRecord , , acPrevious

Exit_GoToPrev_Click:
    Exit Sub

Err_GoToPrev_Click:
    
    Resume Exit_GoToPrev_Click
    
End Sub
Private Sub GoToNext_Click()
On Error GoTo Err_GoToNext_Click


    DoCmd.GoToRecord , , acNext

Exit_GoToNext_Click:
    Exit Sub

Err_GoToNext_Click:
  
    Resume Exit_GoToNext_Click
    
End Sub
Private Sub GoToLast_Click()
On Error GoTo Err_GoToLast_Click


    DoCmd.GoToRecord , , acLast

Exit_GoToLast_Click:
    Exit Sub

Err_GoToLast_Click:
    MsgBox Err.Description
    Resume Exit_GoToLast_Click
    
End Sub
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click


    DoCmd.GoToRecord , , acNewRec


Exit_NewRecord_Click:
    Exit Sub

Err_NewRecord_Click:
    MsgBox Err.Description
    Resume Exit_NewRecord_Click
    
End Sub
Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindRecord_Click:
    Exit Sub

Err_FindRecord_Click:
    MsgBox Err.Description
    Resume Exit_FindRecord_Click
    
End Sub





Private Sub Product_GotFocus()
SendKeys "+^'", True
End Sub

Private Sub Save_Click()
On Error GoTo Err_Save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox "Record Already Saved!"
    Resume Exit_Save_Click
    
End Sub
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command35_Click:
    Exit Sub

Err_Command35_Click:
    MsgBox Err.Description
    Resume Exit_Command35_Click
    
End Sub

Private Sub GoToShift_Click()
On Error GoTo Err_GoToShift_Click

    DoCmd.Close

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "ShiftSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_GoToShift_Click:
    Exit Sub

Err_GoToShift_Click:
    MsgBox Err.Description
    Resume Exit_GoToShift_Click
    
End Sub

Private Sub Shift_GotFocus()
SendKeys "+^'", True
End Sub

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

[Rabbit]

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.

[Denburt]

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.RecordsetClo ne
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!YOURFIELDNAM E also change rs!myName to the field name in the table (controlsource of the text box).

[JamesDC]

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_E nter()

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?

[Denburt]

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_E nter()
On Error GoTo Err_Employee_Na me_Enter
Dim rs As Recordset
Dim strCriteria As String
Dim strNme As String
If Me.NewRecord Then
Set rs = Me.RecordsetClo ne
If Not rs.EOF Then
rs.MoveLast

strNme = rs!H
Me!Employee_Nam e = strNme
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