Opening Forms with Textboxes

[Jenny at APOC]

I have a form (SearchRetains) that opens based on certain criteria from the
form (AddRetains) and then the SearchRetains form could open based on certain
criteria in the AddRetains form. I think I have messed up....When I type in
the Textbox a check is performed and, in both cases each of the forms will
open as specified, but when I open the AddRetains from the SearchRetains the
Textbox is highlighted with the correct info passed, but does not run the
required code until I press back space and enter the last digit again in the
Textbox. However, if I open the SearchRetains from the AddRetains it works
fine and runs....The only think I can think of is that I am hainvg them
depend on each other instead of transeferring the informtaion...Any
suggestions?

'SearchRetains Code:
Private Sub LotNumberTextBox_Change()
Dim length$
Dim SqlSmt$, project$, box$

' waiting for textbox to have 16 characters
length$ = Len(CStr(LotNumberTextBox.Text))
If length$ < 16 Then
Exit Sub
End If
' end of waiting for textbox to have 16 characters


'check to see if lot number is exists in dbo_lnaLotNbrDetailALL
SqlSmt$ = "Select PartNbr from dbo_lnaLotNbrDetailALL as lotcheck where
LotNbr = '" + LotNumberTextBox.Text + "'"
TempDataTable.RowSource = SqlSmt$
TempDataTable.Requery
Me!TempDataTable.Selected(0) = True
If IsNull(TempDataTable.Column(0, TempDataTable.ListIndex)) Then
If (MsgBox("Lot Number Does Not Exist", vbOKOnly, "Action Aborted!")
= vbOK) Then
End If
LotNumberTextBox.Value = Null
Exit Sub
End If
'end of checking to see if lot number is valid

'If lot exists in Maintable then we want to open the search page
SQLStmt = "SELECT LotNbr from MainTable as lotcheck where LotNbr = '" +
LotNumberTextBox.Text + "'"
TempDataTable.RowSource = SQLStmt
TempDataTable.Requery
Me!TempDataTable.Selected(0) = True
If Not IsNull(TempDataTable.Column(0, TempDataTable.ListIndex)) Then
If (MsgBox("This Lot Number has already been entered. Click to open
Search Page", vbOKOnly, "Lot is already in System") = vbOK) Then

'Open Search form
DoCmd.OpenForm ("Search Retains")
[Forms]![Search Retains]![LotNumberTextBox] =
LotNumberTextBox.Text

End If
LotNumberTextBox.Value = Null
Exit Sub
End If

Private Sub LotNumberTextBox_Change()
Dim length$
Dim SqlSmt$, project$, box$

' waiting for textbox to have 16 characters
length$ = Len(CStr(LotNumberTextBox.Text))
If length$ < 16 Then
Exit Sub
End If
' end of waiting for textbox to have 16 characters

'If lot exists in Maintable then we want to be able to choose a search
SQLStmt = "SELECT LotNbr from MainTable as lotcheck where LotNbr = '" +
LotNumberTextBox.Text + "'"
TempDataTable.RowSource = SQLStmt
TempDataTable.Requery
Me!TempDataTable.Selected(0) = False
If IsNull(TempDataTable.Column(0, TempDataTable.ListIndex)) Then
'If lot does not exist
MsgBox "This lot does not exist in this database", vbInformation, "Lot
Non-Existant"
DoCmd.OpenForm ("Add Retains")
[Forms]![Add Retains]![LotNumberTextBox] = LotNumberTextBox.Text

End If
LatestTrans.Requery
'LotNumberTextBox.Value = Null
Exit Sub

End Sub

'AddRetains Code: